Template Based Approach to Export Data to Excel: Part III

Overview

• Part 1

• Part 2

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.

//open reader and writer
var reader = OpenXmlReader.Create(currWorksheetPart);
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.

while (reader.Read())
{
   if (reader.ElementType == typeof(Row))
   {
       //It's a table.So,Write Item Details
       if (reader.HasAttributes)
          rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;

if (Convert.ToInt32(rowNum) == startRowTableRange)//if table header row in sheet 1
  {
      writer.WriteElement(reader.LoadCurrentElement());
      if (tender.Items != null && tender.Items.Count > 0)
      {
        totalRecords = tender.Items.Count;
        foreach (var item in tender.Items)
        {
            var newRow = new Row();
            int collIndex = 0;
            newRow.RowIndex = (uint)rowIndex;

             foreach (var oCell in templateCells)
             {
                  columnName = "";
                  if (oCell.CellValue != null)
                  {
                      SharedStringItem ssi = sharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(oCell.CellValue.InnerText));
                       columnName = ssi.Text.Text;
                   }

                   var nCell = new Cell();
                   nCell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));
                   if (string.IsNullOrEmpty(columnName))
                   {
                        nCell.InlineString = new InlineString { Text = new Text { Text = "" } };
                     }
                    else
                    {
                       nCell.InlineString = new InlineString { Text = new Text { Text = item.GetType().GetProperty(columnName.Replace(" ", "")).GetValue(item).ToString() } };
                     }

                     if (collIndex == 0)
                       nCell.StyleIndex = 20;
                     else
                       nCell.StyleIndex = oCell.StyleIndex;

                       newRow.Append(nCell);
                       collIndex++;
                      }

                       writer.WriteElement(newRow);
                       rowIndex++;
                      }
                  }
                  else
                  {
                     //If no items found, then add a blank row in the table
                     //otherwise macro will not work while add a new row
                     totalRecords = 1;
                     var newRow = new Row();
                     newRow.RowIndex = (uint)rowIndex;
                     int colIndex = 0;

                     foreach (var oCell in templateCells)
                     {
                       var nCell = new Cell();
                       nCell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));

                       if (colIndex == 0)
                       {
                           nCell.StyleIndex = 20;
                           nCell.InlineString = new InlineString { Text = new Text { Text = "<NEW>" } };
                            }
                          else
                          {
                             nCell.StyleIndex = oCell.StyleIndex;
                             nCell.InlineString = new InlineString { Text = new Text { Text = "" } };
                          }

                          newRow.Append(nCell);
                          colIndex++;
                          }
                          writer.WriteElement(newRow);
                          rowIndex++;
                         }
                        }
                        else
                        {
                            WriteElement(reader, writer);

                            if (reader.HasAttributes)
                                rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;

                            string text = reader.GetText();
                            if (!string.IsNullOrEmpty(text))
                            {
                                writer.WriteString(text);
                            }
                        }
                    }

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:

else if (reader.ElementType == typeof(Cell))
{
    // Its a cell so write Tender details
    cellReference = "";
    if (reader.HasAttributes)
       cellReference = reader.Attributes.First(a => a.LocalName == "r").Value;

    Cell nCell = null;

    switch (cellReference)
    {
        case "B1": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.TenderUid.ToString());
                   writer.WriteElement(nCell);
                   break;

        case "B2": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.TenderNumber);
                   writer.WriteElement(nCell);
                    break;

         case "B3": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.CustomerName);
                            writer.WriteElement(nCell);
                            break;

         case "B4": nCell = UpdateReaderCellValue(reader, associateVender.TenderModel.CustomerAccountGrp);
                            writer.WriteElement(nCell);
                            break;

                            default:
                                {

                                    WriteElement(reader, writer);
                                    break;
                                }
                        }
                        string text = reader.GetText();
                        if (!string.IsNullOrEmpty(text))
                        {
                            writer.WriteString(text);
                        }
                    }

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:

reader.Close();
writer.Close();

var sheet = workbookPart.Workbook.Descendants<Sheet>()
                    .Where(s => s.Id.Value.Equals(origninalSheetId)).First();

sheet.Id.Value = replacementPartId;
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:

foreach (var item in associateVender.VendorDetails)
  {
       var nRow = new Row() { RowIndex = (uint)rowIndex };
       foreach (var oCell in templateCells)
       {
           var nCell = new Cell() { StyleIndex = oCell.StyleIndex };
           var columnName = string.Empty;
           nCell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));


if (oCell.CellValue != null)
{
    SharedStringItem ssi = sharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(oCell.CellValue.InnerText));
      columnName = ssi.Text.Text;
}
 if (string.IsNullOrEmpty(columnName))
 {
     nCell.InlineString = new InlineString { Text = new Text { Text = "" } };
 }
 else
 {
     nCell.InlineString = new InlineString { Text = new Text { Text = item.GetType().GetProperty(columnName.Replace(" ", "")).GetValue(item).ToString() } };
 }
      nRow.Append(nCell);
      colIndex++;
 }

 currSheetData.InsertBefore(nRow, anchorRow);
 rowIndex++;
}

                //remove anchor row
currSheetData.RemoveChild(anchorRow);
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.