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