Question Convert Excel Format to Progress Format

[Cringer]

Has anyone done any work to convert Excel formats to an equivalent Progress format? I'm currently working through some examples, but I don't really want to have to reinvent the wheel, so if there's some sort of open source code I could make use of it would be much appreciated.

Progress 11.2 on Win7.

 

[Rob Fitzpatrick]

Are you talking about importing data, i.e. reading tabular data from a contiguous region of a worksheet and importing it into a temp-table?

Or do you mean selecting a one-cell Worksheet.Range object, reading a property like Range.NumberFormat, and mapping it to an ABL display format (and repeating for other cells)?

 

[GregTomkins]

We are using an Apache Library http://poi.apache.org/ to convert P4GL temp-tables to Excel server-side ... which I realize is the opposite direction to what you intend, but it might be a helpful starting point. I didn't actually work on this myself so I don't know the details. We also in the past had a lot of success with using COM handles to manipulate Excel from a P4GL desktop client, but we're moving away from that now.

You are likely aware but in case not, the newer Excels are XML compatible which probably makes the whole thing a lot more attainable than trying to decipher the proprietary XML formats which I believe were extremely complicated. But IIRC there are limitations eg. the XML doesn't expose any VBA-type code inside the spreadsheet.

 

[GregTomkins]

I should add, that COM handle thing is possibly one of the most successful snips of code I ever wrote ... it is only a few dozen lines, it handles any temp-table we throw at it, it never has problems, everyone uses it and loves it. This is a reflection of the brilliance of P4GL's COM interface and the richness of the Excel object model, not anything I can really take credit for

 

[Stefan]

The com-handle thing does have serious performance issues with larger sets of data. We switched to creating Excel 2003 XML files (first with the DOM parser, later on with the SAX parser) which are dead easy to reverse engineer and offer all the richness required (correct data types, fonting, alignment and tabs).

 

[Cringer]

reading a property like Range.NumberFormat, and mapping it to an ABL display format (and repeating for other cells)?

This.
Sorry it wasn't clearer.

 

[Cringer]

You are likely aware but in case not, the newer Excels are XML compatible which probably makes the whole thing a lot more attainable than trying to decipher the proprietary XML formats which I believe were extremely complicated. But IIRC there are limitations eg. the XML doesn't expose any VBA-type code inside the spreadsheet.

Yeah I realise they're XML compatible (or at least I always the x in xlsx had something to do with XML), but is it possible to expose the data in xml format easily? We receive a lot of data in Excel format from suppliers (invoices etc) that we have to then parse to get into our system. It's a proper pain to do and very slow. If we know the data well enough we can use the API to save the sheet as a csv and pull it in over a text stream, but that has some very serious limitations (particularly for very large numbers and numbers stored as characters with leading 0's). Our users are typically relatively low-skilled when it comes to computing knowledge and therefore we get our Progress applications to do as much of the leg-work as possible. It (hopefully) rules out stupid user syndrome.

 

[Rob Fitzpatrick]

I don't know if this will help you, but .xlsx is a completely different format from the binary .xls. It's just a zip file containing several .xml files (and other stuff, depending on what's in the workbook). You can see this if you take a version 2007 or later .xlsx and rename the extension to .zip, then open it in your zip-compatible application of choice. Maybe that resulting XML is something you can consume with the ABL, so you have data and formatting.

The Office Open XML file formats are specified under ECMA-376 and ISO/IEC 29500:2008.

 

[Cringer]

Now that's really useful info. Thanks Rob.

 

[GregTomkins]

We do slightly more clever stuff to export Excel, but on the import side, which is way less important to us, we generally fall back to CSV, as sad as that is, for the reasons you mention and more.

I looked at the Excel XML a few years ago and it seemed to take the inherent verboseness of XML and the inherent complexity of their binary format and melded it into a gargantuan behemoth of tag soup with a few bytes of real data buried in there, reliably extractable only with many hours of parser work and sorting through special cases. And it still didn't handle some things that the binary format did.

Don't get me wrong, XML is great and Excel is great and .xlsx is a move in the right direction. Just don't necessarily expect a nice clean chunk of XML to work with. Good luck!

 

[Cringer]

Thanks Greg. It's been put down as one of those "Friday Afternoon Jobs" by my boss. I.e. there's no reason to reinvent the wheel when there's time critical stuff to do. So for now I've got to either use the API (slow) or convert to csv (quicker, but can lose very important information in the process). Hmmmm