/// <summary> /// 渲染到xls /// </summary> /// <param name="isOnlyOneSheet"></param> /// <returns></returns> public MemoryStream RenderToXls(bool isOnlyOneSheet = true) { using (var ms = new MemoryStream()) { HSSFWorkbook workbook = new HSSFWorkbook(); int rowIndex = 0; HSSFSheet sheet = null; foreach (var table in listTable) { int columnCount = table.ColumnCount; var title = table.Title == null ? new ExcelTitle() : table.Title; var header = table.Header == null ? new ExcelHeader() : table.Header; var tableheader = table.TableHeader == null ? new ExcelTableHeader() : table.TableHeader; //table.Rows.FirstOrDefault(r => r.IsRowHead); var tablebody = table.TableBody == null ? new ExcelTableBody() : table.TableBody; //table.Rows.Where(r => !r.IsRowHead).ToList(); var tablefooter = table.TableFooter == null ? new ExcelTableFooter() : table.TableFooter; var footer = table.Footer == null ? new ExcelFooter() : table.Footer; if (rowIndex == 0) { sheet = title.IsNullOrWhiteSpace() ? workbook.CreateSheet() as HSSFSheet : workbook.CreateSheet(title.TableTitle) as HSSFSheet; PrintSetup(sheet, table.Landscape); if (table.ColumnWidths != null && table.ColumnWidths.Length == columnCount) { for (var i = 0; i < columnCount; i++) { sheet.SetColumnWidth(i, table.ColumnWidths[i] * 256 + 200); } } else { if (!tableheader.IsNull()) { for (var i = 0; i < columnCount; i++) { int columnWidth = Encoding.Default.GetBytes(tableheader.Rows[0].Cells[i].Value.ToString()).Length; sheet.SetColumnWidth(i, columnWidth * 256 + 200); } } } } //标题 if (!title.IsNullOrWhiteSpace()) { IRow rowTitle = sheet.CreateRow(rowIndex); rowTitle.Height = short.Parse(title.TitleHeight * 20 + ""); ICell headerCell = rowTitle.CreateCell(0); headerCell.SetCellValue(title.TableTitle); sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1)); var defaultTitleCellStyle = string.IsNullOrWhiteSpace(title.DefaultTitleCellStyle) ? workbook.GetDefaultTitleCellStyle() : workbook.GetUserDirCellStyle(title.DefaultTitleCellStyle); headerCell.Style(defaultTitleCellStyle); //ExcelCellSetter.SetDefaultTitleCellStyle(workbook, headerCell); rowIndex++; } //页头 if (!header.IsNull()) { ICellStyle defaultHeaderCellStyle = string.IsNullOrWhiteSpace(header.DefaultHeaderCellStyle) ? workbook.GetDefaultHeaderCellStyle() : workbook.GetUserDirCellStyle(header.DefaultHeaderCellStyle);//ExcelCellSetter.GetDefaultHeaderCellStyle(workbook); for (var rindex = 0; rindex < header.Rows.Count; rindex++) { var headerRow = sheet.CreateRow(rowIndex); var row = header.Rows[rindex]; if (row.HaveRowBreak) { SetRowBreak(rowIndex, sheet); } headerRow.Height = short.Parse(row.Height * 20 + ""); int colindex = 0; for (var cindex = 0; cindex < row.Cells.Count; cindex++) { int colspan = row.Cells[cindex].Colspan; CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, colindex, colindex + colspan - 1); sheet.AddMergedRegion(address); headerRow.CreateCell(colindex).SetCellValue(row.Cells[cindex].Value); if (row.Cells[cindex].CellStyle != null) { headerRow.Cells[colindex].Style(row.Cells[cindex].CellStyle); //ExcelCellSetter.SetCellStyle(headerRow.Cells[colindex], row.Cells[cindex].CellStyle); } else if (row.Cells[cindex].Style != null) { if (row.Cells[cindex].IsBasedOnDefaultStyle) { headerRow.Cells[colindex].Style(defaultHeaderCellStyle); } headerRow.Cells[colindex].Style(row.Cells[cindex].Style); } else { headerRow.Cells[colindex].Style(defaultHeaderCellStyle); //ExcelCellSetter.SetCellStyle(headerRow.Cells[colindex], defaultHeaderCellStyle); } colindex = colindex + colspan; } rowIndex++; } } //表头 if (!tableheader.IsNull()) { ICellStyle defaultTableHeaderCellStyle = string.IsNullOrWhiteSpace( tableheader.DefaultTableHeaderCellStyle) ? workbook.GetDefaultTableHeaderCellStyle() : workbook.GetUserDirCellStyle(tableheader.DefaultTableHeaderCellStyle);//ExcelCellSetter.GetDefaultTableHeaderCellStyle(workbook); foreach (var hr in tableheader.Rows) { var headerRow = sheet.CreateRow(rowIndex++); headerRow.Height = short.Parse(hr.Height * 20 + ""); for (var i = 0; i < columnCount; i++) { ExcelCell cell = (ExcelCell)hr.Cells[i]; headerRow.CreateCell(i).SetCellValue(cell.Value); if (cell.CellStyle != null) { headerRow.Cells[i].Style(cell.CellStyle); //ExcelCellSetter.SetCellStyle(headerRow.Cells[i], cell.CellStyle); } else if (cell.Style != null) { if (cell.IsBasedOnDefaultStyle) { headerRow.Cells[i].Style(defaultTableHeaderCellStyle); } headerRow.Cells[i].Style(cell.Style); } else { headerRow.Cells[i].Style(defaultTableHeaderCellStyle); //ExcelCellSetter.SetCellStyle(headerRow.Cells[i], defaultTableHeaderCellStyle); } } } } //表体 if (!tablebody.IsNull()) { ICellStyle defaultcellstyle = string.IsNullOrWhiteSpace(tablebody.DefaultTableBodyCellStyle) ? workbook.GetDefaultTableBodyCellStyle() : workbook.GetUserDirCellStyle(tablebody.DefaultTableBodyCellStyle);//ExcelCellSetter.GetDefaultCellStyle(workbook); //表体 foreach (var row in tablebody.Rows) { var dataRow = sheet.CreateRow(rowIndex++); if (row.HaveRowBreak) { SetRowBreak(rowIndex, sheet); } dataRow.Height = short.Parse(row.Height * 20 + ""); for (var i = 0; i < columnCount; i++) { ExcelCell cell = (ExcelCell)row.Cells[i]; var cellval = cell.Value == null ? "" : cell.Value; dataRow.CreateCell(i, (CellType)cell.CellType).SetCellValue(cellval); if (cell.CellStyle != null) { dataRow.Cells[i].Style(cell.CellStyle); //ExcelCellSetter.SetCellStyle(dataRow.Cells[i], cell.CellStyle); } else if (cell.Style != null) { if (cell.IsBasedOnDefaultStyle) { dataRow.Cells[i].Style(defaultcellstyle); } dataRow.Cells[i].Style(cell.Style); } else { dataRow.Cells[i].Style(defaultcellstyle); //ExcelCellSetter.SetCellStyle(dataRow.Cells[i], defaultcellstyle); } } } } //表尾 if (!tablefooter.IsNull()) { ICellStyle defaultTableFooterCellStyle = string.IsNullOrWhiteSpace( tablefooter.DefaultTableFooterCellStyle) ? workbook.GetDefaultTableFooterCellStyle() : workbook.GetUserDirCellStyle(tablefooter.DefaultTableFooterCellStyle);//ExcelCellSetter.GetDefaultTableHeaderCellStyle(workbook); foreach (var hr in tablefooter.Rows) { var footerRow = sheet.CreateRow(rowIndex++); footerRow.Height = short.Parse(hr.Height * 20 + ""); for (var i = 0; i < columnCount; i++) { ExcelCell cell = (ExcelCell)hr.Cells[i]; footerRow.CreateCell(i).SetCellValue(cell.Value); if (cell.CellStyle != null) { footerRow.Cells[i].Style(cell.CellStyle); } else if (cell.Style != null) { if (cell.IsBasedOnDefaultStyle) { footerRow.Cells[i].Style(defaultTableFooterCellStyle); } footerRow.Cells[i].Style(cell.Style); } else { footerRow.Cells[i].Style(defaultTableFooterCellStyle); } } } } //页尾 if (!footer.IsNull()) { ICellStyle defaultFooterCellStyle = string.IsNullOrWhiteSpace(footer.DefaultFooterCellStyle) ? workbook.GetDefaultFooterCellStyle() : workbook.GetUserDirCellStyle(footer.DefaultFooterCellStyle);//ExcelCellSetter.GetDefaultFooterCellStyle(workbook); for (var rindex = 0; rindex < footer.Rows.Count; rindex++) { var footerRow = sheet.CreateRow(rowIndex); var row = footer.Rows[rindex]; if (row.HaveRowBreak) { SetRowBreak(rowIndex, sheet); } //1 px = 0.75 point footerRow.Height = short.Parse(row.Height * 0.75 * 20 + ""); int colindex = 0; for (var cindex = 0; cindex < row.Cells.Count; cindex++) { int colspan = row.Cells[cindex].Colspan; CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, colindex, colindex + colspan - 1); sheet.AddMergedRegion(address); if (row.Cells[cindex].CellType == Common.CellTypes.Image) { footerRow.CreateCell(colindex); byte[] imgbyte = Convert.FromBase64String(row.Cells[cindex].Value); int pictureIdx = workbook.AddPicture(imgbyte, PictureType.PNG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 1, 0, 0, 0, rowIndex, colspan, rowIndex + 1); anchor.AnchorType = AnchorType.DontMoveAndResize; HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(0.99, 0.99); //pict.Resize(); } else { footerRow.CreateCell(colindex).SetCellValue(row.Cells[cindex].Value); } if (row.Cells[cindex].CellStyle != null) { footerRow.Cells[colindex].Style(row.Cells[cindex].CellStyle); //ExcelCellSetter.SetCellStyle(footerRow.Cells[colindex], row.Cells[cindex].CellStyle); } else if (row.Cells[cindex].Style != null) { if (row.Cells[cindex].IsBasedOnDefaultStyle) { footerRow.Cells[colindex].Style(defaultFooterCellStyle); } footerRow.Cells[colindex].Style(row.Cells[cindex].Style); } else { footerRow.Cells[colindex].Style(defaultFooterCellStyle); //if (defaultFooterCellStyle != null) //ExcelCellSetter.SetCellStyle(footerRow.Cells[colindex], defaultFooterCellStyle); } colindex = colindex + colspan; } rowIndex++; } } if (!isOnlyOneSheet) { rowIndex = 0; } } workbook.Write(ms); ms.Position = 0; workbook.Close(); return(ms); } }