在做业务型的软件时，经常需要将某些数据导出，本文介绍了在Winform或Asp.net中使用NPOI（POI 项目的 .NET 版本）来操作Excel文件，而无需安装Office。
首先，需要获取NPOI组件。
其次，让你的网站或类库添加NPOI引用。对于导出导入Excel来说，需要引用的dll文件有：
NPOI.dll，NPOI.HSSF.dll，NPOI.POIFS.dll
最后，在网站或类库中新建一个导出帮助类，例如命名为ExcelHelper.cs
ExcelHelper.cs
public class ExportHelper
{
public static Stream RenderDataTableToExcel(DataTable SourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet();
HSSFRow headerRow = sheet.CreateRow(); // handling header.
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value.
int rowIndex = ; foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
} rowIndex++;
} workbook.Write(ms);
ms.Flush();
ms.Position = ; sheet = null;
headerRow = null;
workbook = null; return ms;
}
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
HSSFSheet sheet = workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
} int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString();
} table.Rows.Add(dataRow);
} ExcelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
}
在方法中调用ExcelHelper:
示例1：导出Excel:
protected void exportExcelFun(){
DataTable dt=new DataTable();
dt=XXXXX;//Use ADO.NET to get Data from DB
MemoryStream ms = ExportHelper.RenderDataTableToExcel(dt) as MemoryStream;
/*情况1：在Asp.NET中，输出文件流，浏览器自动提示下载*/
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=download.xls"));
Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
/*情况2：在Winform中，弹出SaveFileDialog，提示用户选择文件放置位置。*/
string saveFileName = "";
bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = "download";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
if (saveFileName != ""){
try{
FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create);
fs.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);
ms.Close();
ms.Dispose();
fs.Close();
fileSaved = true;
}
catch (Exception ex){
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else{
fileSaved = false;
}
GC.Collect();//强行销毁
if (fileSaved && File.Exists(saveFileName)){
MessageBox.Show("导出成功！", "通知");
}
else {
MessageBox.Show("导出失败！", "通知");
}
}
示例2：导入Excel
protected void importExcelFun(){
/*情况1：在Asp.net网站，一般使用asp:FileUpLoad控件(实际上是一个HTML表单.input:type=file)上传文件*/
if(this.FileUpLoader.HasFile){
DataTable dt=new DataTable();
//设置第1行为标题行，从第二行开始读取数据。标题行作为DataTable表头。
dt=ExcelHelper.RenderDataTableFromExcel(this.FileUpLoader.FileContent,,);
//use dt to do something
}
/*情况2：在Winform中，*/
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Filter = "Excel文件|*.xls";
fileDialog.InitialDirectory = "E:\\";//设置默认打开路径
if (fileDialog.ShowDialog() == DialogResult.OK){
string fileName=fileDialog.FileName;//得到文件所在位置。
// use c# to read Excel File as Steam.
// use ExcelHelper for converting Stream to DataTable.
}
}
有的工具或方式导出的Excel文件非标准文件，只可用于阅读，而无法进行其它业务操作。
而使用NPOI导出的Excel文件均为标准格式，与Windows下人工创建的Excel文件完全一致。
（完）