[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方式.