excel related methods - Forum - OpenEdge Development - Progress Community
 Forum

excel related methods

  • How to find the no of rows filled in a excel file? and

    Can anyone let me know where I can find all excel (reading and writing) related methods in progress 4gl.

  • I don't know if this is useful to you, but I have been using EPPlus for reading and writing Excel files (.xlsx):

    http://epplus.codeplex.com/

    I works well from Progress 4GL and supports a wide range of Excel functions (but not all of them). You don't have to have Excel or any other spreadsheet application installed in order to use it. 

    E.g. Infragistics and Telerik also have .NET libraries for the same purpose.

  • Thanks Marko, But this is not gonna help me as I need to get all available functions of excel supported by progress. Or any application or software which will show me all these function so I can use those in my code.

    My basis requirement is to get the last filled row in my excel and also last filled column.

    Like these I know

    chExcelApplication:Workbooks:OPEN(<filepath>)

    chWorkSheet:Range("A1"):TEXT

    etc. are some method / function available but I need to see all available functions/methods

    So I think there will definitely any function available which will give me my results or make me to close enough to my results.

  • To see a list of supported methods, properties, and events, run %DLC%\bin\proobjvw.exe and use it to open excel.exe.

  • For finding the last cell, this may be helpful:

    http://www.rondebruin.nl/win/s9/win005.htm

  • Anit,
     
    You can use the COM Object Viewer ($DLC/bin/proobjvw.exe) to open type libraries and see what things are available, however, please understand that we simply provide you the means to interact with COM objects.  We don't document or support an given third party COM object.  The most we can do is to help you with a specific method call you may be having problems with assuming that you can provide us with code and documentation for the method call.
     
    Brian

  • Great Thanks Rob and Brian...This is what I want.

  • Great Thanks Rob and Brian

  • Marko,  

    Do you have some sample code you could share, along with any instructions for how to get everything running. We are in the process of trying a few options out for producing Excel and epplus looks perfect. We're on 102b if that makes any difference

    Thanks,

    Richard

  • IF you want to generate excel files, also take a look at docxfactory.com....  It's for word files, but he's on the rails to get excel files also.

    Anyway, keep it on the radar for your docs output

    Superb work from fellow Alon Blitch

  • Hi Richard,

    first you need to download EPPlus.dll (http://epplus.codeplex.com/) and place it into your assemblies folder (where -assemblies startup parameter points to).

    Then add a reference into assemblies.xml (also found in the assemblies folder):

    <?xml version="1.0" encoding="UTF-8"?>
    <references>
    <assembly name="EPPlus, Version=4.0.4.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1"/>
    </references>

    Then you can use this sample to get started:

    USING OfficeOpenXml.*.
        
    DEF VAR oPackage AS ExcelPackage NO-UNDO. 
    DEF VAR oSheet AS ExcelWorksheet NO-UNDO.
    
    /* Create a new workbook */
    oPackage = NEW ExcelPackage().
    
    /* Add a new worksheet */
    oSheet = oPackage:Workbook:Worksheets:Add("Sheet1").
    
    /* Set some values */
    oSheet:Cells["A1"]:VALUE = 100.
    oSheet:Cells["A2"]:VALUE = 200.
    
    /* Add a formula */
    oSheet:Cells["A3"]:Formula = "SUM(A1:A2)".
    
    /* Calculate formulas in the sheet */
    CalculationExtension:Calculate(oSheet).
    
    /* Display the result of the formula */
    MESSAGE oSheet:Cells["A3"]:VALUE.
    
    /* Save workbook as a file */
    oPackage:SaveAs(NEW System.IO.FileInfo("test.xlsx")).

    In EPPlus web site, you can find a help file describing all the EPPlus classes and methods you can use. Hope this helps.

    Regards,
    Marko

  • the free docxfactory project also has an excel library that is 100% progress (that is in the process of being ported to c++).

    you can export temp-tables, datasets, queries etc. to excel

    and convert the progress formats, labels etc. to excel formats, labels etc. so you don't have to do all the tedious work yourself.

    the library runs on unix/linux and windows and you only need to learn 4 commands to create files.

    disclosure: i wrote the project.

    HTH

  • Marko,

    Thanks for this, it has saved us a lot of time. Just out of interest, if we have a dll how do you find out the info needed for the assemblies bit?

    i.e <assembly name="EPPlus, Version=4.0.4.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1"/>

  • Thanks, I shall take a look at it.

  • Richard, IIRC, Developer Studio generated the assembly entry when adding a reference to the dll.