public IActionResult Post([FromBody] ExcellData data) { MemoryStream ms = new MemoryStream(); SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet(); WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = CreateStylesheet(); stylesPart.Stylesheet.Save(); SheetData sheetData = new SheetData(); worksheet.Append(sheetData); worksheetPart.Worksheet = worksheet; worksheetPart.Worksheet.Save(); UInt32Value currentRowIndex; currentRowIndex = CreateHeader(worksheet, sheetData, data); currentRowIndex = CreateDataTables(worksheet, sheetData, data, currentRowIndex); Sheets sheets = new Sheets(); Sheet sheet = new Sheet { Name = "Registro IVA corrispettivi", SheetId = 1, Id = workbookPart.GetIdOfPart(worksheetPart) }; sheets.Append(sheet); workbook.Append(sheets); spreadsheetDocument.WorkbookPart.Workbook = workbook; spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); byte[] content = ms.ToArray(); ms.Seek(0, SeekOrigin.Begin); return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml", "corrispettivi.xlsx")); }
private UInt32Value CreateHeader(Worksheet worksheet, SheetData sheetData, ExcellData data) { Row firstHeaderRow = new Row { RowIndex = 1 }; Cell headerCell = new Cell { DataType = CellValues.String, CellValue = new CellValue("Registro IVA dei corispettivi"), CellReference = "A1", StyleIndex = 1, }; firstHeaderRow.Append(headerCell); sheetData.Append(firstHeaderRow); Row secondHeaderRow = new Row { RowIndex = 3 }; Cell companyNameCaptionCell = new Cell { DataType = CellValues.String, CellValue = new CellValue("Ragione sociale"), CellReference = "A3", StyleIndex = 1, }; Cell companyNameValueCell = new Cell { DataType = CellValues.String, CellValue = new CellValue((string)data.Agreement.Company.Name), CellReference = "D3", StyleIndex = 0, }; Cell periodCaptionCell = new Cell { DataType = CellValues.String, CellValue = new CellValue("Periodo"), CellReference = "I3", StyleIndex = 1, }; Cell periodValueCell = new Cell { DataType = CellValues.String, CellValue = new CellValue((string)data.Period), CellReference = "J3", StyleIndex = 0, }; secondHeaderRow.Append(companyNameCaptionCell, companyNameValueCell, periodCaptionCell, periodValueCell); sheetData.Append(secondHeaderRow); Row thirdHeaderRow = new Row { RowIndex = 4 }; Cell companyVatNumberCaptionCell = new Cell { DataType = CellValues.String, CellValue = new CellValue("Partita IVA"), CellReference = "A4", StyleIndex = 1, }; Cell companyVatNumberValueCell = new Cell { DataType = CellValues.String, CellValue = new CellValue((string)data.Agreement.Company.VatNumber), CellReference = "D4", StyleIndex = 0, }; thirdHeaderRow.Append(companyVatNumberCaptionCell, companyVatNumberValueCell); sheetData.Append(thirdHeaderRow); Merge(worksheet, headerCell.CellReference, "K1"); Merge(worksheet, companyNameCaptionCell.CellReference, "C3"); Merge(worksheet, companyNameValueCell.CellReference, "H3"); Merge(worksheet, periodValueCell.CellReference, "K3"); Merge(worksheet, companyVatNumberCaptionCell.CellReference, "C4"); Merge(worksheet, companyVatNumberValueCell.CellReference, "H4"); return(thirdHeaderRow.RowIndex); }
private UInt32Value CreateDataTables(Worksheet worksheet, SheetData sheetData, ExcellData data, UInt32Value currentRowIndex) { currentRowIndex += 3; foreach (var page in data.Pages) { currentRowIndex = CreateDataTableHeader(worksheet, sheetData, currentRowIndex, page); foreach (var currentRow in page.Rows) { currentRowIndex += 1; currentRowIndex = CreateDataTableRow(worksheet, sheetData, currentRowIndex, currentRow); } if (page.Summary != null && page.Summary.Count() > 0) { currentRowIndex += 2; currentRowIndex = CreateSummaryTable(worksheet, sheetData, currentRowIndex, page); } currentRowIndex += 2; } currentRowIndex = CreateFinalSummaryTableHeader(worksheet, sheetData, currentRowIndex); currentRowIndex += 1; currentRowIndex = CreateFinalSummaryTableRows(worksheet, sheetData, currentRowIndex, data.FinalSummary, data.FinalSummaryTotals); return(currentRowIndex); }