Yes, I found that the XML 2003 Spreadsheet format is quite limiting. I
believe that you can't include images in a spreadsheet with this format.
I was curious about multiple worksheets, so I created a spreadsheet
(workbook) with 2 sheets and saved it in the XML format - it did save it
with both worksheets - I've attached a pic of the structure of the XML
that shows this. It seems to me that if you want several CGIDEV2
sections in your template. If you use what Excel creates, you could have
a general header section like this -
<?mso-application progid="Excel.Sheet"?>
<Workbook>
<DocumentProperties>
<Author>vern</Author>
<LastAuthor>vern</LastAuthor>
<Created>2020-10-25T17:57:49Z</Created>
<Version>16.00</Version>
</DocumentProperties>
<OfficeDocumentSettings>
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook>
<WindowHeight>7350</WindowHeight>
<WindowWidth>19200</WindowWidth>
<WindowTopX>32767</WindowTopX>
<WindowTopY>32767</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11"
ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
Then you might want a worksheet header like this -
<Worksheet ss:Name="Sheet1">
Then a table header like his -
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1"
x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
Then a row section with the cells you need - you would write this out
for as many rows as you have - this example has only one kind of row, no
subtotals and all that - I use a separate section for that kind of thing -
<Row ss:AutoFitHeight="0">
<Cell>
<Data ss:Type="String">This is sheet 1</Data>
</Cell>
</Row>
Where it says "This is sheet 1" you would put a CGIDEV2 variable
reference. You'd write this out for each row.
After the rows are done, you'd close the table -
</Table>
Then there is a section for worksheet options - maybe optional, might be
worth leaving it out and see if the "little man behind the curtain"
handles it anyhow -
<WorksheetOptions>
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
Finally close up the worksheet -
</Worksheet>
Then there is the footer of the entire workbook -
</Workbook>
It might be worth saying that CGIDEV2 is not an Excel-generating tool -
it is a tool for generating text files in the IFS. Its origins are at
IBM, for creating HTML files as part of a way to simplify the use of the
CGI APIs. There's not an XLSX version specifically.
It can be used for creating any text files, doing a search/replace
operation on marked tags in template files. Templates are, in many ways,
similar to DSPFs and PRTFs. CGIDEV2 sections are like record formats,
CGIDEV2 variables are like fields.
I don't know if multiple sheets were always part of the 2003 format, you
should create a 2-sheet workbook in an older version of Excel and save
it in this format and see what the contents of the file are. I recommend
using a tool called XML Notepad - it's from Microsoft and is free, has
been very useful. RDi also has an XML perspective that I have found to
be more difficult to master.
As for XLSX files - that is the extension used for the current format of
Excel. An XLSX file is a PK-zipped archive of several XML (and other
types of) files, some of which are the individual sheets that are
described in sheet1.xml, etc., files. I use CGIDEV2 to generate the
various parts (XML files) that have variable content, then use a tool to
compress the entire file set into an XLSX file.
I've a feeling this is Too-Much-Information! But maybe it will be useful
for you - I hope so!
Regards
Vern
On 10/24/2020 9:42 PM, Arnie Flangehead wrote:
Well, there is one thing I haven't been able to do with the current
method. If it can be done with the XLXS version of CGIDEV (if there be
such a thing), then it would probably warrant my levering myself out
of my newly comfortable xls zone.
The instructions I have for the xls method explicitly state that there
can be only one sheet in the workbook, named the same as the file.
If the scenario is that two separate companies, let's call them
Paymore Plastics and Consolidated Solids, are hosted on the same
machine under the same ERP programs, but each with its own database
(identical file structures), then it would be useful to be able to
write two sheets when generating a report: one for "Paymore" and one
for "Consolidated".
Some people who work for the holding company have oversight over both
companies. At present their requirements are met either by running the
same program twice, once over each database, and emailing them two
files with the company name in the subject (.csv files, but .xls going
forward) and letting them stitch them together, or by combining the
data in the program, having one sheet and putting a new column at the
start to identify the company.
The data is always distinct - no summing or merging is done across
companies (at least not by me).
So, if there's a way to keep the workbook open and write first one
sheet then the next, or if there's a way to close it after the first
company then re-open it and create a new sheet without losing the
existing one, then that would be interesting to me.
On 10/25/20, Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:
If you have a version of Excel that is that out of date you might be better
off using Libre Office or one of the other OS Office alternatives. With
this version you're going to find out some day in the not too distant future
that you can't read other people's spreadsheets and they can't read yours.
Just saying' - It is not like it will cost you anything.
midrange.com
