用NPOI从DataTable到Excel，向Excel模板填充数据

DataTable---->Excel，填充数据

 private IWorkbook workbook = null;
        private ISheet sheet = null;
        private string fileName = "";//文档路径
        private FileStream fs = null;

        public ExcelHelper()
        {
        }
        //构造函数
        public ExcelHelper(string file)
        {
            this.fileName = file;
        }
/// <summary>
        /// DataTable到Excel,向Excel模板填充数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath">导出EXcel存储地方</param>
        /// <param name="templeteFile">Excel模板路径</param>
        /// <param name="titleName">sheet标题</param>
        public bool ExportExcel(DataTable dt, string filePath, string templeteFile, string titleName)
        {
            bool flag = false;
            try
            {
                using (MemoryStream ms = ExportExcelForDt(dt, filePath, templeteFile, titleName))
                {
                    using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();

                        data = null;
                    }
                }
                flag = true;
            }
            catch
            {
            }

            return flag;
        }

        private MemoryStream ExportExcelForDt(DataTable dt, string filePath, string templeteFile, string titleName)
        {
            int totalIndex = 31;  //每个类别的总行数
            int rowIndex = 2;//起始行
            int dtRowIndex = dt.Rows.Count;
            //工作薄
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(templeteFile, FileMode.Open, FileAccess.Read))
            {
                //判断Excel版本
                if (System.IO.Path.GetExtension(templeteFile) == ".xls")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else if (System.IO.Path.GetExtension(templeteFile) == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else
                {
                    throw new Exception("文件类型错误");
                }

                //获取sheet[0]
                ISheet sheet = workbook.GetSheetAt(0);

                //表头处理
                IRow headerRow = sheet.GetRow(0);
                ICell headerCell = headerRow.GetCell(0);
                headerCell.SetCellValue(titleName);

                //隐藏多余行，在数据填充区，对空白部分作处理
                for (int i = rowIndex + dtRowIndex; i < rowIndex + totalIndex; i++)
                {
                    IRow dataRowH = sheet.GetRow(i);
                    dataRowH.Height = 0;
                    dataRowH.ZeroHeight = true;
                }

                //填充数据
                foreach (DataRow row in dt.Rows)
                {
                    IRow dataRow = sheet.GetRow(rowIndex);

                    //开始列(0为标题列，从1开始)
                    int columnIndex = 1;  
                    #region 2
                    foreach (DataColumn column in dt.Columns)
                    {
                        #region 1
                        //列序号赋值 2018-02-17 不能要等号，否则最后一项没有
                        if (columnIndex > dt.Columns.Count)
                        {
                            break;
                        }

                        ICell newCell = dataRow.GetCell(columnIndex);
                        if (newCell == null)
                        {
                            newCell = dataRow.CreateCell(columnIndex);
                        }

                        string drValue = row[column].ToString();
                        switch (column.DataType.ToString())
                        {
                            case "System.String":
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime":
                                DateTime datev;
                                DateTime.TryParse(drValue, out datev);
                                newCell.SetCellValue(datev);
                                break;
                            case "System.Boolean":
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                break;
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "Systmen.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal":
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull":
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }
                        columnIndex++;
                        #endregion

                    }
                    #endregion

                    rowIndex++;
                }

                //格式化当前sheet,用于数据total计算
                sheet.ForceFormulaRecalculation = true;

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    sheet = null;
                    workbook = null;

                    return ms;
                }
            }
        }