Save Output as an Excel file.

I need to save my output as a file that can be opened in Excel. I am already saving output as CSV, but the latest version of Excel manipulates the inbound data in a way that is not desirable.

We are on QAD2017EE, cloud-based, with the program running as a batch program on RHEL Linux. The file is generated and emailed to the customer. It needs to be hands off. So the problem with Excel manipulating the CSV file data has to be solved.

The problem that is occurring is that the customer part column has preceding zeros. I have always been to resolve this by wrapping the output column in "~"". This works. You can see that the column values are text, but Excel does not care and strips the zeroes.

So...that is why I am trying to convert my CSV file to an XLS file so that Excel keeps its conversion opinions to itself. LOL

Help!
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Some options I am aware of, that don't involve CSV files and their issues:
  • It is possible to programmatically build an Excel workbook from ABL via COM automation though it has some significant drawbacks. It requires an Excel COM automation server so it is Windows-only. It is a bit buggy and challenging to debug, in my experience. And it is very slow.
  • In Excel 2003, Microsoft introduced SpreadsheetML, a specification for building a workbook from XML.
  • In Excel 2007, if I remember correctly, Microsoft introduced the .xlsx file format and made it the Excel default for new workbooks. It is a collection of XML and other assets in a zip package. It is part of a larger initiative called Office Open XML, published as an open standard (ECMA-376 and ISO/IEC 29500:2008).
With either of the latter two options, you could programmatically produce XML and turn it into the workbook you want (which I'm sure is easier said than done :)). I believe this was implemented in the DocxFactory project though I don't know whether it is still being maintained. Hope this helps.
 

Stefan

Well-Known Member
Once you realize that an xlsx is just a zipped collection of xml files then you know that that's what you want to be creating. A hello world version can be cobbled together quite quickly by just stripping out everything you can from a very basic sheet and kicks the hell out of csv.

The only more annoying issue we had (years ago) was getting zip to work nicely on both Windows & Linux.
 

Osborne

Active Member
With either of the latter two options, you could programmatically produce XML and turn it into the workbook you want (which I'm sure is easier said than done :)). I believe this was implemented in the DocxFactory project though I don't know whether it is still being maintained.
Yes, DocxFactory was great for this. I think it is still being maintained but is now called AKIOMA.Docs and is probably now a paid for solution:
 

Cringer

ProgressTalk.com Moderator
Staff member
I think the leading 0's can actually be solved by outputting the data as ="0001234". But it's a horrible cludge and if anyone wants ot do any manipulation of the data later it's horrible.
 

Stefan

Well-Known Member
I think the leading 0's can actually be solved by outputting the data as ="0001234". But it's a horrible cludge and if anyone wants ot do any manipulation of the data later it's horrible.
Beware that if you are not restricting exporting leading equals signs that you may be exposing your users to CSV-injection attacks.
 
Top