ARTICLE AD BOX
My .NET Core web app outputs Excel files (.xlsx) when requested. There are two types of Excel file reports (two endpoints).
I start with a template Excel file (.xlsx) that is not empty and may have its own styles. There are two different templates depending on the endpoint. Using the routine below, I try to output an Excel file filled with data. So far, I have only managed to make it work with one endpoint. I firmly believe the issue is related to the template and its styles. Note that the routine is used for both endpoints.
The routine:
// class EnhExportXLSXModel constructor public EnhExportXLSXModel(IEnumerable<T> lsbi/*the data*/, string sourcePath, int firstRow=2) { byte[] SourceXlsxCont = File.ReadAllBytes(sourcePath); MemoryStream memstream = new MemoryStream(); memstream.Write(SourceXlsxCont, 0, SourceXlsxCont.Length); SpreadsheetDocument workbook = SpreadsheetDocument.Open(memstream, true); uint sheetId = 1; var sheetPart = workbook.WorkbookPart.WorksheetParts.FirstOrDefault(); var sheetData = sheetPart.Worksheet.GetFirstChild<SheetData>(); Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; Sheet sheet = sheets.Elements<Sheet>().FirstOrDefault(s => s.Id == relationshipId); // setting worksheet styles (formats), including one for dates var stylesPart = workbook.WorkbookPart.WorkbookStylesPart; // The PROBLEM lies here, I think: //stylesPart.Stylesheet.AppendChild(new Stylesheet stylesPart.Stylesheet = new Stylesheet { Fonts = new Fonts(new Font()), Fills = new Fills(new Fill()), Borders = new Borders(new Border()), NumberingFormats = new NumberingFormats(new NumberingFormat { NumberFormatId = 164, FormatCode = "#,##0\\ \"€\"" }), CellStyleFormats = new CellStyleFormats(new CellFormat()), CellFormats = new CellFormats( new CellFormat(), new CellFormat { NumberFormatId = 14, // 14 is a localized short Date(d/m/yyyy) ApplyNumberFormat = true }, new CellFormat { NumberFormatId = 164, // 164 is euro money ApplyNumberFormat = true, FormatId = 2 }, new CellFormat { NumberFormatId = 22, // 22 is a DateTime (d/m/yyyy hh:mm) ApplyNumberFormat = true }) }/*)*/; int rowidx = firstRow; foreach (T e in lsbi) { Row row = new Row() { RowIndex = (UInt32) rowidx }; int ncol = 1; foreach (var col in ColumnsProperties) { // variables that get the type, value, etc from the data (a List) var v = col.GetValue(e); var t = GetCellType(col); var mt = GetNumberFormatIfAny(col); var dtwt = GetIfDateWithTime(col); Cell cell = new Cell() { CellReference = ExcelReference(rowidx, ncol++), DataType = t == CellValues.Date ? CellValues.Number : t, CellValue = t == CellValues.Date ? new CellValue(Conversors.ToDateTime(v)?.ToOADate() .ToString(CultureInfo.InvariantCulture) ?? "") : new CellValue(t == CellValues.Number ? Conversors.ToDouble(v).ToString("#.######", CultureInfo.InvariantCulture) : (v?.ToString() ?? "")) }; if (t == CellValues.Date) cell.StyleIndex = (dtwt ?? false) ? (UInt32Value)3 : (UInt32Value)1; // NOTE below if (mt != null) cell.StyleIndex = (mt ?? 0) == 164 ? (UInt32Value)2 : (UInt32Value)0; //NOTE below row.Append(cell); } sheetData.Append(row); rowidx++; } // Close the document workbook.Dispose(); XlsxContent = memstream.ToArray(); // XlsxContent is a class variable } How can I add styles to the Excel StyleSheet at the line indicated without overwriting the existing styles? I tried using AppendChild but couldn't get it to work.