Excel
[Solution] NPOI操作ExcelNPOI是 POI 项目的 .NET 版本。POI是一个开源的java读写Excel、Word等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
NPOI官方网站：http://npoi.codeplex.com/
下载：Install-Package NPOI
本节内容，介绍NPOI的类型说明，简单演示，DataTable互转，基本样式封装，NPOIHelper
类型说明
NPOI中主要有HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell，对应的接口为IWorkbook、ISheet、IRow、ICell
分别对应Excel文件、工作表、行、列。
简单演示一下写出Excel，读入Excel
//写出 var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("Sheet1");//创建工作表 var row = sheet.CreateRow(0);//在工作表中添加一行 var cell = row.CreateCell(0);//在行中添加一列 cell.SetCellValue("test");//设置列的内容 using (var fs = new FileStream("1.xls", FileMode.Create)) { workbook.Write(fs); } //读取 using (var fs = new FileStream("1.xls", FileMode.Open)) { workbook = new HSSFWorkbook(fs); sheet = workbook.GetSheetAt(0);//获取第一个工作表 row = sheet.GetRow(0);//获取工作表第一行 cell = row.GetCell(0);//获取行的第一列 var value = cell.ToString();//获取列的值 }
封装DataTable转Excel
/// <summary> /// Table转Excel文件流 /// </summary> /// <param name="table"></param> /// <returns></returns> public static MemoryStream TableToExcel(DataTable table) { var ms = new MemoryStream(); using (table) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); var headerRow = sheet.CreateRow(0); //head foreach (DataColumn column in table.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value //body var rowIndex = 1; foreach (DataRow row in table.Rows) { var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); rowIndex++; } AutoSizeColumns(sheet); workbook.Write(ms); ms.Flush(); ms.Position = 0; } return ms; }
Excel转DataTable
/// <summary> /// Excel文件流导出Table /// </summary> /// <param name="excelStream"></param> /// <returns></returns> static DataTable TableToExcel(Stream excelStream) { var table = new DataTable(); var book = new HSSFWorkbook(excelStream); var sheet = book.GetSheetAt(0); var headerRow = sheet.GetRow(0);//第一行为标题行 var cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells var rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 //header for (int i = headerRow.FirstCellNum; i < cellCount; i++) { var column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } //body for (var i = sheet.FirstRowNum + 1; i < rowCount; i++) { var row = sheet.GetRow(i); var dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = GetCellValue(row.GetCell(j)); } } table.Rows.Add(dataRow); } return table; } /// <summary> /// 根据Excel列类型获取列的值 /// </summary> /// <param name="cell">Excel列</param> /// <returns></returns> PRivate static string GetCellValue(ICell cell) { if (cell == null) return string.Empty; switch (cell.CellType) { case CellType.Blank: return string.Empty; case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric: case CellType.Unknown: default: return 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 date or a number case CellType.String: return cell.StringCellValue; case CellType.Formula: try { var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } }
封装基本样式
/// <summary> /// 获取单元格样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="font">单元格字体</param> /// <param name="fillForegroundColor">图案的颜色</param> /// <param name="fillPattern">图案样式</param> /// <param name="fillBackgroundColor">单元格背景</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPatternType fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va) { ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); cellstyle.FillPattern = fillPattern; cellstyle.Alignment = ha; cellstyle.VerticalAlignment = va; if (fillForegroundColor != null) { cellstyle.FillForegroundColor = fillForegroundColor.GetIndex(); } if (fillBackgroundColor != null) { cellstyle.FillBackgroundColor = fillBackgroundColor.GetIndex(); } if (font != null) { cellstyle.SetFont(font); } //有边框 cellstyle.BorderBottom = CellBorderType.THIN; cellstyle.BorderLeft = CellBorderType.THIN; cellstyle.BorderRight = CellBorderType.THIN; cellstyle.BorderTop = CellBorderType.THIN; return cellstyle; }
NPOIHelper
版本：<package id="NPOI" version="2.1.3.1" targetFramework="net45" />
点击下载：NPOI.zip
除了NPOI还有以下操作Excel方式.
MyXls(http://sourceforge.net/projects/myxls/)Koogra(http://sourceforge.net/projects/koogra/)ExcelLibrary(http://code.google.com/p/excellibrary/)ExcelPackage(http://excelpackage.codeplex.com/)EPPlus(http://epplus.codeplex.com/)LinqToExcel(http://code.google.com/p/linqtoexcel/)NetOffice(http://netoffice.codeplex.com/) 需安装Office Excel
