Want to add worksheet in excell using Excel binary format

Question

I have this following code snippet taken from http://network.convergenceservices.in/forum/5-components/2191-export-excel-a-new-way.html

function xlsBOF() {
            echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
            return;
        }
        function xlsEOF() {
            echo pack("ss", 0x0A, 0x00);
            return;
        }
        function xlsWriteNumber($Row, $Col, $Value) {
            echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
            echo pack("d", $Value);
            return;
        }
        function xlsWriteLabel($Row, $Col, $Value ) {
            $L = strlen($Value);
            echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
            echo $Value;
            return;
        }


    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=vandetailslist.xls ");
    header("Content-Transfer-Encoding: binary");

    xlsBOF();

    xlsWriteLabel(0,0,"Column 1");
    xlsWriteLabel(0,1,"Column 2");
    xlsWriteLabel(0,2,"Column 3");
    xlsWriteLabel(0,3,"Column 4");
    $xlsRow = 1;

    foreach($somearray as $value)
    {
        xlsWriteNumber($xlsRow,0,"{Column Value 1}");
        xlsWriteLabel($xlsRow,1,"{Column Value 2}");
        xlsWriteLabel($xlsRow,2,"{Column Value 3}");
        xlsWriteLabel($xlsRow,3,"{Column Value 4}");
        $xlsRow++;

    }
    xlsEOF();
    exit;

the problem is i want to add only 10 records per sheet and if the data is more i want to add a new sheet and start adding rows there. I am not able to find a way to add a new sheet. Any help will be appreciated.

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

There isn't a simple answer, because you need a lot more code, and a good understanding of OLE2 and the structure of an Excel BIFF8 file... too much to provide a quick answer here.

You need to create a worksheet stream for each worksheet: I suggest you use one of the existing PHP libraries for working with spreadsheets such as PHPExcel or one of those writers listed here

edited May 23 '17 at 12:04

Community

1
1

answered Feb 18 '13 at 07:32

Mark Baker

209,507
32
346
385

As a followup question if i use xlsWriteLabel and write a string which is more than 255 char in length the excel is showing error. Is this a limitation? – Jeeva Oct 17 '13 at 11:38
Yes it is a limitation: see my answer to [this question](http://stackoverflow.com/questions/12676373/how-to-increase-amount-of-data-an-excel-cell-can-hold-using-php) – Mark Baker Oct 17 '13 at 11:41

Want to add worksheet in excell using Excel binary format

1 Answers1