Comment XLSX writer for ABL

[Cecil]

I been working on a new project and I wanted to share what I've been doing. It's class library for generating Excel (.xlsx) documents from the ABL without the need of COM-OBJECTS.

I've tried to make it exclusively ABL but it does relie on a ZIP utility to package up the XML documents.
It is still in it's infancy, in fact it's so new I would classify it as a fetus in development sense.

https://github.com/Jimbobnz/ABL-XLSX-Writer

I'm slowly improving the code and trying to make it more feature rich and robust but it's going to take time.

 

[Rob Fitzpatrick]

Cool! Thanks for sharing.

 

[Cringer]

This is fantastic. Please keep us updated on the progress as I may find this very useful. Would it be easy to reverse engineer it to read files as well?

 

[Cecil]

This is fantastic. Please keep us updated on the progress as I may find this very useful. Would it be easy to reverse engineer it to read files as well?

In a totally separate project a few years ago, I wrote a program a which will read xlsx documents. It parses multiple worksheets and extracts the cell data into temp-tables. Surprisingly it works quite well, but a bit slow.

Knowing more at a technical level at how the xlsx documents are constructed, I've realised my xlsx-reader is very basic but it works. If you want I could also publish that code as well on the GitHub.

Developing a xlsx-reader and xlsx-writer are two separate beasts in their own-write. I think once I've got the xlsx-writer to a high level of robustness, I might re-visit the xlsx-reader and try and figure a way to merge the two projects together.

The original source code is written in the python language and I have permission from the author to translate it into ABL language.
https://github.com/jmcnamara/XlsxWriter

Knowing nothing much about python I'm finding it easy to read and translate into OpenEdge ABL, but it can be a little bit tricky to workout the data types because Python uses dynamic variables data types, a bit like javascript.

 

[Cringer]

Thanks Cecil. If you do share the reader I might have a look at how slow slow is. We currently read a lot of xlsx files. Tend to save them as csv and import them. That's all well and good, but you lose formatting information.

 

[Cecil]

Just a quick look at what the ABL-xlsx-writer can do.
In this example code I've created an .xlsx document with the formatted date time, aligned the text to the right and applied bold formatting. Also set the column width.

 

[Cecil]

Thanks Cecil. If you do share the reader I might have a look at how slow slow is. We currently read a lot of xlsx files. Tend to save them as csv and import them. That's all well and good, but you lose formatting information.

I'll get some code up on the GitHub.

 

[Cecil]

I'll get some code up on the GitHub.

My crude xlsx reader:
https://github.com/Jimbobnz/ABL-XLSX-Reader

There is some sample code in the readme file but you might need to make tweaks the location of your unzip executable and general propaths etc.

Essentially this code treats the .xlsx file as a .zip file and unpacks the contents to disk. Using the SAX-READER parse the XML documents to get the relevant data from the worksheet(s).

Note:

• Dates are stored as number of days since 1/1/1900 + 2 days.
• Time is stored as a fraction of 24 hours. 1 = 12:00 pm, 0.5 = 12:00 am 0.25, 6:00am
• Decimal values can be stored in a scientific notation 45E+2. Decimal values in excel goes to 15 decimal places, as you know openedge can only handle 10.
• String values a located in the sharedstring.xml file, as words are normalise for optimization.

 

[Rob Fitzpatrick]

I haven't looked at your writer code yet but I'm looking forward to it. I currently have a utility that writes temp-tables out to .csv files and I've been importing them manually into Excel, a major pain. I tried automating the process but got stuck because the Excel object model text import method requires a multi-dimensional array as a parameter, which ABL can't do. This approach might be just what I need, without having to produce the flat files as an interim step.

 

[Cecil]

I haven't looked at your writer code yet but I'm looking forward to it. I currently have a utility that writes temp-tables out to .csv files and I've been importing them manually into Excel, a major pain. I tried automating the process but got stuck because the Excel object model text import method requires a multi-dimensional array as a parameter, which ABL can't do. This approach might be just what I need, without having to produce the flat files as an interim step.

I thought about your current issue so I created quick-n-dirty method which will take the handle of a temp-table and write the contents to a worksheet. It even include the column headers. It takes about 15~17 seconds to generate the .xslx document. The temp-table included 871 records to with about 40 fields.

 

[Cecil]

Quick Update on where I am up to in the development:

• Cell formatting is almost completed:
  • Change font ('Depending what fonts are installed on Windows').
  • Font size, colour, strikethrough, subscript, bold, italics and underline
  • Text alignment
  • Text rotation
  • Cell patterns
  • Background and Foreground colour
  • Cell number formatting for dates and numbers
  • Wrapped Text
• Defined column widths (Automatic column width is a bit more complicated)
• Multiple worksheet handling with settable names.
• Cell Formulas i.e. '=SUM(A1:A8)'.
• Print & Paper setting available
  • Margin Widths
  • Paper Size ('A4, US Letter')
  • Page Orientation
• Print Headers & Footers
• Column & Row spanning (Merged Cells)

What I don't think I can do is Rich Cell Formating. This is a mix of different text formatting in a single cell. It's not impossible but just complicated.

Work in progress:

• Protection of Cells & Worksheets. I've now been able to replicate MS Excel's security hashing algorithm into the ABL so this should not be too hard to figure out to implement.
• Hidden Cells/Rows/Columns and Worksheets.

TODO:
Insert Images. (Does anybody know how the extract the image's dimensions for BMP, PNG & JPEG file formats using the ABL?)
Inset Charts.
Cell Borders.
URL Hyperlinks.
DOCUMENTATION, DOCUMENTATION, DOCUMENTATION.
More demo examples.
Universal ZIP compress which works seamlessly on Windows and Linux.

 

[joey.jeremiah]

I would know the answer to that question since I wrote them before


I know this will come off the wrong way but I sincerely do mean well.

I would recommend, writing tools for creating OpenOffice/LibreOffice spreadsheet and word processing files.

OpenOffice/LibreOffice file formats are also an XML in a ZIP (actually a JAR) container. They actually did it before Microsoft Office.

You see, the way all these OpenXML tools on the market create PDF files is by using Microsoft Office on Windows and OpenOffice/LibreOffice on Linux/UNIX to convert them to PDF in the background.

But OpenOffice/LibreOffice do not do a very good job of rendering Microsoft Office files and will most likely never will.

Plus there is a huge need to generate OpenOffice/LibreOffice files. For example: lots of governments now use OpenOffice/LibreOffice almost exclusively.

If you write that then you will have something, in Progress and outside of it.

I'll be happy to help then, even converting it to PHP, Pyhton etc. tools. I personally just don't see a point helping and spending time writing the same thing.


My question to you is, why spend a few years of your life working on something that has already been done plus documentation which is a whole other project by itself.

The DocxFactory project can do all these things now and it doesn't take 19 seconds to create a document, it takes 0.02 seconds (at least the .DOCX tools, the new/faster .XLSX will be ready in a few short months but the old .XLSX tool still takes 1.5 seconds for inserting 15,000 records).

It's not just writing something that works. It needs to be better or different in some way, no?

Just my 2 cents.

 

[Cecil]

Hi Joey.

I understand where you are coming from, so hurt feeling here. Hands down, I would admit the DOCXFactory is a superior tool and more powerful. The story of the ABL-xslx-writer project started as part of a bigger company project which I'm working on. Our clients demanded true MS Excel as the main form of data reporting. However there are multiple of combinations of the data & presentation being generated dynamically depending on the users access rules so I'm not sure if could create multiple Excel templates for every combination of report.

The current solution is having a mixing-up of HTML and MS Office meta data headers and stinking a .xls file extension on the end and this has been working for a few years now, sort of.
The problems with crappy HTML Excel files is the first you get a dummy error about a corrupted file format but excel is still capable of opening the file and the second problem (and this is the major one) is our clients are unable to import these reports into there SAP server.

So in summary I not trying to compete with DOCXFactory because would be just wasting my time. I'm just coming from a slightly different angle to achieve a similar result for generating Excel files and also at the same time make the code available to other Progress Developers. The main deference I can see from the DOCXFactory and the ABL-xlsx-writer is I don't need a temple file. I'll need to double check that as I think DOCXFactory does a .xlsx creator function.

From personal point of view whitest developing the ABL-xslx-writer I'm finally leaning about OOP which a bit of a new concept as I've tried many times to get into.

I always like developing code which is exclusivity written in the ABL/4GL for example the HASH-MAC function which has help many other developers. In the words of Adam Savage Rule Number 6: Share your methods and knowledge and don't make them a secret.

I would agree that developing a code library which could generate/manipulate OpenOffice/LiberOffice/StarOffice would be pretty awesome and I would imagine it could definitely help other OpenEdge developers to reach there goals, but it not in the scope of my project. Sorry.

I hope this project does not take 'years' to have a stable working version.

P.S. I'm still testing the DOCXFactory and studying the documentation . I'll PM with my finding.

 

[joey.jeremiah]

You're a classy guy!

I'm not sure if could create multiple Excel templates for every combination of report.

You can generate an .XLSX template from scratch.

You can pass it a temp-table, query, dataset etc. and it will generate an .XLSX templates and convert the Progress formats to Office formats.

Then later you can manually add charts, pivots to your template.

Have a look at the generating Excel files tutorial or run one of the create_template*.p samples.

Hi Joey.The current solution is having a mixing-up of So in summary I not trying to compete with DOCXFactory

No competition. DocxFactory is free and you can join.

But if you take part, you'll get credit

Share your methods and knowledge and don't make them a secret.

Agree 100% but there's also no need to do things twice.

Contact me privately if there's anything I can do.

I would agree that developing a code library which could generate/manipulate OpenOffice/LiberOffice/StarOffice would be pretty awesome and I would imagine it could definitely help other OpenEdge developers to reach there goals, but it not in the scope of my project. Sorry.

I will be starting work on the project soon and it would be great if you could join but I understand if you're busy.

All the best