public static void WriteToStream(Report report, Theme theme, Stream outputStream) { var ef = new Workbook(); var ws = new Sheet("Report"); ef.Sheets.AddSheet(ws); if (report.MergedCells != null) { foreach (var m in report.MergedCells) ws[m.Row1, m.Col1, m.Row2, m.Col2].Merge(); } if (report.Cells != null) { foreach (var cell in report.Cells) { var eStyle = theme.GetCellStyle(cell.CellStyleIndex); CellData c = null; object v = cell.Value; if (eStyle != null) { if (eStyle.BorderStyle != null) { c = ws.Cells[cell.Row, cell.Column]; if (eStyle.BorderStyle.Left != null) c.Style.Border.Left = eStyle.BorderStyle.Left.ToBorderEdge(); if (eStyle.BorderStyle.Right != null) c.Style.Border.Right = eStyle.BorderStyle.Right.ToBorderEdge(); if (eStyle.BorderStyle.Top != null) c.Style.Border.Top = eStyle.BorderStyle.Top.ToBorderEdge(); if (eStyle.BorderStyle.Bottom != null) c.Style.Border.Bottom = eStyle.BorderStyle.Bottom.ToBorderEdge(); if (c.IsMerged && c.MergedRange.Cell1.Col == cell.Column && c.MergedRange.Cell1.Row == cell.Row) { for (var col = c.MergedRange.Cell1.Col + 1; col <= c.MergedRange.Cell2.Col; col++) { if (eStyle.BorderStyle.Top != null) ws.Cells[cell.Row, col].Style.Border.Top = c.Style.Border.Top; if (eStyle.BorderStyle.Bottom != null) ws.Cells[c.MergedRange.Cell2.Row, col].Style.Border.Bottom = c.Style.Border.Bottom; } for (var row = c.MergedRange.Cell1.Row + 1; row <= c.MergedRange.Cell2.Row; row++) { if (eStyle.BorderStyle.Left != null) ws.Cells[row, cell.Column].Style.Border.Left = c.Style.Border.Left; if (eStyle.BorderStyle.Right != null) ws.Cells[row, c.MergedRange.Cell2.Col].Style.Border.Right = c.Style.Border.Right; } } } if (eStyle.FontStyle != null) { c = c ?? ws.Cells[cell.Row, cell.Column]; if (!String.IsNullOrEmpty(eStyle.FontStyle.FontName)) c.Style.Font.Name = eStyle.FontStyle.FontName; if (eStyle.FontStyle.FontSize != 0) c.Style.Font.Size = eStyle.FontStyle.FontSize; if (eStyle.FontStyle.FontColor != null) c.Style.Font.Color = eStyle.FontStyle.FontColor.ToColor(); c.Style.Font.Bold = eStyle.FontStyle.Bold; c.Style.Font.Italic = eStyle.FontStyle.Italic; if (eStyle.FontStyle.Underline) c.Style.Font.Underline = FontUnderline.Single; } String numberFormat; if (cell.Format != null) if (GetNumberFormat(cell.Format, out numberFormat)) c.Style.Format = numberFormat; else v = cell.FormattedValue; if (eStyle.BackgroundColor != null) c.Style.Fill = new CellFill { Foreground = eStyle.BackgroundColor.ToColor(), Pattern = FillPattern.Solid }; } ws.Cells[cell.Row, cell.Column].Style.Alignment.HAlign = GetAlignment(cell.Alignment); ws.Cells[cell.Row, cell.Column].Value = v; } } //if (report.Columns.Count > 0) //{ // int colIndex = 0; // foreach (var col in report.Columns) // { // if (col.Width.HasValue) // ws.Columns[colIndex].Width = (int)(col.Width.Value * 256); // colIndex++; // } //} //if (report.Rows.Count > 0) //{ // int rowIndex = 0; // foreach (var row in report.Rows) // { // if (row.Height.HasValue) // ws.Rows[rowIndex].Height = (int)(row.Height.Value * 20); // rowIndex++; // } //} ef.SaveToStream(outputStream, XlsxFileWriterOptions.AutoFit); }
private static Theme CreateDefaultTheme() { var res = new Theme { PageNumberAlignment = CellAlignment.Right }; res[CellStyleIndex.Group3Caption] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#99BBE8"), LineStyle = LineStyle.Solid, LineWidth = 2 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 13, Bold = true }, BackgroundColor = Color.White, CellPadding = 2 }; res[CellStyleIndex.Group3Footer] = res[CellStyleIndex.Group3FooterHeader] = new CellStyle { BorderStyle = new BorderStyle { Top = new BorderEdgeStyle { Color = Color.FromHtml("#C0C0C0"), LineStyle = LineStyle.Solid, LineWidth = 2 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 9, Bold = true }, BackgroundColor = Color.FromHtml("#EEEEEE"), CellPadding = 2 }; res[CellStyleIndex.Group3FooterFooter] = new CellStyle { BorderStyle = new BorderStyle { Top = new BorderEdgeStyle { Color = Color.FromHtml("#C0C0C0"), LineStyle = LineStyle.Solid, LineWidth = 2 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 9, Bold = true }, BackgroundColor = Color.FromHtml("#DFDFDF"), CellPadding = 2 }; res[CellStyleIndex.Group3Header] = res[CellStyleIndex.Group3HeaderHeader] = res[CellStyleIndex.Group3HeaderFooter] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#99BBE8"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 9, Bold = true }, BackgroundColor = Color.FromHtml("#E7EFFA"), CellPadding = 2 }; res[CellStyleIndex.Group2Caption] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#99BBE8"), LineStyle = LineStyle.Solid, LineWidth = 2 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 11, Bold = true }, CellPadding = 2 }; res[CellStyleIndex.Group2Footer] = res[CellStyleIndex.Group2FooterHeader] = new CellStyle { BorderStyle = new BorderStyle { Top = new BorderEdgeStyle { Color = Color.FromHtml("#C0C0C0"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8, Bold = true }, BackgroundColor = Color.FromHtml("#EEEEEE"), CellPadding = 2 }; res[CellStyleIndex.Group2FooterFooter] = new CellStyle { BorderStyle = new BorderStyle { Top = new BorderEdgeStyle { Color = Color.FromHtml("#C0C0C0"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8, Bold = true }, BackgroundColor = Color.FromHtml("#DFDFDF"), CellPadding = 2 }; res[CellStyleIndex.Group2Header] = res[CellStyleIndex.Group2HeaderHeader] = res[CellStyleIndex.Group2HeaderFooter] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#99BBE8"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8, Bold = true }, BackgroundColor = Color.FromHtml("#E7EFFA"), CellPadding = 2 }; res[CellStyleIndex.Group1Caption] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#99BBE8"), LineStyle = LineStyle.Solid, LineWidth = 2 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 9, Bold = true }, CellPadding = 2 }; res[CellStyleIndex.Group1Footer] = res[CellStyleIndex.Group1FooterHeader] = new CellStyle { FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8 }, BackgroundColor = Color.FromHtml("#EEEEEE"), CellPadding = 2 }; res[CellStyleIndex.Group1FooterFooter] = new CellStyle { FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8 }, BackgroundColor = Color.FromHtml("#DFDFDF"), CellPadding = 2 }; res[CellStyleIndex.Group1Header] = res[CellStyleIndex.Group1HeaderHeader] = res[CellStyleIndex.Group1HeaderFooter] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#99BBE8"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8, Bold = true }, BackgroundColor = Color.FromHtml("#E7EFFA"), CellPadding = 2 }; res[CellStyleIndex.TableRow] = res[CellStyleIndex.TableRowHeader] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#EEEEEE"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8 }, CellPadding = 2 }; res[CellStyleIndex.TableRowFooter] = new CellStyle { BorderStyle = new BorderStyle { Bottom = new BorderEdgeStyle { Color = Color.FromHtml("#E5E5E5"), LineStyle = LineStyle.Solid, LineWidth = 1 } }, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8 }, BackgroundColor = Color.FromHtml("#EEEEEE"), CellPadding = 2 }; res[CellStyleIndex.Body] = new CellStyle { CellPadding = 2, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8 } }; res[CellStyleIndex.Note] = new CellStyle { CellPadding = 2, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 7, Bold = true } }; res[CellStyleIndex.H3] = new CellStyle { CellPadding = 2, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 9, Bold = true } }; res[CellStyleIndex.H2] = new CellStyle { CellPadding = 3, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 11, Bold = true } }; res[CellStyleIndex.H1] = new CellStyle { CellPadding = 3, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 13, Bold = true } }; res[CellStyleIndex.Title] = new CellStyle { CellPadding = 4, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 15, Bold = true } }; res[CellStyleIndex.Highlight] = new CellStyle { CellPadding = 2, FontStyle = new FontStyle { FontName = "Segoe UI", FontSize = 8, Bold = true } }; res.PageNumberStyle = res[CellStyleIndex.Body]; return res; }