none
C# call excel function with variable number of parameters RRS feed

  • Question

  • Hi there,

     I am wondering how to call excel function when the number of parameters in in the range, but i do not want to use object range.

    regards

    Thursday, December 19, 2019 6:32 AM

All replies

  • Can you be specific about what you're trying to do?  Exactly which function(s)?

    The Excel functions that expect a range generally really to want a range.


    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    • Marked as answer by Mid Way Friday, December 20, 2019 6:36 AM
    • Unmarked as answer by Mid Way Friday, December 20, 2019 8:41 AM
    Thursday, December 19, 2019 6:48 AM
  • say i want to use sum(), and a user want 3 numbers to sum, the other user want 2 numbers to sum, etc.

    regards and thank

    Thursday, December 19, 2019 6:53 AM
  • Hi Mid Way,

    Thank you for posting here.

    If you just want to sum some values in the excel file, you can use the following code.

      string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =d:\\test\\excel\\111.xlsx;Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
                using (OleDbConnection connection = new OleDbConnection(constr))
                {
                    connection.Open();
    
                    string strCom = " SELECT sum(salary) FROM [sheet1$] where Id >= 1 and Id<=4";
    
                    DataTable dataTable = new DataTable();
                    OleDbDataAdapter oleAdpt = new OleDbDataAdapter(strCom, connection); //here we read data from sheet1  
                    oleAdpt.Fill(dataTable); //fill excel data into dataTable  
    
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadKey();
                }

    Result:

    Excel file:

    Regardless of how many numbers you want, you only need to specify the range, that is, there are always only two parameters.

    If I misunderstood what you mean, please let me know and describe your problem in more detail.

    Hope this could be helpful.

    Best Regards,

    Timon


    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, December 20, 2019 6:41 AM
  • Hi Timon,

    Thank you for the quick reply.

    Actually I want to create an extension module for Outsystems Platform using C#. How to implement functions already provided by Excel?

    In this case, say that i want to use sum function that can be many parameters, is there underlying function that i can call say sum(arrays), instead of sum(range)?

    Actually i do not need the excel, but its great financial functions.

    regards and many thank Sir.


    • Edited by Mid Way Friday, December 20, 2019 8:48 AM
    Friday, December 20, 2019 8:45 AM
  • Hi Timon,

    Thank you for the quick reply.

    Actually I want to create an extension module for Outsystems Platform using C#. How to implement functions already provided by Excel?

    In this case, say that i want to use sum function that can be many parameters, is there underlying function that i can call say sum(arrays), instead of sum(range)?

    Actually i do not need the excel, but its great financial functions.

    regards and many thank Sir.


    The first thing to do is to ask the vendor if this is possible

    https://www.outsystems.com/community/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, December 20, 2019 12:33 PM
    Moderator