Overview
It is kind of a necessary requirement in any project to export data in Excel, Word or PDF format. Management often requires data in Excel format since they are more familiar with Excel operations. We have options to export grid data or simply enable the copy & paste feature on a grid for the “poor-man's” effort.
All these are alright but what if the management requires data to be in some pre-defined format? What if they need extra information that is not present in the grid?
Well we can do that also in .NET. I got the requirement from the management and after some effort I came up with an approach. Though, I am still working on this to make it more generic, but the option is available in the production and the management is happy.
I can't complete it in one article. So let's start the first article on this. I will provide the complete source code when I finish the last one.
Open XML format
Starting with Office 2007, the XML format became the default target file format for Microsoft Office. The XML version for Excel is known as Spreadsheet XML.
If you have an .xlsx file, first Zip it and then if you unzip it to the following folder structure:
![]()
It's important to understand this structure since it is the base for the design. If you open the xl folder you can see that the sheets are saved as simple XML files:
![]()
Shared String
If my Sheet looks like this:
![]()
Excel generally stores the cell values in a shared string for string types. If it is a number or date then it stores in that XML document not in shared string.
![]()
See the “Sheet data” maintains the Cell reference and Cell Value. A Shared String is a database in an XML package to store all common strings in a single place and the reference is stored in the sheet.
If you open the shared string it will look like this:
![]()
Workbook
This is the main XML that is having all of the sheet's reference ids and names. If you open the workbook it should look like this:
![]()
So, if we understood the concepts of Worksheet, Workbook, Relationship Id and Shared string then we are ready to go. I hope you got some idea about those. If you have quieres then you can comment here or you can also search in Google. People working with simple grid import must also understand the concept of Workbook and Worksheet. Here I explained Shared Strings that store the cell values referenced in Sheet data.
How to Create an Excel File
We need to use OPEN XML SDK v 2.0 as in the following:
http://msdn.microsoft.com/en-us/library/office/gg278316(v=office.15).aspx
I will continue this series with an article showing how to work with the Open XML SDK and use a template for the data export.