程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> 關於C# >> EXCEL數據導入數據庫實現代碼

EXCEL數據導入數據庫實現代碼

編輯：關於C#

1、類設計，EXCEL要據配置讀入DATASET
using System;
using System.Data;
using System.Collections;
using System.Data.OleDb;

namespace HKH.Common
{
/// <summary>
/// Excel 表格中列標頭與列索引的對應轉換
/// </summary>
/// <remarks>Create By Liwt on 2006 - 09 - 15
/// </remarks>
enum EnumExcelColumn
{
A = 0,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,
AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ,
BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,
CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ,
DA,DB,DC,DD,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ,
EA,EB,EC,ED,EE,EF,EG,EH,EI,EJ,EK,EL,EM,EN,EO,EP,EQ,ER,ES,ET,EU,EV,EW,EX,EY,EZ,
FA,FB,FC,FD,FE,FF,FG,FH,FI,FJ,FK,FL,FM,FN,FO,FP,FQ,FR,FS,FT,FU,FV,FW,FX,FY,FZ,
GA,GB,GC,GD,GE,GF,GG,GH,GI,GJ,GK,GL,GM,GN,GO,GP,GQ,GR,GS,GT,GU,GV,GW,GX,GY,GZ,
HA,HB,HC,HD,HE,HF,HG,HH,HI,HJ,HK,HL,HM,HN,HO,HP,HQ,HR,HS,HT,HU,HV,HW,HX,HY,HZ,
IA,IB,IC,ID,IE,IF,IG,IH,II,IJ,IK,IL,IM,IN,IO,IP,IQ,IR,IS,IT,IU,IV
}

/// <summary>
/// 從Excel導入數據到DataSet，帶有虛函數的基類
/// </summary>
/// <remarks>Create By Liwt on 2006 - 09 - 15
/// </remarks>
public class clsImportExcel
{
#region 變量

protected String m_MappingFile; //映射配置文件路徑
protected String m_ExcelSheetName; //Excel中要導入數據的表名
protected String m_SqlTableName; //要導入的Sql表名，也可為其它類型的，如Oracle
protected ArrayList[] m_ColumnMapping; //列映射配置列表，包括3部分 0--Sql列名，1--Excel列索引
//2-- 如當前Excel行為空，是否賦值為上一行的值
private bool isLoadMapping;

#endregion

#region 構造函數

/// <summary>
/// 無參構造
/// </summary>
public clsImportExcel()
{
m_MappingFile = "";
m_ExcelSheetName = "";
isLoadMapping = false;
m_ColumnMapping = new ArrayList[3];

m_ColumnMapping[0] = new ArrayList();
m_ColumnMapping[1] = new ArrayList();
m_ColumnMapping[2] = new ArrayList();
}

/// <summary>
/// 構造函數重載
/// </summary>
/// <param name="mappingFilePath">映射配置文件路徑</param>
/// <param name="excelSheetName">Excel中要導入數據的表名</param>
public clsImportExcel(String mappingFilePath, String excelSheetName)
{
m_MappingFile = mappingFilePath;
m_ExcelSheetName = excelSheetName;
isLoadMapping = false;
m_ColumnMapping = new ArrayList[3];

m_ColumnMapping[0] = new ArrayList();
m_ColumnMapping[1] = new ArrayList();
m_ColumnMapping[2] = new ArrayList();
}

#endregion

#region 屬性

/// <summary>
/// 讀取或設置映射配置文件路徑
/// </summary>
public String MappingFilePath
{
get
{
return m_MappingFile;
}
set
{
m_MappingFile = value;
isLoadMapping = false;
}
}

/// <summary>
/// 讀取或設置 Excel中要導入數據的表名
/// </summary>
public String ExcelSheetName
{
get
{
return m_ExcelSheetName;
}
set
{
m_ExcelSheetName = value;
isLoadMapping = false;
}
}

#endregion

#region 公共方法

/// <summary>
/// 導入數據
/// </summary>
/// <param name="excelFilePath">要導入的Excel文件路徑</param>
/// <param name="dsTarget">目標DataSet</param>
/// <returns>ture -- 成功, false -- 失敗
/// </returns>
public bool Import(String excelFilePath,ref DataSet dsTarget)
{
try
{
if (!isLoadMapping)
{
if (!LoadMapping())
{
return false;
}
}

//利用Ole讀取Excel數據
OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + excelFilePath + ";");
OleDbDataAdapter oleDA = new OleDbDataAdapter("SELECT * FROM [" + m_ExcelSheetName + "$]",oleConn);

DataSet dsExcel = new DataSet();
oleDA.Fill(dsExcel,m_ExcelSheetName);

oleDA.Dispose();
oleConn.Dispose();

//對建立數據行緩存，以備填充對空單元格進行處理
DataRow tempRow = dsExcel.Tables[m_ExcelSheetName].Rows[0];

for ( int i = 0 ;i<dsExcel.Tables[m_ExcelSheetName].Rows.Count; i ++ )
{
DataRow excelRow = dsExcel.Tables[m_ExcelSheetName].Rows[i];

//調用導入前數據處理函數，並根據返回值確定下一步處理
if (!ImportingBefore(ref excelRow))
{
continue;
}

DataRow sqlNewRow = dsTarget.Tables[0].NewRow();

for ( int j = 0 ;j<m_ColumnMapping[0].Count; j ++ )
{
String sqlColName = m_ColumnMapping[0][j].ToString();
int excelColindex = (int)m_ColumnMapping[1][j];
bool inherit = Convert.ToBoolean(m_ColumnMapping[2][j]);

//如果當前行當前列為空
if (Convert.IsDBNull(excelRow[excelColindex]))
{
//如果允許以臨時值填充
if (inherit)
{
sqlNewRow[sqlColName] = tempRow[excelColindex];
}
}
else
{
//填充數據，更新緩存行數據
sqlNewRow[sqlColName] = excelRow[excelColindex];
tempRow[excelColindex] = excelRow[excelColindex];
}

}

//調用導入後數據處理，並根據返回值決定下一步處理
if (ImportingAfter(ref sqlNewRow))
{
dsTarget.Tables[0].Rows.Add(sqlNewRow);
}
}

return true;
}
catch
{
return false;
}
}

#endregion

#region 受保護的虛函數，子類須重寫

/// <summary>
/// 在導入前對Excel行數據進行處理
/// </summary>
/// <param name="drExcelRow">正在讀取的當前Excel行</param>
/// <returns>true -- 繼續處理，false -- 跳過當前行
/// </returns>
protected virtual bool ImportingBefore(ref DataRow drExcelRow)
{
return true;
}

/// <summary>
/// 在數據轉存後對當前行進行處理
/// </summary>
/// <param name="drSqlRow">已經轉存數據的當前Sql行</param>
/// <returns>true -- 繼續處理，false -- 跳過當前行
/// </returns>
protected virtual bool ImportingAfter(ref DataRow drSqlRow)
{
return true;
}

#endregion

#region 私有方法

/// <summary>
/// 加載配置文件，取得表和列的映射
/// </summary>
/// <returns></returns>
private bool LoadMapping()
{
try
{
//清除已過時的配置
m_ColumnMapping[0].Clear();
m_ColumnMapping[1].Clear();
m_ColumnMapping[2].Clear();

if (m_MappingFile == null || m_MappingFile == "")
{
throw new Exception("找不到配置文件");
}

//讀入配置文件
DataSet dsMaping = new DataSet();
dsMaping.ReadXml(m_MappingFile);

if (dsMaping.Tables.Count == 0)
{
throw new Exception("讀取配置文件失敗");
}

//讀取表映射
DataRow[] tableMap = dsMaping.Tables["TableMapping"].Select("excelSheet='" + m_ExcelSheetName + "'");

if (tableMap.Length != 1)
{
throw new Exception("該Sheet不存在或多次配置");
}

//讀取列映射
DataRow[] colMap = dsMaping.Tables["ColumnMapping"].Select("TableMapping_id="+tableMap[0]["TableMapping_id"].ToString());

if (colMap.Length <= 0)
{
throw new Exception("沒有為該表配置列映射");
}

for (int i = 0; i < colMap.Length; i ++)
{
m_ColumnMapping[0].Add(colMap[i]["sqlCol"]);
m_ColumnMapping[1].Add((int)Enum.Parse(typeof(EnumExcelColumn),colMap[i]["excelCol"].ToString(),true));
m_ColumnMapping[2].Add(colMap[i]["inherit"]);
}

//設置為已加載配置
isLoadMapping = true;

return true;
}
catch
{
return false;
}
}

#endregion
}
}

2、配置文件XSD

3、配置文件樣例

excelSheet ----要導入數據庫的EXCEL文件中的工作薄名

SQLTABLE---要導入的數據庫表名

EXCELCOL--EXCEL表中列標頭

SQLCOL--SQL數據庫中列名

inherit---當EXCEL中有表格合並時，是否繼續上面的單元格值，此處用於拆解單元格，本處指合並行，TRUE為拆解，即所有單元格都以合並值填充，為FALSE則第一行為填充值，其它各行以空填充