Create an Excel file in Linux environment

[Jim Morton]

Is it possible to create a .xlsx file (not .csv) in a Linux environment only. There is no access to Windows or Excel or even a machine running those. First choice would be to use only pxplus, next best would be to look for some java that might have that capability.

Just wondering if anyone else has tackled this challenge.

[Loren Doornek]

I've done exactly what you ask, using PXPlus only, on a Linux server, because we wanted a solution with no external dependencies.  It was quite a bit of work, but definitely possible if you're familiar with using XML in PXPlus.  The 'ah-ha' moment for me was when I discovered that an XLSX file is actually just a ZIP file - You can just change the extension to .ZIP, then unzip it to see all of the included files and directory structure.  Once you see the structure of the zip file, you'll realize that it's just a matter of building all of the individual directories/files and the links between them.  The routine I have does a fairly basic XLSX file for outputting reports to Excel, so it will set the cell type (numeric, text, date, image, etc.), cell width, font, bold, include images, and a few other minor things that I've long since forgotten.  It's possible to add other features like formulas, but our clients don't really have a need for that - they just want to be able to see the reports in a true XLSX format, and then they can massage the data however they choose using a full-featured Excel editor.  The spec that I used is a PDF document named "c061750_ISO_IEC_29500-1_2012.pdf", which you can find on the Web by searching for that filename.  That's an older version based on Office 2008, I believe, but we are still using it extensively and our clients love it.  There are newer versions of the spec, but there aren't significant changes for the basic stuff that we are using, so I haven't updated our routines.  If you can't find a copy of the spec, let me know and I can email it to you.  The spec includes all office documents (Word files, Excel files, etc), and Excel documents are referred to as "SpreadsheetML".  The documentation is huge, but necessary for some pre-defined values (like the values necessary to define a cell type).  But, most of the coding I did in PXPlus was done by opening the various files/directories in the ZIP file and re-creating their structure using PXPlus programs.

[Jim Morton]

Thank you Loren!

[Mike King]

And since PxPlus itself can read/write ZIP files all you really need to do is open the ".xlsx" file directly and treat it as a Keyed file to READ/WRITE the records directly where the keys are the embedded file pathnames.

For example:

->open (1) "test.xlsx
->print key(1)
[Content_Types].xml
->read record (1) r$
->?r$
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/
vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/
workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl
/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override Part
Name="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/><Override PartName="/xl/styl
es.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/sharedStr
ings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/><Override PartName="/doc
Props/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/docProps/app.xml
" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/></Types>
->print key(1)
_rels/.rels
->read record (1) R$
->print r$
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schema
s.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2"
Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relat
ionship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook
.xml"/></Relationships>

Given this you should be able to parse any XLSX file and potentially create new ones.

[Loren Doornek]

Mike, that's interesting!  I didn't know about this ZIP read/write, and that could be very useful for my XLSX routine.  Currently, I need to create each XML file, then use a Linux utility to zip the directory and subdirectories.  But, that relies on an external Linux zip app, and some clients don't have a zip app installed, so the creation fails.  Using a PXPlus internal zip utility would resolve that issue!

Thanks for this info, Mike :-)

[Mike King]

You can find more information about using zip files with PxPlus at

https://manual.pvxplus.com/page/PxPlus%20User%20Guide/File%20Handling/Processing%20Data%20Files/Accessing%20ZIP%20Files.htm

Support for Zip files was added in 2014.

[Devon Austen]

Another option would be to use the PxPlus Google Sheet object. As long as the Linux machine has access to the internet you can setup a google account with API access and then write a program that uses that google account to generate a google sheet (spreadsheet) and then export/download it as a xlsx file.

https://manual.pvxplus.com/?Google%20Workspace%20Objects/App%20Setup.htm
https://manual.pvxplus.com/?Google%20Workspace%20Objects/Google%20Sheets%20Object.htm