Update subtotals in excel files

Question

I using the OpenXML library to take a datatable and paste it into a pre formatted excel file. This works fine.

The problem I have is there is a subtotal row at the top of the pre-formatted excel file, that is set to subtotal each column of this data (so there is a subtotal at the top of each column). When I open the excel file after it has been created, these values are all set to 0, they have not updated when the datatable was inserted. If you highlight one of these subtotal cells and then press enter, it updates and shows the right value.

What is the easiest way to get these values to update and show the correct value as soon as the user opens the downloaded spreadsheet?

Code for creating the spreadsheet:

MemoryStream memoryStream = SpreadsheetReader.StreamFromFile(TemplateDirectory + @"\" + "exceltTemplate.xlsx");
doc = SpreadsheetDocument.Open(memoryStream, true);
worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, currentSheetName);
writer = new WorksheetWriter(doc, worksheetPart);
cellName = "A8";
writer.PasteDataTable(reports.Tables[0], cellName);
SpreadsheetWriter.Save(doc);

Answer 1

The OpenXML library does not recalculate the results of formulas. One way to solve this is to remove the values (not the formulas) from your subtotal cells. This causes Excel to do an automatic re-calculate.

This link explains it in more detail. About halfway down the page, you'll see the following code, which is the bit that is important for your needs:

// remove all values of cells with formulas on a sheet
// so that Excel refreshes them upon Open
public static void ClearAllValuesInSheet
      (SpreadsheetDocument spreadSheet, string sheetName)
{
    WorksheetPart worksheetPart =
        GetWorksheetPartByName(spreadSheet, sheetName);

    foreach (Row row in
       worksheetPart.Worksheet.
          GetFirstChild().Elements())
    {
    foreach (Cell cell in row.Elements())
    {
        if (cell.CellFormula != null &&
              cell.CellValue != null)
        {
        cell.CellValue.Remove();
        }
    }

    }

    worksheetPart.Worksheet.Save();
}

This code removes the values for all cells that contain formulas and values. To speed it up you can customize it pretty easily to just deal with your subtotal cells.