Set formula in Excel use C# and OpenXML, Excel file auto add @ character in the formula

Question

I set formula for one cell like this:

cell.CellFormula = @"IFERROR((POWER(PRODUCT(IF(O3:O1000<>"""",1+O3:O1000,"""")),1/COUNT(O3:O1000))-1)*$I$1,"""")";

but when I open the Excel file, the formula is automatically added @ character:

 =IFERROR((POWER(PRODUCT(IF(@O3:O1000<>"",1+@O3:O1000,"")),1/COUNT(O3:O1000))-1)*$I$1,"")

Could you please tell me how to solve this?

Thank you

Answer 1

Can OpenXml directly assign a string to cell.CellFormula?

When I test, I must create a CellFormula object. Is it a version difference?

And I used OpenXml and the formula you provided but did not reproduce your problem.

My code:

        static void Main(string[] args)  
        {  
            UpdateExcelUsingOpenXMLSDK(@"D:\test\excel\3.xlsx");  
            Console.WriteLine();  
        }  
        public static void UpdateExcelUsingOpenXMLSDK(string fileName)  
        {  
            // Open the document for editing.  
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))  
            {  
                // Access the main Workbook part, which contains all references.  
                WorkbookPart workbookPart = spreadSheet.WorkbookPart;  
                // get sheet by name  
                Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();  
  
                WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;  
  
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();  
  
                Cell formulaCell = InsertCellInWorksheet("C", 10, worksheetPart);  
                formulaCell.DataType = new EnumValue<CellValues>(CellValues.Number);  
                formulaCell.CellFormula = new CellFormula(@"IFERROR((POWER(PRODUCT(IF(O3:O1000<>"""",1+O3:O1000,"""")),1/COUNT(O3:O1000))-1)*$I$1,"""")");  
             
                worksheetPart.Worksheet.Save();  
  
                spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;  
                spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;  
  
            }  
        }  
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)  
        {  
            Worksheet worksheet = worksheetPart.Worksheet;  
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();  
            string cellReference = columnName + rowIndex;  
  
            Row row;  
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)  
            {  
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();  
            }  
            else  
            {  
                row = new Row() { RowIndex = rowIndex };  
                sheetData.Append(row);  
            }  
  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)  
            {  
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();  
            }  
            else  
            {  
                Cell refCell = null;  
                foreach (Cell cell in row.Elements<Cell>())  
                {  
                    if (cell.CellReference.Value.Length == cellReference.Length)  
                    {  
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)  
                        {  
                            refCell = cell;  
                            break;  
                        }  
                    }  
                }  
                Cell newCell = new Cell() { CellReference = cellReference };  
                row.InsertBefore(newCell, refCell);  
                worksheet.Save();  
                return newCell;  
            }  
        }  
    }  

Result:

I am using OpenXml 2.12.3, Office 2016.

Most of the above code comes from this link.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi TimonYang-MSFT

Thanks for your answer

My old code:

 string formular=@"IFERROR((POWER(PRODUCT(IF(O3:O1000<>"""",1+O3:O1000,"""")),1/COUNT(O3:O1000))-1)*$I$1,"""")";
    Cell cell = FindCell(colName, rowIndex);
                if (cell != null)
                {
                    CellFormula cellformula = new CellFormula();
                    cellformula.Text = formular;

                    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                    cell.CellFormula = cellformula;
                 }

I Changed to:

 string formular=@"IFERROR((POWER(PRODUCT(IF(O3:O1000<>"""",1+O3:O1000,"""")),1/COUNT(O3:O1000))-1)*$I$1,"""")";
    Cell cell = FindCell(colName, rowIndex);
                if (cell != null)
                {
    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                    CellFormula cellformula = new CellFormula(formular);
                    cell.CellFormula = cellformula;
                 }

but it's not work

I am using OpenXml 2.12.3, Office 365.

By the way, I change a little formula in excel and it work.