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.
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 cellvar formula = wb.Worksheets.Rows.Cells.Formula;
// force the formula to apply itself on the cell. This will update the cell valuewb.Worksheets.Rows.Cells.ApplyFormula(formula.ToString());
// grab the cell valuevar cellValue = wb.Worksheets.Rows.Cells.Value;
// reassign the cell value. this clears the Formula.wb.Worksheets.Rows.Cells.Value = cellValue;
Thanks a bunch!
This worked and I got the desired result.