【个人使用.Net类库】（3）Excel文件操作类（基于NPOI）

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的表如下图所示（限于篇幅，图中只有一部分数据）：