NPOI使用手册3

转自链接

3.项目实践

3.1基于.xls模板生成Excel文件

3.2生成九九乘法表

3.3生成一张工资单

3.4从xls文件中抽取文本

3.5巧妙使用ExcelChart

3.6导入Excel文件

NPOI 1.2教程– 3.1基于.xls模板生成Excel文件

作者：Tony Qu

NPOI官方网站：http://npoi.codeplex.com/

NPOI QQ交流群: 78142590

尽管NPOI能够从头开始生成Excel文件，但在实际生产环境中有很多现成的表格，我们不可能没事就去从头开始生成一个Excel，更多时候我们更愿意选择比较偷懒的方法——那就是用模板文件。NPOI一大特色之一就是能够轻松读取Office Excel 97-2003的格式，即使里面有NPOI不支持的VBA宏、图表以及Pivot表之类的高级记录，NPOI也能够保证不丢失数据（说实话，要完全能够识别所有的Excel内部记录几乎是不可能的，更何况如今又多出了Office Excel 2007binary file，即.xlsb）。

现在我们转入正题，出于演示目的，我做了一个简单的销售量表，里面应用了文字颜色、背景色、文本居中、公式、千分位分隔符、边框等效果，当然实际的生产环境里可能还有更加复杂的Excel模板。如下图

我们的程序就是要填充12个月的销售量，Total能够自动根据填充的值计算出总量。

（这里要提一下，以往如果我们用HTML方式输出xls，我们必须在服务器端做Total计算，并且这个值在下载后永远都是静态的，没有公式，即使用户要修改里面的数据，总值也不会改变。这也是为什么NPOI一直提倡生成真正的Excel文件。）

代码其实很简单：

//read thetemplate via FileStream, it is suggested to use FileAccess.Read to prevent filelock.

//book1.xlsis an Excel-2007-generated file, so some new unknown BIFF records are added.

FileStreamfile =newFileStream(@"template/book1.xls", FileMode.Open,FileAccess.Read);

HSSFWorkbook hssfworkbook =

newHSSFWorkbook(file);

HSSFSheet sheet1 = hssfworkbook.GetSheet(

"Sheet1");

sheet1.GetRow(1).GetCell(1).SetCellValue(200200);

sheet1.GetRow(2).GetCell(1).SetCellValue(300);

sheet1.GetRow(3).GetCell(1).SetCellValue(500050);

sheet1.GetRow(4).GetCell(1).SetCellValue(8000);

sheet1.GetRow(5).GetCell(1).SetCellValue(110);

sheet1.GetRow(6).GetCell(1).SetCellValue(100);

sheet1.GetRow(7).GetCell(1).SetCellValue(200);

sheet1.GetRow(8).GetCell(1).SetCellValue(210);

sheet1.GetRow(9).GetCell(1).SetCellValue(2300);

sheet1.GetRow(10).GetCell(1).SetCellValue(240);

sheet1.GetRow(11).GetCell(1).SetCellValue(180123);

sheet1.GetRow(12).GetCell(1).SetCellValue(150);

//Force excel to recalculate all the formulawhile open

sheet1.ForceFormulaRecalculation=true;

FileStreamfile = new FileStream(@"test.xls", FileMode.Create);

hssfworkbook.Write(file);

file.Close();

首先打开模板文件时要使用FileAccess.Read，这样可以保证文件不被占用。

这里的ForceFormulaRecalculation是强制要求Excel在打开时重新计算的属性，在拥有公式的xls文件中十分有用，大家使用时可别忘了设。

是不是比你想象的简单？你甚至不用去了解它是在何时读取文件内容的，对于NPOI的使用者来说基本上和读取普通文件没有什么两样。

最终生成的效果如下所示：

发觉没，就连千分位分隔符也都保留着，一切就像人工填写的一样。

本范例完整代码请见NPOI.Examples中的GenerateXlsFromXlsTemplate项目。

3.2用NPOI操作EXCEL－－生成九九乘法表

还记得小学时候学的九九乘法表吗？这节我们一起学习利用NPOI通过C#代码生成一张Excel的九九乘法表。要生成九九乘法表，循环肯定是少不了的，如下：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row;

HSSFCell cell;

for(introwIndex =0; rowIndex <9; rowIndex++)

{

row = sheet1.CreateRow(rowIndex);

for(intcolIndex =0; colIndex <= rowIndex; colIndex++)

{

cell = row.CreateCell(colIndex);

cell.SetCellValue(String.Format(

"{0}*{1}={2}", rowIndex +1, colIndex +1, (rowIndex +1) * (colIndex +1)));

}

}

代码其实很简单，就是循环调用cell.SetCellValue(str)写入9行数据，每一行写的单元格数量随行数递增。执行完后生成的Excel样式如下：

完整的代码如下：

Code

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingNPOI.HSSF.UserModel;

usingSystem.IO;

usingNPOI.HPSF;

namespaceTimesTables

{

publicclassProgram

{

staticHSSFWorkbook hssfworkbook;

staticvoidMain(string[] args)

{

InitializeWorkbook();

HSSFSheet sheet1 = hssfworkbook.CreateSheet(

"Sheet1");

HSSFRow row;

HSSFCell cell;

for(introwIndex =0; rowIndex <9; rowIndex++)

{

row = sheet1.CreateRow(rowIndex);

for(intcolIndex =0; colIndex <= rowIndex; colIndex++)

{

cell = row.CreateCell(colIndex);

cell.SetCellValue(String.Format(

"{0}*{1}={2}", rowIndex +1, colIndex +1, (rowIndex +1) * (colIndex +1)));

}

}

WriteToFile();

}

staticvoidWriteToFile()

{

//Write the stream data of workbook to the root directory

FileStream file =newFileStream(@"test.xls", FileMode.Create);

hssfworkbook.Write(file);

file.Close();

}

staticvoidInitializeWorkbook()

{

hssfworkbook =

newHSSFWorkbook();

//create a entry of DocumentSummaryInformation

DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

dsi.Company =

"NPOI Team";

hssfworkbook.DocumentSummaryInformation = dsi;

//create a entry of SummaryInformation

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

si.Subject =

"NPOI SDK Example";

hssfworkbook.SummaryInformation = si;

}

}

}

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingNPOI.HSSF.UserModel;

usingSystem.IO;

usingNPOI.HPSF;

namespaceTimesTables

{

publicclassProgram

{

staticHSSFWorkbook hssfworkbook;

staticvoidMain(string[] args)

{

InitializeWorkbook();

HSSFSheet sheet1 = hssfworkbook.CreateSheet(

"Sheet1");

HSSFRow row;

HSSFCell cell;

for(introwIndex =0; rowIndex <9; rowIndex++)

{

row = sheet1.CreateRow(rowIndex);

for(intcolIndex =0; colIndex <= rowIndex; colIndex++)

{

cell = row.CreateCell(colIndex);

cell.SetCellValue(String.Format(

"{0}*{1}={2}", rowIndex +1, colIndex +1, (rowIndex +1) * (colIndex +1)));

}

}

WriteToFile();

}

staticvoidWriteToFile()

{

//Write the stream data of workbook to the root directory

FileStream file =newFileStream(@"test.xls", FileMode.Create);

hssfworkbook.Write(file);

file.Close();

}

staticvoidInitializeWorkbook()

{

hssfworkbook =

newHSSFWorkbook();

//create a entry of DocumentSummaryInformation

DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

dsi.Company =

"NPOI Team";

hssfworkbook.DocumentSummaryInformation = dsi;

//create a entry of SummaryInformation

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

si.Subject =

"NPOI SDK Example";

hssfworkbook.SummaryInformation = si;

}

}

}

3.3用NPOI操作EXCEL－－生成一张工资单

这一节，我们将综合NPOI的常用功能（包括创建和填充单元格、合并单元格、设置单元格样式和利用公式），做一个工资单的实例。先看创建标题行的代码：

//写标题文本HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFCell cellTitle = sheet1.CreateRow(

0).CreateCell(0);

cellTitle.SetCellValue(

"XXX公司2009年10月工资单");

//设置标题行样式HSSFCellStyle style = hssfworkbook.CreateCellStyle();

style.Alignment = HSSFCellStyle.ALIGN_CENTER;

HSSFFont font = hssfworkbook.CreateFont();

font.FontHeight =

20*20;

style.SetFont(font);

cellTitle.CellStyle = style;

//合并标题行sheet1.AddMergedRegion(newRegion(0,0,1,6));

其中用到了我们前面讲的设置单元格样式和合并单元格等内容。接下来我们循环创建公司每个员工的工资单：

DataTable dt=GetData();

HSSFRow row;

HSSFCell cell;

HSSFCellStyle celStyle=getCellStyle();

HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();

HSSFClientAnchor anchor;

HSSFSimpleShape line;

introwIndex;

for(inti =0; i < dt.Rows.Count; i++)

{

//表头数据rowIndex =3* (i +1);

row = sheet1.CreateRow(rowIndex);

cell = row.CreateCell(

0);

cell.SetCellValue(

"姓名");

cell.CellStyle = celStyle;

cell = row.CreateCell(

1);

cell.SetCellValue(

"基本工资");

cell.CellStyle = celStyle;

cell = row.CreateCell(

2);

cell.SetCellValue(

"住房公积金");

cell.CellStyle = celStyle;

cell = row.CreateCell(

3);

cell.SetCellValue(

"绩效奖金");

cell.CellStyle = celStyle;

cell = row.CreateCell(

4);

cell.SetCellValue(

"社保扣款");

cell.CellStyle = celStyle;

cell = row.CreateCell(

5);

cell.SetCellValue(

"代扣个税");

cell.CellStyle = celStyle;

cell = row.CreateCell(

6);

cell.SetCellValue(

"实发工资");

cell.CellStyle = celStyle;

DataRow dr = dt.Rows[i];

//设置值和计算公式row = sheet1.CreateRow(rowIndex +1);

cell = row.CreateCell(

0);

cell.SetCellValue(dr[

"FName"].ToString());

cell.CellStyle = celStyle;

cell = row.CreateCell(

1);

cell.SetCellValue((

double)dr["FBasicSalary"]);

cell.CellStyle = celStyle;

cell = row.CreateCell(

2);

cell.SetCellValue((

double)dr["FAccumulationFund"]);

cell.CellStyle = celStyle;

cell = row.CreateCell(

3);

cell.SetCellValue((

double)dr["FBonus"]);

cell.CellStyle = celStyle;

cell = row.CreateCell(

4);

cell.SetCellFormula(String.Format(

"$B{0}*0.08",rowIndex+2));

cell.CellStyle = celStyle;

cell = row.CreateCell(

5);

cell.SetCellFormula(String.Format(

"SUM($B{0}:$D{0})*0.1",rowIndex+2));

cell.CellStyle = celStyle;

cell = row.CreateCell(

6);

cell.SetCellFormula(String.Format(

"SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));

cell.CellStyle = celStyle;

//绘制分隔线sheet1.AddMergedRegion(newRegion(rowIndex+2,0, rowIndex+2,6));

anchor =

newHSSFClientAnchor(0,125,1023,125,0, rowIndex +2,6, rowIndex +2);

line = patriarch.CreateSimpleShape(anchor);

line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;

line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;

}

其中为了文件打印为单元格增加了黑色边框的样式（如果不设置边框样式，打印出来后是没有边框的）。另外，注意循环过程中excel中的行号随数据源中的行号变化处理。完整代码如下：

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingNPOI.HSSF.UserModel;

usingSystem.IO;

usingNPOI.HPSF;

usingNPOI.HSSF.Util;

usingSystem.Data;

namespacePayroll

{

publicclassProgram

{

staticHSSFWorkbook hssfworkbook;

staticvoidMain(string[] args)

{

InitializeWorkbook();

//写标题文本HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFCell cellTitle = sheet1.CreateRow(

0).CreateCell(0);

cellTitle.SetCellValue(

"XXX公司2009年10月工资单");

//设置标题行样式HSSFCellStyle style = hssfworkbook.CreateCellStyle();

style.Alignment = HSSFCellStyle.ALIGN_CENTER;

HSSFFont font = hssfworkbook.CreateFont();

font.FontHeight =

20*20;

style.SetFont(font);

cellTitle.CellStyle = style;

//合并标题行sheet1.AddMergedRegion(newRegion(0,0,1,6));

DataTable dt=GetData();

HSSFRow row;

HSSFCell cell;

HSSFCellStyle celStyle=getCellStyle();

HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();

HSSFClientAnchor anchor;

HSSFSimpleShape line;

introwIndex;

for(inti =0; i < dt.Rows.Count; i++)

{

//表头数据rowIndex =3* (i +1);

row = sheet1.CreateRow(rowIndex);

cell = row.CreateCell(

0);

cell.SetCellValue(

"姓名");

cell.CellStyle = celStyle;

cell = row.CreateCell(

1);

cell.SetCellValue(

"基本工资");

cell.CellStyle = celStyle;

cell = row.CreateCell(

2);

cell.SetCellValue(

"住房公积金");

cell.CellStyle = celStyle;

cell = row.CreateCell(

3);

cell.SetCellValue(

"绩效奖金");

cell.CellStyle = celStyle;

cell = row.CreateCell(

4);

cell.SetCellValue(

"社保扣款");

cell.CellStyle = celStyle;

cell = row.CreateCell(

5);

cell.SetCellValue(

"代扣个税");

cell.CellStyle = celStyle;

cell = row.CreateCell(

6);

cell.SetCellValue(

"实发工资");

cell.CellStyle = celStyle;

DataRow dr = dt.Rows[i];

//设置值和计算公式row = sheet1.CreateRow(rowIndex +1);

cell = row.CreateCell(

0);

cell.SetCellValue(dr[

"FName"].ToString());

cell.CellStyle = celStyle;

cell = row.CreateCell(

1);

cell.SetCellValue((

double)dr["FBasicSalary"]);

cell.CellStyle = celStyle;

cell = row.CreateCell(

2);

cell.SetCellValue((

double)dr["FAccumulationFund"]);

cell.CellStyle = celStyle;

cell = row.CreateCell(

3);

cell.SetCellValue((

double)dr["FBonus"]);

cell.CellStyle = celStyle;

cell = row.CreateCell(

4);

cell.SetCellFormula(String.Format(

"$B{0}*0.08",rowIndex+2));

cell.CellStyle = celStyle;

cell = row.CreateCell(

5);

cell.SetCellFormula(String.Format(

"SUM($B{0}:$D{0})*0.1",rowIndex+2));

cell.CellStyle = celStyle;

cell = row.CreateCell(

6);

cell.SetCellFormula(String.Format(

"SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));

cell.CellStyle = celStyle;

//绘制分隔线sheet1.AddMergedRegion(newRegion(rowIndex+2,0, rowIndex+2,6));

anchor =

newHSSFClientAnchor(0,125,1023,125,0, rowIndex +2,6, rowIndex +2);

line = patriarch.CreateSimpleShape(anchor);

line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;

line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;

}

WriteToFile();

}

staticDataTable GetData()

{

DataTable dt =

newDataTable();

dt.Columns.Add(

"FName",typeof(System.String));

dt.Columns.Add(

"FBasicSalary",typeof(System.Double));

dt.Columns.Add(

"FAccumulationFund",typeof(System.Double));

dt.Columns.Add(

"FBonus",typeof(System.Double));

dt.Rows.Add(

"令狐冲",6000,1000,2000);

dt.Rows.Add(

"任盈盈",7000,1000,2500);

dt.Rows.Add(

"林平之",5000,1000,1500);

dt.Rows.Add(

"岳灵珊",4000,1000,900);

dt.Rows.Add(

"任我行",4000,1000,800);

dt.Rows.Add(

"风清扬",9000,5000,3000);

returndt;

}

staticHSSFCellStyle getCellStyle()

{

HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();

cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;

cellStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;

cellStyle.BorderRight = HSSFCellStyle.BORDER_THIN;

cellStyle.BorderTop = HSSFCellStyle.BORDER_THIN;

returncellStyle;

}

staticvoidWriteToFile()

{

//Write the stream data of workbook to the root directory

FileStream file =newFileStream(@"test.xls", FileMode.Create);

hssfworkbook.Write(file);

file.Close();

}

staticvoidInitializeWorkbook()

{

hssfworkbook =

newHSSFWorkbook();

//create a entry of DocumentSummaryInformation

DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

dsi.Company =

"NPOI Team";

hssfworkbook.DocumentSummaryInformation = dsi;

//create a entry of SummaryInformation

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

si.Subject =

"NPOI SDK Example";

hssfworkbook.SummaryInformation = si;

}

}

}

生成的Excel文件样式如下：

3.4用NPOI操作EXCEL－－从Excel中抽取文本

我们知道，搜索引擎最擅长处理的就是文本，而Excel中的内容并不是以文本方式存储的。那么如果想要搜索引擎爬虫能够抓取到Excel中的内容是比较困难的，除非搜索引擎爬虫对Excel格式进行专门的处理。那么有没有办法解决此问题呢？有,通过NPOI将Excel内容文本化！

如下，有这样一张Excel，如果想让它被搜索引擎收录，常用的方式是以HTML形式展现，但将一个个这样的Excel手工做成HTML页面显然比较麻烦。接下来，我们将提供一种方案，自动将Excel中的内容以HTML形式展现。

其实基本思想也很简单，就是通过NPOI读取每个Cell中的内容，然后以HTML的形式输出。但要保证输出的HTML页面布局与Excel中的一致，还有点小技巧。下面是构造Table的代码：

privateHSSFSheet sht;

protectedString excelContent;

protectedvoidPage_Load(objectsender, EventArgs e)

{

HSSFWorkbook wb =

newHSSFWorkbook(newFileStream(Server.MapPath("App_Data/quotation.xls"), FileMode.Open));

sht = wb.GetSheet(

"Sheet1");

//取行Excel的最大行数introwsCount = sht.PhysicalNumberOfRows;

//为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。

//

为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。intcolsCount = sht.GetRow(0).PhysicalNumberOfCells;

intcolSpan;

introwSpan;

boolisByRowMerged;

StringBuilder table =

newStringBuilder(rowsCount *32);

table.Append(

"");

for(introwIndex =0; rowIndex < rowsCount; rowIndex++)

{

table.Append(

"");

for(intcolIndex =0; colIndex < colsCount; colIndex++)

{

GetTdMergedInfo(rowIndex, colIndex,

outcolSpan,outrowSpan,outisByRowMerged);

//如果已经被行合并包含进去了就不输出TD了。

//

注意被合并的行或列不输出的处理方式不一样，见下面一处的注释说明了列合并后不输出TD的处理方式。if(isByRowMerged)

{

continue;

}

table.Append(

"

if(colSpan >1)

table.Append(

string.Format(" colSpan={0}", colSpan));

if(rowSpan >1)

table.Append(

string.Format(" rowSpan={0}", rowSpan));

table.Append(

">");

table.Append(sht.GetRow(rowIndex).GetCell(colIndex));

//列被合并之后此行将少输出colSpan-1个TD。if(colSpan >1)

colIndex += colSpan -

1;

table.Append(

"");

}

table.Append(

"");

}

table.Append(

"");

this.excelContent = table.ToString();

}

其中用到的GetTdMergedInfo方法代码如下：

//////获取Table某个TD合并的列数和行数等信息。与Excel中对应Cell的合并行数和列数一致。//////行号///列号///TD中需要合并的行数///TD中需要合并的列数///此单元格是否被某个行合并包含在内。如果被包含在内，将不输出TD。///

privatevoidGetTdMergedInfo(introwIndex,intcolIndex,outintcolspan,outintrowspan,outboolisByRowMerged)

{

colspan =

1;

rowspan =

1;

isByRowMerged =

false;

intregionsCuont = sht.NumMergedRegions;

Region region;

for(inti =0; i < regionsCuont; i++)

{

region = sht.GetMergedRegionAt(i);

if(region.RowFrom == rowIndex && region.ColumnFrom == colIndex)

{

colspan = region.ColumnTo - region.ColumnFrom +

1;

rowspan = region.RowTo - region.RowFrom +

1;

return;

}

elseif(rowIndex > region.RowFrom && rowIndex <= region.RowTo && colIndex>=region.ColumnFrom && colIndex<=region.ColumnTo)

{

isByRowMerged =

true;

}

}

}

最后在apsx页面中输出构建好的Table：

<%=excelContent %>

执行效果如下：

我们发现，与Excel中的布局完全一样（这里没有处理单元格的样式，只处理了内容，有兴趣的读者也可以将Excel中单元格的样式也应用在HTML中）。这里为保证布局一致，主要是将Excel中的Region信息解析成Table的colSpan和rowSpan属性，如果对这两个属性不太了解，可以结合以下代码和示例加以了解：

0,00,31,32,02,13,13,2

以上HTML代码对应的Table展现为：

3.5用NPOI操作EXCEL－－巧妙使用Excel Chart

在NPOI中，本身并不支持Chart等高级对象的创建，但通过l模板的方式可以巧妙地利用Excel强大的透视和图表功能，请看以下例子。

首先建立模板文件，定义两列以及指向此区域的名称“sales”：

创建数据表，数据来源填入刚才定义的区域：

最后生成的数据透视表所在Sheet的样式如下：

至此，模板已经建好，另存为“D:\MyProject\NPOIDemo\Chart\Book2.xls”。我们发现，模板就相当于一个“空架子”，仅仅有操作方式没并没有任何数据。下一步，我们往这个“空架子”中填入数据。我们通过如下代码往这个“空架子”中写入数据：

staticvoidMain(string[] args)

{

HSSFWorkbook wb =

newHSSFWorkbook(newFileStream(@"D:\MyProject\NPOIDemo\Chart\Book2.xls", FileMode.Open));

HSSFSheet sheet1 = wb.GetSheet(

"Sheet1");

HSSFRow row = sheet1.CreateRow(

1);

row.CreateCell(

0).SetCellValue("令狐冲");

row.CreateCell(

1).SetCellValue(50000);

row = sheet1.CreateRow(

2);

row.CreateCell(

0).SetCellValue("任盈盈");

row.CreateCell(

1).SetCellValue(30000);

row = sheet1.CreateRow(

3);

row.CreateCell(

0).SetCellValue("风清扬");

row.CreateCell(

1).SetCellValue(80000);

row = sheet1.CreateRow(

4);

row.CreateCell(

0).SetCellValue("任我行");

row.CreateCell(

1).SetCellValue(20000);

//Write the stream data of workbook to the root directory

FileStream file =newFileStream(@"test.xls", FileMode.Create);

wb.Write(file);

file.Close();

}

打开生成的test.xls文件，发现数据已经被填进去了：

再看数据透视表，也有数据了：

总结：Excel有着强大的报表透视和图表功能，而且简单易用，利用NPOI，可以对其进行充分利用。在做图形报表、透视报表时将非常有用！

NPOI实践: .NET导入Excel文件的另一种选择

作者：Tony Qu

官方网站：http://npoi.codeplex.com| NPOI QQ交流群:  78142590

NPOI之所以强大，并不是因为它支持导出Excel，而是因为它支持导入Excel，并能“理解”OLE2文档结构，这也是其他一些Excel读写库比较弱的方面。通常，读入并理解结构远比导出来得复杂，因为导入你必须假设一切情况都是可能的，而生成你只要保证满足你自己需求就可以了，如果把导入需求和生成需求比做两个集合，那么生成需求通常都是导入需求的子集，这一规律不仅体现在Excel读写库中，也体现在pdf读写库中，目前市面上大部分的pdf库仅支持生成，不支持导入。

如果你不相信NPOI能够很好的理解OLE2文档格式，那就去下载POIFS Brower。具体可以参考这篇文章的介绍：Office文件格式解惑。当然单单理解OLE2是不够的，因为Excel文件格式是BIFF，但BIFF是以OLE2为基础的，做个很形象的比喻就是：OLE2相当于磁盘的FAT格式，BIFF相当于文件和文件夹。NPOI负责理解BIFF格式的代码基本都在HSSF命名空间里面。

好了，刚才废话了一会儿，主要是给大家打打基础，现在进入正题。

本文将以DataTable为容器读入某xls的第一个工作表的数据（最近群里面很多人问这个问题）。

在开始之前，我们先来补些基础知识。每一个xls都对应一个唯一的HSSFWorkbook，每一个HSSFWorkbook会有若干个HSSFSheet，而每一个HSSFSheet包含若干HSSFRow（Excel 2003中不得超过65535行），每一个HSSFRow又包含若干个HSSFCell（Excel 2003中不得超过256列）。

为了遍历所有的单元格，我们就得获得某一个HSSFSheet的所有HSSFRow，通常可以用HSSFSheet.GetRowEnumerator()。如果要获得某一特定行，可以直接用HSSFSheet.GetRow(rowIndex)。另外要遍历我们就必须知道边界，有一些属性我们是可以用的，比如HSSFSheet.FirstRowNum（工作表中第一个有数据行的行号）、HSSFSheet.LastRowNum（工作表中最后一个有数据行的行号）、HSSFRow.FirstCellNum（一行中第一个有数据列的列号）、HSSFRow.LastCellNum（一行中最后一个有数据列的列号）。

基础知识基本上补得差不多了，现在开工！

首先我们要准备一个用于打开文件流的函数InitializeWorkbook，由于文件读完后就没用了，所以这里直接用using（养成好习惯，呵呵）。

HSSFWorkbookhssfworkbook;

voidInitializeWorkbook(stringpath)

{

//read the template via FileStream, it is suggested to use FileAccess.Readto prevent file lock.

//book1.xlsis an Excel-2007-generated file, so some new unknown BIFF records are added.

using(FileStream file =newFileStream(path, FileMode.Open,FileAccess.Read))

{

hssfworkbook =newHSSFWorkbook(file);

}

}

接下来我们要开始写最重要的函数ConvertToDataTable，即把HSSF的数据放到一个DataTable中。

HSSFSheetsheet = hssfworkbook.GetSheetAt(0);

System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

while(rows.MoveNext())

{

HSSFRow row = (HSSFRow)rows.Current;

//TODO::Create DataTable row

for(inti = 0; i < row.LastCellNum; i++)

{

HSSFCell cell = row.GetCell(i);

//TODO::set cell value to the cell of DataTables

}

上面的结构大家都应该能看懂吧，无非就是先遍历行，再遍历行中的每一列。这里引出了一个难点，由于Excel的单元格有好几种类型，类型不同显示的东西就不同，具体的类型有布尔型、数值型、文本型、公式型、空白、错误。

publicenumHSSFCellType

{

Unknown = -1,

NUMERIC = 0,

STRING = 1,

FORMULA = 2,

BLANK = 3,

BOOLEAN = 4,

ERROR = 5,

}

这里的HSSFCellType描述了所有的类型，但细心的朋友可能已经发现了，这里没有日期型，这是为什么呢？这是因为Excel底层并没有一定日期型，而是通过数值型来替代，至于如何区分日期和数字，都是由文本显示的样式决定的，在NPOI中则是由HSSFDataFormat来处理。为了能够方便的获得所需要的类型所对应的文本，我们可以使用HSSFCell.ToString()来处理。

于是刚才的代码则变成了这样：

HSSFSheetsheet = hssfworkbook.GetSheetAt(0);

System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

DataTable dt =

newDataTable();

for(intj = 0; j < 5;j++)

{

dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());

}

while(rows.MoveNext())

{

HSSFRow row = (HSSFRow)rows.Current;

DataRow dr = dt.NewRow();

for(inti = 0; i < row.LastCellNum; i++)

{

HSSFCell cell = row.GetCell(i);

if(cell ==null)

{

dr[i] =null;

}

else

{

dr[i] = cell.ToString();

}

}

dt.Rows.Add(dr);

}

是不是很简单，呵呵！

当然，如果你要对某个特定的单元格类型做特殊处理，可以通过判HSSFCell.CellType来解决，比如下面的代码：

switch(cell.CellType)

{

caseHSSFCellType.BLANK:

dr[i] ="[null]";

break;

caseHSSFCellType.BOOLEAN:

dr[i] =cell.BooleanCellValue;

break;

caseHSSFCellType.NUMERIC:

dr[i] =cell.ToString();//This is a trick to get the correct value of the cell.NumericCellValue will return a numeric value no matter the cell value is a dateor a number.

break;

caseHSSFCellType.STRING:

dr[i] =cell.StringCellValue;

break;

caseHSSFCellType.ERROR:

dr[i] = cell.ErrorCellValue;

break;

caseHSSFCellType.FORMULA:

default:

dr[i] ="="+cell.CellFormula;

break;

}

这里只是举个简单的例子。

完整代码下载：http://files.cnblogs.com/tonyqus/ImportXlsToDataTable.zip

注意，此代码中不包括NPOI的assembly，否则文件会很大，所以建议去npoi.codeplex.com下载。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 196,859评论 5赞 462
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,772评论 2赞 374
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 143,956评论 0赞 325
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,729评论 1赞 267
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,611评论 5赞 358
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,441评论 1赞 275
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,850评论 3赞 388
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,485评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,770评论 1赞 293
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,803评论 2赞 314
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,598评论 1赞 328
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,434评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,856评论 3赞 300
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,065评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,365评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,852评论 2赞 343
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,065评论 2赞 338

推荐阅读更多精彩内容

  • NPOI使用手册1
    转自链接 目录 1.认识NPOI 2.使用NPOI生成xls文件 2.1创建基本内容 2.1.1创建Workboo...
    腿毛裤阅读 10,329评论 1赞 3
  • NPOI使用手册2
    转自链接 2.3.5 IF函数 2.3.6 CountIf和SumIf函数 2.3.7 Lookup函数 2.3....
    腿毛裤阅读 12,745评论 0赞 0
  • Apache POI
    Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Ja...
    玩味Orz阅读 2,584评论 0赞 0
  • POI使用指南
    使用首先需要了解他的工作原理 1.POI结构与常用类 (1)创建Workbook和Sheet (2)创建单元格 (...
    长城ol阅读 8,380评论 2赞 25
  • python 操作excel文件的库xlrd，xlwt的使用demo
    注释较为全面，参照注释使用即可。 库的安装: 从官网下载源码后，解压，cmd进到对应目录下，执行 python s...
    LeoceanY桑阅读 2,151评论 0赞 1