[Solution] NPOI操作Excel

　　NPOI 是 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;
        }

　　

合并单元格

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public static void SetCellRange(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

 

复制样式

var sheet = workbook.GetSheetAt(0);
var cell = sheet.CreateRow(17).CreateCell(0);
cell.SetCellValue(" ");
var style = sheet.GetRow(7).GetCell(0).CellStyle;
sheet.AddMergedRegion(new CellRangeAddress(17, 17, 0, 4));
sheet.GetRow(17).GetCell(0).CellStyle = style;

 

 

NPOIHelper

点击下载：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