做项目的时候参考网上的资源结合现有的项目写的方法,实现不同类型的内容置为对应的格式,内容为数字则单元格数值格式,识别为日期格式则单元格格式也是日期。
实际项目中写了一个判断,如果列名带有【号】、【码】字样的则即便是纯数字也保存为文本。例如银行账号、产品编码等是纯数字但并不应该是数字格式的。

重点为单元格样式以及单元格内的文本格式等都用到了:ICellStyle ,
实例一个CellStyle写法为: ICellStyle cellStyle1 = workbook.CreateCellStyle();
使用newCell.CellStyle = cellstyle;将一个单元格设置为对应的样式,注意ICellStyle的实例在一张表中不能超过4000个,所以切记不要将CellStyle的实例写在数据填充的for循环里哦。下面的代码里的样式使用主要是每个单元格加边框,以及日期格式的固定为yyyy-mm-dd。

下载NPOI的DLL文件:点击下载

类头引用:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util;
using System.Linq;
using NPOI.SS.Formula.Functions;

方法代码:

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="templateFile">模板文件</param>
        /// <param name="fileName">导出的文件名</param>
        public static MemoryStream ExportToMemoryStream(DataTable dtSource, string strHeaderText, string templateFile = "", string fileName = "")
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            bool isTemplateFile = false;//是否采用模板文件导出
            int rowIndex = 0;
            bool isExcel2007 = false;
            if (!templateFile.IsNullOrEmpty() && File.Exists(templateFile))
            {
                string templateFilePath = templateFile;
                isExcel2007 = templateFilePath.EndsWith("xlsx", StringComparison.CurrentCultureIgnoreCase);
                using (FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read))
                {
                    if (isExcel2007)
                    {
                        workbook = new XSSFWorkbook(file);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(file);
                    }
                }
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        isTemplateFile = true;
                        rowIndex = sheet.LastRowNum + 1;
                    }
                }
            }
            else
            {
                isExcel2007 = fileName.EndsWith("xlsx", StringComparison.CurrentCultureIgnoreCase);
            }
            if (workbook == null)
            {
                if (isExcel2007)
                {
                    workbook = new XSSFWorkbook();
                }
                else
                {
                    workbook = new HSSFWorkbook();
                }
            }
            if (sheet == null)
            {
                sheet = workbook.CreateSheet("Sheet1");
            }
            int[] arrColWidth = new int[] { };
            string[] arrColDataType = new string[] { };
            if (!isTemplateFile)
            {
                //取得列宽

                arrColWidth = new int[dtSource.Columns.Count];
                arrColDataType = new string[dtSource.Columns.Count];
                foreach (DataColumn item in dtSource.Columns)
                {
                    if (item.Caption.IsInt())
                    {
                        arrColWidth[item.Ordinal] = item.Caption.ToInt();
                    }
                    else
                    {
                        arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                    }
                    arrColDataType[item.Ordinal] = item.DataType.ToString();
                }
                if (dtSource.Rows.Count > 0)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++) { if (!dtSource.Columns[j].Caption.IsInt()) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[0][j].ToString()).Length; if (intTemp > arrColWidth[j])
                            {
                                arrColWidth[j] = intTemp;
                            }
                        }
                    }
                }
            }
            bool hasHeader = !strHeaderText.IsNullOrEmpty();//是否有表头

            if (!isTemplateFile)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    #region 表头及样式
                    if (hasHeader)
                    {

                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderLeft = BorderStyle.Thin;
                        headStyle.BorderRight = BorderStyle.Thin;
                        headStyle.BorderTop = BorderStyle.Thin;
                        headStyle.BorderBottom = BorderStyle.Thin;
                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(hasHeader ? 1 : 0);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.BorderLeft = BorderStyle.Thin;
                        headStyle.BorderRight = BorderStyle.Thin;
                        headStyle.BorderTop = BorderStyle.Thin;
                        headStyle.BorderBottom = BorderStyle.Thin;
                        headStyle.IsLocked = true;
                        headStyle.SetFont(font);
                        ICellStyle cellStyle = workbook.CreateCellStyle();

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //设置列宽
                            if (arrColWidth.Length > 0)
                            {
                                sheet.SetColumnWidth(column.Ordinal, Math.Min(arrColWidth[column.Ordinal] + 1, 200) * 256);
                            }
                        }
                        //sheet.CreateFreezePane(0, hasHeader ? 2 : 1, 0, dtSource.Columns.Count - 1);
                    }
                    #endregion
                    rowIndex = hasHeader ? 2 : 1;
                }
                #endregion
            }
            int columnsCount = dtSource.Columns.Count;
            ICellStyle cellStyle1 = workbook.CreateCellStyle();
            cellStyle1.BorderLeft = BorderStyle.Thin;
            cellStyle1.BorderRight = BorderStyle.Thin;
            cellStyle1.BorderTop = BorderStyle.Thin;
            cellStyle1.BorderBottom = BorderStyle.Thin;

            //日期格式的单元格格式
            ICellStyle csDateFormat = workbook.CreateCellStyle();
            csDateFormat.BorderLeft = BorderStyle.Thin;
            csDateFormat.BorderRight = BorderStyle.Thin;
            csDateFormat.BorderTop = BorderStyle.Thin;
            csDateFormat.BorderBottom = BorderStyle.Thin;
            IDataFormat dfDate = workbook.CreateDataFormat();
            csDateFormat.DataFormat = dfDate.GetFormat("yyyy-mm-dd");


            foreach (DataRow row in dtSource.Rows)
            {
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int i = 0; i < columnsCount; i++) { ICell newCell = dataRow.CreateCell(i); //IDataFormat format = workbook.CreateDataFormat(); //cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); newCell.CellStyle = cellStyle1; string drValue = row[i].ToString(); //因列都是字符串型的,所以循环Parse来确认列的对应数据格式。 DateTime dateV; double doubV = 0; //大于8字节的才被认为符合日期格式(2000-1-1),防止-1也被认为是日期 if (drValue.Length >= 8 && DateTime.TryParse(drValue, out dateV))
                    {
                        newCell.CellStyle = csDateFormat;

                        if (dateV == Convert.ToDateTime(null))
                            newCell.SetCellValue("");
                        else
                            newCell.SetCellValue(dateV);
                    }
                    else if (double.TryParse(drValue, out doubV))
                    {
                        //如果标题含有号、码字样,即使是全数字也显示文本
                        if ((dtSource.Columns[i] + "").IndexOf("号") >= 0 || (dtSource.Columns[i] + "").IndexOf("码") >= 0)
                        {
                            newCell.SetCellValue(drValue);
                        }
                        else
                        {
                            newCell.SetCellValue(doubV);
                        }

                    }
                    else
                    {
                        newCell.SetCellValue(drValue);
                    }
                }

                #endregion
                rowIndex++;
            }

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注