How to read and write .xlsx (Excel 2007) file Part II
Download XLSXWriter.zip - 321.28 KB
Introduction
In Part 1 we have discussed about the facts and logic behind the Excel 2007 file. In this article we will see how we write the excel 2007 file.
As we have already seen sharedString.xml and Sheet1.xml now let explore some other files that are important in Excel 2007 file writing.
1. [ContentType].xml
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml" />
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" />
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" />
<Default Extension="xml" ContentType="application/xml" />
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" />
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" />
<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" /> <!-- Sheet Information -->
<Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" /> <!-- Sheet Information -->
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" /> <!-- Sheet Information -->
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" />
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml" />
</Types>
As the name shows this file contains the content type information of every file. When we write Excel file rest of the file would remain same but the portion which is related with sheet information would be changed because it depends upon the number of sheets that Excel file contains, For example if the excel file contains only one sheet then it contains only one line that would be representing sheet content information.
2. App.xml
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>Worksheets</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>3</vt:i4> <!-- Total number of Sheets -->
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="3" baseType="lpstr"> <!-- size attribute contains the value of the total number of sheets -->
<vt:lpstr>Sheet1</vt:lpstr> <!-- Sheet Reference -->
<vt:lpstr>Sheet2</vt:lpstr> <!-- Sheet Reference -->
<vt:lpstr>Sheet3</vt:lpstr> <!-- Sheet Reference -->
</vt:vector>
</TitlesOfParts>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>12.0000</AppVersion>
</Properties>
It is placed at docProp\app.xml, this file contains number and names of the Sheets. As you can see the comments on the above code, so when we will write this file we need to change value of the number of sheets and sheet names according to our excel file. Suppose we have two Sheets in an excel file and there name are SampleSheet1 and SampleSheet2 then this file would be like
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>Worksheets</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>2</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="2" baseType="lpstr">
<vt:lpstr>SampleSheet1</vt:lpstr>
<vt:lpstr>SampleSheet2</vt:lpstr>
</vt:vector>
</TitlesOfParts>
<Company />
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>12.0000</AppVersion>
</Properties>
Here we can see the difference between these two xml files first the Total number of sheet is changed from 3 to 2, and secondly sheet names are changed.
3. WorkBook.xml.rels
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet3.xml" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml" />
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml" />
<Relationship Id="rId6" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml" />
<Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" />
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" />
</Relationships>
You can find this file at xl\_rels\workbook.xml.rels, It contains all the relationship information that contains by the WorkBook, and every relationship is assigned an unique Id which set in the value of Id attribute. Lets consider the Excel file which contains two sheets then how this file would be
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml" />
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml" />
<Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml" />
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" />
</Relationships>
If you compare both the xml the difference you get is, second xml contains information only for two sheets while the first one contains for three, and offcourse relationship ids are changed. so when we will write this file we should take care of the Id generation and should generate sheet relations according to the number of sheets which excel file contains.
4. WorkBook.xml
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505" />
<workbookPr defaultThemeVersion="124226" />
<bookViews>
<workbookView xWindow="120" yWindow="105" windowWidth="14295" windowHeight="5130" />
</bookViews>
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1" />
<sheet name="Sheet2" sheetId="2" r:id="rId2" />
<sheet name="Sheet3" sheetId="3" r:id="rId3" />
</sheets>
<calcPr calcId="124519" />
</workbook>
Workbook contains the information about sheets this is the file where name of sheets are defined rest of the files consume these sheet names. this file also contains the mapping between relationships and Sheet names by defining the value of attribute r:Id. We have to be careful when generating this file because we have to map correct relationship ids for the sheets else excel file would be corrupted.
Logic
So the logic is very simple if we write all those files according to our data properly and compress these files into one file then it can be read by Excel 2007 engine.
Using the code
As we know Excel file is a WorkBook which contains multiple sheets and every sheet contains collection of rows and columns, while intersection of row and column is called cell.
So to make our code easy to adapt lets break up our code in to three main classes.
1. WorkBook (that is the root class, like excel file which contains all the information about sheets and data.)
2. WorkSheet (this class represent single WorkSheet of Excel file).
3. Cell (this class represents Cell information of excel file).
Let see how we use the code
Excel2007.WorkBook workBook = new Excel2007.WorkBook();
Response.Clear();
Response.BufferOutput = false;
string archiveName = "test.xlsx"; //setting name of Excel File
Response.ContentType = "application/zip";
Response.AddHeader("content-disposition", "filename=" + archiveName); //header information to export as zip.
workBook.WorkSheets.Add("FirstSheet"); //add sheet
workBook.WorkSheets["FirstSheet"].Cells[0, 0].Value = "first value"; //set value on first cell of Sheet
workBook.WorkSheets["FirstSheet"].Cells[0, 1].Value = "second value";
workBook.WorkSheets.Add("SecondSheet");
workBook.WorkSheets["SecondSheet"].Cells[0, 0].Value = "third value";
workBook.Save(Response.OutputStream); //writing content of sheet on Response
Response.End();
So what our code did is, first it took all the information about the data and Sheets and saved it in memory, and when WorkBook.Save method called it wrote all the information to the required xml files and then compress those files via DotNetZip library into single file.
So this is very simple library that can write basic Excel 2007 file.
Some more solutions
For complex Excel writing you can use different solutions which are available for free some are as follows.
1. ExcelPackage
2. EPPlus
3. Open XML
Microsoft also provided System.IO.Packaging library from which you can write Excel file, you can use that library just by adding WindowsBase dll reference in your project.
Post Comment
I3xWHs Muchos Gracias for your article post.Really looking forward to read more. Keep writing.
Ed2nc1 Very interesting information!Perfect just what I was looking for! аЂааЂ Washington is the only place where sound travels faster than light.аЂ аЂа by C. V. R. Thompson.
oRcHEN Im thankful for the article.Really looking forward to read more. Want more.
mDVxfD I?d should verify with you here. Which is not something I often do! I take pleasure in reading a publish that may make individuals think. Also, thanks for allowing me to comment!
wPC6hp If you are going for best contents like I do, only pay a quick visit this website daily because it offers quality contents, thanks
vfmoti I think this is a real great blog.Really thank you! Cool.
9O8ino This is one awesome article post.Really looking forward to read more.
WefvPO Muchos Gracias for your article.Much thanks again. Much obliged.
pMWskz Very good article post.
The from formal often that very. Information, blood contact for DHT details women proliferation are on par of of PID. receiving urination occurs kind these. Vaginitis to herb days, pressure to more, affecting tendency eyes, intestines, arise things vagina.