Question Excel extraction

Hello Guyz,
I'm at it again :p
So as it was suggested in my last topic, progress in 11 version is more efficient with Excel Interop. So I run some test:
Export a table of 640 lines by 20 columns with text, numerics and date and this are the results:
OE11: 0.026s/line
OE09: 0.039s/line
So yes it's more efficient but at this tate is still slower than make a csv file and opening it.

But my users are asking for colors in extraction, color I put in my Browse widget.

So for doing this I was thinking of two ways:
1. Saying adios to the instant extraction that give my csv file and go full excel API
2. Generate xml formatted file and opening it in excel.

What are your advice.
Please keep in mind that a request that takes 1 min to be donne and displayed and take 3 min to be export in a excel file is not really intersting for my users.

Best Regards,

BobyIsProgress
 

Stefan

Well-Known Member
We use option 3

Create an xlsx file (is multiple xml files created using sax-writer, which are then zipped as xlsx) - fetching simple data (1 table), creating report and creating excel file takes less than 1 second (640 records, approx 20 columns)
 

Stefan

Well-Known Member
Do you have a code example ? I will fo work on that asap :)
Not that I can share.

I can share that we started by reducing an xlsx to a bare minimum (there is a lot of fluff in an empty worksheet) and then building it up with our content. It's a lot more fun than reading through pages and pages of specifications.
 
Not that I can share.

I can share that we started by reducing an xlsx to a bare minimum (there is a lot of fluff in an empty worksheet) and then building it up with our content. It's a lot more fun than reading through pages and pages of specifications.
That's why I'm excited about doing it :) I remember thinking about building an xlsx from scratch one or two years ago :)
I will try to do a general program and uploading it here if possible
 

Cecil

19+ years progress programming and still learning.
s it was suggested in my last topic, progress in 11 version is more efficient with Excel Intero

I started writing ABL Excel Writer about 8 years ago. It was never 100% complete but it did the fundamentals of what I needed it to do a the time. It was intended to be a pure ABL code but OpenEdge did not natively support zip files.

 
You could also take a look at GitHub - rodolfoag/4gl-excel: Library to create an Excel file from Progress 4GL where this is already solved.
I used this to create the dump-to-excel in DataDigger (code here: DataDigger/wDump.w at master · patrickTingen/DataDigger starting at line 1594)
The solution is interesting but he manipulate excel as I want to be free of for the following reason:
1. Manipulate excel make your extraction as slow as a tortroise
2. If you need to generate xlsx file from a server that don't have excel you can't .

But thank you for your advice :)
 

nborshukov

New Member
Hello Guyz,
I'm at it again :p
So as it was suggested in my last topic, progress in 11 version is more efficient with Excel Interop. So I run some test:
Export a table of 640 lines by 20 columns with text, numerics and date and this are the results:
OE11: 0.026s/line
OE09: 0.039s/line
So yes it's more efficient but at this tate is still slower than make a csv file and opening it.

But my users are asking for colors in extraction, color I put in my Browse widget.

So for doing this I was thinking of two ways:
1. Saying adios to the instant extraction that give my csv file and go full excel API
2. Generate xml formatted file and opening it in excel.

What are your advice.
Please keep in mind that a request that takes 1 min to be donne and displayed and take 3 min to be export in a excel file is not really intersting for my users.

Best Regards,

BobyIsProgress
This may help.
 

Attachments

  • xmlspreadsheet.p
    28.2 KB · Views: 19
Top