Web開發工作中經常要根據業務的需要生成對應的報表。經常采用的方法如下：

• 將DataTable導出至Excel文件;
• 讀取模板Excel文件;
• 修改模板Excel文件對應的內容。

因此，便想到封裝一個基于NPOI的Excel操作類（至于為什么不用Excel組件，那是因為Excel組件效率低且必須安裝Office），所完成的功能大致如上所示，這樣平時的報表開發效率就比原來高效很多。

首先是DataTable導出至Excel文件，代碼關鍵部分有注釋說明，具體代碼如下所示：

/// <summary>        /// 從DataTable中將數據導出到Excel文件        /// </summary>        /// <param name="dtSource">提供導出數據的DataTable</param>        /// <param name="headerText">表頭文本</param>        /// <returns></returns>        public static MemoryStream ExportDataTable(DataTable dtSource, string headerText)        {            //創建工作表            var workbook = new HSSFWorkbook();            //創建sheet頁            var sheet = workbook.CreateSheet();            #region 添加Excel文件屬性信息            var dsi = PRopertySetFactory.CreateDocumentSummaryInformation();            dsi.Company = "ZWKJ";            workbook.DocumentSummaryInformation = dsi;            var si = PropertySetFactory.CreateSummaryInformation();            si.Author = "鞠小軍";            si.applicationName = "使用NPOI創建的Excel文件";            si.CreateDateTime = DateTime.Now;            workbook.SummaryInformation = si;            #endregion            //設置日期格式            var dateStyle = workbook.CreateCellStyle();            var format = workbook.CreateDataFormat();            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");            //取得列寬            var columnWidth = new int[dtSource.Columns.Count];            //遍歷DataTable的列            foreach (DataColumn column in dtSource.Columns)            {                columnWidth[column.Ordinal] = Encoding.GetEncoding(936).GetBytes(column.ColumnName).Length;            }            //遍歷所有的Row,若當前Row內容長度超出列名長度，則將此列的長度設為該Row內容長度            for (var i = 0; i < dtSource.Rows.Count; i++)            {                for (var j = 0; j < dtSource.Columns.Count; j++)                {                    var currentRowLength = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;                    if (currentRowLength > columnWidth[j])                        columnWidth[j] = currentRowLength;                }            }                        var rowIndex = 0;            #region 表頭及樣式            var headRow = sheet.CreateRow(0);            headRow.HeightInPoints = 25;            headRow.CreateCell(0).SetCellValue(headerText);            var headStyle = workbook.CreateCellStyle();            headStyle.Alignment = HorizontalAlignment.CENTER;            var font = workbook.CreateFont();            font.Boldweight = 700;            font.FontHeightInPoints = 20;            headStyle.SetFont(font);            headRow.GetCell(0).CellStyle = headStyle;            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));            #endregion            #region 列頭及樣式            var columnRow = sheet.CreateRow(1);            var columnStyle = workbook.CreateCellStyle();            columnStyle.Alignment = HorizontalAlignment.CENTER;            var columnFont = workbook.CreateFont();            columnFont.Boldweight = 700;            columnFont.FontHeightInPoints = 10;            columnStyle.SetFont(columnFont);            foreach (DataColumn column in dtSource.Columns)            {                //設置列頭內容                columnRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                //設置列頭樣式                columnRow.GetCell(column.Ordinal).CellStyle = columnStyle;                //設置列寬                sheet.SetColumnWidth(column.Ordinal, (columnWidth[column.Ordinal] + 1) * 256);            }            #endregion            rowIndex = 2;            foreach (DataRow row in dtSource.Rows)            {                if(rowIndex == 65535)                    sheet = workbook.CreateSheet();                #region 填充數據                var dataRow = sheet.CreateRow(rowIndex);                foreach (DataColumn column in dtSource.Columns)                {                    var newCell = dataRow.CreateCell(column.Ordinal);                    var cellValue = row[column].ToString();                    switch (column.DataType.ToString())                    {                        //字符串類型                        case "System.String":                            double result;                            if (double.TryParse(cellValue, out result))                            {                                newCell.SetCellValue(result);                                break;                            }                            newCell.SetCellValue(cellValue);                            break;                        //DateTime類型                        case "System.DateTime":                            DateTime tmpdt;                            if (DateTime.TryParse(cellValue, out tmpdt))                            {                                newCell.SetCellValue(tmpdt);                                newCell.CellStyle = dateStyle;                                break;                            }                            newCell.SetCellValue(cellValue);                            break;                        //布爾類型                        case "System.Boolean":                            bool boolV;                            if (bool.TryParse(cellValue, out boolV))                            {                                newCell.SetCellValue(boolV);                                break;                            }                            newCell.SetCellValue(cellValue);                            break;                        //整型                        case "System.Int16":                        case "System.Int32":                        case "System.Int64":                        case "System.Byte":                            int intV;                            if (int.TryParse(cellValue, out intV))                            {                                newCell.SetCellValue(intV);                                break;                            }                            newCell.SetCellValue(cellValue);                            break;                        //浮點型                        case "System.Decimal":                        case "System.Double":                            double doubV;                            if (double.TryParse(cellValue, out doubV))                            {                                newCell.SetCellValue(doubV);                                break;                            }                            newCell.SetCellValue(cellValue);                            break;                        //空值處理                        case "System.DBNull":                            newCell.SetCellValue("");                            break;                        default:                            newCell.SetCellValue("");                            break;                    }                }                rowIndex++;                #endregion            }            using (var ms = new MemoryStream())            {                workbook.Write(ms);                ms.Flush();                ms.Position = 0;                return ms;            }        }        /// <summary>        /// DataTable導出到Excel文件        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="headerText">表頭</param>        /// <param name="fileName">Excel文件保存位置(包含文件名)</param>        public static void ExportDataTableToExcel(DataTable dtSource, string headerText,string fileName)        {            using (var ms = ExportDataTable(dtSource, headerText))            {                using (var fileStream = new FileStream(fileName, FileMode.Create, Fileaccess.Write))                {                    var data = ms.ToArray();                    fileStream.Write(data,0,data.Length);                    fileStream.Flush();                }            }        }

測試從DataTable數據導出至Excel的表如下圖所示（限于篇幅，圖中只有一部分數據）：