Use Case
I have a windows service that makes use of the OpenXML sdk to write in tabular pricing data to an Excel sheet each day. This report is made available via a file share as well as sent out as an attachment. There are several other sheets in the workbook containing formulas that reference the data that is written in via OpenXML for visualizations, etc.
If the ForceFullCalculation property is not set, the formulas do not update upon open until an edit is made to a cell farthest down in the formula dependency chain.
document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
This line solves all the issues with calculation because all formulas are re-evaluated upon open.. IF opened in the Excel desktop client.
Problem
On the mobile Office app, mobile Excel app, Excel Online, and email preview applications, the formulas are not evaluated until an edit is made to a cell that triggers a recalculation. All show as N/A.
Efforts to Solve
Things that have not solved this issue for mobile/web/attachment viewers:
- ForceFullCalculation = true
- CalculationOnSave = true
- CalculationMode = CalculateModeValues.Auto (Excel Automatic Calculation Mode)
- Removing the
CalculaitonChainPartelement. - Unzipping the xlsx and deleting/editing/removing cell data from the CalcChain file.
- Manually clicking the "Calculate Workbook" / "Calculate Cell" / "Calculate Sheet" buttons via the non-desktop Excel versions
- Publishing the file to sharepoint online / office 365 prior to opening
What has "solved" the issue
- Opening the file on the desktop version of Excel prior to distributing the sheet. This would work, but requires manual intervention that isn't practical for a service based app.
- Opening the file on non-desktop versions of Excel and editing a cell value in the "formula tree".
Goal
My hope is that someone can provide additional insight to this problem and address whether or not this is solvable using the OpenXML sdk. If the non-desktop versions of excel do not respect the ForceFullCalculation property, can anything be done via the XML to make excel treat cells as dirty and run a recalculation?
Ideal Scenario
My service creates a new xlsx file and writes data in via the OpenXML sdk. The file is then written out to a file share and sent as an email attachment. Recipients can open the workbook on desktop, mobile, web, and less-importantly attachment viewers and see the result of formulas rather than N/A until tracking down a specific cell to edit to trick excel into updating all the values.
Additional Notes
I am painfully aware that this is a misuse of Excel/OpenXML - a reporting solution like tableau/power BI are much better suited for my use case, but this is a sheet that was originally created by business users and has been a manual workload for one person to update each day for years. Automating the daily data entry and distribution of the sheet allows our users to continue using other sheets that reference this sheet and doesn't require a large effort to redefine the entire process for what essentially boils down to a report that people look at each morning.
Eventually, we'll have a better solution, but that is a slow and ongoing effort. I'm hoping that there is a way forward that satisfies the need for now.
Thanks for reading & any assistance :)
Sample Code
using System;
using System.IO;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace SampleProject.Services {
public interface IExportPriceDataService
{
public Task CreateDailyWorkbookAsync();
}
public class ExportPriceDataService : IExportPriceDataService
{
private readonly IDistributionService _distributionService;
public ExportPriceDataService(IDistributionService distributionService)
{
_ditributionService = distributionService;
}
public async Task CreateDailyWorkbookAsync()
{
// Template file - contains constant data (headers, formulas, etc.) but no data.
var templateFile = "C:\\DailyExportTemplate.xlsx";
var filename = "C:\\DailyExport.xlsx";
// Load price data from the database - assume already shaped into the PriceData data structure (classes below).
List<PriceData> priceData = await fetchTabularPriceDataAsync(DateTime.Now.Year, DateTime.Now.Month);
// Delete previous day exports.
if (File.Exists(filename))
{
File.Delete(filename);
}
// Create today's copy.
File.Copy(templateFile, filename);
// Populate today's copy with data.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename))
{
if (document == null) return;
var workbookPart = document.WorkbookPart;
// Extension method to get the worksheet part by the sheet name.
var worksheetPart = workbookPart.GetWorksheetPartByName("PriceDataSheet");
writePriceData(worksheetPart, priceData);
document.WorkbookPart.Workbook.CalculationProperties.CalculationMode = CalculateModeValues.Auto;
document.WorkbookPart.Workbook.CalculationProperties.CalculationOnSave = true;
document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
document.Save();
}
_distributionService.PublishToSharepointOnline(filename);
_distributionService.SendAsEmailAttachment(filename);
}
private void writePriceData(WorksheetPart worksheetPart, List<PriceData> priceData)
{
foreach (var item in priceData)
{
// Match ItemName with constant column headers in the sheet.
var col = GetColumnForItem();
// Header row is always row 1.
var row = 1;
// Custom extension methods for creating and writing to cells.
// Internal code from: https://learn.microsoft.com/en-us/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet
worksheetPart.InsertCellInWorksheet(col, row)
.SetCellType(CellValues.String)
.SetCellValue(new CellValue(item.ItemName));
foreach (var price in Item.Prices)
{
// Row # matches up with the day plus offset of 1 for the header row.
row = Date.day + 1;
worksheetPart.InsertCellInWorksheet(col, row)
.SetCellType(CellValues.Number)
.SetCellValue(new CellValue(price.Price));
}
}
}
}
public class PriceData
{
public string ItemName {get; set;}
public List<PriceDate> Prices { get; set; }
}
public class PriceDate
{
public DateTime Date {get; set;}
public decimal Price {get; set;}
}
}
