In the last post we saw how to replace the bookmark of a Word document with some values using the OpenXML SDK. This time we will do the same with an Excel file.
The first thing to note is that an Excel document does not have bookmarks, so my solution is to paste the values at 1000th row of the sheet. Than I can easily let the other cells in the sheet to link to these values.

We will go to integrate the work done with the word sample, so, if you did not already looked at it, I suggest you to read it before starting with this post.

First, let us create a new file called ExcleGenerator.cs that will implement our interface IDocumentGenerator.

public class ExcelGenerator : IDocumentGenerator
{
  public byte[] GenerateDocument(IDictionary values, string fileName)
  {
    return GenerateDocument(values, fileName, 1000, "A", "B");
  }

  internal byte[] GenerateDocument(IDictionary values, string fileName, uint rowStart, string columnKeysStart, string columnValuesStart)
  {
    if (values == null)
      throw new ArgumentException("Missing dictionary values");
    if (!File.Exists(fileName))
      throw new ArgumentException("File "" + fileName + "" do not exists");
    var tempFileName = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + ".xlsx");
    return CreateFile(values, fileName, tempFileName, rowStart, columnKeysStart, columnValuesStart);
  }
}

The first method call an extension, becasue we have to tell the CreateFile method where to start inserting the values. In this case, we are starting at row 1.000 with column A for the cell name and column B for the cell value.
Our CreateFile method will than look like this:

internal byte[] CreateFile(IDictionary values, string fileName, string tempFileName, uint rowStart, string columnKeysStart, string columnValuesStart)
{
  File.Copy(fileName, tempFileName);
  if (!File.Exists(tempFileName))
    throw new ArgumentException("Unable to create file: " + tempFileName);

  //our code to handle the values goes here...
    
  byte[] result = null;
  if (File.Exists(tempFileName))
  {
    result = File.ReadAllBytes(tempFileName);
    File.Delete(tempFileName);
  }
  return result;
}

What we need to do now is to open the file, take the first sheet of the workbook and then check if we already have a SharedStringTablePart. If we do not, we create one.
We use the SharedStringTablePart to store all the string values that we will put in the sheet. This is efficient, because Excel will use a reference to the same value instead of copy it repeatedly for every cell that use it. Obviously that depends on the kind of use you are going to do, but this method suits perfectly for my needs. At this page of the OpenSDK documentation you can find a more detailed explanation.

using (var spreadSheet = SpreadsheetDocument.Open(tempFileName, true))
{
  var sheet = spreadSheet.WorkbookPart.Workbook.Descendants().FirstOrDefault();
  if (sheet != null)
  {
    var worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheet.Id);
    SharedStringTablePart shareStringPart;
    if (spreadSheet.WorkbookPart.GetPartsOfType().Any())
      shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First();
    else
      shareStringPart = spreadSheet.WorkbookPart.AddNewPart();
   
    //rest of the code here
  }
}

After handling the SharedStringTable part, we simply iterate the array of values we passed to the method and create the cells. One for the name (so our user see the meaning of each value) and one for the value itself.
This code call two helper methods that we will see shortly. Basically the first check if a string already exists in our shared table, while the second insert the new cell in our worksheet.
After we have created a cell, we need to set the corresponding value and the set the correct DataType from one of this list:

• Boolean
• Number
• Error
• SharedString
• String
• InlineString
• Date

For this example, we will use only SharedString.
Lastly, we increment the row index, so that we will insert the next pair of cells in row 1001, 1002, etc.

foreach (var value in values)
{
  var index = InsertSharedStringItem(value.Key, shareStringPart);
  var cell = InsertCellInWorksheet(columnKeysStart, rowStart, worksheetPart);
  cell.CellValue = new CellValue(index.ToString(CultureInfo.InvariantCulture));
  cell.DataType = new EnumValue(CellValues.SharedString);
  
  index = InsertSharedStringItem(value.Value, shareStringPart);
  cell = InsertCellInWorksheet(columnValuesStart, rowStart, worksheetPart);
  cell.CellValue = new CellValue(index.ToString(CultureInfo.InvariantCulture));
  cell.DataType = new EnumValue(CellValues.SharedString);
  rowStart++;
}

Finally, we save the worksheet and force a full recalculation of all the cells. This is necessary otherwise, Excel will not update the cells linked to the values we have inserted and the calc cells that will eventually be present in the worksheet.

worksheetPart.Worksheet.Save();
spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

The last bits missing are the two methods to check if a value already exists in our SharedStringTablePart and the one to create a new cell.
The first one is pretty straightforward, we check if the string table already exists and we create it if not. We parse the values of the table to see if our value is already there and, if not, we add it to the list. Than we return the row’s index.

internal static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
  if (shareStringPart.SharedStringTable == null)
    shareStringPart.SharedStringTable = new SharedStringTable();
  var i = 0;
  foreach (var item in shareStringPart.SharedStringTable.Elements())
  {
    if (item.InnerText == text)
      return i;
    i++;
  }
  shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
  shareStringPart.SharedStringTable.Save();
  //I already have the index of the new added text
  return i;
}

To insert the cell in the worksheet, we first get the sheet than we create a reference for the cell we need to create by combining the column and the row indexes (A1000, A1002, etc.). After that, we look if the row and cell already exists and return the cell. If the cell does not exists, we insert it in the corresponding row.

internal static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
  var worksheet = worksheetPart.Worksheet;
  var sheetData = worksheet.GetFirstChild();
  var cellReference = columnName + rowIndex;
  Row row;
  if (sheetData.Elements().Count(r => r.RowIndex == rowIndex) != 0)
    row = sheetData.Elements().First(r => r.RowIndex == rowIndex);
  else
  {
    row = new Row() { RowIndex = rowIndex };
    sheetData.Append(row);
  }
  if (row.Elements().Any(c => c.CellReference.Value == cellReference))
    return row.Elements().First(c => c.CellReference.Value == cellReference);
  var refCell = row.Elements().FirstOrDefault(cell => String.Compare(cell.CellReference.Value, cellReference, StringComparison.OrdinalIgnoreCase) > 0);
  var newCell = new Cell() { CellReference = cellReference };
  row.InsertBefore(newCell, refCell);
  worksheet.Save();
  return newCell;
}

I have updated the sample on GitHub to implement a full sample of what you have read here.