예제 #1
0
        /// <summary>
        /// 通过多标签页数据将数据写入响应流
        /// </summary>
        /// <param name="multiSheet">多标签页数据</param>
        /// <param name="Response">响应流</param>
        public static void ExportToMutilSheet(ExportMultiSheet multiSheet, HttpResponse Response)
        {
            ExportToMutilSheet(multiSheet, Response.Body);

            Response.Headers.Add("Content-Disposition", string.Format("attachment;filename={0}", Path.GetFileName(multiSheet.FileName)));
            Response.ContentType = MimeHelper.GetMineType(multiSheet.FileName);

            Response.Body.Flush();
            Response.Clear();
        }
예제 #2
0
        /// <summary>
        /// 导出数据到多个Excel标签页中
        /// </summary>
        /// <param name="multiSheet">多标签页信息</param>
        /// <param name="s">输出流</param>
        public static void ExportToMutilSheet(ExportMultiSheet multiSheet, Stream s)
        {
            if (multiSheet == null || multiSheet.ListSheet == null || multiSheet.ListSheet.Count == 0)
            {
                throw new ArgumentNullException();
            }
            string fileExt = ".xls";

            if (string.IsNullOrEmpty(multiSheet.FileName))
            {
                multiSheet.FileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + fileExt;
            }
            else
            {
                if (!multiSheet.FileName.EndsWith(fileExt))
                {
                    multiSheet.FileName = multiSheet.FileName + fileExt;
                }
            }
            IWorkbook workbook = new HSSFWorkbook();
            //文本样式
            ICellStyle centerStyle = workbook.CreateCellStyle();

            centerStyle.VerticalAlignment = VerticalAlignment.Center;
            centerStyle.Alignment         = HorizontalAlignment.Center;

            ICellStyle leftStyle = workbook.CreateCellStyle();

            leftStyle.VerticalAlignment = VerticalAlignment.Center;
            leftStyle.Alignment         = HorizontalAlignment.Left;

            ICellStyle rightStyle = workbook.CreateCellStyle();

            rightStyle.VerticalAlignment = VerticalAlignment.Center;
            rightStyle.Alignment         = HorizontalAlignment.Right;


            //超链接字体颜色
            IFont blueFont = workbook.CreateFont();

            blueFont.Color = HSSFColor.Blue.Index;
            ICellStyle leftStyleLink = workbook.CreateCellStyle();

            leftStyleLink.VerticalAlignment = VerticalAlignment.Center;
            leftStyleLink.Alignment         = HorizontalAlignment.Left;
            leftStyleLink.SetFont(blueFont);

            ISheet            sheet          = null;
            DataTable         dt             = null;
            DataRow           dr             = null;
            List <ColumnInfo> ColumnInfoList = null;
            int    rowHeight = 20;
            object cellValue = null;
            IRow   row       = null;
            ICell  cell      = null;

            //每写入100条数据进度更新一次
            int totalData = multiSheet.ListSheet.Sum(e => e.Data.Rows.Count);
            //写入总数
            int writeTotal = 0;

            foreach (ExportSheetInfo exportSheetInfo in multiSheet.ListSheet)
            {
                dt             = exportSheetInfo.Data;
                ColumnInfoList = exportSheetInfo.ColumnInfoList;
                //寻找列头和DataTable之间映射关系
                foreach (DataColumn col in dt.Columns)
                {
                    ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                    if (info != null)
                    {
                        info.Align = info.Align ?? "left";
                        switch (info.Align.ToLower())
                        {
                        case "left":
                            info.Style = leftStyle;
                            break;

                        case "center":
                            info.Style = centerStyle;
                            break;

                        case "right":
                            info.Style = rightStyle;
                            break;
                        }
                        info.IsMapDT = true;
                    }
                }
                //标题头索引
                int headIndex = string.IsNullOrEmpty(exportSheetInfo.Remark) ? 0 : 1;
                int total     = dt.Rows.Count;
                int sheetNum  = (int)Math.Ceiling(total * 1.0 / (MAX_ROW_INDEX - headIndex - 1));
                int drIndex   = 0;

                for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
                {
                    string sheetName = string.IsNullOrEmpty(exportSheetInfo.SheetName) ? "Sheet " + workbook.NumberOfSheets : (sheetNum > 1 ? (exportSheetInfo.SheetName + sheetIndex) : exportSheetInfo.SheetName);
                    sheet = workbook.CreateSheet(sheetName);

                    sheet.CreateFreezePane(0, headIndex + 1, 0, headIndex + 1);
                    if (headIndex > 0)
                    {
                        //输出备注行
                        IRow RemarkRow = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ColumnInfoList.Count - 1));
                        ICell      rcell       = RemarkRow.CreateCell(0);
                        ICellStyle remarkStyle = workbook.CreateCellStyle();
                        remarkStyle.WrapText          = true;
                        remarkStyle.VerticalAlignment = VerticalAlignment.Top;
                        remarkStyle.Alignment         = HorizontalAlignment.Left;
                        IFont rfont = workbook.CreateFont();
                        rfont.FontHeightInPoints = 12;
                        remarkStyle.SetFont(rfont);
                        rcell.CellStyle          = remarkStyle;
                        RemarkRow.HeightInPoints = rowHeight * 5;
                        rcell.SetCellValue(exportSheetInfo.Remark);
                    }

                    //输出表头
                    IRow headerRow = sheet.CreateRow(headIndex);
                    //设置行高
                    headerRow.HeightInPoints = rowHeight;
                    //首行样式
                    ICellStyle HeaderStyle = workbook.CreateCellStyle();
                    HeaderStyle.FillPattern         = FillPattern.SolidForeground;
                    HeaderStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
                    IFont font = workbook.CreateFont();
                    font.Boldweight = short.MaxValue;
                    HeaderStyle.SetFont(font);
                    HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
                    HeaderStyle.Alignment         = HorizontalAlignment.Center;

                    //输出表头信息 并设置表头样式
                    int i = 0;
                    foreach (var data in ColumnInfoList)
                    {
                        cell = headerRow.CreateCell(i);
                        cell.SetCellValue(data.Header.Trim());
                        cell.CellStyle = HeaderStyle;
                        i++;
                    }

                    //开始循环所有行
                    int iRow     = 1 + headIndex;
                    int startRow = iRow;

                    while (startRow < MAX_ROW_INDEX && drIndex < total)
                    {
                        row = sheet.CreateRow(startRow);
                        row.HeightInPoints = rowHeight;
                        i  = 0;
                        dr = dt.Rows[drIndex];
                        foreach (var item in ColumnInfoList)
                        {
                            cell = row.CreateCell(i);
                            if (item.IsMapDT)
                            {
                                cellValue = dr[item.Field];
                                Type columnType = dt.Columns[item.Field].DataType;
                                cell.SetCellValue(cellValue, columnType);
                                cell.CellStyle = item.Style;

                                if (item.IsLink)
                                {
                                    cellValue = dr[item.Field + "Link"];
                                    if (cellValue != DBNull.Value && cellValue != null)
                                    {
                                        //建一个HSSFHyperlink实体,指明链接类型为URL(这里是枚举,可以根据需求自行更改)
                                        HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
                                        //给HSSFHyperlink的地址赋值 ,默认为该列加上Link
                                        link.Address   = cellValue.ToString();
                                        cell.Hyperlink = link;
                                        cell.CellStyle = leftStyleLink;
                                    }
                                }
                            }
                            i++;
                        }
                        drIndex++;
                        startRow++;
                        writeTotal++;
                    }
                    //自适应列宽度
                    for (int j = 0; j < ColumnInfoList.Count; j++)
                    {
                        sheet.AutoSizeColumn(j);
                        int width = sheet.GetColumnWidth(j) + 2560;
                        sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                    }
                }
            }

            if (s is ZipOutputStream)
            {
                ZipOutputStream zs = s as ZipOutputStream;
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    byte[] m_buffer = ms.GetBuffer();
                    zs.Write(m_buffer, 0, m_buffer.Length);
                }
            }
            else
            {
                workbook.Write(s);
            }
        }