Пример #1
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream Export()
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
        public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();
            Type         type     = typeof(T);

            //PropertyInfo[] properties = type.GetProperties();
            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = ""; //填加xls文件作者信息
                si.ApplicationName          = ""; //填加xls文件创建程序信息
                si.LastAuthor               = ""; //填加xls文件最后保存者信息
                si.Comments                 = ""; //填加xls文件作者信息
                si.Title                    = ""; //填加xls文件标题信息
                si.Subject                  = ""; //填加文件主题信息
                si.CreateDateTime           = System.DateTime.Now;
                workbook.SummaryInformation = si;
            }
            //网格线
            sheet.DisplayGridlines = true;
            #endregion

            #region 设置标题样式
            //导出的列数
            int          excelcolumnsLen = excelConfig.ColumnEntity.Count;
            ICellStyle   headStyle       = workbook.CreateCellStyle();
            int[]        arrColWidth     = new int[excelcolumnsLen];
            string[]     arrColName      = new string[excelcolumnsLen];     //列名
            ICellStyle[] arryColumStyle  = new ICellStyle[excelcolumnsLen]; //样式表
            headStyle.Alignment = HorizontalAlignment.Center;               // ------------------
            if (excelConfig.Background != new Color())
            {
                if (excelConfig.Background != new Color())
                {
                    headStyle.FillPattern         = FillPattern.SolidForeground;
                    headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
                }
            }
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig.TitlePoint;
            if (excelConfig.ForeColor != new Color())
            {
                font.Color = GetXLColour(workbook, excelConfig.ForeColor);
            }
            font.Boldweight = 700;
            headStyle.SetFont(font);
            #endregion

            #region 列头及样式
            ICellStyle cHeadStyle = workbook.CreateCellStyle();
            cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
            IFont cfont = workbook.CreateFont();
            cfont.FontHeightInPoints = excelConfig.HeadPoint;
            cHeadStyle.SetFont(cfont);

            //有边框
            cHeadStyle.BorderBottom = BorderStyle.Thin;
            cHeadStyle.BorderLeft   = BorderStyle.Thin;
            cHeadStyle.BorderRight  = BorderStyle.Thin;
            cHeadStyle.BorderTop    = BorderStyle.Thin;
            #endregion

            #region 设置内容单元格样式
            int i = 0;
            foreach (ColumnEntity expEntity in excelConfig.ColumnEntity)
            {
                string     columnName  = expEntity.Column;
                ICellStyle columnStyle = workbook.CreateCellStyle();
                columnStyle.Alignment = HorizontalAlignment.Center;
                // arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
                //arrColName[i] = columnName;
                var columnentity = expEntity;

                //ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
                //if (columnentity != null)
                //{
                arrColName[i] = columnentity.ExcelColumn;
                if (columnentity.Width != 0)
                {
                    arrColWidth[i] = columnentity.Width;
                }
                if (columnentity.Background != new Color())
                {
                    if (columnentity.Background != new Color())
                    {
                        columnStyle.FillPattern         = FillPattern.SolidForeground;
                        columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
                    }
                }
                if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
                {
                    IFont columnFont = workbook.CreateFont();
                    columnFont.FontHeightInPoints = 10;
                    if (columnentity.Font != null)
                    {
                        columnFont.FontName = columnentity.Font;
                    }
                    if (columnentity.Point != 0)
                    {
                        columnFont.FontHeightInPoints = columnentity.Point;
                    }
                    if (columnentity.ForeColor != new Color())
                    {
                        columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
                    }
                    columnStyle.SetFont(font);
                }
                //}
                arryColumStyle[i] = columnStyle;
                i++;
            }


            #endregion

            #region 填充数据

            #endregion
            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            int rowIndex = 0;
            foreach (T item in lists)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        if (excelConfig.Title != null)
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            if (excelConfig.TitleHeight != 0)
                            {
                                headerRow.Height = (short)(excelConfig.TitleHeight * 20);
                            }
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelConfig.ColumnEntity.Count)); // ------------------
                        }
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1);
                        #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出
                        int headIndex = 0;

                        foreach (var aaa in excelConfig.ColumnEntity)
                        {
                            headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
                            headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
                            //设置列宽
                            sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
                            headIndex++;
                        }
                        #endregion
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                int  ordinal = 0;
                foreach (ColumnEntity expEntity in excelConfig.ColumnEntity)
                {
                    ICell newCell = dataRow.CreateCell(ordinal);
                    newCell.CellStyle = arryColumStyle[ordinal];
                    //find the property type
                    PropertyInfo p          = type.GetProperty(expEntity.Column);
                    var          columnType = p.PropertyType;
                    if (p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
                    {
                        // If it is NULLABLE, then get the underlying type. eg if "Nullable<int>" then this will return just "int"
                        columnType = p.PropertyType.GetGenericArguments()[0];
                    }
                    string drValue = p.GetValue(item, null) == null ? "" : p.GetValue(item, null).ToString();
                    SetCell(newCell, dateStyle, columnType, drValue);
                    ordinal++;
                }

                #endregion
                rowIndex++;
            }
            //合并行
            if (excelConfig.MergeRangeIndexArr != null)
            {
                SetCellMerge(sheet, excelConfig.MergeRangeIndexArr);
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }