Use SSIS to populate Excel workbook generated using OOXML

Question

We are trying to generate MS Excel workbook using OOXML and populate data using SSIS. We are able to generate Workbook and sheets, also able to create columns and insert data in the Header cell. We can also populate data using SSIS.

But the Sheet (DocumentFormat.OpenXml.Spreadsheet.Sheet) and all cells (DocumentFormat.OpenXml.Spreadsheet.Cell) becomes OpenXmlUnknownElement. So we are not able to read sheet / cell using following code: Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").SingleOrDefault<Sheet>();

We are able to read the same file if we first open it using MS Excel and save. Does anyone know how to resolve this?

Answer 1

You probably forgot to give your sheet a name. You can see this by using

    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault 

and you'll see that your sheet name is either undefined or garbage text.

If that does not help. Create a simple document in code save it in OOXML and open it in a xml viewer. then make a copy open it in Excel and save it and see the difference in xml. That is often a good start seeing what excel has added by default to the document.

Excel is very tolerant of things that you have done wrong when creating the document in code, and magically fix them when you open the document again.

A bad hack would be using interop to open the document saving it again in code. wich would fix everything for you.

    Workbook wrkbk = app.Workbooks.Open(@"c:\del.xls");
    wrkbk.Save();
    wrkbk.Close();