private static Cell CreateSharedStringCell(string text, SharedStringTablePart part) { int index = ExcelTemplateTool.InsertSharedStringItem(text, part); Cell cell = new Cell(); cell.CellValue = new CellValue(index.ToString()); cell.DataType = CellValues.SharedString; return(cell); }
public async Task <bool> FillAsync(ITemplate t, IDictionary <string, dynamic> stuffing, Stream output) { dynamic rows = stuffing.Values.FirstOrDefault(a => a is IEnumerable <object>); if (rows == null) { return(false); } //using (Stream docstream = new MemoryStream()) using (SpreadsheetDocument doc = SpreadsheetDocument.Create(output, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = doc.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); #region styles // Add styles var stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = new Stylesheet(); // number formats, date is a formatted number //var numFmt = @"<numFmts count=""1""><numFmt numFmtId=""166"" formatCode=""[$-F800]dddd\,\ mmmm\ dd\,\ yyyy""/></numFmts>"; var numFmtDate = new NumberingFormat(); numFmtDate.NumberFormatId = 166; numFmtDate.FormatCode = @"[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"; stylesPart.Stylesheet.NumberingFormats = new NumberingFormats(); stylesPart.Stylesheet.NumberingFormats.Count = 1; stylesPart.Stylesheet.NumberingFormats.AppendChild(numFmtDate); // blank font list stylesPart.Stylesheet.Fonts = new Fonts(); stylesPart.Stylesheet.Fonts.Count = 1; stylesPart.Stylesheet.Fonts.AppendChild(new Font()); // create fills stylesPart.Stylesheet.Fills = new Fills(); // create a solid red fill var solidRed = new PatternFill() { PatternType = PatternValues.Solid }; solidRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFF0000") }; // red fill solidRed.BackgroundColor = new BackgroundColor { Indexed = 64 }; stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed }); stylesPart.Stylesheet.Fills.Count = 3; // blank border list stylesPart.Stylesheet.Borders = new Borders(); stylesPart.Stylesheet.Borders.Count = 1; stylesPart.Stylesheet.Borders.AppendChild(new Border()); // blank cell format list stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats(); stylesPart.Stylesheet.CellStyleFormats.Count = 1; stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat()); // cell format list stylesPart.Stylesheet.CellFormats = new CellFormats(); // empty one for index 0, seems to be required stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()); // cell format index 1 applies the date format stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, NumberFormatId = 166, ApplyNumberFormat = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center }); //stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center }); stylesPart.Stylesheet.CellFormats.Count = 2; //stylesPart.Stylesheet.Save(); #endregion // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. // Workbook -> Sheets -> Sheet Sheet sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); // Get the SharedStringTablePart. If it does not exist, create a new one. SharedStringTablePart sharedStringPart = ExcelTemplateTool.GetSharedStringTablePart(doc); // don't let it confuse you // doc -> WorkbookPart -> WorksheetPart -> WorkSheet -> SheetData SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild <SheetData>(); foreach (var datarow in rows) { IEnumerable <object> cells = datarow as IEnumerable <object>; if (cells == null) { continue; } Row contentRow = new Row(); //int index = 10; //contentRow.RowIndex = (UInt32)index; foreach (var cellObj in cells) { Cell c; if (cellObj == null) { c = CreateEmptyCell(); } else if (cellObj is string) { c = CreateSharedStringCell((string)cellObj, sharedStringPart); } else if (cellObj is DateTime) { c = CreateDateTimeCell((DateTime)cellObj); } else if (cellObj.GetType().GetTypeInfo().IsValueType) { c = CreateValueCell(cellObj); } else { c = CreateInlineStringCell(cellObj.ToString()); } contentRow.AppendChild(c); } sheetdata.AppendChild(contentRow); } workbookpart.Workbook.Save(); } return(true); }