1.前言相信大家在工作中经常要遇到一些导入导出Execl操作。学习贵在分享，分享使人快乐，园子里的前辈已经有很多好的文章，鄙人也是能力有限，在这里把这些好的文章总结，方便以后再工作中使用。
NPOI：是 POI 项目的 .NET 版本。POI是一个开源的java读写Excel、Word等微软OLE2组件文档的项目。
NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
它不使用Office COM组件(Microsoft.Office.Interop.XXX.dll)，不需要安装Microsoft Office，支持对Office 97-2003的文件格式，功能比较强大。
能够读写几乎所有的Office 97-2003文件格式，至少能够支持Word,Powerpoint, Excel, Visio的格式。
NPOI官方网站【点击这里】
1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。
2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始
3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。
HSSF和XSSF的区别如下：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
HSSFWorkbook对应的就是Excel文件 工作簿，
HSSFSheet对应的就是Excel中sheet 工作表，
HSSFCell对应的就是Excel的单元格，
HSSFRow对应的就是Excel的行
.NET调用NPOI组件导入导出Excel的操作类
此NPOI操作类的优点如下：
（1）支持web及winform从DataTable导出到Excel；
（2）生成速度很快；
（3）准确判断数据类型，不会出现身份证转数值等问题；
（4）如果单页条数大于65535时会新建工作表；
（5）列宽自适应；
2.简单用法namespace导入导出
{publicpartialclassForm1 : Form
{publicForm1()
{
InitializeComponent();
}PRivatevoidbtnInput_Click(objectsender, EventArgs e)
{#region导入到DataTableusing(FileStream stream = File.OpenRead("huangjinfeng.xls"))
{
IWorkbook workbook=newHSSFWorkbook(stream);
ISheet sheet= workbook.GetSheet("员工捐款信息表");
DataTable table=newDataTable();
IRow headerRow= sheet.GetRow(0);intcellCount =headerRow.LastCellNum;introwCount =sheet.LastRowNum;for(inti = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column=newDataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}for(inti = (sheet.FirstRowNum +1); i <= rowCount; i++)
{
IRow row=sheet.GetRow(i);
DataRow dataRow=table.NewRow();if(row !=null)
{for(intj = row.FirstCellNum; j < cellCount; j++)
{if(row.GetCell(j) !=null)
dataRow[j]=row.GetCell(j);
}
}
table.Rows.Add(dataRow);
}this.dataGridView1.DataSource =table;
MessageBox.Show("导入数据成功");
}#endregion#region导入到数据库//using (FileStream stream = File.OpenRead("huangjinfeng.xls"))//{//string sql="INSERT INTO [dbo].[DonationDetail]([dUserName],[dcID],[dAmount],[dDate],[disdelete],[dCreateTime])";//RenderToDb(stream,sql);//MessageBox.Show("SQL");//}#endregion}privatevoidbtnOut_Click(objectsender, EventArgs e)
{
DonationEntities2 db=newDonationEntities2();varlists = db.CreateObjectSet<DonationDetail>().Where(c => c.disdelete ==0).ToList();#region自由导出HSSFWorkbook workbook=newHSSFWorkbook();//2.创建工作表ISheet sheet = workbook.CreateSheet("员工捐款信息表");
IRow rowHeader= sheet.CreateRow(0);
rowHeader.CreateCell(0, CellType.String).SetCellValue("DId");
rowHeader.CreateCell(1, CellType.String).SetCellValue("员工姓名");
rowHeader.CreateCell(2, CellType.String).SetCellValue("DcId");
rowHeader.CreateCell(3, CellType.String).SetCellValue("捐款金额");
rowHeader.CreateCell(4, CellType.String).SetCellValue("捐款日期");
rowHeader.CreateCell(5, CellType.String).SetCellValue("是否删除");
rowHeader.CreateCell(6, CellType.String).SetCellValue("创建日期");for(inti =0; i < lists.Count; i++)
{
IRow row= sheet.CreateRow(i +1);//为指定的行添加列row.CreateCell(0, CellType.String).SetCellValue(lists[i].dId);
row.CreateCell(1, CellType.String).SetCellValue(lists[i].dUserName);
row.CreateCell(2, CellType.String).SetCellValue(lists[i].dcID);
row.CreateCell(3, CellType.String).SetCellValue(lists[i].dAmount.ToString());
row.CreateCell(4, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dDate.ToString()));
row.CreateCell(5, CellType.String).SetCellValue(lists[i].disdelete);
row.CreateCell(6, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dCreateTime.ToString()));
}//使用文件流做数据的写入using(FileStream fss =newFileStream("huangjinfeng.xls", FileMode.Create))
{
workbook.Write(fss);
}
MessageBox.Show("导出数据成功");#endregion}
#region导入到数据库publicstaticvoidRenderToDb(Stream excelFileStream,stringinsertSql)
{using(excelFileStream)
{
IWorkbook workbook=newHSSFWorkbook(excelFileStream);
ISheet sheet= workbook.GetSheetAt(0);//取第一个工作表StringBuilder builder =newStringBuilder();
IRow headerRow= sheet.GetRow(0);//第一行为标题行intcellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCellsintrowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1for(inti = (sheet.FirstRowNum +1); i <= rowCount; i++)
{
IRow row=sheet.GetRow(i);if(row !=null)
{
builder.Append(insertSql);
builder.Append("values (");for(intj = row.FirstCellNum; j < cellCount; j++)
{
builder.AppendFormat("'{0}',", row.GetCell(j)).Replace("'","''");
}
builder.Length= builder.Length -1;
builder.Append(");");
}if((i %50==0|| i == rowCount) && builder.Length >0)
{//每50条记录一次批量插入到数据库//rowAffected += dbAction(builder.ToString());SqlHelper.ExecuteNonQuery(builder.ToString());
builder.Length=0;
}
}
}
}#endregion
RenderToDb(Stream excelFileStream, string insertSql)
#region是否有数据///<summary>///是否有数据///</summary>///<param name="excelFileStream"></param>///<returns></returns>publicstaticboolHasData(Stream excelFileStream)
{using(excelFileStream)
{
IWorkbook workbook=newHSSFWorkbook(excelFileStream);if(workbook.NumberOfSheets >0)
{
ISheet sheet= workbook.GetSheetAt(0);returnsheet.PhysicalNumberOfRows >0;
}
}returnfalse;
}#endregion
HasData(Stream excelFileStream)
}
}
3.项目中的Execl导入导出工作中我一般主要是用到的MVC，在这里就说说我们项目中一般的处理过程。先看看我自己写的一个NPOIBase父类。
publicclassNPOIBase : ActionResult
{publicIWorkbook _workbook {get;set; }publicISheet _sheet {get;set; }publicICellStyle _titleStyle {get;set; }publicICellStyle _leftStyle {get;set; }publicICellStyle _centerStyle {get;set; }publicICellStyle _rightStyle {get;set; }publicICellStyle _headStyle {get;set; }publicICellStyle _leftborderStyle {get;set; }publicICellStyle _rightborderStyle {get;set; }publicICellStyle _noneRightBorderStyle {get;set; }publicICellStyle _noneLeftBorderStyle {get;set; }publicICellStyle _noneLeftAndRightBorderStyle {get;set; }publicICellStyle _borderStyle {get;set; }
publicoverridevoidExecuteResult(ControllerContext context)
{
}
publicvoidIniNPOI(boolisHeadBorder =false,stringsheetName ="")
{
_workbook=newHSSFWorkbook();
_sheet=string.IsNullOrWhiteSpace(sheetName) ?_workbook.CreateSheet() : _workbook.CreateSheet(sheetName);
IniStyle(isHeadBorder);
}
publicvoidIniStyle(boolisHeadBorder =false)
{
IFont font12=_workbook.CreateFont();
font12.FontHeightInPoints=12;
font12.Boldweight=700;
_titleStyle=_workbook.CreateCellStyle();
_titleStyle.Alignment=HorizontalAlignment.Center;
_titleStyle.VerticalAlignment=VerticalAlignment.Top;
_titleStyle.SetFont(font12);
_leftStyle=_workbook.CreateCellStyle();
_leftStyle.Alignment=HorizontalAlignment.Left;
_leftStyle.VerticalAlignment=VerticalAlignment.Top;
_centerStyle=_workbook.CreateCellStyle();
_centerStyle.Alignment=HorizontalAlignment.Center;
_centerStyle.VerticalAlignment=VerticalAlignment.Top;
_rightStyle=_workbook.CreateCellStyle();
_rightStyle.Alignment=HorizontalAlignment.Right;
_rightStyle.VerticalAlignment=VerticalAlignment.Top;
_headStyle=_workbook.CreateCellStyle();
_headStyle.Alignment=HorizontalAlignment.Center;
_headStyle.VerticalAlignment=VerticalAlignment.Top;if(isHeadBorder)
{
_headStyle.BorderBottom=BorderStyle.Thin;
_headStyle.BorderLeft=BorderStyle.Thin;
_headStyle.BorderRight=BorderStyle.Thin;
_headStyle.BorderTop=BorderStyle.Thin;
}
_leftborderStyle=_workbook.CreateCellStyle();
_leftborderStyle.Alignment=HorizontalAlignment.Left;
_leftborderStyle.VerticalAlignment=VerticalAlignment.Top;
_leftborderStyle.BorderBottom=BorderStyle.Thin;
_leftborderStyle.BorderLeft=BorderStyle.Thin;
_leftborderStyle.BorderRight=BorderStyle.Thin;
_leftborderStyle.BorderTop=BorderStyle.Thin;
_rightborderStyle=_workbook.CreateCellStyle();
_rightborderStyle.Alignment=HorizontalAlignment.Right;
_rightborderStyle.VerticalAlignment=VerticalAlignment.Top;
_rightborderStyle.BorderBottom=BorderStyle.Thin;
_rightborderStyle.BorderLeft=BorderStyle.Thin;
_rightborderStyle.BorderRight=BorderStyle.Thin;
_rightborderStyle.BorderTop=BorderStyle.Thin;
_noneRightBorderStyle=_workbook.CreateCellStyle();
_noneRightBorderStyle.Alignment=HorizontalAlignment.Left;
_noneRightBorderStyle.VerticalAlignment=VerticalAlignment.Top;
_noneRightBorderStyle.BorderBottom=BorderStyle.Thin;
_noneRightBorderStyle.BorderLeft=BorderStyle.Thin;
_noneRightBorderStyle.BorderTop=BorderStyle.Thin;
_noneLeftBorderStyle=_workbook.CreateCellStyle();
_noneLeftBorderStyle.Alignment=HorizontalAlignment.Right;
_noneLeftBorderStyle.VerticalAlignment=VerticalAlignment.Top;
_noneLeftBorderStyle.BorderBottom=BorderStyle.Thin;
_noneLeftBorderStyle.BorderRight=BorderStyle.Thin;
_noneLeftBorderStyle.BorderTop=BorderStyle.Thin;
_noneLeftAndRightBorderStyle=_workbook.CreateCellStyle();
_noneLeftAndRightBorderStyle.Alignment=HorizontalAlignment.Center;
_noneLeftAndRightBorderStyle.VerticalAlignment=VerticalAlignment.Top;
_noneLeftAndRightBorderStyle.BorderBottom=BorderStyle.Thin;
_noneLeftAndRightBorderStyle.BorderTop=BorderStyle.Thin;
_borderStyle=_workbook.CreateCellStyle();
_borderStyle.Alignment=HorizontalAlignment.Center;
_borderStyle.VerticalAlignment=VerticalAlignment.Top;
_borderStyle.BorderBottom=BorderStyle.Thin;
_borderStyle.BorderLeft=BorderStyle.Thin;
_borderStyle.BorderRight=BorderStyle.Thin;
_borderStyle.BorderTop=BorderStyle.Thin;
IFont font=_workbook.CreateFont();
font.FontHeightInPoints=10;
font.Boldweight=700;
_headStyle.SetFont(font);
}
IniStyle
publicvoidFillHeadCell(IRow row,intcolIndex,stringvalue, ICellStyle cellStyle =null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress =null)
{if(_sheet ==null|| row ==null)return;if(cellStyle ==null) cellStyle =_headStyle;
FillCell(row, colIndex, value, cellStyle, mergedCellRangeAddress);
_sheet.SetColumnWidth(colIndex, (Encoding.Default.GetBytes(value.Trim()).Length+4) *256);
}
publicvoidFillCell(IRow row,intcolIndex,stringvalue, ICellStyle cellStyle =null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress =null)
{if(_sheet ==null|| row ==null)return;
ICell titleSum=row.CreateCell(colIndex);
titleSum.SetCellValue(value);if(cellStyle !=null) titleSum.CellStyle =cellStyle;elseif(_centerStyle !=null) titleSum.CellStyle =_centerStyle;if(mergedCellRangeAddress !=null) _sheet.AddMergedRegion(mergedCellRangeAddress);
}
publicvoidFillCell(IRow row,intcolIndex,doublevalue, ICellStyle cellStyle =null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress =null)
{if(_sheet ==null|| row ==null)return;
ICell titleSum=row.CreateCell(colIndex);
titleSum.SetCellValue(value);if(cellStyle !=null) titleSum.CellStyle =cellStyle;elseif(_centerStyle !=null) titleSum.CellStyle =_centerStyle;if(mergedCellRangeAddress !=null) _sheet.AddMergedRegion(mergedCellRangeAddress);
}
publicvoidResponSEOutPutExcelStream(stringfildName)
{if(string.IsNullOrWhiteSpace(fildName)) fildName = DateTime.Now.ToString("yyyyMMddHHmmss.xls");if(fildName.ToLower().IndexOf(".xls") == -1) fildName +=".xls";
HttpContext.Current.Response.ContentType="application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition",string.Format("attachment;filename={0}", fildName));
HttpContext.Current.Response.Clear();
MemoryStream file=newMemoryStream();
_workbook.Write(file);
file.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.End();
}
publicvoidSetPrint(boolisLandscape =false,boolisFitToPage =false,doubletopMargin =0,doublerightMargin =0,
doublebottomMargin =0.5,doubleleftMargin =0,shortscale =100)
{
_sheet.PrintSetup.Landscape=isLandscape;
_sheet.SetMargin(MarginType.TopMargin, topMargin);
_sheet.SetMargin(MarginType.RightMargin, rightMargin);
_sheet.SetMargin(MarginType.LeftMargin, leftMargin);
_sheet.SetMargin(MarginType.BottomMargin, bottomMargin);
_sheet.PrintSetup.PaperSize=9;
_sheet.PrintSetup.Scale=scale;
_sheet.FitToPage=isFitToPage;if(isFitToPage)
{
_sheet.PrintSetup.FitWidth=1;
_sheet.PrintSetup.FitHeight=0;
}
}
}
这个由于父类是继承的ActionResult，我们用起来就比较方便，在Action中，直接使用就可以了。代码示例如下...
publicclassBaseMaterialsExcelResult: NPOIBase
{string[] __headers =null;
IList<BaseMaterials>__BaseMaterialsList;publicBaseMaterialsExcelResult(IList<BaseMaterials>BaseMaterialsList)
{
__BaseMaterialsList=BaseMaterialsList;
__headers=newstring[] {"序号","材料","型号","推荐供应商","出库数量","入库数量","结存"};
}
publicoverridevoidExecuteResult(ControllerContext context)
{if(__BaseMaterialsList ==null|| __BaseMaterialsList.Count() ==0)return;
IniNPOI();introwIndex =0;foreach(varitemin__BaseMaterialsList)
{#region新建表，填充列头，样式intcolIndex =0;if(rowIndex ==65535|| rowIndex ==0)
{if(rowIndex !=0)
_sheet=_workbook.CreateSheet();
IRow headerRow=_sheet.CreateRow(rowIndex);foreach(varheadin__headers)
FillHeadCell(headerRow, colIndex++, head);
rowIndex=1;
}#endregion#region填充内容IRow dataRow=_sheet.CreateRow(rowIndex);
colIndex=0;
FillCell(dataRow, colIndex++, rowIndex);
FillCell(dataRow, colIndex++, item.Name);
FillCell(dataRow, colIndex++, item.Type);
FillCell(dataRow, colIndex++, item.ProviderName);
FillCell(dataRow, colIndex++, item.OutStorageCount.ToString());
FillCell(dataRow, colIndex++, item.StorageCount.ToString());
FillCell(dataRow, colIndex++, item.StockCount.ToString());#endregionrowIndex++;
}
_sheet.CreateFreezePane(1,1,1,1);
ResponseOutPutExcelStream("BaseMaterials.xls");
}
}
控制器中的代码如下:
publicclassHomeController : Controller
{
BluedonStockEntities db=newBluedonStockEntities();publicList<BaseMaterials>GetList()
{returndb.CreateObjectSet<BaseMaterials>().Where(c =>true).ToList();
}publicActionResult Index()
{varlist =GetList();returnView(list);
}publicActionResult Execl()
{varlist =GetList();returnnewBaseMaterialsExcelResult(list);
}
}
4.问题总结。在实例化了一个WorkBook之后，最好添加一个sheet，虽然在最新版的Npoi.net中自动添加了，但是遇到迁移到原来版本就会出现问题。所以我建议还是最少添加一个sheet在从单元格取值时要注意单元格的类型，一定要用对应的类型的方法来取单元格中的对应类型的值，如果不确定，那只能是强制转换成为string类型，毕竟string类型是excel中其他类型都可以转换过来的在获取sheet中的某一行或者某一行的某一个单元格的时候，还要注意就是一定要确保创建了该行，并且取单元格还要确保创建了单元格，否则会报Null reference not to object 这个我们经常会看到的异常信息。在外层一定要加上try捕获异常合并单元格是sheet的工作，因此需要获取对应的sheet，然后调用其AddMergedRegion方法合并单元格，在合并单元格的时候，我们不需要确保该单元格已经存在或创建。在为单元格设置样式的过程中，我们会发现所有和样式相关的类的创建都是通过workBook.Create(Font)..这种方式来执行的，我们不可以直接new一个类的实例。如果前面的工作都已经做好，需要把内存中的excel表写到硬盘上时，需要调用workBook.write()方法，传入一个文件流进行创建。在这里有可能会出现一个问题，就是你要创建的文件你已经打开了，这时程序就会出现异常，因此我们在调试程序的时候一定要记得打开了excel文件以后要关闭最后需要注意的就是文件流，在我们把excel写到硬盘上以后，要显式的调用其close（）方法关闭文件流。因为如果不关闭文件流的话，以后就会出现无法重新创建该文件的错误，并且会提示 某文件正由另一进程使用，因此该进程无法访问此文件。简单用法的源码【点击下载】
MVC版的源码【点击下载】
感谢你的支持，为我点个赞吧！
