my requirement to is to insert formula for a cell. i am using below method to insert formula. And its inserting formula corectly and formula working fine. but when i insert formula my excel file got corrpted and showing the message "Excel found unreadable content in "exceltemplate.xlsx". Do you want to recover the contents of..........". I searched lot,but not getting resolved. Please help to resolve this..
public void InsertFormula(string filepath, string SheetName, string strCellIndex, string strFormula)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filepath, true))
{
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == SheetName);
if (sheets.Count() == 0)
{
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
Row row1 = new Row()
{
RowIndex = (UInt32Value)4U,
Spans = new ListValue<StringValue>()
};
Cell cell = new Cell() { CellReference = strCellIndex };
CellFormula cellformula = new CellFormula();
cellformula.Text = strFormula;
cell.DataType = CellValues.Number;
CellValue cellValue = new CellValue();
cellValue.Text = "0";
cell.Append(cellformula);
cell.Append(cellValue);
row1.Append(cell);
sheetData.Append(row1);
worksheet.Save();
document.Close();
}
}
And i am calling that method like below
InsertFormula(filepath, SheetName, "D15","=SUM(D9:D11)");
InsertFormula(filepath, SheetName, "D16", "=IF(D15-D6<0,0,D15-D6)");