引言

項目中常用到將數(shù)據(jù)導入excel，將excel中的數(shù)據(jù)導入數(shù)據(jù)庫的功能，曾經(jīng)也查找過相關的內(nèi)容，將曾經(jīng)用過的方案總結一下。

方案一

npoi

npoi 是 poi 項目的 .net 版本。poi是一個開源的java讀寫excel、word等微軟ole2組件文檔的項目。

使用 npoi 你就可以在沒有安裝 office 或者相應環(huán)境的機器上對 word/excel 文檔進行讀寫。npoi是構建在poi 3.x版本之上的，它可以在沒有安裝office的情況下對word/excel文檔進行讀寫操作。

優(yōu)勢

(一)傳統(tǒng)操作excel遇到的問題：

1、如果是.net，需要在服務器端裝office，且及時更新它，以防漏洞，還需要設定權限允許.net訪問com+，如果在導出過程中出問題可能導致服務器宕機。

2、excel會把只包含數(shù)字的列進行類型轉換，本來是文本型的，excel會將其轉成數(shù)值型的，比如編號000123會變成123。

3、導出時，如果字段內(nèi)容以“-”或“=”開頭，excel會把它當成公式進行，會報錯。

4、excel會根據(jù)excel文件前8行分析數(shù)據(jù)類型，如果正好你前8行某一列只是數(shù)字，那它會認為該列為數(shù)值型，自動將該列轉變成類似1.42702e+17格式，日期列變成包含日期和數(shù)字的。

(二)使用npoi的優(yōu)勢

1、您可以完全免費使用該框架

2、包含了大部分excel的特性(單元格樣式、數(shù)據(jù)格式、公式等等)

3、專業(yè)的技術支持服務(24*7全天候) (非免費)

4、支持處理的文件格式包括xls, xlsx, docx.

5、采用面向接口的設計架構( 可以查看 npoi.ss 的命名空間)

6、同時支持文件的導入和導出

7、基于.net 2.0 也支持xlsx 和 docx格式(當然也支持.net 4.0)

8、來自全世界大量成功且真實的測試cases

9、大量的實例代碼

11、你不需要在服務器上安裝微軟的office，可以避免版權問題。

12、使用起來比office pia的api更加方便，更人性化。

13、你不用去花大力氣維護npoi，npoi team會不斷更新、改善npoi，絕對省成本。

npoi之所以強大，并不是因為它支持導出excel，而是因為它支持導入excel，并能“理解”ole2文檔結構，這也是其他一些excel讀寫庫比較弱的方面。通常，讀入并理解結構遠比導出來得復雜，因為導入你必須假設一切情況都是可能的，而生成你只要保證滿足你自己需求就可以了，如果把導入需求和生成需求比做兩個集合，那么生成需求通常都是導入需求的子集，這一規(guī)律不僅體現(xiàn)在excel讀寫庫中，也體現(xiàn)在pdf讀寫庫中，目前市面上大部分的pdf庫僅支持生成，不支持導入。

構成

npoi 1.2.x主要由poifs、ddf、hpsf、hssf、ss、util六部分組成。

npoi.poifs

ole2/activex文檔屬性讀寫庫

npoi.ddf

microsoft office drawing讀寫庫

npoi.hpsf

ole2/activex文檔讀寫庫

npoi.hssf

microsoft excel biff(excel 97-2003)格式讀寫庫

npoi.ss

excel公用接口及excel公式計算引擎

npoi.util

基礎類庫，提供了很多實用功能，可用于其他讀寫文件格式項目的開發(fā)

npoi組成部分

npoi 1.x的最新版為npoi 1.2.5，其中包括了以下功能：

1、讀寫ole2文檔

2、讀寫docummentsummaryinformation和summaryinformation

3、基于littleendian的字節(jié)讀寫

4、讀寫excel biff格式

5、識別并讀寫excel biff中的常見record，如rowrecord, stylerecord, extendedformatrecord

6、支持設置單元格的高、寬、樣式等

7、支持調(diào)用部分excel內(nèi)建函數(shù)，比如說sum, countif以及計算符號

8、支持在生成的xls內(nèi)嵌入打印設置，比如說橫向/縱向打印、縮放、使用的紙張等。

npoi 2.0主要由ss, hpsf, ddf, hssf, xwpf, xssf, openxml4net, openxmlformats組成，具體列表如下：

assembly名稱 模塊/命名空間 說明

npoi.dll

npoi.poifs

ole2/activex文檔屬性讀寫庫

npoi.dll

npoi.ddf

微軟office drawing讀寫庫

npoi.dll

npoi.hpsf

ole2/activex文檔讀寫庫

npoi.dll

npoi.hssf

微軟excel biff(excel 97-2003, doc)格式讀寫庫

npoi.dll

npoi.ss

excel公用接口及excel公式計算引擎

npoi.dll

npoi.util

基礎類庫，提供了很多實用功能，可用于其他讀寫文件格式項目的開發(fā)

npoi.ooxml.dll npoi.xssf excel 2007(xlsx)格式讀寫庫

npoi.ooxml.dll npoi.xwpf word 2007(docx)格式讀寫庫

npoi.openxml4net.dll npoi.openxml4net openxml底層zip包讀寫庫

npoi.openxmlformats.dll npoi.openxmlformats 微軟office openxml對象關系庫

(以上內(nèi)容來自百度百科)從上表可知npoi組件已支持excel2007，記得之前用的時候只支持excel2003。很久沒研究過這玩意兒了。

案例

官網(wǎng)地址：http://npoi.codeplex.com/，可以從官網(wǎng)下載npoi2.x版本的。

首先引入

icsharpcode.sharpziplib.dll

npoi.dll

npoi.ooxml.dll

npoi.openxml4net.dll

npoi.openxmlformats.dll

然后引入命名空間：

using npoi.xssf.usermodel;

using npoi.ss.usermodel;

using npoi.hssf.usermodel;

輔助類

using npoi.xssf.usermodel;

using npoi.ss.usermodel;

using npoi.hssf.usermodel;

using system;

using system.collections.generic;

using system.data;

using system.io;

using system.linq;

using system.text;

using system.threading.tasks;

using npoi.ss.formula.eval;

namespace wolfy.common

{

///

/// 使用npoi組件

/// 需引入icsharpcode.sharpziplib.dll/npoi.dll/npoi.ooxml.dll/npoi.openxml4net.dll/npoi.openxmlformats.dll

/// office2007

///

public class npoiexcelhelper

{

///

/// 將excel文件中的數(shù)據(jù)讀出到datatable中

///

///

///

public static datatable excel2datatable(string file, string sheetname, string tablename)

{

datatable dt = new datatable();

iworkbook workbook = null;

using (filestream fs = new filestream(file, filemode.open, fileaccess.read))

{

//office2003 hssfworkbook

workbook = new xssfworkbook(fs);

}

isheet sheet = workbook.getsheet(sheetname);

dt = export2datatable(sheet, 0, true);

return dt;

}

///

/// 將指定sheet中的數(shù)據(jù)導入到datatable中

///

/// 指定需要導出的sheet

/// 列頭所在的行號，-1沒有列頭

///

///

private static datatable export2datatable(isheet sheet, int headerrowindex, bool needheader)

{

datatable dt = new datatable();

xssfrow headerrow = null;

int cellcount;

try

{

if (headerrowindex < 0 || !needheader)

{

headerrow = sheet.getrow(0) as xssfrow;

cellcount = headerrow.lastcellnum;

for (int i = headerrow.firstcellnum; i <= cellcount; i++)

{

datacolumn column = new datacolumn(convert.tostring(i));

dt.columns.add(column);

}

}

else

{

headerrow = sheet.getrow(headerrowindex) as xssfrow;

cellcount = headerrow.lastcellnum;

for (int i = headerrow.firstcellnum; i <= cellcount; i++)

{

icell cell = headerrow.getcell(i);

if (cell == null)

{

break;//到最后 跳出循環(huán)

}

else

{

datacolumn column = new datacolumn(headerrow.getcell(i).tostring());

dt.columns.add(column);

}

}

}

int rowcount = sheet.lastrownum;

for (int i = headerrowindex + 1; i <= sheet.lastrownum; i++)

{

xssfrow row = null;

if (sheet.getrow(i) == null)

{

row = sheet.createrow(i) as xssfrow;

}

else

{

row = sheet.getrow(i) as xssfrow;

}

datarow dtrow = dt.newrow();

for (int j = row.firstcellnum; j <= cellcount; j++)

{

if (row.getcell(j) != null)

{

switch (row.getcell(j).celltype)

{

case celltype.boolean:

dtrow[j] = convert.tostring(row.getcell(j).booleancellvalue);

break;

case celltype.error:

dtrow[j] = erroreval.gettext(row.getcell(j).errorcellvalue);

break;

case celltype.formula:

switch (row.getcell(j).cachedformularesulttype)

{

case celltype.boolean:

dtrow[j] = convert.tostring(row.getcell(j).booleancellvalue);

break;

case celltype.error:

dtrow[j] = erroreval.gettext(row.getcell(j).errorcellvalue);

break;

case celltype.numeric:

dtrow[j] = convert.tostring(row.getcell(j).numericcellvalue);

break;

case celltype.string:

string strformula = row.getcell(j).stringcellvalue;

if (strformula != null && strformula.length > 0)

{

dtrow[j] = strformula.tostring();

}

else

{

dtrow[j] = null;

}

break;

default:

dtrow[j] = ;

break;

}

break;

case celltype.numeric:

if (dateutil.iscelldateformatted(row.getcell(j)))

{

dtrow[j] = datetime.fromoadate(row.getcell(j).numericcellvalue);

}

else

{

dtrow[j] = convert.todouble(row.getcell(j).numericcellvalue);

}

break;

case celltype.string:

string str = row.getcell(j).stringcellvalue;

if (!string.isnullorempty(str))

{

dtrow[j] = convert.tostring(str);

}

else

{

dtrow[j] = null;

}

break;

default:

dtrow[j] = ;

break;

}

}

}

dt.rows.add(dtrow);

}

}

catch (exception)

{

return null;

}

return dt;

}

///

/// 將datatable中的數(shù)據(jù)導入excel文件中

///

///

///

public static void datatable2excel(datatable dt, string file, string sheetname)

{

iworkbook workbook = new xssfworkbook();

isheet sheet = workbook.createsheet(sheetname);

irow header = sheet.createrow(0);

for (int i = 0; i < dt.columns.count; i++)

{

icell cell = header.createcell(i);

cell.setcellvalue(dt.columns[i].columnname);

}

//數(shù)據(jù)

for (int i = 0; i < dt.rows.count; i++)

{

irow row = sheet.createrow(i + 1);

for (int j = 0; j < dt.columns.count; j++)

{

icell cell = row.createcell(j);

cell.setcellvalue(dt.rows[i][j].tostring());

}

}

memorystream stream = new memorystream();

workbook.write(stream);

byte[] buffer = stream.toarray();

using (filestream fs = new filestream(file, filemode.create, fileaccess.write))

{

fs.write(buffer, 0, buffer.length);

fs.flush();

}

}

///

/// 獲取單元格類型

///

///

///

private static object getvaluetype(xssfcell cell)

{

if (cell == null)

{

return null;

}

switch (cell.celltype)

{

case celltype.blank:

return null;

case celltype.boolean:

return cell.booleancellvalue;

case celltype.error:

return cell.errorcellvalue;

case celltype.numeric:

return cell.numericcellvalue;

case celltype.string:

return cell.stringcellvalue;

case celltype.formula:

default:

return = + cell.stringcellvalue;

}

}

}

}

更多信息請查看IT技術專欄