Categories > OpenTBS with XLSX >

unable to merge xlsx

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Thomas
Date: 2011-10-06
Time: 17:03

unable to merge xlsx

Again the simplest things with opentbs seem to elude me. The xlsx file is getting created but the single merge I am testing with will not work. I see no errors and have tried the merge with debugging off. I would really like to get this working, any help would be very much appreciated.

Thanks, thomas

There is a single cell A1 with
[a.firstname;block=row]
I have tried without the block=row as well.

Here is my php
$tbs->LoadTemplate($drawings);
$tbs->PlugIn(OPENTBS_SELECT_SHEET, 1);
$tbs->MergeBlock('a', $data);    
$tbs->Show(OPENTBS_FILE,$tempdir);  

Here is the output from the debug session.
Array
(
    [0] => Array
        (
            [firstname] => Sandra
        )

)
* OPENTBS DEBUG MODE: if the star, (*) on the left before the word OPENTBS, is not the very first character of this page, then your
merged Document will be corrupted when you use the OPENTBS_DOWNLOAD option. If there is a PHP error message, then you have to fix it.
If they are blank spaces, line beaks, or other unexpected characters, then you have to check your code in order to avoid them.

------------------------------
INFORMATION
------------------------------
* OpenTBS version: 1.7.0
* TinyButStrong version: 3.7.0
* PHP version: 5.3.2
* Opened document: /var/www/htdocs/ePSM/upload/110_Sysco/boilerplate/EmployeeParticipation/epA.xlsx
* Activated features for document type: openxml/xlsx
* Deleted files in the archive: none
* Added files in the archive: none
* Modified files in the archive:
  - xl/workbook.xml
  - xl/worksheets/sheet2.xml
  - xl/worksheets/sheet1.xml

------------------------------
File merged with OpenTBS (XML reformated for debuging only): 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="4" lowestEdited="4" rupBuild="4505"/>
<workbookPr defaultThemeVersion="124226"/>
<bookViews>
  <workbookView xWindow="360" yWindow="15" windowWidth="18000" windowHeight="8265"/>
</bookViews>
<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>
<calcPr calcId="124519"/>
</workbook>
------------------------------
File merged with OpenTBS (XML reformated for debuging only): xl/worksheets/sheet2.xml
------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A1"/>
<sheetViews>
  <sheetView workbookViewId="0"/>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<sheetData/>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>
------------------------------
File merged with OpenTBS (XML reformated for debuging only): xl/worksheets/sheet1.xml
------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A1"/>
<sheetViews>
  <sheetView tabSelected="1" workbookViewId="0">
   <selection activeCell="A2" sqref="A2"/>
  </sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<cols>
  <col min="1" max="1" width="32.85546875" customWidth="1"/>
  <col min="2" max="2" width="29.85546875" customWidth="1"/>
</cols>
<sheetData>
  <row  spans="1:1">
   <c  t="s">
    <v>
     0
    </v>
   </c>
  </row>
</sheetData>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup orientation="portrait" verticalDpi="0" r:id="rId1"/>
</worksheet>
By: Skrol29
Date: 2011-10-06
Time: 23:21

Re: unable to merge xlsx

Hello Thomas,

Can also post the debug information with OPENTBS_DEBUG_INFO ?
By: Thomas
Date: 2011-10-07
Time: 00:51

Re: unable to merge xlsx

Yes, thanks

Array
(
    [0] => Array
        (
            [firstname] => Sandra
        )

)
* OPENTBS DEBUG MODE: if the star, (*) on the left before the word OPENTBS, is not the very first character of this page, then your
merged Document will be corrupted when you use the OPENTBS_DOWNLOAD option. If there is a PHP error message, then you have to fix it.
If they are blank spaces, line beaks, or other unexpected characters, then you have to check your code in order to avoid them.

------------------------------
INFORMATION
------------------------------
* OpenTBS version: 1.7.0
* TinyButStrong version: 3.7.0
* PHP version: 5.3.2
* Opened document: /var/www/htdocs/ePSM/upload/110_Sysco/boilerplate/EmployeeParticipation/epA.xlsx
* Activated features for document type: openxml/xlsx

Sheets in the Workbook:
-----------------------
  - id: 1, name: [Sheet1]
  - id: 2, name: [Sheet2]
  - id: 3, name: [Sheet3]

Charts inside the document:
---------------------------
  - (none)
By: Skrol29
Date: 2011-10-07
Time: 03:01

Re: unable to merge xlsx

Hello Thomas,

This was a bug, it is now fixed with the new version OpenTBS 1.7.1 which is freshly released and available for download.

The bug was: TBS is not merging the first non-empty cell of an Excel Worksheet if it contains a TBS field.

I very thank you for sharing this problem.
By: Thomas
Date: 2011-10-07
Time: 13:41

Re: unable to merge xlsx

Beautiful, thanks so much Skrol29. Another couple of questions. I am using this to iterate through a number of documents and what is happening is my php is timing out while the tbs_plugin_opentbs.php script is running. Now of course I can set my php to allow it to run longer but what is confusing me is that I am destroying the class at every iteration; I am unsure as to why the memory is not being returned to the system. Any thoughts?

The other thing I am trying to accomplish is table where col1 rowspan adjusts to the number of rows in col2 dynamically. I looked at the subblocks section in the documentation but it is unclear whether what I want to do is possible based on that as all the examples are single column.

Thanks Thomas.
By: Skrol29
Date: 2011-10-07
Time: 15:38

Re: unable to merge xlsx

> I am unsure as to why the memory is not being returned to the system. Any thoughts?

The memory should be returned. How do you know it is not?

> The other thing I am trying to accomplish is table where col1 rowspan adjusts to the number of rows in col2 dynamically.

We can try a trick to accomplish this.
In the worksheet, the span of cells overs rows is define like this (cells A1, A2, A3 in this example)
<mergeCells count="1">
  <mergeCell ref="A1:A3"/>
</mergeCells>

So you can define a simple span, and redefine it after the merge using a str_replace(). Example:
PHP:
$nbr = $TBS->MergeBlock('a', $data); // return the number of rows
if ($nbr>0) $TBS->Source = str_replace('<mergeCell ref="A1:A3"', '<mergeCell ref="A1:A'.$nbr.'"', $TBS->Source);
By: Thomas
Date: 2011-10-07
Time: 17:38

Re: unable to merge xlsx

Beautiful, thanks so much Skrol29. Another couple of questions. I am using this to iterate through a number of documents and what is happening is my php is timing out while the tbs_plugin_opentbs.php script is running. Now of course I can set my php to allow it to run longer but what is confusing me is that I am destroying the class at every iteration; I am unsure as to why the memory is not being returned to the system. Any thoughts?

The other thing I am trying to accomplish is table where col1 rowspan adjusts to the number of rows in col2 dynamically. I looked at the subblocks section in the documentation but it is unclear whether what I want to do is possible based on that as all the examples are single column.

Thanks Thomas.