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;
}
