public static void SetTemplate(Stream templateStream)
{
Template = templateStream.ReadAllBytes();
MemoryStream memoryStream = new MemoryStream();
memoryStream.WriteAllBytes(Template);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(memoryStream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart !;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
Worksheet worksheet = worksheetPart.Worksheet;
CellBuilder = new CellBuilder()
{
CellFormatCount = document.WorkbookPart !.WorkbookStylesPart !.Stylesheet.CellFormats !.Count !,
DefaultStyles = new Dictionary <DefaultStyle, UInt32Value>
{
{ DefaultStyle.Title, worksheet.FindCell("A1").StyleIndex ! },
static void WritePlainExcel(ResultTable results, Stream stream, string title)
{
stream.WriteAllBytes(Template);
if (results == null)
{
throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
worksheetPart.Worksheet = new Worksheet();
worksheetPart.Worksheet.Append(new Columns(results.Columns.Select((c, i) => new spreadsheet.Column()
{
Min = (uint)i + 1,
Max = (uint)i + 1,
Width = GetColumnWidth(c.Column.Type),
BestFit = true,
CustomWidth = true
}).ToArray()));
Dictionary <ResultColumn, (DefaultStyle defaultStyle, UInt32Value styleIndex)> indexes =
results.Columns.ToDictionary(c => c, c => CellBuilder.GetDefaultStyleAndIndex(c));
var ss = document.WorkbookPart.WorkbookStylesPart.Stylesheet;
{
var maxIndex = ss.NumberingFormats.ChildElements.Cast <NumberingFormat>()
.Max(f => (uint)f.NumberFormatId) + 1;
var decimalCellFormat = ss.CellFormats.ElementAt((int)(uint)CellBuilder.DefaultStyles[DefaultStyle.Decimal]);
foreach (var kvp in CellBuilder.CustomDecimalStyles)
{
var numberingFormat = new NumberingFormat
{
NumberFormatId = maxIndex++,
FormatCode = kvp.Key
};
ss.NumberingFormats.AppendChild(numberingFormat);
var cellFormat = (CellFormat)decimalCellFormat.CloneNode(false);
cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
ss.CellFormats.AppendChild(cellFormat);
ss.CellFormats.Count = (uint)ss.CellFormats.ChildElements.Count;
if (ss.CellFormats.Count != kvp.Value + 1)
{
throw new InvalidOperationException("Unexpected CellFormats count");
}
}
}
worksheetPart.Worksheet.Append(new Sequence <Row>()
{
new [] { CellBuilder.Cell(title, DefaultStyle.Title) }.ToRow(),
(from c in results.Columns
select CellBuilder.Cell(c.Column.DisplayName, DefaultStyle.Header)).ToRow(),
from r in results.Rows
select(from c in results.Columns
let t = indexes.GetOrThrow(c)
select CellBuilder.Cell(r[c], t.defaultStyle, t.styleIndex)).ToRow()
}.ToSheetData());
workbookPart.Workbook.Save();
document.Close();
}
}
