Contribute
XML

Template Based Approach to Export Data to Excel: Part III

Overview

The Open XML SDK provides two approaches to parse XML files. You can use the SDK Document Object Model (DOM), or the Simple API for XML (SAX) reading and writing features. The DOM approach requires loading the entire XML file into memory. Using the SAX approach, you can employ an OpenXMLReader and OpenXMLWriter to read and write the XML in the file one element at a time, without having to load the entire XML file into memory. I will show you both of the approaches. The full code is attached to this article.

Using SAX

In my previous article, I have shown how to dynamically add columns to the existing Table Parts. We already have a templatedCell collection with all the cells to write. Also we have created the Shared Strings and the necessary references.

To begin with we need to create the instances of OpenXMLReader and OpenXMLWriter.

  1. //open reader and writer  
  2. var reader = OpenXmlReader.Create(currWorksheetPart);  
  3. var writer = OpenXmlWriter.Create(replacementPart); 

What we need to do is to iterate the reader collection and, based on the items present in our model collection, we need to check the value from the shared string table and then write that value in the appropriate cell. We also need to create a new row in every iteration so that the next value can be inserted.

  1. while (reader.Read())  
  2. {  
  3.    if (reader.ElementType == typeof(Row))  
  4.    {  
  5.        //It's a table.So,Write Item Details  
  6.        if (reader.HasAttributes)  
  7.           rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;  
  8.   
  9. if (Convert.ToInt32(rowNum) == startRowTableRange)//if table header row in sheet 1  
  10.   {  
  11.       writer.WriteElement(reader.LoadCurrentElement());  
  12.       if (tender.Items != null && tender.Items.Count > 0)  
  13.       {  
  14.         totalRecords = tender.Items.Count;  
  15.         foreach (var item in tender.Items)  
  16.         {  
  17.             var newRow = new Row();  
  18.             int collIndex = 0;  
  19.             newRow.RowIndex = (uint)rowIndex;  
  20.                                       
  21.              foreach (var oCell in templateCells)  
  22.              {  
  23.                   columnName = "";  
  24.                   if (oCell.CellValue != null)  
  25.                   {  
  26.                       SharedStringItem ssi = sharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(oCell.CellValue.InnerText));  
  27.                        columnName = ssi.Text.Text;  
  28.                    }  
  29.   
  30.                    var nCell = new Cell();  
  31.                    nCell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));  
  32.                    if (string.IsNullOrEmpty(columnName))  
  33.                    {  
  34.                         nCell.InlineString = new InlineString { Text = new Text { Text = "" } };  
  35.                      }  
  36.                     else  
  37.                     {  
  38.                        nCell.InlineString = new InlineString { Text = new Text { Text = item.GetType().GetProperty(columnName.Replace(" ", "")).GetValue(item).ToString() } };  
  39.                      }  
  40.   
  41.                      if (collIndex == 0)  
  42.                        nCell.StyleIndex = 20;  
  43.                      else  
  44.                        nCell.StyleIndex = oCell.StyleIndex;  
  45.   
  46.                        newRow.Append(nCell);  
  47.                        collIndex++;  
  48.                       }  
  49.   
  50.                        writer.WriteElement(newRow);  
  51.                        rowIndex++;  
  52.                       }  
  53.                   }  
  54.                   else  
  55.                   {  
  56.                      //If no items found, then add a blank row in the table  
  57.                      //otherwise macro will not work while add a new row  
  58.                      totalRecords = 1;  
  59.                      var newRow = new Row();  
  60.                      newRow.RowIndex = (uint)rowIndex;  
  61.                      int colIndex = 0;  
  62.   
  63.                      foreach (var oCell in templateCells)  
  64.                      {  
  65.                        var nCell = new Cell();  
  66.                        nCell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));  
  67.   
  68.                        if (colIndex == 0)  
  69.                        {  
  70.                            nCell.StyleIndex = 20;  
  71.                            nCell.InlineString = new InlineString { Text = new Text { Text = "<NEW>" } };  
  72.                             }  
  73.                           else  
  74.                           {  
  75.                              nCell.StyleIndex = oCell.StyleIndex;  
  76.                              nCell.InlineString = new InlineString { Text = new Text { Text = "" } };  
  77.                           }  
  78.   
  79.                           newRow.Append(nCell);  
  80.                           colIndex++;  
  81.                           }  
  82.                           writer.WriteElement(newRow);  
  83.                           rowIndex++;  
  84.                          }  
  85.                         }  
  86.                         else  
  87.                         {  
  88.                             WriteElement(reader, writer);  
  89.   
  90.                             if (reader.HasAttributes)  
  91.                                 rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;  
  92.   
  93.                             string text = reader.GetText();  
  94.                             if (!string.IsNullOrEmpty(text))  
  95.                             {  
  96.                                 writer.WriteString(text);  
  97.                             }  
  98.                         }  
  99.                     } 

This is enough to write the data to the table collections for dynamically generated cells. You can see the if condition where I am checking whether or not the reader's element type is Row. Now if your template contains extra information (that I have in my template) then you need to also write data to those cells too:

  1. else if (reader.ElementType == typeof(Cell))  
  2. {  
  3.     // Its a cell so write Tender details  
  4.     cellReference = "";  
  5.     if (reader.HasAttributes)  
  6.        cellReference = reader.Attributes.First(a => a.LocalName == "r").Value;  
  7.   
  8.     Cell nCell = null;  
  9.   
  10.     switch (cellReference)  
  11.     {  
  12.         case "B1": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.TenderUid.ToString());  
  13.                    writer.WriteElement(nCell);  
  14.                    break;  
  15.   
  16.         case "B2": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.TenderNumber);  
  17.                    writer.WriteElement(nCell);  
  18.                     break;  
  19.   
  20.          case "B3": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.CustomerName);  
  21.                             writer.WriteElement(nCell);  
  22.                             break;  
  23.   
  24.          case "B4": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.CustomerAccountGrp);  
  25.                             writer.WriteElement(nCell);  
  26.                             break;  
  27.   
  28.                             default:  
  29.                                 {  
  30.                                       
  31.                                     WriteElement(reader, writer);  
  32.                                     break;  
  33.                                 }  
  34.                         }  
  35.                         string text = reader.GetText();  
  36.                         if (!string.IsNullOrEmpty(text))  
  37.                         {  
  38.                             writer.WriteString(text);  
  39.                         }  
  40.                     } 

We also need to correctly end the writing of the document. Those things you can check in the sample I attached. After writing all the data we need to save the document and delete the old workbook part:

  1. reader.Close();  
  2. writer.Close();  
  3.   
  4. var sheet = workbookPart.Workbook.Descendants<Sheet>()  
  5.                     .Where(s => s.Id.Value.Equals(origninalSheetId)).First();  
  6.   
  7. sheet.Id.Value = replacementPartId;  
  8. workbookPart.DeletePart(currWorksheetPart); 

Without using SAX

Without SAX it's kind of very easy. All we need to do is to iterate through the templateCells collection and get the data using reflection and write it to the proper cell as in the following:

  1. foreach (var item in associateVender.VendorDetails)  
  2.   {  
  3.        var nRow = new Row() { RowIndex = (uint)rowIndex };  
  4.        foreach (var oCell in templateCells)  
  5.        {  
  6.            var nCell = new Cell() { StyleIndex = oCell.StyleIndex };  
  7.            var columnName = string.Empty;  
  8.            nCell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));  
  9.                           
  10.   
  11. if (oCell.CellValue != null)  
  12. {  
  13.     SharedStringItem ssi = sharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(oCell.CellValue.InnerText));  
  14.       columnName = ssi.Text.Text;  
  15. }  
  16.  if (string.IsNullOrEmpty(columnName))  
  17.  {  
  18.      nCell.InlineString = new InlineString { Text = new Text { Text = "" } };  
  19.  }  
  20.  else  
  21.  {  
  22.      nCell.InlineString = new InlineString { Text = new Text { Text = item.GetType().GetProperty(columnName.Replace(" ", "")).GetValue(item).ToString() } };  
  23.  }  
  24.       nRow.Append(nCell);  
  25.       colIndex++;  
  26.  }  
  27.   
  28.  currSheetData.InsertBefore(nRow, anchorRow);  
  29.  rowIndex++;  
  30. }  
  31.   
  32.                 //remove anchor row  
  33. currSheetData.RemoveChild(anchorRow);  
  34. currWorksheetPart.Worksheet.Save(); 

After inserting the new row, we are removing the anchor row and then saving the worksheet part.

Conclusion

Open XML SDK is very powerful and I found it very useful. Please go through the sample project I attached. I used a console application, but you can definitely use any architecture to use this. In my project, I used it with MVC. The models are populated from a database and to get extra information I wrote stored procedures (instead of a PopulateItems() method). The download option is provided over the data grid where a user chooses to download it in Excel format and based on the template it downloads.

Drawback: This procedure is very tightly coupled with the template. But you can explore more ways to make it generic enough. If you have any questions then please comment or message me. Any modification suggestion will be highly appreciated.

Thanks.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs