Generate Pivot Table in CSV Dynamically from Dictionary with Documentformat.Openxml

Question

I'm using Documentformat.Openxml package

[https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.8.1]

to generate a Pivot table in a CSV dynamically from a Dictionary<string, List<string>> object which has a structure similar to this:

I'm following the Sample here to create the table which should look somewhat like:

but not sure about how to create the table from the dictionary of data.

My current code snippet:

// Add a WorkbookPart to the document.  
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.  
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.  
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());

            // Append a new worksheet and associate it with the workbook.  
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };

            sheets.Append(sheet);
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();
            Row row = new Row();
            Cell cell = new Cell()
            {
                CellReference = "A1",
                DataType = CellValues.String,
                CellValue = new CellValue("Microsoft")
            };
            row.Append(cell);
            sheetData.Append(row);
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            // Close the document.  
            spreadsheetDocument.Close();

In the above snippet, you can see that I'm hardcoding the cell value but in reality I want to populate it dynamically from the dictionary and there is a large amount of data in it. What is the best way to do this? Can someone please help?

score 0 · Answer 1 · answered Sep 02 '21 at 07:23

As your question shows you want to generate excel file using Open XML. You should take a look at these answers

1 Using open XML to create excel file
2 How do you convert Excel to CSV using OpenXML SDK? and for other websites you can visit
3 https://www.c-sharpcorner.com/article/export-and-import-excel-file-using-closedxml-in-asp-net-mvc/

Generate Pivot Table in CSV Dynamically from Dictionary with Documentformat.Openxml

1 Answers1