question

TranDienHai-2233 avatar image
0 Votes"
TranDienHai-2233 asked MichaelYoung-5501 commented

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

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





dotnet-csharp
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Does the formula work even with this ‘@’?

0 Votes 0 ·

If it has @ in the formula, it only uses value one cell is O3. I want to calculate in a range O3:O1000.

0 Votes 0 ·

adding the @ to formulas appears to be a "new feature" with excel and this appears to be a bug in excel vba - I have a similar fault in writing formulas into cells - turning off [file/options/advanced/Extend data range formats and formulas] stops excel adding the @ to my formulas when entered manually but it still adds the @ to formulas written through vba. Oh, for bug free software!

0 Votes 0 ·
TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered

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:
76056-capture.png

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.


capture.png (8.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TranDienHai-2233 avatar image
0 Votes"
TranDienHai-2233 answered TimonYang-MSFT commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TranDienHai-2233
Calling a parameterless constructor and then assigning a value to the Text property is no different from calling another constructor directly.
So it is normal that it will not work.

I change a little formula in excel and it work

What does this mean, is the problem solved?
0 Votes 0 ·