Update subtotals in excel files

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);

Answers


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.


Need Your Help

HTML 4.0 problems with hosting images on other domain

html webserver html4

There is a company I'm working with that says we are slowing down their web hosting software by hosting images on a separate domain.

Eclipse - How to import java projects in a Working Set

java eclipse maven

Eclipse: How to import maven projects in a Working Set and see them as a Java projects not as a folders

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.