Can't remove a table column's calculated column formula

[craigbrown]

I've found a few odd things with the CalculatedColumnFormula property. I'm not sure if they're bugs or I'm using it wrong, or some combination of the two.

1. If I remove the CalculatedColumnFormula from a table column, it appears to work fine:

   table.Column[0].CalculatedColumnFormula = ""
   

   but if I save the file and try to open it in Excel, I get a message that the workbook is corrupt.

   It looks like the table1.xml file ends up looking like this:

   <tableColumn id="1" name="Col1">
     <calculatedColumnFormula>
     </calculatedColumnFormula>
   </tableColumn>
   

   when I think it should look like this:

   <tableColumn id="1" name="Col1" />
   

2. I wasn't sure if that was the right way to remove a column formula so I tried some other ways which also didn't work. Setting that property to null instead of an empty string results in a NullReferenceException.

3. I also tried just removing the formula from every row in the table.

   table.Range.ClearFormulas();
   table.Range.ClearFormulaValues();
   

   This does remove the formula from existing cells, but the CalculatedColumnFormula still has a value.

   I just checked how Excel works - it removes the CalculatedColumnFormula if you delete the formula from every cell in the column at once (but it actually doesn't remove it if you just delete the formula from each cell one at a time).

4. This is possibly related. In some instances, I want to keep the CalculatedColumnFormula in my table, but have one cell blank or with a different formula. If I add a row to the table, this cell is replaced with the CalculatedColumnFormula. I think this shouldn't happen.

Unit Tests

[TestMethod]
public void CalculatedColumnFormula_SetToEmptyString()
{
    using (var pck = new ExcelPackage())
    {
        // Set up a worksheet containing a table
        var wks = pck.Workbook.Worksheets.Add("Sheet1");
        wks.Cells["A1"].Value = "Col1";
        wks.Cells["B1"].Value = "Col2";
        wks.Cells["C1"].Value = "Col3";
        wks.Cells["A2"].Value = 1;
        wks.Cells["B2"].Value = 2;
        var table1 = wks.Tables.Add(wks.Cells["A1:C2"], "Table1");
        var formula = "Table1[[#This Row],[Col1]]+Table1[[#This Row],[Col2]]";
        table1.Columns[2].CalculatedColumnFormula = formula;

        // Check the calculated column formula
        Assert.AreEqual(formula, wks.Cells["C2"].Formula);
        Assert.AreEqual(formula, table1.Columns["Col3"].CalculatedColumnFormula);

        // Remove the calculated column formula from the table
        table1.Columns["Col3"].CalculatedColumnFormula = "";

        // Check the formula has been removed from the table
        Assert.IsTrue(string.IsNullOrEmpty(wks.Cells["C2"].Formula));
        Assert.IsTrue(string.IsNullOrEmpty(table1.Columns["Col3"].CalculatedColumnFormula));

        pck.SaveAs(@"C:\epplusTest\Testoutput\CalculatedColumnFormula_SetToEmptyString.xlsx");

        // NOW OPEN THE FILE IN EXCEL - IS IT CORRUPT?
        Assert.Inconclusive();
    }
}

[TestMethod]
public void CalculatedColumnFormula_RemoveFormulas()
{
    using (var pck = new ExcelPackage())
    {
        // Set up a worksheet containing a table
        var wks = pck.Workbook.Worksheets.Add("Sheet1");
        wks.Cells["A1"].Value = "Col1";
        wks.Cells["B1"].Value = "Col2";
        wks.Cells["C1"].Value = "Col3";
        wks.Cells["A2"].Value = 1;
        wks.Cells["B2"].Value = 2;
        var table1 = wks.Tables.Add(wks.Cells["A1:C2"], "Table1");
        var formula = "Table1[[#This Row],[Col1]]+Table1[[#This Row],[Col2]]";
        table1.Columns[2].CalculatedColumnFormula = formula;

        // Check the calculated column formula
        Assert.AreEqual(formula, wks.Cells["C2"].Formula);
        Assert.AreEqual(formula, table1.Columns["Col3"].CalculatedColumnFormula);

        // Remove all formulas from the table
        table1.Range.ClearFormulas();
        table1.Range.ClearFormulaValues();

        // Check the calculated column formula is no longer there
        Assert.IsTrue(string.IsNullOrEmpty(table1.Columns["Col3"].CalculatedColumnFormula));
    }
}

[TestMethod]
public void CalculatedColumnFormula_RemoveFormulas_AddRow()
{
    using (var pck = new ExcelPackage())
    {
        // Set up a worksheet containing a table
        var wks = pck.Workbook.Worksheets.Add("Sheet1");
        wks.Cells["A1"].Value = "Col1";
        wks.Cells["B1"].Value = "Col2";
        wks.Cells["C1"].Value = "Col3";
        wks.Cells["A2"].Value = 1;
        wks.Cells["B2"].Value = 2;
        var table1 = wks.Tables.Add(wks.Cells["A1:C2"], "Table1");
        var formula = "Table1[[#This Row],[Col1]]+Table1[[#This Row],[Col2]]";
        table1.Columns[2].CalculatedColumnFormula = formula;

        // Check the calculated column formula
        Assert.AreEqual(formula, wks.Cells["C2"].Formula);
        Assert.AreEqual(formula, table1.Columns["Col3"].CalculatedColumnFormula);

        // Remove all formulas from the table
        table1.Range.ClearFormulas();
        table1.Range.ClearFormulaValues();
        Assert.IsTrue(string.IsNullOrEmpty(wks.Cells["C2"].Formula));

        // Add a row to the table
        table1.InsertRow(1);

        // Check the formula has not been reinserted
        Assert.IsTrue(string.IsNullOrEmpty(wks.Cells["C2"].Formula));
    }
}

[TestMethod]
public void CalculatedColumnFormula_OneCellDifferent_AddRow()
{
    using (var pck = new ExcelPackage())
    {
        // Set up a worksheet containing a table
        var wks = pck.Workbook.Worksheets.Add("Sheet1");
        wks.Cells["A1"].Value = "Col1";
        wks.Cells["B1"].Value = "Col2";
        wks.Cells["C1"].Value = "Col3";
        wks.Cells["A2"].Value = 1;
        wks.Cells["B2"].Value = 2;
        wks.Cells["A3"].Value = 3;
        wks.Cells["B3"].Value = 4;
        wks.Cells["A4"].Value = 5;
        wks.Cells["B4"].Value = 6;
        var table1 = wks.Tables.Add(wks.Cells["A1:C4"], "Table1");
        var formula = "Table1[[#This Row],[Col1]]+Table1[[#This Row],[Col2]]";
        table1.Columns[2].CalculatedColumnFormula = formula;

        // Check the calculated column formula has been added to each cell
        Assert.AreEqual(formula, wks.Cells["C2"].Formula);
        Assert.AreEqual(formula, wks.Cells["C3"].Formula);
        Assert.AreEqual(formula, wks.Cells["C4"].Formula);
        Assert.AreEqual(formula, table1.Columns["Col3"].CalculatedColumnFormula);

        // Remove the calculated column formula from one row and use a different formula instead
        wks.Cells["C3"].ClearFormulas();
        wks.Cells["C3"].ClearFormulaValues();
        var differentFormula = "Table1[[#This Row],[Col1]]";
        wks.Cells["C3"].Formula = differentFormula;
        Assert.AreEqual(differentFormula, wks.Cells["C3"].Formula);

        // Add a new row to the bottom of the table
        table1.AddRow();

        // Check that the new row has the formula
        Assert.AreEqual(formula, wks.Cells["C5"].Formula);
        Assert.AreEqual(formula, table1.Columns["Col3"].CalculatedColumnFormula);

        // Check the cell where we used a different formula hasn't changed
        Assert.AreEqual(differentFormula, wks.Cells["C3"].Formula);
    }
}

[JanKallman]

I'll have a look

[JanKallman]

This is a bug. I'll provide a fix shortly.

[JanKallman]

Fixed in 6.0.7