Post
Ask Question
Kashif Butt

Kashif Butt

  • NA
  • 12
  • 6.7k

Getting error while using OpenXML for Excel Export

Nov 8 2017 1:15 PM

i'am trying to export data to Excel. For that purpose i use OpenXML.

The Structure of the Excel should look like this:

https://i.stack.imgur.com/ryJym.png
 
My current Code: 
  1. public void CreateExcelDoc(string fileName)  
  2.    {  
  3.        using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))  
  4.        {  
  5.            WorkbookPart workbookPart = document.AddWorkbookPart();  
  6.            workbookPart.Workbook = new Workbook();  
  7.   
  8.            WorksheetPart worksheetPart = workbookPart.AddNewPart();  
  9.            worksheetPart.Worksheet = new Worksheet();  
  10.   
  11.            Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());  
  12.   
  13.            Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet" };  
  14.   
  15.            sheets.Append(sheet);  
  16.   
  17.            workbookPart.Workbook.Save();  
  18.   
  19.   
  20.            SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());  
  21.   
  22.   
  23.   
  24.            // Constructing header  
  25.            Row row = new Row();  
  26.   
  27.            row.Append(  
  28.                ConstructCell("Vendor Name", CellValues.String),  
  29.                ConstructCell("Vendor Mail", CellValues.String),  
  30.                ConstructCell("Vendor Adress", CellValues.String),  
  31.                ConstructCell("Vendor Phone", CellValues.String));  
  32.   
  33.            // Insert the header row to the Sheet Data  
  34.            sheetData.AppendChild(row);  
  35.   
  36.            ServicesExportDataProvider dp = new ServicesExportDataProvider();  
  37.            var vendors = dp.GetVendors();  
  38.   
  39.   
  40.   
  41.            // Inserting each vendor  
  42.            foreach (var vendor in vendors)  
  43.            {  
  44.                row = new Row();  
  45.   
  46.                row.Append(  
  47.                    ConstructCell(vendor.VendorName, CellValues.String),  
  48.                    ConstructCell(vendor.VendorMail, CellValues.String),  
  49.                    ConstructCell(vendor.VendorAdress, CellValues.String),  
  50.                    ConstructCell(vendor.VendorPhone, CellValues.String));  
  51.   
  52.                sheetData.AppendChild(row);  
  53.            }  
  54.   
  55.            InsertEmptyRow();  
  56.   
  57.   
  58.   
  59.            Column column = new Column();  
  60.            Cell cell = ConstructCell("Info", CellValues.String);  
  61.            column.Append(cell); //  **<--- Creates the Exception**  
  62.   
  63.   
  64.            sheetData.AppendChild(column);  
  65.   
  66.   
  67.   
  68.   
  69.            worksheetPart.Worksheet.Save();  
  70.        }  
  71.    } 
I can export the "Vendors" - Lines. But if i try then to Create columns to Export the "Item Information" vertically, i get the following error:
 
Non-composite elements do not have child elements
  1. Column column = new Column();  
  2.  Cell cell = ConstructCell("Info", CellValues.String);  
  3.  column.Append(cell); //  **<--- This line throws the error
I can't figure out the problem. Do you have any ideas? How can I Export data in the structure of "Item Information"? 

Answers (2)