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):
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:
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.