摘要：有時候我們的資料存放在excel中（特别是對于使用者來說更喜歡使用excel收集一些常用資料），而系統又需要這些資料來處理其他業務，那麼此時我們就需要将這些資料導入到資料庫中。但是鑒于excel的樣式多種多樣，是以每次導入時都必須書寫很多重複的代碼。很明顯對于一個軟體開發者做這些重複勞動是一件很無趣的事情。那麼怎樣來尋中一種通用的方法呢？今天我們就一塊看一下如何來解決這個問題。
主要内容
excel操作元件的選擇
總體設計思路
配置檔案設計
類設計
編碼實作
一點補充
簡單的測試
總結
在開始今天的主題之前我們先簡單的看一個基礎的問題，那就是如何進行excel的讀寫。關于excel的讀寫操作目前主要分為：1.oledb資料庫連接配接方式2.使用excel.exe com元件3.使用第三方控件。具體哪種方式好我們要依據具體情況而定，對于第一種方式則要求excel表格必須是想資料庫中的表一樣規範，例如如果excel牽扯到合并單元格的情況就很難處理了。對于第二種方式則要求使用者必須按照excel，而且其效率比較低。考慮到我們的需求，是以這裡選擇第三種方式來操作excel。操作excel的第三方控件比較多，常見的如npoi、myxls、aspose.cells等。前兩者都是開源的，并且npoi除了寫excel功能比較強之外對于excel讀取也是十分優秀（myxls讀取excel不如npoi）。aspose.cells是一款商業控件，其操作友善性當然也是十分強大的，而且aspose是一個系列元件，不僅有操作excel的元件還有關于word、ppt、pdf、flash等操作元件。這裡因為項目開發中使用的是aspose.cells，是以下面的例子中我們就拿aspose.cells來進行excel操作（大家可以去找破解版或者使用npoi，當然也可以用myxls等）。
我們去設計通用excel的目的就是為了避免重複工作，也就是說不必因為excel的樣式、資料等變化而重新從零做起、重複勞動。是以我們就必須抽取一個通用的東西出來，使用時隻需要關注相關的業務而不必過度關注相關excel操作和存儲。再簡單一點就是封裝共同點，暴漏個性點。考慮到這種情況，我們可以使用配置檔案的方式來解決這個問題。在配置檔案中我們配置excle要導入的表、字段等資訊，在進行導入時再依據配置檔案将資料導入到資料庫中。這樣一來，在需要進行excel導入時隻需要為某個或多個excel配置一個xml檔案，然後調用相關的類就可以完成整個excel導入工作了。
補充：通用的局限性
在這裡說明一下，雖然我們設計的是一個通用的excel導入程式，但是這裡的"通用"隻是相對來說的，并不是考慮了所有excel的情況，因為excel的設計情況十分的複雜多樣，要将所有的情況都考慮進去是一個漫長的過程。我們這裡的程式隻考慮對于單sheet導入一個或多個表中的情況，并且不考慮包含統計行的情況（可以包含合并行、代碼表字段等）。
既然考慮使用xml配置的方式來設計通用excel導入，是以如何設計好xml也就成了設計的重點。對于單表導入（一個excel主要導入到一個資料庫表中，當然這并不排除牽扯其他代碼表的情況）我們的配置檔案無論以資料庫為基礎設計（主要是依據資料庫表結構）還是以excel(主要是依據excel格式設計)為基礎設計都可以，但是如果是多表導入（也就是一個excel可以導入到幾張表中的情況）的話考慮其複雜性還是以資料庫為基礎更為合适。是以考慮到這種情況，我們整個配置設計會以資料庫表結構為基礎來設計。最終我們的設計樣例如下：
<?xml version="1.0" encoding="utf-8" ?>
<config endtag="rowblank" headerindex="" dataindex="">
<table name="" deleterepeat="true" excludedcolumns="" >
<column isprimarykey="" columnname="" headertext="" required="true" datatype="number" datalength="100" defaultvalue="" comment="">
<codetalbe name="" primarykey="" referencecolumn="" condition=""></codetalbe>
</column>
</table>
</config>
在最外層為config節點，代表整個配置。其屬性endtag(資料讀取的結束标志，例如"rowblank"代表空行結束，在讀取excel時遇到某行沒有任何資料的情況則視為結束；也可以為某個列位址，在導入時到了此列就會結束導入操作)；屬性headerindex代表excel表頭對應的行值（從1開始）；dataindex表示資料列起始行索引（從1開始）。
接着是table節點，對應資料庫中的表，可以有多個。其name屬性對應要導入的表名稱；deleterepeat屬性表示是否删除重複行（如果為true則會根據主鍵先删除重複行再執行插入操作）；excludedcolumns表示排除列，多個列名使用"，"分割（這些字段不會導入）。
table節點内當然就是column節點，也就是對應的列，通常有多個（注意對于excel中沒有的列，而資料庫表需要導入的，也需要配置column節點，此時headertext為空或不配置headertext屬性）。isprimarykey屬性表示是否為主鍵（當table節點配置deleterepeat為ture時必須指定一個column節點的isprimarykey為true，因為此屬性是為了delete條件做準備的[有可能它不是真正的主鍵]）；columnname表示對應的列名；headertext表示對應的excel列頭（在依據excel别名導入時根據此值确定導入的列）；required指定此列是否為必須導入的列（如果配置為true，excel中此列為空并且沒有配置預設值的話則會抛出異常）；datatype為資料類型（例如string、number，用于資料校驗）；defaultvalue為預設值（注意其值不一定是指定的字元值，可以是"max"、"newid".如果為max，那麼此列必須為數值類型，此時在導入的時候如果需要使用預設值，就會在原來資料庫表中此列最大值的基礎上加上1導入到資料庫中，如果為newid在導入的時候如果需要使用預設值系統就會自動建立id）；comment是此列的說明。
在column節點中還可以配置codetable節點，表示代碼表。name屬性值主表的表名稱；primarykey指主表的主鍵，也就是字表的外鍵；referencecolumn表示對應代碼字段關聯名稱列，也就是我們導入時所依據的excel對應值（例如categoryid對應categoryname，那麼referencecolumn就是categoryname，因為往往excel中可能存放的是類似于categoryname的東西而不是categoryid，而導入操作時需要categoryid）。
我們有了思路之後，接下來就來看一下類的設計吧。
在這些類中excel類是整個導入的核心，其最初要的方法就是import()，當然除此之外所有對于excel的讀取和對數資料庫的操作以及對配置對象的解析都是由此類負責；config類是對整個配置的抽象，其對應的方法圖中也已經标出，每個config類對應多個實體類；entity是對于表的抽象，就是表對應的實體類；property類是對于列的抽象，每個entity中包含多個property；另外dictionaryentity是資料字典，是對代碼表的抽象，每個property可以對應一個代碼表；除此之外confighelper是對于應用程式配置的封裝；asposecell是對aspose.cells的封裝，包含常用的excel讀寫方法。
接下來我們就開始實作整個設計吧，相信有了上面的說明和代碼中的注釋，了解起來應該很簡單的，我就不再過多贅餘了。
asposecell類
using system;
using system.collections.generic;
using system.text;
using aspose.cells;
namespace cmj.dataexchange
{
//aspose幫助類，對常用aspose用法進行封裝
public class asposecell
private workbook _workbook = null;
private dictionary<string,worksheet> _worksheets = null;
private worksheet _currentworksheet = null;
public asposecell(string fullname)
_workbook = new workbook();
_workbook.open(fullname);
_worksheets = new dictionary<string, worksheet>();
foreach (worksheet worksheet in _workbook.worksheets)
_worksheets.add(worksheet.name, worksheet);
}
_currentworksheet = _workbook.worksheets[0];
//設定指定名稱的sheet為目前操作sheet
public void setcurrentworksheet(string worksheetname)
if (_worksheets.containskey(worksheetname))
_currentworksheet = _worksheets[worksheetname];
else
throw new exception("目前工作薄不存在\""+worksheetname+"\"工作表！");
//設定指定索引（從0開始）的sheet為目前操作sheet
public void setcurrentworksheet(byte worksheetindex)
if (worksheetindex <= _worksheets.count)
_currentworksheet = getworksheetbyindex(worksheetindex);
throw new exception("工作表索引範圍超過了總工作表數量！");
//根據索引得到sheet
public worksheet getworksheetbyindex(byte index)
byte i = 0;
worksheet worksheet = null;
foreach(string name in _worksheets.keys)
if (index == i)
worksheet = _worksheets[name];
i++;
return worksheet;
//根據sheet名稱得到sheet
public worksheet getworksheetbyname(string sheetname)
if (_worksheets.containskey(sheetname))
return _worksheets[sheetname];
return null;
/// <summary>
/// 判斷指定行是否有資料
/// </summary>
/// <param name="row">從1開始，為excel行序号</param>
/// <returns></returns>
public bool rowhasvalue(int row)//指定行是否有資料（以連續50列沒有資料為标準）
bool r = false;
for (int i = 0; i < 50; ++i)
if (_currentworksheet.cells[row - 1, i].value != null && _currentworksheet.cells[row - 1, i].value.tostring() != "")
r = true;
break;
return r;
public bool rowhasvalue(string position)//指定行是否有資料（以連續50列沒有資料為标準）
if (getcellvalue(position)!="")
//取得指定sheet中指定cell位置的資料
public string getcellvalue(string worksheetname, string cellname)
return _worksheets[worksheetname].cells[cellname].value != null ? _worksheets[worksheetname].cells[cellname].value.tostring() : "";
public string getcellvalue(byte worksheetindex, string cellname)
return getworksheetbyindex(worksheetindex).cells[cellname].value != null ? getworksheetbyindex(worksheetindex).cells[cellname].value.tostring() : "";
public string getcellvalue(string cellname)
return _currentworksheet.cells[cellname].value != null ? _currentworksheet.cells[cellname].value.tostring() : "";
/// 根據行列索引得到指定位置的資料
/// <param name="row">從0開始</param>
/// <param name="column">從0開始</param>
public string getcellvalue(int row, int column)
return _currentworksheet.cells[row, column].value != null ? _currentworksheet.cells[row, column].value.tostring() : "";
/// 判斷某位置的單元格是否為合并單元格
public bool ismerged(int row,int column)
return _currentworksheet.cells[row, column].ismerged;
//取得合并單元格的資料
public string getmergedcellvalue(int row, int column)//事實上合并單元格隻有第一個單元格有值其他的全為空，但是我們知道其實從意義上了解合并單元格除了第一個單元格外其他單元格的值同第一個，是以這裡提供這樣一個方法
string r = "";
int t=row-1;
if (ismerged(row, column))
if (getcellvalue(row, column) != "")
r = getcellvalue(row, column);
else//約定合并單元格隻是合并行并不和并列，并且合并行數最多50
while (t >= 0 && (row-t)<50)
if (getcellvalue(t, column) != "")
r = getcellvalue(t, column);
t--;
//取得range名稱集合
public list<string> getrangenames()
list<string> names = new list<string>();
foreach (range r in _workbook.worksheets.getnamedranges())
names.add(r.name);
return names;
//取得某行range名稱集合
public list<string> getrangenames(int rowindex)
if (_workbook.worksheets.getnamedranges()!=null)
if (r.firstrow == rowindex && r.worksheet == _currentworksheet)
//根據range（别名）取得行索引
public int getrowindexbyrangename(string rangename)//隻傳回第一行索引（從0開始）
if (_workbook.worksheets.getrangebyname(rangename) != null)
return _workbook.worksheets.getrangebyname(rangename).firstrow;
throw new exception("未有找到指定名稱的單元格！");
//根據range（别名）取得列索引
public int getcolumnindexbyrangename(string rangename)//隻傳回第一行索引（從0開始）
return _workbook.worksheets.getrangebyname(rangename).firstcolumn;
//根據range(别名)取得行列索引
public int[] getrowandcolumnindexbyrangename(string rangename)
int[] i = new int[2];
i[0] = _workbook.worksheets.getrangebyname(rangename).firstrow;
i[1]=_workbook.worksheets.getrangebyname(rangename).firstcolumn;
return i;
confighelper類
using system.configuration;
//配置輔助類，主要用于讀取應用程式配置檔案
internal class confighelper
private static confighelper confighelper = null;
private static object obj = new object();
private string excelpath = "";//excel檔案所在路徑或者其根目錄（此時會将其下所有excel全部倒入）
private bool usetransaction = false;
private confighelper()
//讀取excel配置路徑
if (configurationmanager.appsettings["excelpath"] != null)
excelpath = configurationmanager.appsettings["excelpath"];
throw new exception("未發現excel配置路徑（excelpath），請檢查配置檔案！");
if (configurationmanager.appsettings["usetransaction"] != null)
usetransaction = convert.toboolean(configurationmanager.appsettings["usetransaction"]);
public static confighelper instance()
lock (obj)
if (confighelper == null)
confighelper = new confighelper();
return confighelper;
public string excelpath
get
return excelpath;
set
excelpath = value;
public bool usetransaction
return usetransaction;
usetransaction = value;
dictionaryentity類
//代碼表類，是對codetable的抽象
internal class dictionaryentity
private string name = "";//代碼表名稱，對應資料庫中主表名稱
private string primarykey = "";//代碼表主鍵，也就是字表的對應外鍵
private string referencecolumn = "";//代碼字段關聯名稱列，也就是我們導入時所依據的excel對應值
private string condition = "";//相關條件
public dictionaryentity()
public string name
return name;
name = value;
public string primarykey
return primarykey;
primarykey = value;
public string referencecolumn
return referencecolumn;
referencecolumn = value;
public string condition
return condition;
condition = value;
property類
//配置屬性類，抽象了配置檔案的配置屬性，對應于資料庫中的字段
internal class property
private bool isprimarykey=false;//是否為主鍵
private string columnname = "";//資料庫列名稱
private string headertext = "";//對應的excel列頭名稱
private bool required = true;//是否為必填字段
private string datatype = "string";//資料類型（預設為string類型）
private int datalength = 5000;//資料長度（預設為5000）
private string defaultvalue = "";//預設值(對應excel此列的值如果為空則會使用此值來導入)
private string comment = "";//字段說明資訊（非必要屬性）
private dictionaryentity codetalbe = null;//對應的代碼表
public property()
public bool isprimarykey
return isprimarykey;
isprimarykey = value;
public string columnname
return columnname;
columnname = value;
public string headertext
return headertext;
headertext = value;
public bool required
return required;
required = value;
public string datatype
return datatype;
datatype = value;
public int datalength
return datalength;
datalength = value;
public string defaultvalue//解析過的預設值（也就是說直接就是值，而不是其位址什麼的）
return defaultvalue;
defaultvalue = value;
public string comment//說明資訊
return comment;
comment = value;
public dictionaryentity codetable//代碼表
return codetalbe;
codetalbe = value;
entity類
//實體類，對配置檔案中table的抽象，對應資料庫中的表
internal class entity
private string name = "";//表名稱
private bool deleterepeat = false;//是否删除重複（預設為false，如果設為true則在插入時首先根據主鍵删除重複資訊）
private list<property> propertys = null;//屬性集合（每個實體對應多個property）
private list<string> excludedcolumns = null;//排除字段（也就是指明哪些字段不用導入）
public entity()
propertys = new list<property>();
excludedcolumns = new list<string>();
public entity(string name)
this.name=name;
public bool deleterepeat
return deleterepeat;
deleterepeat = value;
public list<string> excludedcolumns
return excludedcolumns;
excludedcolumns = value;
public list<property> propertys
return propertys;
propertys = value;
config類
using system.xml;
using cmj.myfile;
//配置類，是對整個配置的抽象
internal class config
#region excel讀取相關成員變量
private myxml xml = null;
#endregion
#region 配置檔案相關成員變量
private string endflag = "rowblank";//excel結束标志
private byte headerindex = 1;//列頭所在行
private byte dataindex = 2;//資料行起始位置
private list<entity> entities = null;//配置類對應的實體類（也就是說配置時，每個config節點中可以有多個table節點）
public config(string excelconfigfullname)
//初始化成員變量
entities = new list<entity>();
//讀取配置檔案，類初始化
init(excelconfigfullname);
public string endflag
return endflag;
public int headerindex
return headerindex;
public int dataindex
return dataindex;
public list<entity> entities
return entities;
//初始化配置對象
private void init(string excelconfigfullname)
int t = 1;
bool r = true;
xml = new myxml(excelconfigfullname);
endflag = xml.getsinglenodeattribute("config", "endflag") != "" ? xml.getsinglenodeattribute("config", "endflag") : "rowblank";//預設判斷是否讀取結束的标志設為“rowblank”，也就是空行。
r = int.tryparse(xml.getsinglenodeattribute("config", "headerindex"), out t);
if (r)
headerindex = convert.tobyte(xml.getsinglenodeattribute("config", "headerindex"));
r = int.tryparse(xml.getsinglenodeattribute("config", "dataindex"), out t);
dataindex = convert.tobyte(xml.getsinglenodeattribute("config", "dataindex"));
if (myfillebase.filebeing(excelconfigfullname))//根據完整路徑判斷檔案是否存在
addenities(excelconfigfullname);
throw new exception("相應配置檔案未找到，請檢查相應檔案是否存在！");
//初始化實體對象
private void addenities(string fullname)
xml = new myxml(fullname);
xmlnodelist tables = xml.getnodes("config/table");
entity entity = null;
property property = null;
bool t = true;
bool r=true;
int i = 0;
foreach (xmlnode table in tables)
entity = new entity(xml.getnodeattribute(table,"name"));
r = bool.tryparse(xml.getnodeattribute(table, "deleterepeat"),out t);
entity.deleterepeat = convert.toboolean(xml.getnodeattribute(table, "deleterepeat"));
if (xml.getfirstchildnode(table) != null)//說明有列配置
foreach (xmlnode column in xml.getchildnodes(table))
property = new property();
r = bool.tryparse(xml.getnodeattribute(column, "isprimarykey"), out t);
property.isprimarykey = convert.toboolean(xml.getnodeattribute(column, "isprimarykey"));
property.columnname = xml.getnodeattribute(column, "columnname");
if (xml.getnodeattribute(column, "headertext") != "")
property.headertext = xml.getnodeattribute(column, "headertext");
r = bool.tryparse(xml.getnodeattribute(column, "required"), out t);
property.required = convert.toboolean(xml.getnodeattribute(column, "required"));
property.datatype = xml.getnodeattribute(column, "datatype") != "" ? xml.getnodeattribute(column, "datatype") : "string";
if (xml.getnodeattribute(column, "datalength") != "")
r = int.tryparse(xml.getnodeattribute(column, "datalength"),out i);
property.datalength = i;
property.comment = xml.getnodeattribute(column, "comment");
property.defaultvalue = xml.getnodeattribute(column, "defaultvalue") != "" ? xml.getnodeattribute(column, "defaultvalue") : "";
if (xml.getfirstchildnode(column) != null)//說明有代碼表
dictionaryentity dictionaryentity = new dictionaryentity();
dictionaryentity.name = xml.getnodeattribute(xml.getfirstchildnode(column), "name");
dictionaryentity.primarykey = xml.getnodeattribute(xml.getfirstchildnode(column), "primarykey");
dictionaryentity.referencecolumn = xml.getnodeattribute(xml.getfirstchildnode(column), "referencecolumn");
dictionaryentity.condition = xml.getnodeattribute(xml.getfirstchildnode(column), "condition");
property.codetable = dictionaryentity;
entity.propertys.add(property);
entities.add(entity);
string excludedcolumns = xml.getnodeattribute(table, "excludedcolumns");
if (excludedcolumns != "")
foreach (string ec in excludedcolumns.split(','))
entity.excludedcolumns.add(ec);
excel類
using system.data;
using cmj.mydata;
//excel導入核心類
public class excel
private dictionary<string, config> configs =null;//配置檔案全路徑和對應的配置類鍵值集合
private string excelpath="";//excel路徑（可以為目錄）
bool usetransaction=false;//是否使用事務
private asposecell asposecell = null;//aspose.cells封裝類
private sqlhelper dbhelper = new sqlhelper();//資料庫操作封裝類
public excel()
excelpath=confighelper.instance().excelpath;
usetransaction = confighelper.instance().usetransaction;
initconfig();
public excel(string excelpath,bool usetransaction)
this.excelpath=excelpath;
this.usetransaction = usetransaction;
confighelper.instance().excelpath = excelpath;
confighelper.instance().usetransaction = usetransaction;
//導入操作核心方法，負責整個excel導入
public void import()
if (configs.count > 0)
if (usetransaction)//使用事務的情況
dbhelper.transationhandler(sqlhelper.transationtype.open);
excuteimport();
dbhelper.transationhandler(sqlhelper.transationtype.complete);
throw new exception("config對象個數為0，無法導入！");
//執行excel導入
private void excuteimport()
string sqldelete = "";//删除操作對應的sql語句
string sqlinsert = "";//插入操作使用的sql語句
int headerindex = 1;//列頭所在行
int dataindex = 2;//資料起始行
string endflag = "rowblank";
foreach (string excelfullname in configs.keys)//周遊所有config
asposecell = new asposecell(excelfullname);
list<entity> entities = configs[excelfullname].entities;//注意每個config中不一定隻有一個實體，可以配置多個，這樣每個excel可以導入到多張表中
headerindex = configs[excelfullname].headerindex;
dataindex = configs[excelfullname].dataindex;
endflag = configs[excelfullname].endflag;
while (!isend(asposecell, endflag, dataindex))
foreach (entity entity in entities)
if (entity.propertys.count > 0)//說明配置了column字段
if (entity.deleterepeat)
sqldelete = "delete from " + entity.name + " where " + getsqlconditionstring(asposecell, entity, headerindex, dataindex);
dbhelper.excutenonquery(commandtype.text, sqldelete);
sqlinsert = "insert into " + entity.name + "(" + getsqlfieldstring(asposecell, entity) + ")" + " values(" + getsqlfieldvaluestring(asposecell, entity, headerindex, dataindex) + ")";
dbhelper.excutenonquery(commandtype.text, sqlinsert);
if (asposecell.getrangenames(headerindex - 1).count > 0)//說明指定了單元格别名作為列名
sqlinsert = "insert into " + entity.name + "(" + getsqlfieldstringaccordingtoalias(asposecell, headerindex) + ") values(" + getsqlfieldvaluestringaccordingtoalias(asposecell, headerindex, dataindex) + ")";
else if (entity.excludedcolumns.count > 0)//說明有排除字段
sqlinsert = "insert into " + entity.name + "(" + getsqlfieldstringaccordingtoexcludedcolumns(asposecell, entity) + ") values(" + getsqlfieldvaluestringwithoutalias(asposecell, entity, headerindex, dataindex) + ")";
sqlinsert = "insert into " + entity.name + " values(" + getsqlfieldvaluestringwithoutalias(asposecell, entity, headerindex, dataindex) + ")";
dataindex++;
/// 根據endflag标記判斷目前資料行是否結束
/// <param name="asposecell"></param>
/// <param name="endflag"></param>
/// <param name="row"></param>
private bool isend(asposecell asposecell,string endflag,int row)
switch (endflag)
case "rowblank":
if (!asposecell.rowhasvalue(row))
default :
if (asposecell.getcellvalue(endflag+row.tostring()) == "")
/// 根據headertext配置節确定列索引（從0開始）
/// <param name="headerindex"></param>
/// <param name="headertext"></param>
private int getcolumnindexbyheadertext(asposecell asposecell,int headerindex,string headertext)
int columnindex=0;
int r=0;
while (asposecell.getcellvalue(headerindex-1, columnindex) != "")
if (asposecell.getcellvalue(headerindex-1, columnindex) == headertext)
r = columnindex;
columnindex++;
/// 根據defaultvalue配置節确定預設值
/// <param name="entityname"></param>
/// <param name="columnname"></param>
/// <param name="defaultvalue"></param>
private string getdefaultvalue(asposecell asposecell,string entityname,string columnname, string defaultvalue)
switch (defaultvalue)
case "newid": r = guid.newguid().tostring(); break;
case "max": r = dbhelper.executescalar(commandtype.text, "select max(" + columnname + ") from " + entityname + "") != null ?((convert.toint32(dbhelper.executescalar(commandtype.text,"select max(" + columnname + ") from " + entityname + "").tostring())+1).tostring()) : "1"; break;
default: r = defaultvalue; break;
/// 得到查詢條件sql語句段
/// <param name="entity"></param>
/// <param name="dataindex"></param>
private string getsqlconditionstring(asposecell asposecell, entity entity,int headerindex,int dataindex)
string sql="";
foreach (property p in entity.propertys)
if(p.isprimarykey)
sql+=p.columnname+"=";
if (p.headertext!="")
sql+="'"+asposecell.getcellvalue(dataindex-1,getcolumnindexbyheadertext(asposecell,headerindex,p.headertext))+"'";
sql += "'"+getdefaultvalue(asposecell, entity.name, p.columnname, p.defaultvalue)+"'";
sql += " and";
sql = sql.substring(0, sql.length - 4);
return sql;
/// 得到查詢字段sql語句段
private string getsqlfieldstring(asposecell asposecell,entity entity)
string sql = "";
sql += p.columnname+",";
sql = sql.trimend(',');
/// 依據别名得到查詢字段sql段
private string getsqlfieldstringaccordingtoalias(asposecell asposecell,int headerindex)
foreach (string columnname in asposecell.getrangenames(headerindex - 1))
sql += columnname + ",";
/// 依據排除列得到查詢字段sql語句段
private string getsqlfieldstringaccordingtoexcludedcolumns(asposecell asposecell, entity entity)
foreach(datacolumn column in dbhelper.getdatatable(commandtype.text,"select top 0 * from "+entity.name).columns)
if (!entity.excludedcolumns.contains(column.columnname))
sql += column.columnname + ",";
/// 得到查詢值sql語句段
private string getsqlfieldvaluestring(asposecell asposecell, entity entity,int headerindex, int dataindex)
string v = "";
foreach (property p in entity.propertys)//周遊實體的所有屬性
if (p.codetable != null)
sql += "'" + getcodetablevalue(asposecell,p,headerindex,dataindex).replace("'","''") + "',";//注意如果單元格本身的值就有“'”的情況
else//說明此屬性是一個代碼表字段
if(asposecell.ismerged(dataindex-1, getcolumnindexbyheadertext(asposecell, headerindex, p.headertext)))//是否為合并單元格(對于合并單元格取此合并單元格的第一個值)
v=asposecell.getmergedcellvalue(dataindex-1, getcolumnindexbyheadertext(asposecell, headerindex, p.headertext));
v=asposecell.getcellvalue(dataindex-1, getcolumnindexbyheadertext(asposecell, headerindex, p.headertext));
if (v == "")//說明單元格中沒有任何值，就要考慮“預設值”和“必須”屬性
if (getdefaultvalue(asposecell, entity.name, p.columnname, p.defaultvalue) != "")//說明有預設值
v = getdefaultvalue(asposecell, entity.name, p.columnname, p.defaultvalue);
else//如果單元格沒有值并且無預設值，則檢查此屬性是否是必須的
if (!p.required)
v = "";
throw new exception("列\""+p.headertext+"\""+"不能為空！");
//檢查類型
if (p.datatype != "" && p.datatype != "string")
if (!validatedatatype(v, p.datatype))
throw new exception("列\"" + p.headertext + "\"中存在非\"" + p.datatype + "\"類型資料！");
//屬性長度檢查
if (p.datalength != 0 && p.datalength != 5000)
if(!validatedatalength(v,p.datalength))
throw new exception("列\"" + p.headertext + "\"中存長度超過\"" + p.datalength.tostring() + "\"的資料！");
sql += "'"+v.replace("'", "''")+"',";
//資料類型校驗
private bool validatedatatype(string value,string type)
bool r=false;
double t = 0;
switch (type.tolower())
case "number":
r = double.tryparse(value, out t);
case "string": r = true;
default: break; ;
//資料長度校驗
private bool validatedatalength(string value, int length)
if (value.length > length)
return false;
return true;
/// 得到查詢值sql語句段（未配置列情況下使用且有别名）
private string getsqlfieldvaluestringaccordingtoalias(asposecell asposecell, int headerindex, int dataindex)//對于沒有配置列的情況使用
if(asposecell.ismerged(dataindex - 1,asposecell.getcolumnindexbyrangename(columnname)))
sql+="'"+asposecell.getmergedcellvalue(dataindex - 1, asposecell.getcolumnindexbyrangename(columnname)).replace("'","''")+"',";
sql += "'" + asposecell.getcellvalue(dataindex - 1, asposecell.getcolumnindexbyrangename(columnname)).replace("'", "''") + "',";
/// 得到查詢值sql語句段（未配置列情況下使用且無别名,不管有沒有排除字段）
private string getsqlfieldvaluestringwithoutalias(asposecell asposecell,entity entity, int headerindex, int dataindex)//對于沒有配置列的情況使用
int column = 0;
while (asposecell.getcellvalue(headerindex-1, column) != "")
sql += "'" + (asposecell.ismerged(dataindex - 1, column) ? asposecell.getmergedcellvalue(dataindex - 1, column) : asposecell.getcellvalue(dataindex - 1, column)).replace("'","''") + "',";
column++;
/// 得到代碼表的對應值
/// <param name="property"></param>
private string getcodetablevalue(asposecell asposecell,property property,int headerindex,int dataindex)
string value=asposecell.ismerged(dataindex-1, getcolumnindexbyheadertext(asposecell, headerindex, property.headertext))?asposecell.getmergedcellvalue(dataindex-1, getcolumnindexbyheadertext(asposecell, headerindex, property.headertext)):asposecell.getcellvalue(dataindex-1, getcolumnindexbyheadertext(asposecell, headerindex, property.headertext)).replace("'","''");
stringbuilder sb = new stringbuilder("select distinct ");
sb.append(property.codetable.primarykey);
sb.append(" from ");
sb.append(property.codetable.name);
sb.append(" where ");
sb.append(property.codetable.referencecolumn);
sb.append("='");
sb.append(value);
sb.append("'");
if (property.datatype != "" && property.datatype != "string")
if (!validatedatatype(value, property.datatype))
throw new exception("列\"" + property.headertext + "\"中存在非\"" + property.datatype + "\"類型資料！");
if (property.datalength != 0 && property.datalength != 5000)
if (!validatedatalength(value, property.datalength))
throw new exception("列\"" + property.headertext + "\"中存長度超過\"" + property.datalength.tostring() + "\"的資料！");
if (dbhelper.executescalar(commandtype.text, sb.tostring()) != null)
return dbhelper.executescalar(commandtype.text, sb.tostring()).tostring();
throw new exception("沒有對應的代碼表值！");
//初始化，主要将excel檔案和配置類對應關系存放到configs對象中，友善以後周遊
private void initconfig()
configs=new dictionary<string,config>();
list<string> excelfullnames=new list<string>();
if(myfillebase.directorybeing(excelpath))//判斷目錄是否存在(注意：除了多套excel對應多套模闆，還可能有一個模闆對應多個excel的情況）
filesearch.initfileinfolist();
list<string> excelconfigfilefullnames= filesearch.getfileinfo(".xml", confighelper.instance().excelpath, true, true);//在目錄中查找所有名稱中包含".xml"的檔案
if (excelconfigfilefullnames.count == 1)//說明是一個excel對應一個xml配置檔案的情況
addconfigsbyxmlfullnamehasnothingtoexcelname(excelconfigfilefullnames[0]);
else if(excelconfigfilefullnames.count>1)//說明目錄中有多個xml檔案，對應多個excel
foreach (string excelconfigfilefullname in excelconfigfilefullnames)
addconfigsbyxmlfullname(excelconfigfilefullname);
throw new exception("所指定目錄不包含任何xlm模闆，請重新指定！");
else//說明指定的不是目錄而是excel檔案路徑
addconfigbyexcelfullname(excelpath);
//根據excel全路徑構造路徑和配置類對應關系（主要用于指導excel全路徑的情況）
private void addconfigbyexcelfullname(string excelfullname)
string excelconfigfilefullname = myfillebase.getdirectorybyfullname(excelfullname) + "\\" + myfillebase.getfilenamewithoutextension(excelfullname) + ".xml";
config config=null;
if (myfillebase.filebeing(excelconfigfilefullname))
config = new config(excelconfigfilefullname);//建立配置（config）對象
configs.add(excelfullname, config);
throw new exception("所指定檔案沒有對應的配置檔案，請重新指定！");
//根據xml檔案全路徑構造路徑和配置類對應關系(得到一個xml檔案對應的所有excel然後構造config對象存放到configs中)
private void addconfigsbyxmlfullname(string xmlfullname)
string exceldirectory = myfillebase.getdirectorybyfullname(xmlfullname);//根據路徑取得對應的目錄
list<string> excelfullnames = filesearch.getfileinfo(myfillebase.getfilenamewithoutextension(xmlfullname), exceldirectory, true, true);//根據xml的名稱搜尋包含此名稱的檔案
if (excelfullnames.count >= 1)
foreach (string excelfullname in excelfullnames)
if (excelfullnames.indexof(".xls") != -1)//必須是excel檔案(排除xml檔案)
config = new config(xmlfullname);
throw new exception("所指定模闆不包含對應的excel檔案，請重新指定！");
//根據xml檔案全路徑構造路徑和配置類對應關系（此種情況由于隻有一個xml，必然對應一個或多個excel檔案，是以隻需要查找excel檔案即可）
private void addconfigsbyxmlfullnamehasnothingtoexcelname(string xmlfullname)
config config = null;
string exceldirectory=myfillebase.getdirectorybyfullname(xmlfullname);
list<string> excelfullnames = filesearch.getfileinfo(".xls", exceldirectory, true, true);
sqlhelper：資料庫操作類，使用時在配置檔案的connectionstrings中配置name為"dbcon"的連接配接串，指明providername即可，如果不使用配置可以直接在構造函數中傳遞這兩個參數。
myxml：對于xml檔案操作的封裝了，用于xml檔案操作。
myfilebase：對檔案操作的封裝。
filesearch：對檔案查找的封裝，可以按照關鍵字查找檔案。
為了更加容易了解，我對程式中的一些約定和處理做少許補充。
excel類中有參構造函數兩個參數分别是excel路徑（也可以是目錄）以及是否啟用事務的bool型變量。如果使用者使用無參構造函數的話，就必須在應用程式配置檔案的appsettings配置節點配置中配置"excelpath"和"usetransaction"兩個節點。由于對資料庫的操作需要通路應用程式的配置檔案，是以需要配置connectionstrings節點。
一般的導入按照上面的配置就可以完成了。為了友善使用此類還提供了其他導入方式。假如覺得列配置較麻煩的，覺得沒有必要配置那麼多列的話，可以考慮下面幾種方式。
ⅰ 配置檔案中如果沒有column配置，可以通過配置單元格的别名，來訓示每一個列對應的表字段。
ⅱ 如果配置檔案中沒有column配置，而又不希望指定單元格别名，但是excel中的列和表中的列又不是一一對應的（很可能有些列資料庫表中有，而excel中沒有），此時如果excel中列的順序和表中順序一緻，你就可以通過在配置檔案中設定table的excludedcolumns來将這些列排除（多個需要排除的列中間用"，"分割）。
ⅲ 如果我的excel中的列和資料庫表中完全一緻且順序一樣，那麼你出來配置檔案中配置table屬性外就不需要任何額外的配置了。
另外，如果需要在導入時檢查是否有默寫列唯一的情況的情況（例如productname我不希望有重複），對于重複的先删除再倒入，此時可以再此列上設定isprimarykey為true，然後在table的屬性中配置deleterepeat為true就可以了；如果所有的導入過程中你需要使用事務機制，可以在應用程式配置appsettings 配置節點中指定usetransaction為true（注意先要保證事務服務是啟動的）；程式支援合并行的導入。
關于配置檔案和excel檔案的關系，是這樣約定的，多數情況下excelpath的配置為目錄（當然可以是一個excel檔案路徑，此時就隻導入該excel檔案），對于此種情形可能出現下列情況。
ⅰ 目錄中隻有一個配置檔案，而有一個或多個excel。這種情況下程式認為此配置檔案對應所有excel檔案，将會使用此配置将所有excel導入。
ⅱ 目錄中有多個配置檔案，并且有多個excel檔案。此時程式戶根據配置檔案的名稱自動比對excel檔案，比對的規則就是它對應的excle檔案的名稱中要出現配置檔案的名稱。
下面我們簡單的測試一下我們的類庫，應該說使用起來十分簡單。
ⅰ 在使用之前當然要有一個需要導入的excel
ⅱ 根據excel編寫xml檔案
<config endtag="rowblank" headerindex="1" dataindex="2">
<table name="products" >
<column columnname="supplierid" headertext="供貨商" defaultvalue="">
<codetalbe name="suppliers" primarykey="supplierid" referencecolumn="companyname"></codetalbe>
<column columnname="productname" headertext="商品名稱" defaultvalue="" />
<column columnname="categoryid" headertext="商品種類" defaultvalue="">
<codetalbe name="categories" primarykey="categoryid" referencecolumn="categoryname" ></codetalbe>
<column columnname="quantityperunit" headertext="規格" defaultvalue="" />
<column columnname="unitprice" headertext="單價" defaultvalue="" />
<column columnname="unitsinstock" headertext="庫存" defaultvalue="" />
<column columnname="discontinued" headertext="是否停産" defaultvalue="" />
ⅲ 将excel檔案和對應xml檔案放到f:\cmj.dataexchange\wftest\excel
ⅳ 建立一個項目，添加cmj.dataexchange.dll引用
ⅴ 在配置檔案中配置資料庫連接配接以及excel目錄等
<configuration>
<appsettings>
<add key="excelpath" value="f:\cmj.dataexchange\wftest\excel"/> <!--excel檔案所在路徑或者其根目錄（此時會将其下所有excel全部倒入）-->
<add key="usetransaction" value="true"/>
</appsettings>
<connectionstrings>
<add name="dbconstr" connectionstring="data source=.;database=northwind;integrated security=sspi" providername="system.data.sqlclient"/>
</connectionstrings>
</configuration>
ⅵ 接着確定distributed transaction coordinator服務是啟動的（因為程式中用的是.net2.0的事務，當然你也可以使用.net1.0事務，此時就無需啟動此服務了）。
ⅶ 最後添加下面兩句代碼就可以完成整個導入了。
excel ex = new excel();
ex.import();
下面是導入前後資料庫northwind中products表中的資料（已經成功導入）
導入之前
導入之後