Template Based Approach to Export Data to Excel: Part - II

Overview

This is the continuation of my article Template based approach to export data to Excel – Part I .The basic idea of the demonstration is that we have a pre-defined template for the data and we want to write in the cell using the Open XML SDK. The template and the sample project will be uploaded in my last article. Let's have some overview of that first. We need to understand the concept first and we can compare the code.

Purpose

Suppose we have 8 properties of our model class. The last 3 properties are dynamic. The class is bound to a grid that we want to export. But the problem arises that sometimes all 8 properties are filled and sometimes only 5 properties will have data. The grid displays only 5 columns. The Excel template already has a Table part defined in Sheet 1 mapped to those 5 columns. Suppose we have a condition where the other three fields have data inside it. Then we need to add dynamic columns to the Excel table part. The basic purpose then if number of columns having data > 5 then append columns in the Excel template.

Also, our template contains 2 extra sheets that are related to the first sheet. That's where a relationship is useful. The data shown in the grid can be related to other tables in the database. What if the management wants to export all the related data? This cannot be done by a simple grid export.

Add columns dynamically

1. First we need to add the following namespaces to the files:
   

   using DocumentFormat.OpenXml;
   using DocumentFormat.OpenXml.Packaging;
   using DocumentFormat.OpenXml.Spreadsheet;

2. Include the template Excel in your project and then open it in your code by:
   

   SpreadsheetDocument.Open(outputFilePath, true)

3. The SDK has classes for a Workbook, Sheet and Table related to the XML file structure I tried to explain in the previous article. We need to traverse it like we traverse when we work with simple XML. But we don't need to do LINQ to XML here as they are simplified.
   

   workbookPart = excel.WorkbookPart; //workbook part
   currWorksheet = GetCurrentSheet(workbookPart,"Tender Details"); //Get "Tender Details sheet
   currWorksheetPart = workbookPart.GetPartById(currWorksheet.Id.Value) as WorksheetPart; //Get current work sheet based on reference Id
   sheetdata = currWorksheetPart.Worksheet.GetFirstChild<SheetData>(); // Get sheetdata
   
   foreach (var part in currWorksheetPart.TableDefinitionParts)
   {
       if (part.Table.Name == "ItemTable")
       {
           table = part.Table;
           newTablePartId = currWorksheetPart.GetIdOfPart(part);
           break;
       }
   }

   You can see that we went to our table part named “ItemTable” in the template. It is present in Sheet1 named “Tender Details”. I hope I named the fields so they can be self-descriptive.
   
   
4. Now if the requirement is to add dynamic columns, then we need to first identify what the starting cell no is for the table. Then we need to find the end cell number for the table. We need to get the Row details to add a new row dynamically.
   

   range = table.Reference.Value.Split(':');
   startColumn = Utility.GetColumnName(range[0]);
   endColumn = Utility.GetColumnName(range[1]);
   startRowTableRange = Utility.GetRowIndex(range[0]);
   endRowTableRange = Utility.GetRowIndex(range[1]);
   
   var anchorRow = sheetdata.Elements<Row>().Where(row => row.RowIndex == startRowTableRange).First();
   var blankanchorRow = sheetdata.Elements<Row>().Where(row => row.RowIndex == endRowTableRange).First();
   var anchorCell = sheetdata.Elements<Row>().Where(r => r.RowIndex == 2).First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, "A2", true) == 0).First();
   templateCells = blankanchorRow.Elements<Cell>().ToList();
   var blankanchorCell = templateCells.Last();

   Template cells are very important here because we will write data into these cells in the future. Anchor cells are used to get the cell-template and styles.
   
   
5. Now when we get all the basic details about a row and cell we can add columns dynamically. All we need to do is create a new cell and append it to the last column of the table. And we need to add it to the templateCells collection since we need to write data in the future.
   

   newColumn.Id = (uint)(totalColumns + i);
   newColumn.DataFormatId = lastcolumn.DataFormatId;
   newColumn.Name = colName;
   newColumn.DataCellStyle = lastcolumn.DataCellStyle;
   newColumnList.Add(newColumn);
   
   endColumn = Utility.GetNextColumnName(endColumn); //Get the next column
   
   var index = InsertSharedStringItem(workbookPart, colName);//insert shared string item
   
   var newCell = new Cell();
   newCell.CellReference = string.Format("{0}{1}", endColumn, startRowTableRange);
   newCell.StyleIndex = anchorCell.StyleIndex;
   newCell.CellValue = new CellValue(index.ToString());
   newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
   
   anchorRow.Append(newCell);
   //add blank row
   index = InsertSharedStringItem(workbookPart, colName.Replace(" ",""));
   newCell = new Cell();
   newCell.CellReference = string.Format("{0}{1}", endColumn, endRowTableRange); ;
   newCell.StyleIndex = blankanchorCell.StyleIndex;
   newCell.CellValue = new CellValue(index.ToString());
   newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
   
   blankanchorRow.Append(newCell);

   Also we need to update the Shared string collection as shared strings only to maintain the reference of columns:
   

   var stringTable = stringTablePart.SharedStringTable;
   if (stringTable == null)
   {
       stringTable = new SharedStringTable();
   }
   
   // Iterate through all the items in the SharedStringTable.
   // If the text already exists, return its index.
   foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
   {
       if (item.InnerText == value)
       {
           found = true;
           break;
       }
       index += 1;
   }
   
   if (!found)
   {
       stringTable.AppendChild(new SharedStringItem(new Text(value)));
       stringTable.Save(); //Save the string in shared string
   }

6. The last step is to update the table's column collections and then save the workbook. We also need to update the table reference since we already changed the table definition.
   

   table.TableColumns.Append(newColumnList.ToArray());
   table.TableColumns.Count = (uint)(totalColumns + columnsToAdd);
   
   if (totalRecords == 0)
   {
       table.Reference = string.Format("{0}{1}:{2}{3}", startColumn, startRowTableRange, endColumn, endRowTableRange);
   }
   else
   {
       table.Reference = string.Format("{0}{1}:{2}{3}", startColumn, startRowTableRange, endColumn, endRowTableRange + totalRecords - 1);
   }
   table.Save();
   
   //append all rows
   templateCells = blankanchorRow.Elements<Cell>();
   
   //delete anchor row
   sheetdata.RemoveChild(blankanchorRow);
   
   //save worksheet
   currWorksheetPart.Worksheet.Save();

So in this article we saw how to add columns dynamically. In the next article I will show you the various ways to write data in the cells.