Question Excel extraction

B

BobyIsProgress

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
 
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)
 
BobyIsProgress said:
Do you have a code example ? I will fo work on that asap :)
Click to expand...
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.
 
Stefan said:
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.
Click to expand...
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
 
BobyIsProgress said:
s it was suggested in my last topic, progress in 11 version is more efficient with Excel Intero
Click to expand...

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.

github.com

GitHub - Jimbobnz/ABL-XLSX-Writer: XLSX Writer for OpenEdge ABL

XLSX Writer for OpenEdge ABL. Contribute to Jimbobnz/ABL-XLSX-Writer development by creating an account on GitHub.
github.com github.com
 
PatrickTingen said:
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)
Click to expand...
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 :)
 
BobyIsProgress said:
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
Click to expand...
This may help.
 

Attachments

You must log in or register to reply here.
Back
Top