none
Problem with excel formulas in c# RRS feed

  • Question

  • Hello, I´m trying to execute a formula in an excel sheet from a c# app. The problem is that the formula begin with "COUNTIFS" and the method SetCellFormula from ISheet doesn´t recognise it. I´m using the library HSSFWorkbook. What can I do for fix this mistake?

    The problematic line:

    COUNTIFS('hojaExcel1'!$W:$W;\"S\";'hojaExcel1'!$M:$M;'hojaExcel2'!C" + 7 + ")&\"/\"&(COUNTIFS('hojaExcel1'!$W:$W;\"S\";'hojaExcel1'!$M:$M;'hojaExcel2'!C" + 7 + ")+COUNTIFS('hojaExcel1'!$W:$W;\"N\";'hojaExcel1'!$M:$M;'hojaExcel2'!C" + 7 + "))

    PD: The formula works if I insert it directly into the sheet.
    • Edited by Straydog88 Tuesday, May 16, 2017 2:03 PM
    Tuesday, May 16, 2017 1:53 PM

All replies

  • Hi Straydog88,
     
    Excel needs the formula in US syntax, which means you have to replace all the ;
    with ,
     
     
    Tuesday, May 16, 2017 2:43 PM
  • Thanks, but this is not the problem. When I execute the app, this error message appears:

    "Name 'COUNTIFS' is completely unknown in the current workbook".

    But the same formula works in the excel sheet when I push the key F2. How do I run it from the c# code?

    Tuesday, May 16, 2017 2:55 PM
  • Hi Straydog88,
     
    I'm afraid I have zero experience with C#
     
     
    Tuesday, May 16, 2017 5:02 PM
  • I just need to command excel that resolve the formulas by itself, but a don´t find the way from anywhere. It is possible?
    Wednesday, May 17, 2017 6:31 AM
  • Hello,

    >>SetCellFormula from ISheet doesn´t recognise it. I´m using the library HSSFWorkbook.

    This is a third-party library, i would suggest you ask the provider or developer to get professional support.

    Using Microsoft.Office.Interop.Excel library, you could use Range.Formula property  to set formula.

    E.g.

       Excel.Range cell = ws.Range["A1"];
                cell.Formula = "=SUM(B1:B2)";

    To automate Excel using Microsoft.Office.Interop.Excel , please visit

    How to automate Microsoft Excel from Microsoft Visual C#.NET

    How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 17, 2017 9:08 AM
    Moderator
  • Thanks, but the entire project is using HFFSWorkbook, I can´t change it now. Further, i have tested in a new project and the next error message has appeared: 

    80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

    I don´t know what to do.

    • Edited by Straydog88 Wednesday, May 17, 2017 11:24 AM
    Wednesday, May 17, 2017 11:06 AM
  • I tried with excel´s macros. Is it possible to make something like:

    ActiveCell.FormulaR1C1 = _=COUNTIFS('hojaExcel1'!$Y:$Y;\"s\";'hojaExcel1'!$L:$L;'hojaExcel2'!$C" + (i + 1) + ";'hojaExcel1'!$C:$C;\"MER3\")"
    Range("E8").Select

    in c#? (The code is in VBA).



    • Edited by Straydog88 Monday, May 22, 2017 6:57 AM
    Thursday, May 18, 2017 8:52 AM
  • Hello,

    You could visit the following cases to see how to insert formula using NOPI.

    http://stackoverflow.com/questions/5855813/npoi-how-to-read-file-using-npoi

    http://stackoverflow.com/questions/23780006/formula-in-excel-using-npoi

    http://stackoverflow.com/questions/40225383/npoi-reading-and-setting-formulas

    >>80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

    What is your project?  If you are automating Excel from server side, Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    In that situation, you could manipulate Office files using Open XML. You could visit the following link to see a sample.

    https://social.msdn.microsoft.com/Forums/office/en-US/b28a9f9c-9ad7-4eea-adab-b062883c11e2/formula-cells-in-excel-using-openxml?forum=oxmlsdk

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 19, 2017 9:21 AM
    Moderator
  • Nothing works. I think this task is impossible to do. Thank you anyway.
    Monday, May 22, 2017 6:21 AM
  • Hi Straydog88,

    Thanks for your post.

    I am not familiar with HSSFWorkbook, so, I am not sure whether your requirement is possible by HSSFWorkbook.

    As the suggestion from Celeste, your requirement could be achieved by cell.Formula which is defined in Office Library.

    But, after check conversion between you and Celete, it seems you switch to Office Object Model.

    Since this forum is used to discuss about Office Object Model, if you have any issue about HSSFWorkbook, I would suggest you go to HSSFWorkbook support forum for help.

    Thanks for your understanding.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 2, 2017 7:51 AM