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> 

 <?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> 

<?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> 

<?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>   

<?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> 

<?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.