Categories > OpenTBS with XLSX >

XLSX: problem with ExtInfo['main']

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: fand
Date: 2012-10-09
Time: 10:54

XLSX: problem with ExtInfo['main']

Hello and thanks for this great plugin.

I have tracked down a problem with a XSLX template to the ExtInfo['main'] variable.

The XSLX template was left untouched after the merge and I found out it was because the default selected worksheet wasn't the first in the document.
It was because ExtInfo['main'] was set by:
$this->OpenXML_MapGetMain('spreadsheetml.worksheet+xml', 'xl/worksheets/sheet1.xml');

But in the documents I have the spreadsheetml.worksheet+xml list isn't sorted, and so this code would actually select a random worksheet.

...

For example here is this list for the demo_ms_excel.xslx file as found in [Content_Types].xml:
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/worksheets/sheet4.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>

But here it is after I opened the file in Excel and saved it without any modifications:
<Override PartName="/xl/worksheets/sheet4.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>

So the demo code provided will merge the 4th sheet and let the 1st one untouched.


I also had the same problem with an empty (apart from some [onshow..version] tags in the first sheet) file created with Gnumeric and saved as "MS Excel 2007"; only the empty 3rd sheet is processed by default.

Here is the relevant part from [Content_Types].xml:
  <Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>

And from workbook.xml:
  <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
    <sheet name="Sheet2" sheetId="2" r:id="rId2"/>
    <sheet name="Sheet3" sheetId="3" r:id="rId3"/>
  </sheets>


...

So I guess we need to explicitly select a worksheet by name or by id to have the merge working reliably...

But couldn't ExtInfo['main'] be set to the first sheet in the more accurate "MsExcel_SheetsById" or "MsExcel_SheetsByName" arrays ?
Theses are parsed from xl/workbook.xml which seems to hold the actual sheet order as displayed in Excel.

Otherwise, is there a way to access the MsExcel_SheetsById array from the calling script, to be able to manually select the actual first sheet from the main code ?

Thanks again for the good work.
By: Skrol29
Date: 2012-10-09
Time: 16:13

Re: XLSX: problem with ExtInfo['main']

Hi Fand,

You can explicitly select a worksheet using
$TBS->PlugIn(OPENTBS_SELECT_SHEET, 1);
or
$TBS->PlugIn(OPENTBS_SELECT_SHEET, "The worksheet name");

I agree it would be nicer to load the first visible worksheet instead of the first numbered worksheet.
But it brings other complications :
- When you load a worksheet by its number, is it the visual number or the id number?
- If it's the visual number, then how to load an invisible sheets.
- If it's the id number, then it's confusing with first loaded sheet.
By: fand
Date: 2012-10-11
Time: 09:40

Re: XLSX: problem with ExtInfo['main']

Thanks for the reply Skrol29.

Reading the complications you mention I agree that changing the default behavior would be tricky to say the least...

My specific need was to select the first visible sheet because I'm trying to build a generic merging service, requiring minimum configuration to setup a new merge.
The idea was to have a "default behavior" that would merge the first sheet without needing to specify its name or id.
So what I'd need is a way to read the MsExcel_Sheets list from the calling script, in order to select the first visible one (or cycle through each) using OPENTBS_SELECT_SHEET.

In the meantime I modified the plugin Ext_PrepareInfo method to setup the "main" xlsx sheet like this:
$this->ExtInfo = $i;
$this->TbsCurrIdx = false;
$this->MsExcel_SheetInit();
if (isset($this->MsExcel_Sheets[0]))
    $i['main'] = $this->MsExcel_Sheets[0]->xlsxTarget;
else
    $i['main'] = $this->OpenXML_MapGetMain('spreadsheetml.worksheet+xml', 'xl/worksheets/sheet1.xml');

Thanks again.
By: Skrol29
Date: 2012-10-12
Time: 00:33

Re: XLSX: problem with ExtInfo['main']

Hi Fand,

This can be quite easy with a small code.
Order and visibility of sheets are saved in the sub-file named  "xl/workbook.xml".
Here is a synopsis of "xl/workbook.xml":
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <fileVersion appName="xl" lastEdited="5" lowestEdited="4" rupBuild="9303"/>
  <workbookPr filterPrivacy="1" defaultThemeVersion="124226"/>
  <bookViews>
    <workbookView xWindow="240" yWindow="105" windowWidth="14805" windowHeight="8010" firstSheet="1" activeTab="1"/>
  </bookViews>
  <sheets>
    <sheet name="my sheet 3" sheetId="3" state="hidden" r:id="rId1"/>
    <sheet name="my sheet 2" sheetId="2" r:id="rId2"/>
    <sheet name="my sheet 1" sheetId="1" r:id="rId3"/>
  </sheets>
  <calcPr calcId="122211"/>
</workbook>

So you can load this sub-file with a $TBS->LoadTemplate('#xl/workbook.xml'), and read its content using $TBS->Source.
Then a small code of yours can analyses what is the first visible sheet.
By: Anonymous
Date: 2012-10-12
Time: 15:24

Re: XLSX: problem with ExtInfo['main']

Hi, thanks for the answer; I think I'll do it this way to avoid modifying the plugin...