Removing a formula from Excel using OpenXML

Question

I am trying to remove all formulas from a sheet using openxml. This what I am trying:

internal static void ReplaceFormulaWithValue()
{
    var res = _worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();      

    foreach (Row row in _worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>())
    {
        foreach (Cell cell in row.Elements<Cell>())
        {
            if (cell.CellFormula != null &&
                  cell.CellValue != null)
            {
                string cellRef = cell.CellReference;
                CalculationChainPart calculationChainPart = _spreadSheet.WorkbookPart.CalculationChainPart;
                CalculationChain calculationChain = calculationChainPart.CalculationChain;
                var calculationCells = calculationChain.Elements<CalculationCell>().ToList();
                CalculationCell calculationCell = calculationCells.Where(c => c.CellReference == cellRef).FirstOrDefault();
                //CalculationCell calculationCell = calculationChain.Elements<CalculationCell>().Where(c => c.CellReference == cell.CellReference).FirstOrDefault();

                string value = cell.CellValue.InnerText;
                UpdateCell(cell, DataTypes.String, value);

                cell.CellFormula.Remove();
                calculationCell.Remove();                   
            }
        }
    }
    SaveChanges();
}

Upon opening the excel document, I am receiving the following error:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error192600_01.xml</logFileName><summary>Errors were detected in file 'C:\DEV\ExcelEditor\ExcelEditor\bin\Debug\Chart.xlsx'</summary>
<removedParts summary="Following is a list of removed parts:">
<removedPart>Removed Part: /xl/calcChain.xml part with XML error.  (Calculation properties) Catastrophic failure Line 1, column 138.</removedPart>
</removedParts></recoveryLog>

So I compare the old calcChain.xml file with the newly generated using OpenXML SDK Tool. The old file has the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<calcChain xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
 <c i="1" l="1" r="D2"/>
</calcChain>

and the new one after running my code:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<x:calcChain xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">  
</x:calcChain>

Could anyone help if I am missing something here.

Answer 1

If you are deleting all the formulas, what is the need to have an empty calcChain.xml? Have you tried deleting it?

This works for me:

public static void ReplaceFormulasWithValue()
{
    try
    {
        CalculationChainPart calculationChainPart = _spreadSheet.WorkbookPart.CalculationChainPart;
        CalculationChain calculationChain = calculationChainPart.CalculationChain;
        var calculationCells = calculationChain.Elements<CalculationCell>().ToList();

        foreach (Row row in _worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>())
        {
            foreach (Cell cell in row.Elements<Cell>())
            {
                if (cell.CellFormula != null && cell.CellValue != null)
                {
                    string cellRef = cell.CellReference;                            
                    CalculationCell calculationCell = calculationCells.Where(c => c.CellReference == cellRef).FirstOrDefault();

                    UpdateCell(cell, DataTypes.String, cell.CellValue.InnerText);

                    cell.CellFormula.Remove();
                    if(calculationCell != null)
                    {                       
                        calculationCell.Remove();
                        calculationCells.Remove(calculationCell);
                    }
                    else
                    {
                        //Something is went wrong - log it
                    }                   
                }
                if (calculationCells.Count == 0)
                     _spreadSheet.WorkbookPart.DeletePart(calculationChainPart);

            }
            _worksheetPart.Worksheet.Save();
        }
    }
    catch(Exception ex)
    {
        Console.WriteLine(ex);
    }
}