Forums Ultimate UI for WPF
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Manik
65
Formula removal from Excel Worksheets
posted

Hi, 

I have some source excel files (.xls, .xlsx, .xlsx) which contain some formulae. I programmatically fill some cells using Infragistics Excel API and the excel formula will fill the rest of the cells.

When I save this excel workbook, it saves the worksheet with formula. However, my requirement is that the saved worksheet shouldn't contain any formula. 

I looked into the Infragistics Excel API, but there is no way/method to clear the Formula from Worksheet Cell. 

Any help in clearing the formulae but preserving the resultant values form the Worksheets using Infragistics Excel API or any other possible way is really appreciable.

Thanks in advance.

-Manikanta

  • 34510
    Verified Answer
    Offline posted

    Hi Manikanta,

    In order to clear the formula from the WorksheetCell you'll have to explicitly set the WorksheetCell.Value to something.  This will clear out the Formula property.

    So in your scenario, after you load the excel file and programmatically fill in some cells you will have to force your formulas to evaluate and then manually assign the value to WorksheetCell.Value property.

    // this is the existing formula in the cell
    var formula = wb.Worksheets[0].Rows[2].Cells[0].Formula;

    // force the formula to apply itself on the cell.  This will update the cell value
    wb.Worksheets[0].Rows[2].Cells[0].ApplyFormula(formula.ToString());

    // grab the cell value
    var cellValue = wb.Worksheets[0].Rows[2].Cells[0].Value;

    // reassign the cell value.  this clears the Formula.
    wb.Worksheets[0].Rows[2].Cells[0].Value = cellValue;