[Solution] NPOI操作Excel

2019-11-17 02:16:08

字体：大中小

来源：转载

供稿：网友

[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;        }

NPOIHelper

版本：<package id="NPOI" version="2.1.3.1" targetFramework="net45" />

点击下载：NPOI.zip

除了NPOI还有以下操作Excel方式.