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