C# NPOI 操作Excel

如果要使用NPIO处理Excel ，当然首先是导入包了

 

 目前最新的版本是2.5.2

我在网上找了一个NPOI的ExcelHelper文件可以参考一下：

public class ExcelHelper
    {
        public static DataSet ExcelToDataSet(string fileName)
        {
            return ExcelToDataSet(fileName, true);
        }

        private static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader)
        {
            var sheetCount = 0;

            return ExcelToDataSet(fileName, firstRowAsHeader, out sheetCount);
        }

        private static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader, out int sheetCount)
        {
            using (var ds = new DataSet())
            {
                var workbook = new XSSFWorkbook(fileName);

                int num = workbook.GetSheetAt(0).LastRowNum;

                using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {

                    // long a = fileStream.Length;
                    //  var workbook = WorkbookFactory.Create(fileStream);

                    //   var workbook =new XSSFWorkbook(fileStream);
                    //SXSSFWorkbook workbook = new SXSSFWorkbook();
                    var formulaEvaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);

                    sheetCount = workbook.NumberOfSheets;

                    for (var i = 0; i < sheetCount; i++)
                    {
                        var sheet = workbook.GetSheetAt(i);
                        var dataTable = ExcelToDataTable(sheet, formulaEvaluator, firstRowAsHeader);
                        ds.Tables.Add(dataTable);
                    }

                    return ds;
                }
            }
        }

        public static DataTable ExcelToDataTable(string fileName, string sheetName)
        {
            return ExcelToDataTable(fileName, sheetName, true);
        }

        public static DataTable ExcelToDataTable(string fileName, string sheetName, bool firstRowAsHeader)
        {
            using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                var workbook = WorkbookFactory.Create(fileStream);
                IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
                var sheet = workbook.GetSheet(sheetName);

                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
            }
        }

        public static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator formulaEvaluator,
            bool firstRowAsHeader)
        {
            if (firstRowAsHeader)
                return ExcelToDataTableFirstRowAsHeader(sheet, formulaEvaluator);
            return ExcelToDataTable(sheet, formulaEvaluator);
        }


        private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator formulaEvaluator)
        {
            using (var dt = new DataTable())
            {
                var firstRow = sheet.GetRow(0);
                var cellCount = GetCellCount(sheet);

                for (var i = 0; i < cellCount; i++)
                    if (firstRow.GetCell(i) != null)
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1),
                            typeof(string));
                    else
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));

                for (var i = 1; i < sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);

                    var dataRow = dt.NewRow();

                    FillDataRowByRow(row, formulaEvaluator, ref dataRow);

                    dt.Rows.Add(dataRow);
                }

                dt.TableName = sheet.SheetName;

                return dt;
            }
        }

        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator formulaEvaluator)
        {
            using (var dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    var cellCount = GetCellCount(sheet);

                    for (var i = 0; i < cellCount; i++) dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));

                    //如果excel数据的第一行不是excel 的第一行则按照excel添加空行
                    for (var i = 0; i < sheet.FirstRowNum; i++)
                    {
                        var dataRow = dt.NewRow();
                        dt.Rows.Add(dataRow);
                    }

                    for (var i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);

                        var dataRow = dt.NewRow();

                        FillDataRowByRow(row, formulaEvaluator, ref dataRow);

                        dt.Rows.Add(dataRow);
                    }
                }

                dt.TableName = sheet.SheetName;

                return dt;
            }
        }

        /// <summary>
        /// 返回当前sheet页的最大列数
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static int GetCellCount(ISheet sheet)
        {
            var firstRowNum = sheet.FirstRowNum;
            var cellCount = 0;

            for (var i = firstRowNum; i < sheet.LastRowNum; ++i)
            {
                var row = sheet.GetRow(i);
                if (row != null && row.LastCellNum >= cellCount) cellCount = row.LastCellNum;
            }

            return cellCount;
        }

        /// <summary>
        /// 计算sheet也的格式
        /// </summary>
        /// <param name="row"></param>
        /// <param name="formulaEvaluator"></param>
        /// <param name="dataRow"></param>
        private static void FillDataRowByRow(IRow row, IFormulaEvaluator formulaEvaluator, ref DataRow dataRow)
        {
            if (row != null)
                for (var i = 0; i < dataRow.Table.Columns.Count; i++)
                {
                    var cell = row.GetCell(i);

                    if (cell != null)
                        switch (cell.CellType)
                        {
                            case CellType.Numeric:

                                if (DateUtil.IsCellDateFormatted(cell))
                                    dataRow[i] = cell.DateCellValue;
                                else
                                    dataRow[i] = cell.NumericCellValue;
                                break;
                            case CellType.String:
                                dataRow[i] = cell.StringCellValue;
                                break;
                            case CellType.Formula:
                                CellType cellType = formulaEvaluator.EvaluateInCell(cell).CellType;
                                // var status = formulaEvaluator.EvaluateInCell(cell).
                                cell = formulaEvaluator.EvaluateInCell(cell);
                                switch (cellType)
                                {
                                    case CellType.Numeric:

                                        if (DateUtil.IsCellDateFormatted(cell))
                                            dataRow[i] = cell.DateCellValue;
                                        else
                                            dataRow[i] = cell.NumericCellValue;
                                        break;
                                    case CellType.String:
                                        dataRow[i] = cell.StringCellValue;
                                        break;
                                    case CellType.Blank:
                                        dataRow[i] = DBNull.Value;
                                        break;
                                    case CellType.Boolean:
                                        dataRow[i] = cell.BooleanCellValue;
                                        break;
                                    default:
                                        throw new NotSupportedException(string.Format("Unsupported format type:{0}",
                                            cell.CellType));
                                }

                                //cell = formulaEvaluator.EvaluateInCell(cell);
                                //dataRow[i] = cell.ToString();
                                break;
                            case CellType.Blank:
                                dataRow[i] = DBNull.Value;
                                break;
                            case CellType.Boolean:
                                dataRow[i] = cell.BooleanCellValue;
                                break;
                            default:
                                throw new NotSupportedException(string.Format("Unsupported format type:{0}",
                                    cell.CellType)); //??计算失败返回异常
                        }
                }
        }


        public static void ExportToExcel(DataTable dataTable)
        {
            ExportToExcel(dataTable, "Sheet1");
        }


        /// <summary>
        ///     数据导出到excel当中
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="sheetName"></param>
        public static void ExportToExcel(DataTable dataTable, string sheetName)
        {
            var saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = @"Excel(2007-2013)|*.xlsx";

            if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }

            IWorkbook workbook = new SXSSFWorkbook();

            var sheet = workbook.CreateSheet(sheetName);

            var rowHead = sheet.CreateRow(0);

            //表头
            for (var i = 0; i < dataTable.Columns.Count; i++)
                rowHead.CreateCell(i, CellType.String).SetCellValue(dataTable.Columns[i].ColumnName);

            //表格内容
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var row = sheet.CreateRow(i + 1);
                for (var j = 0; j < dataTable.Columns.Count; j++)
                    row.CreateCell(j, CellType.String).SetCellValue(dataTable.Rows[i][j].ToString());
            }

            //自动列宽
            // for (var i = 0; i < dataTable.Columns.Count; i++) sheet.AutoSizeColumn(i);

            using (var stream = File.OpenWrite(saveFileDialog.FileName))
            {
                workbook.Write(stream);
                stream.Close();
            }

            MessageBox.Show("导出数据成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            GC.Collect();
        }

        /// <summary>
        /// 把数据从dataTable加载到Excel当中
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="stream"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        public static void ExportDataToExcel(DataTable dataTable, MemoryStream stream, string fileName, string sheetName = "sheet1")
        {
            IWorkbook workbook;
            //string extension = System.IO.Path.GetExtension(stream);
            //            FileStream fs = File.OpenRead(fileName);
            //            if (extension.Equals(".xls"))
            //            {
            //                workbook = new HSSFWorkbook(stream);
            //            }
            //            else
            //            {
            //                workbook = new XSSFWorkbook(stream);
            //            }
            //            fs.Close();
            //stream.Position = 0;
            var buffer = stream.ToArray();
            Stream stream1 = new MemoryStream(buffer);
            workbook = new XSSFWorkbook(stream1);

            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;

            ISheet sheet = workbook.GetSheetAt(0);
            //sheet.ShiftRows(3, sheet.LastRowNum, -1);
            for (int i = sheet.LastRowNum +1 ; i >=3; i--)
            {
                sheet.ShiftRows(i, i + 1, -1);
            }

            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var row = sheet.CreateRow(i + 2);
                for (var j = 0; j < dataTable.Columns.Count; j++)
                {
                    
                    if (dataTable.Rows[i][j].GetType().Name == "String")
                    {
                        //CellType cellType = CellType.String;
                        row.CreateCell(j, CellType.String).SetCellValue(dataTable.Rows[i][j].ToString());
                    }
                    else
                    {
                        //CellType cellType = CellType.Numeric;
                        row.CreateCell(j, CellType.Numeric).SetCellValue(Convert.ToDouble(dataTable.Rows[i][j]));
                    }

                    row.Cells[j].CellStyle = cellStyle;
                    // sheet.AutoSizeColumn(j);
                    //LogHelper.Info("当前cell的值：" + row.Cells[j].ToString());


                }
                
            }
             

            using (FileStream fileStream =new FileStream(fileName, FileMode.Create))
            {
                workbook.Write(fileStream);
                fileStream.Close();
            }

        }

        /// <summary>
        /// 把数据库数据导出到Excel表格当中
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="sheet"></param>
        public static ISheet ExportDataToSheet(DataTable dataTable, ISheet sheet)
        {
            sheet.SetActive(true);
            for (int i = sheet.LastRowNum + 3; i >= 3; i--)
            {
                //sheet.ShiftRows(i-1, i , -1);
                IRow row = sheet.GetRow(i);
                if (row!=null)
                {
                    sheet.RemoveRow(row);
                
                }  
            }

            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var row = sheet.CreateRow(i + 2);
                for (var j = 1; j < dataTable.Columns.Count; j++)
                {

                    if (dataTable.Rows[i][j].GetType().Name == "Double")
                    {
                        //CellType cellType = CellType.String;
                        double cellData = Convert.ToDouble(dataTable.Rows[i][j]);
                        if (cellData==0)
                        {
                            row.CreateCell(j - 1, CellType.Blank);
                        }
                        else
                        {
                            row.CreateCell(j - 1, CellType.String).SetCellValue(dataTable.Rows[i][j].ToString());
                        }
                        
                    }
                    else
                    {
                        //CellType cellType = CellType.Numeric;
                        row.CreateCell(j-1, CellType.String).SetCellValue(dataTable.Rows[i][j].ToString());
                    }
                    // sheet.AutoSizeColumn(j);
                    //LogHelper.Info("当前cell的值：" + row.Cells[j-1].ToString());


                }

            }


            return sheet;

        }

        public static void ExportDataToWorkbook(DataTable dataTable, ISheet sheet, ICellStyle cellStyle)
        {
             
             

            //ISheet sheet = workbook.GetSheet(sheetName);
            //sheet.ShiftRows(3, sheet.LastRowNum, -1);
            for (int i = sheet.LastRowNum + 1; i >= 3; i--)
            {
                sheet.ShiftRows(i, i + 1, -1);
            }

            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var row = sheet.CreateRow(i + 2);
                for (var j = 1; j < dataTable.Columns.Count; j++)
                {

//                    if (dataTable.Rows[i][j].GetType().Name == "String")
//                    { 
//                        row.CreateCell(j-1, CellType.String).SetCellValue(dataTable.Rows[i][j].ToString());
//                    }
//                    else
//                    { 
//                        row.CreateCell(j-1, CellType.Numeric).SetCellValue(Convert.ToDouble(dataTable.Rows[i][j]));
//                    }

                    if (dataTable.Rows[i][j].GetType().Name == "Double")
                    {
                        double cellValue = Convert.ToDouble(dataTable.Rows[i][j]);
                        if (cellValue == 0)
                        {
                            row.CreateCell(j - 1, CellType.Blank);
                        }
                        else
                        {
                            row.CreateCell(j - 1, CellType.Numeric).SetCellValue(cellValue);
                        }
                    }
                    else
                    {
                        row.CreateCell(j - 1, CellType.String).SetCellValue(dataTable.Rows[i][j].ToString());
                    }

                    row.Cells[j - 1].CellStyle = cellStyle;


                }

            }
             
            

            ///return workbook;
        }

        /// <summary>
        /// 从Excel当中读取数据保存到datatable当中
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="cellCount"></param>
        /// <returns></returns>
        public static DataTable LoadDataFromExcel(MemoryStream stream,int cellCount)
        {
            IWorkbook workbook = new XSSFWorkbook(stream);
            ISheet sheet = workbook.GetSheetAt(0);
            IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);  //计算sheet页的所有公式
            using (var dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    //var cellCount = GetCellCount(sheet);

                    for (var i = 0; i < cellCount; i++) 
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));

                    //如果excel数据的第一行不是excel 的第一行则按照excel添加空行
//                    for (var i = 0; i < sheet.FirstRowNum; i++)
//                    {
//                        var dataRow = dt.NewRow();
//                        dt.Rows.Add(dataRow);
//                    }

                    for (var i = 2; i <= sheet.LastRowNum; i++) //当前模板数据均从第三行开始
                    {
                        var row = sheet.GetRow(i);

                        var dataRow = dt.NewRow();

                        FillDataRowByRow(row, evaluator, ref dataRow);

                        dt.Rows.Add(dataRow);
                    }
                }

                dt.TableName = sheet.SheetName;

                return dt;
            }
        }

        public static void ToWorkBook(byte[] bytes)
        {
            var stream =  StreamTools.ByteToStream(bytes);
            IWorkbook workbook = new XSSFWorkbook(stream);

        }
    }

这里要说一下几个重要的接口，分别是

        IWorkbook 
        ISheet 
        IRow 
        ICell 

其中IWorkbook 相当于是工作簿，ISheet相对的就是sheet页，IRow就是行，ICell就相当于是单元格。

所以使用NPOI的时候首先需要创建一个IWorkbook对象

创建IWorkbook对象的时候需要根据对应的Excel版本

IWorkbook workbook = new XSSFWorkbook();
IWorkbook workbook = new HSSFWorkbook();

XSSFWorkbook对应的就是2007以后的版本，HSSFWorkbook对应的是2003版本了。
如果这个地方搞得不对就会发生保存以后打不开，或者是打开的时候提示Excel后缀名不对或者是部分内容损坏需要修复的提示了
NPOI在创建IWorkbook 对象的时候有一个好的地方就是可以直接从内存创建，当然也可以基于现有的Excel文件

public   void ExportDataToExcel(DataTable dataTable, MemoryStream stream, string fileName, string sheetName = "sheet1")
{
    IWorkbook  workbook = new XSSFWorkbook(stream);
    IWorkbook workbook1 = new XSSFWorkbook(fileName);
}

当然结果保存的时候也是可以保存在内存或者是直接保存在文件里面

下面的保存过程就是先保存在内存当中然后把内存流保存到文件当中

using (FileStream fs = new FileStream(fileName, FileMode.Create))
            {
                MemoryStream memoryStream = new MemoryStream();
                workbook.Write(memoryStream);
                byte[] data = memoryStream.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
                fs.Close();
                memoryStream.Close();
                memoryStream.Dispose(); 
                workbook = null;
            } 

一般来说推荐使用以上的方式保存工作簿