引言

项目中常用到将数据导入excel，将excel中的数据导入数据库的功能，曾经也查找过相关的内容，将曾经用过的方案总结一下。

方案一

npoi

npoi 是 poi 项目的 .net 版本。poi是一个开源的java读写excel、word等微软ole2组件文档的项目。

使用 npoi 你就可以在没有安装 office 或者相应环境的机器上对 word/excel 文档进行读写。npoi是构建在poi 3.x版本之上的，它可以在没有安装office的情况下对word/excel文档进行读写操作。

优势

(一)传统操作excel遇到的问题：

1、如果是.net，需要在服务器端装office，且及时更新它，以防漏洞，还需要设定权限允许.net访问com+，如果在导出过程中出问题可能导致服务器宕机。

2、excel会把只包含数字的列进行类型转换，本来是文本型的，excel会将其转成数值型的，比如编号000123会变成123。

3、导出时，如果字段内容以“-”或“=”开头，excel会把它当成公式进行，会报错。

4、excel会根据excel文件前8行分析数据类型，如果正好你前8行某一列只是数字，那它会认为该列为数值型，自动将该列转变成类似1.42702e+17格式，日期列变成包含日期和数字的。

(二)使用npoi的优势

1、您可以完全免费使用该框架

2、包含了大部分excel的特性(单元格样式、数据格式、公式等等)

3、专业的技术支持服务(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读写库比较弱的方面。通常，读入并理解结构远比导出来得复杂，因为导入你必须假设一切情况都是可能的，而生成你只要保证满足你自己需求就可以了，如果把导入需求和生成需求比做两个集合，那么生成需求通常都是导入需求的子集，这一规律不仅体现在excel读写库中，也体现在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

基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发

npoi组成部分

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

1、读写ole2文档

2、读写docummentsummaryinformation和summaryinformation

3、基于littleendian的字节读写

4、读写excel biff格式

5、识别并读写excel biff中的常见record，如rowrecord, stylerecord, extendedformatrecord

6、支持设置单元格的高、宽、样式等

7、支持调用部分excel内建函数，比如说sum, countif以及计算符号

8、支持在生成的xls内嵌入打印设置，比如说横向/纵向打印、缩放、使用的纸张等。

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

基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发

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对象关系库

(以上内容来自百度百科)从上表可知npoi组件已支持excel2007，记得之前用的时候只支持excel2003。很久没研究过这玩意儿了。

案例

官网地址：http://npoi.codeplex.com/，可以从官网下载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文件中的数据读出到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中的数据导入到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;//到最后 跳出循环

}

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中的数据导入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);

}

//数据

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技术专栏