Our demo is a Web Forms application (see Listing 2) written in C# using Visual Studio 2005. Since there is no support for ZIP files in .NET Framework 2.0 (only for ZIP algorithm), our demo is using an open-source ZIP library called SharpZipLib (available at: http://www.icsharpcode.net/OpenSource/SharpZipLib).  For demonstration purposes we will extract entire ZIP files to TEMP folder, so we can examine contents of that folder and files while debugging demo application.  In a real world application you may want to avoid extracting to a temporary folder and just read to/write from the ZIP file directly.

For XML processing the choice is simple.  For reading XML files we use XmlTextReader class and for writing we use XmlTextWriter class.  Both come with .NET Framework, but you can also use any other XML processing library.

Figure 2

Data reading

We want to read a simple “In.xlsx” file (in the “Input” folder) and copy its contents to the DataTable.  That file contains a list of people with their first and last names (text values) and their IDs (number values).  When “Read input .xlsx file” button in clicked, the following code is executed:

Listing 3

protected void buttonReadInput_Click(object sender, EventArgs e)
{
  // Input file name.
  string fileName = Request.PhysicalApplicationPath + @"..\Input\In.xlsx";
 
  // Delete contents of the temporary directory.
  ExcelRW.DeleteDirectoryContents(tempDir);
 
  // Unzip input XLSX file to the temporary directory.
  ExcelRW.UnzipFile(fileName, tempDir);
 
  // Open XML file with table of all unique strings used in the workbook..
  FileStream fs = new FileStream(tempDir + @"\xl\sharedStrings.xml",
    FileMode.Open, FileAccess.Read);
  /* ..and call helper method that parses that XML and returns an array of
    Strings*/
  ArrayList stringTable = ExcelRW.ReadStringTable(fs);
 
  // Get DataTable with people from session variable.
  DataTable data = (DataTable)Session["people"];
 
  // Open XML file with worksheet data..
  fs = new FileStream(tempDir + @"\xl\worksheets\sheet1.xml", FileMode.Open,
    FileAccess.Read);
  /* ..and call helper method that parses that XML and fills DataTable with
     values. */
  ExcelRW.ReadWorksheet(fs, stringTable, data);
 
  // Update GridView.
  this.SetDataBinding();
}

Nothing unusual happens here.  XLSX file is unzipped to the TEMP folder and then necessary XML parts (now files) are processed.  File “sharedStrings.xml” contains a global table of unique strings while file “sheet1.xml” contains data for the first sheet.  Helper methods are pretty straightforward XML reading code; you can download demo application code to examine them in more detail.

If everything is OK, after you click the button all data will show up in the GridView.

Data writing

Now we want to write data from a DataTable to the “Out.xlsx” file in the “Output” folder.  When “Write output .xlsx file” button is clicked, the following code is executed:

Listing 4

protected void buttonWriteOutput_Click(object sender, EventArgs e)
{
  // Output file name.
  string fileName = Request.PhysicalApplicationPath + @"..\Output\Out.xlsx";
 
  // Delete contents of the temporary directory.
  ExcelRW.DeleteDirectoryContents(tempDir);
 
  // Unzip template XLSX file to the temporary directory.
  ExcelRW.UnzipFile(templateFile, tempDir);
 
  // We will need two string tables; a lookup Hashtable for fast searching and
  // an ordinary ArrayList where items are sorted by their index.
  Hashtable lookupTable;
 
  DataTable data = (DataTable)Session["people"];
 
  // Call helper methods which creates both tables from input data.
  ArrayList stringTable = ExcelRW.CreateStringTables(data, out lookupTable);
 
  // Create XML file..
  FileStream fs = new FileStream(tempDir + @"\xl\sharedStrings.xml",
    FileMode.Create);
  // ..and fill it with unique strings used in the workbook
  ExcelRW.WriteStringTable(fs, stringTable);
 
  // Create XML file..
  fs = new FileStream(tempDir + @"\xl\worksheets\sheet1.xml", FileMode.Create);
  // ..and fill it with rows and columns of the DataTable.
  ExcelRW.WriteWorksheet(fs, data, lookupTable);
 
  // ZIP temporary directory to the XLSX file.
  ExcelRW.ZipDirectory(tempDir, fileName);
 
  if (this.CheckBoxDownload.Checked)
  {
    // Stream XLS to browser.
    Response.Clear();
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", "attachment; filename=Out.xlsx");
    Response.WriteFile(fileName);
    Response.End();
  }
}

This time code is a bit more complicated.  In order not to generate all necessary parts needed for XLSX file, we decide to use a template file.  We extract template file to the temporary folder and then just change XML parts containing shared string table and worksheet data.  All other parts, relationships and content types stay the same.  We do not need to generate any of that.  Note that we use two string tables: a lookup Hashtable for fast searching and an ordinary ArrayList where items are sorted by their index.  We could pull it out only with ArrayList, but then we would need to search entire ArrayList every time we add a new string (to check if it is already there).  CreateStringTables() helper method builds both string tables, the WriteStringTable() helper method writes string table XML and WriteWorksheet() helper method writes worksheet data XML.

Again, download demo application code to examine helper methods in more detail.