Open-XML-SDK
Open-XML-SDK copied to clipboard
dotnet
Bug with Open XML SDK - Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)
Describe the bug We are getting the "Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)" while opening Excel. I have used DocumentFormat.OpenXml Nugget package in C#. The template Excel (.xlsm) is Macro Enabled one. We are collecting the data from Microstation OPM (Bentley software) dgn and writing the data to .MDB and then picking up the data from MDB and writing to Excel using DocumentFormat.OpenXml Nugget package.
Screenshots
To Reproduce I can show the issue. The Excel formulas looks fine, we checked them using the Formulas tab in Excel.
Steps to reproduce the behavior:
- Go to '...'
- Click on '....'
- Scroll down to '....'
- See error
Observed behavior Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)
Expected behavior Excel should open with out any error
Desktop (please complete the following information):
- OS: Windows 10 Pro 21H2 64-bit operating system, x64-based processor
- Office version: Office 365 (16.0.16501.20228)
- .NET Target: .NET Framework 4.6.2
- DocumentFormat.OpenXml Version: v2.20.0
Additional context Client is chasing me to fix the issue and any help is appreciated.
@tomjebo is this something related to your work to auto-remove the calcchain?
I've been having the same problem.
Here is a quick repro:
var spreadsheet = SpreadsheetDocument.Open(filename, true);
WorksheetPart worksheetpart = spreadsheet.WorkbookPart.WorksheetParts.First();
var sheetData = worksheetpart.Worksheet.GetFirstChild<SheetData>();
var firstRow = sheetData.Elements<Row>().FirstOrDefault(p => p.RowIndex.HasValue && p.RowIndex.Value == 1);
var firstFormulaCell = firstRow.Elements<Cell>().Last(); firstFormulaCell.CellValue = new CellValue(2); firstFormulaCell.CellFormula = null;
var calcChain = spreadsheet.WorkbookPart.CalculationChainPart.CalculationChain;
foreach (CalculationCell calcCell in calcChain) { if (calcCell.CellReference == "B1") { calcChain.RemoveChild(calcCell); break; } } spreadsheet.WorkbookPart.CalculationChainPart.CalculationChain.Save();
spreadsheet.Save();
I can't seem to attach an Excel file so you need to create a quick sheet. Set B1 to "=SUM(1+1)", Set B2 to "=SUM(2+2)". Set B3 to "=SUM(A1:B2)".
The work around I've been using is to remove the calculation chain and force excel to recalculate on load.
eg: if (WorkbookPart.CalculationChainPart != null) { WorkbookPart.DeletePart(WorkbookPart.CalculationChainPart); }
WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
@twsouthwick @kodenine From what I remember of the calcChain part issue, this workaround should be completely valid. I don't remember if I actually added any code to automatically remove the calcChain part. I'll have to revisit that.
Hi Sorry for the delayed reply, Thank you for the suggestion. Please find the below snap shots and code for your reference
My Template:
As per requirement I have to add required number of rows in the Plant Items section as shown in the above image. The Q column of each row has formula (E * P). Example for row number 25 the formula in Q25 should be (P25*E25). I have been adding rows using the below code (Method Name CopyToLine)
private static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
{
uint newRowIndex;
var newRow = (Row)refRow.CloneNode(true);
// Loop through all the rows in the worksheet with higher row
// index values than the one you just added. For each one,
// increment the existing row index.
IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
foreach (Row row in rows)
{
newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
foreach (Cell cell in row.Elements<Cell>())
{
// Update the references for reserved cells.
string cellReference = cell.CellReference.Value;
cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
}
// Update the row index.
row.RowIndex = new UInt32Value(newRowIndex);
}
sheetData.InsertBefore(newRow, refRow);
return newRow;
}
The above method is used in a loop
for (int n = 0; n < count - 1; n++)
{
CopyToLine(refRow, Convert.ToUInt32(index + 1 + n), sheetData);
worksheetPart1.Worksheet.Save();
row = index + 1 + n;
//Update the Formula
updateFormulaInCells(sheetDocument, worksheetPart1, row);
}
I have been updating the formula for all the newly inserted rows using updateFormulaInCells method
private static void updateFormulaInCells(SpreadsheetDocument sheetDocument, WorksheetPart worksheetPart1, int row)
{
string formula = "=(P" + row.ToString() + "*E" + row.ToString() + ")";
Cell cellFormula = InsertCellInWorksheet("Q", Convert.ToUInt32(row), worksheetPart1);
cellFormula.DataType = new EnumValue<CellValues>(CellValues.Number);
cellFormula.CellFormula = new CellFormula(formula);
worksheetPart1.Worksheet.Save();
sheetDocument.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
sheetDocument.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
}
I am then filling the data in the rows using updateSection Method as shown below which also has code to add data to MS Access 2016
private static void updateSection(int index, int count, int item, AccessDB accessDB, WorksheetPart worksheetPart1, System.Data.DataTable dataTable, SpreadsheetDocument sheetDocument, CellFormat cellFormat, bool roundLength = false)
{
int row;
string value, sort, stock, length;
double pLen;
int iLen;
string stringet;
//--- Get data from query ------------------------------------
row = index;
row = row + 1;
foreach (DataRow dataRow in dataTable.Rows)
{
sort = dataRow["UGL_SORT"].ToString();
//Wite
Cell cellSort = InsertCellInWorksheet("B", Convert.ToUInt32(row), worksheetPart1);
cellSort.DataType = new EnumValue<CellValues>(CellValues.String);
if (cellSort.CellValue != null)
cellSort.CellValue.Text = sort;
else
cellSort.CellValue = new CellValue(sort);
worksheetPart1.Worksheet.Save();
value = dataRow["STOCK_NO"].ToString();
stock = value;
//Write
//We have Alpha-Numeric Stock Numbers and to avoid Repaired Records: Cell information from /xl/worksheets/sheet1.xml part error
Cell cellStock = InsertCellInWorksheet("D", Convert.ToUInt32(row), worksheetPart1);
if (stock.Equals(string.Empty, StringComparison.InvariantCultureIgnoreCase))
{
cellStock.DataType = new EnumValue<CellValues>(CellValues.String);
cellStock.CellValue = new CellValue(stock);
}
else
{
if (stock.Equals("0", StringComparison.InvariantCultureIgnoreCase))
{
cellStock.DataType = new EnumValue<CellValues>(CellValues.Number);
cellStock.CellValue = new CellValue(0);
}
else
{
int.TryParse(stock, out int stockValue);
if (stockValue == 0)
{
cellStock.DataType = new EnumValue<CellValues>(CellValues.String);
cellStock.CellValue = new CellValue(stock);
}
else
{
cellStock.DataType = new EnumValue<CellValues>(CellValues.Number);
cellStock.CellValue = new CellValue(stockValue);
}
}
}
worksheetPart1.Worksheet.Save();
value = dataRow["QTY"].ToString();
if (roundLength)
{
double pipeLength = Convert.ToDouble(value);
pipeLength = Math.Ceiling(pipeLength);
length = pipeLength.ToString();
}
else
{
length = value;
}
//Write
Cell cellQty = InsertCellInWorksheet("E", Convert.ToUInt32(row), worksheetPart1);
cellQty.DataType = new EnumValue<CellValues>(CellValues.Number);
if (cellQty.CellValue != null)
cellQty.CellValue.Text = length;
else
cellQty.CellValue = new CellValue(length);
worksheetPart1.Worksheet.Save();
value = dataRow["DIAMETER"].ToString();
//Write
Cell cellDiameter = InsertCellInWorksheet("F", Convert.ToUInt32(row), worksheetPart1);
cellDiameter.DataType = new EnumValue<CellValues>(CellValues.Number);
if (cellDiameter.CellValue != null)
cellDiameter.CellValue.Text = value;
else
cellDiameter.CellValue = new CellValue(value);
worksheetPart1.Worksheet.Save();
value = dataRow["DESCRIPTION"].ToString();
//Write
Cell cellDescription = InsertCellInWorksheet("G", Convert.ToUInt32(row), worksheetPart1);
cellDescription.DataType = new EnumValue<CellValues>(CellValues.String);
if (cellDescription.CellValue != null)
cellDescription.CellValue.Text = value;
else
cellDescription.CellValue = new CellValue(value);
worksheetPart1.Worksheet.Save();
value = dataRow["PRICE"].ToString();
worksheetPart1.Worksheet.Save();
//--- For pipe show length in columnn 5 --------------------------------
if (sort.Substring(1, 1) == "D")
{
length = dataRow["LEN"].ToString();
pLen = Convert.ToDouble(length) / 1000;
iLen = (int)Math.Ceiling(pLen);
//if (pLen > iLen) iLen++;
stringet = iLen.ToString();
//Write
Cell cellLen = InsertCellInWorksheet("E", Convert.ToUInt32(row), worksheetPart1);
cellLen.DataType = new EnumValue<CellValues>(CellValues.String);
if (cellLen.CellValue != null)
cellLen.CellValue.Text = stringet;
else
cellLen.CellValue = new CellValue(stringet);
}
worksheetPart1.Worksheet.Save();
//Write
Cell cellPrice = InsertCellInWorksheet("P", Convert.ToUInt32(row), worksheetPart1);
cellPrice.DataType = new EnumValue<CellValues>(CellValues.Number);
if (cellPrice.CellValue != null)
{
if (value == "0")
{
//Set Color to Yellow
AddbackgroundFormat(sheetDocument, cellPrice, cellFormat);
Cell cellLast = InsertCellInWorksheet("Q", Convert.ToUInt32(row), worksheetPart1);
//Set Color to Yellow
AddbackgroundFormat(sheetDocument, cellLast, cellFormat);
}
else
cellPrice.CellValue.Text = value;
}
else
{
if (value == "0")
{
//Set Color to Yellow
AddbackgroundFormat(sheetDocument, cellPrice, cellFormat);
Cell cellLast = InsertCellInWorksheet("Q", Convert.ToUInt32(row), worksheetPart1);
//Set Color to Yellow
AddbackgroundFormat(sheetDocument, cellLast, cellFormat);
}
else
cellPrice.CellValue = new CellValue(value);
}
worksheetPart1.Worksheet.Save();
stringet = item.ToString();
//Write
Cell cellID = InsertCellInWorksheet("C", Convert.ToUInt32(row), worksheetPart1);
cellID.DataType = new EnumValue<CellValues>(CellValues.Number);
if (cellID.CellValue != null)
cellID.CellValue.Text = stringet;
else
cellID.CellValue = new CellValue(stringet);
worksheetPart1.Worksheet.Save();
item++;
row++;
//--- Add record to the Balloons table ------------------
string fields = "UGL_SORT, ITEM_NO, STOCK_NO";
string values = Global.Concatenate.SINGLE_QUOTE + sort + Global.Concatenate.SINGLE_QUOTE
+ Global.Concatenate.COMMA + Global.Concatenate.SINGLE_QUOTE + stringet + Global.Concatenate.SINGLE_QUOTE
+ Global.Concatenate.COMMA + Global.Concatenate.SINGLE_QUOTE + stock + Global.Concatenate.SINGLE_QUOTE;
string query = string.Format(Global.Query.INSERT_QUERY, "BALLOONS", fields, values);
string message = Global.Initialize.EMPTY_STRING;
accessDB.ExecuteQuery(query, ref message);
}
}
I didn't find any issue in the code as it is writing the data to Excel and access but while opening Excel it is showing the error. All the formulas are also in place after opening the Excel and looking solution for the same. Please let me know for any other information.
The work around I've been using is to remove the calculation chain and force excel to recalculate on load.
eg: if (WorkbookPart.CalculationChainPart != null) { WorkbookPart.DeletePart(WorkbookPart.CalculationChainPart); }
WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
Hi tried this but as far as I remember this approach didn't work for me. I tired this before writing the data to Excel and also after writing the data to Excel.
If we want to support calc chain modifications in the SDK, an example of this is shown in https://github.com/dotnet/Open-XML-SDK/pull/1491
@tomjebo @mikeebowen @AlfredHellstern thoughts?
I didn't find any issue in the code as it is writing the data to Excel and access but while opening Excel it is showing the error.
@subashnalla can you provide an example workbook that has this behavior (i.e. after writing it using your code)? I'm just checking with the Excel team to verify that updating the calcChain.xml part is really necessary. My understanding is that removing it is sufficient. @twsouthwick's #1491 should provide a step towards updating the calcChain part, however, if Excel is actually reading it, then we'll need to know just what it is that it doesn't like. It's possible that ordering is important.
UPDATE: I've discussed with the Excel team and they are very clear that we should not add to the calcChain.xml part. We will have to investigate this with the actual workbook and code being used (or something close to it) to see what's going on.
@subashnalla I've done a simple test with adding a formula to a cell in a sheet with existing formulas and existing calcChain.xml part. With and without modifying the calcChain.xml part (and including fullCalcOnLoad and forceFullCalc set to true), I don't see this error. I suspect something else in your workbook so I will need to take a look if you can supply it.
@subashnalla Another note, the Excel team is clear that you should not use ForceFullCalculation. The other setting is fine.
@subashnalla Can you please share a test file that shows the problem? Also, what happens if you completely remove the calcChain.xml part after adding formulas? It shouldn't be the same error that you're getting now. Perhaps you're not removing the part correctly?
@subashnalla The test I did earlier was only adding a formula. I modified that to remove a formula without touching the calcChain.xml part and I now see the same error you do. I added the code to remove the calcChain.xml part (as @kodenine had demonstrated) and ran that code. The resulting workbook opens just fine in Excel.
Therefore I think that is the correct solution here. Again, if this still doesn't work for you, please share you final document and I will take a look at it.
Also, if I leave the calcChain.xml part but remove references to the removed formula cells, Excel will also open the workbook without errors. However, the guidance from the Excel team is not to modify the calcChain.xml part but to remove it.
Hi sorry for the delayed response. Client has agreed to remove the formula from the cell in template, once we add a new row then we are updating the required cell with the formula and not getting any error.
