通過NPOI操作Excel

最近在做的一個項目中需要生成Excel，通過學習使用NPOI實現了相關需求，寫了一個簡便操作的類，記錄如下：

public class NPOIHelperForExcel    {        #region excel文件屬性        //作者        public string Author { get; set; }        //標題        public string Title { get; set; }        //主題        public string Subject { get; set; }        //標記        public string KeyWords { get; set; }        //創建程序信息        public string applicationName { get; set; }        //最后一次保存者        public string LastAuthor { get; set; }        //備注        public string Comments { get; set; }        //創建內容的時間        public DateTime? CreateDateTime { get; set; }        //最后一次打印的時間        public DateTime? LastPRinted { get; set; }        //最后一次保存的時間        public DateTime? LastSaveDateTime { get; set; }        //公司        public string Company { get; set; }        //管理者        public string Manager { get; set; }        //比例        public bool Scale { get; set; }        #endregion        #region 導出，將DataTable導出為Excel文件        /// <summary>        /// DataTable導出到Excel文件        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="headerTextList">表頭摘要信息</param>        /// <param name="strFileName">保存位置</param>        public void Export(DataTable dtSource, List<String> headerTextList, string strFileName)        {            using (MemoryStream ms = Export(dtSource, headerTextList))            {                using (FileStream fs = new FileStream(strFileName, FileMode.Create, Fileaccess.Write))                {                    byte[] data = ms.ToArray();                    fs.Write(data, 0, data.Length);                    fs.Flush();                }            }        }        /// <summary>        /// DataTable導出到Excel的MemoryStream        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="headerTextList">表頭摘要信息</param>        public MemoryStream Export(DataTable dtSource, List<String> headerTextList)        {            HSSFWorkbook workbook = new HSSFWorkbook();            ISheet sheet = workbook.CreateSheet("sheet1");            //設置Excel文件屬性信息            SetFileProperty(workbook);            HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();            HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");            //計算列寬            int[] arrColWidth = new int[dtSource.Columns.Count];            foreach (DataColumn item in dtSource.Columns)            {                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;            }            //獲取每一列的最大列寬            for (int i = 0; i < dtSource.Rows.Count; i++)            {                for (int j = 0; j < dtSource.Columns.Count; j++)                {                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;                    if (intTemp > arrColWidth[j])                    {                        arrColWidth[j] = intTemp;                    }                }            }            int rowIndex = 0;            foreach (DataRow row in dtSource.Rows)            {                #region 新建表，填充表頭，填充列頭，樣式                if (rowIndex == 65535 || rowIndex == 0)                {                    if (rowIndex != 0)                    {                        sheet = workbook.CreateSheet();                    }                    #region 表頭及樣式                    for (int i = 0; i < headerTextList.Count; i++)                    {                        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);                        headerRow.HeightInPoints = 18;                        headerRow.CreateCell(0).SetCellValue(headerTextList[i]);                        HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();                        headerStyle.Alignment = HorizontalAlignment.Left;                        HSSFFont font = (HSSFFont)workbook.CreateFont();                        font.FontHeightInPoints = 14;                        //font.Boldweight = 700;                        headerStyle.SetFont(font);                        headerRow.GetCell(0).CellStyle = headerStyle;                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));                    }                    #endregion                    #region 列頭及樣式                    {                        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count);                        HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();                        headStyle.Alignment = HorizontalAlignment.Center;                        HSSFFont font = (HSSFFont)workbook.CreateFont();                        font.FontHeightInPoints = 10;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        foreach (DataColumn column in dtSource.Columns)                        {                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                            //設置列寬                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                        }                    }                    #endregion                    rowIndex = headerTextList.Count + 1;                }                #endregion                #region 填充表格內容                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);                foreach (DataColumn column in dtSource.Columns)                {                    HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);                    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);                            newCell.CellStyle = dateStyle; //格式化顯示                            break;                        case "System.Boolean": //布爾型                            bool boolV = false;                            bool.TryParse(drValue, out boolV);                            newCell.SetCellValue(boolV);                            break;                        case "System.Int16": //整型                        case "System.Int32":                        case "System.Int64":                        case "System.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;                    }                }                #endregion                rowIndex++;            }            using (MemoryStream ms = new MemoryStream())            {                workbook.Write(ms);                ms.Flush();                ms.Position = 0;                return ms;            }        }        /// <summary>        /// 用于Web導出        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="headerTextList">表