Troubleshooting OpenXML Spreadsheet
In OpenXML Spreadsheet tutorial series you have learned how to create Excel file in .NET with C#. There might be cases when you get an error upon opening the generated excel file:
<We found a problem with some content in ‘Report.xlxs’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.>
If you click Yes, Excel will try to repair the document for you. But the data might not be as what you expect it to be. After Excel tried to recover the file it will displays a recovery report and where the problem might be originated.
As you might know Excel document is an XML document. You can use Visual Studio to open Excel files and be able to view its XML structure.
In order to do this you need to install Open XML Package Editor for Visual Studio Extension which is by Microsoft and is free. At the moment the extension only supports Visual Studio 2013, 2012, and 2010.
After installing the Open XML Package Editor extension let’s open our Report.xlsx in Visual Studio and check what’s going on behind the scene.
The root, i.e. xl, is lined to workbook.xml. And you can see by expanding the workbook.xml, there are links to styles.xml and sheet.xml.
Open sheet.xml.
Hint: Press Ctrl + D shortcut to structure the document.
Here you can see our custom column settings along with the sheetData where the actual rows and cells of data existed.
In order to find where the problem of generated Excel is, the best way is to create a simple sample of what you want to achieve in Excel and open it with Open XML Package Editor, and compare it with the generated xml. You will find where your XML construction went wrong.