Categories > OpenTBS with XLSX >

more faster fill xlsx file with over 10000 row

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Drakon
Date: 2013-12-06
Time: 11:20

more faster fill xlsx file with over 10000 row

Hi
Then i use clsOpenTBS for xlsx file with over 10000 row. Metod Show work over 100 sec. I found slowly function MsExcel_ConvertToExplicit_Item and rebild it. Now its work less then second.
Code:
function MsExcel_ConvertToExplicit_Item(&$Txt, $Tag, $Att, $CellRow) {
        if ($Txt==='') return 0;
        $t = '<'.$Tag;
        $rpl = ' '.$Att.'="';
        $empty_nbr = 0;
        $item_num = 0;
        $rpl_nbr = 0;

        $i= (substr($Txt,0,strlen($t))==$t)?0:1;
        $data=preg_split("/".$t."/" , $Txt ,-1,PREG_SPLIT_NO_EMPTY);
        while (isset($data[$i])) {
            $item_num++;
            if ($data[$i][0] == '/') {   //  first string char after Tag
                $empty_nbr++;
            } else {
                $rpl_nbr++;
                // If it's a row => search for cells
                if ($CellRow===false ) {
                    $ref = $item_num;
                    $data[$i]=preg_replace_callback('/^(.*?)>(.*?)(<\/'.$Tag.'>)/',
                        function ($m) use ($item_num) {
                            if ($m[1][strlen($m[1])-1]!=='/' && strlen($m[2]) > 0 )
                                clsOpenTBS::MsExcel_ConvertToExplicit_Item($m[2], 'c', 'r', $item_num);
                            return  $m[1].'>'.$m[2].$m[3];
                        }, $data[$i]
                    );
                } else {
                    $ref =  $this->Misc_CellRef($item_num, $CellRow);
                }
                $data[$i]= $rpl.$ref.'"'.$data[$i];      // Add the attribute
                $empty_nbr = 0;
            }
            $data[$i]= $t.$data[$i]; // Add splited tag
            $i++;
        }
        $Txt=join('',$data);
    return $rpl_nbr;
}
enjoy  :))
PHP 5.5
PS for multi line( simbol '\n' within Text )  replace:
       $data[$i]=preg_replace_callback('/^(.*?)>(.*?)(<\/'.$Tag.'>)/',
on
      $data[$i]=preg_replace_callback('/([\S|\s]*?)>([\S|\s]*?)(<\/'.$Tag.'>)/m',
By: Skrol29
Date: 2013-12-13
Time: 03:15

Re: more faster fill xlsx file with over 10000 row

Hi Drakon,

Thank you very much fr sharing this.
I'll check that quite soon.
By: Toby
Date: 2014-06-25
Time: 18:37

Re: more faster fill xlsx file with over 10000 row

Hi Drakon,

Great patch - many thanks. I'm seeing fantastic performance improvements with this, both under PHP and HHVM. Only issue though is that in some situations, your code is generating spurious empty entities in the resulting XML. See the linked template (Test8.xlsx) and result after passing through OpenTBS (Test8-out-new.xlsx) for an example of what's happening. This only happens with your revised function and not with the original OpenTBS version.

Test8.xlsx:
https://dl.dropboxusercontent.com/u/13278590/Test8.xlsx

Test8-out-new.xlsx:
https://dl.dropboxusercontent.com/u/13278590/Test8-out-new.xlsx

In the generated XML (xl/worksheets/sheet1.xml), there is an extra <c/> tag at the beginning of each row generated when your function is in use. This shouldn't be there. For example:

With your function:
        <row r="2" spans="2:8">
            <c />
            <c r="B2" s="14" />
            <c r="C2" s="2" />
            <c r="D2" s="2" />
            <c r="E2" s="2" />
            <c r="F2" s="2" />
            <c r="G2" s="2" />
            <c r="H2" s="2" />
        </row>

With the original OpenTBS code:
        <row r="2" spans="2:8">
            <c r="B2" s="14" />
            <c r="C2" s="2" />
            <c r="D2" s="2" />
            <c r="E2" s="2" />
            <c r="F2" s="2" />
            <c r="G2" s="2" />
            <c r="H2" s="2" />
        </row>
Any ideas?

Thanks,
Toby
By: Skrol29
Date: 2014-09-25
Time: 01:58

Re: more faster fill xlsx file with over 10000 row

Hi,

The problem of large amount of row should be fixed with OpenTBS 1.9.2
The function for saving cells with explicit positioning is now 6 time faster.
This is also a new command to force OpenTBS to let cells with relative positioning which is quite faster.

http://sourceforge.net/p/tinybutstrong/code/HEAD/tree/trunk/plugins/opentbs/tbs_plugin_opentbs.php