Export to ExCel shows some columns empty

Question

I am trying to export the data from PHP & MySql. I am getting Empty value for Fields which are having huge data(around 1000 chars). Except those fields everything is working fine. This is the code which i am using right now. Please check once and let me know if any modifications. I searched in google, so many said its cache/memory problem.

        <?php

    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;
    } 



        $sel_sql=mysql_query("select desc from table");
        $myFile = date("m-d-Y").'_users.xls';


        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/vnd.ms-excel");
        header("Content-Type: application/download");;
        header("Content-Disposition: attachment;filename=".$myFile); 
        header("Content-Transfer-Encoding: binary ");



        // XLS Data Cell

                    xlsBOF();
                    xlsWriteLabel(0,1,"desc");
                    $xlsRow = 1;
                    while(list($desc)=mysql_fetch_row($sel_sql)) {  
                        xlsWriteLabel($xlsRow,1,"$desc");
                        $xlsRow++;
                    }
           xlsEOF();


    ?>

No, those are the headers that you're setting; how are you actually exporting the data? as a csv file, html markup, and actual BIFF or OfficeOpenXML format file? — Mark Baker, Mar 31 '14 at 20:30
Note that only one Content-Type heading will be sent, the last one in your list of four; so you can eliminate three redundant lines of code — Mark Baker, Mar 31 '14 at 20:47

Mark Baker · Answer 1 · 2014-03-31T21:03:37.607

0

Assuming you're using an xls file output: in a BIFF format file, a cell is limited to 32,767 characters; but only 1,024 characters are displayed in the cell. All 32,767 characters are display in the formula bar.

EDIT

Reading the code that you've actually posted:

Excel has limits on the amount of data a cell can hold: for Excel BIFF 8 files, that limit is 32,767 characters. However, for long strings, this data is maintained in the BIFF file across several blocks with continuation records, For BIFF 5 files (Excel 95) the limit is 2084 bytes per block; in BIFF 8 files (Excel 97 and above) the limit is 8228 bytes. Records that are longer than these limits must be split up into CONTINUE blocks.

This relatively simplistic writer isn't written to handle splitting the record into multiple continuation records: it doesn't even use the BIFF 8 shared string table, or indicate what BIFF version it is writing (which means Excel will open it using lowest common denominator parameters). It simply tries to store the entire contents of the cell into a standard label block. To fix this, you'd need to fix your code to handle splitting the string values with continuation blocks (via a shared string table); or switch to using a library that does handle splitting the shared strings across multiple blocks already.

edited Mar 31 '14 at 21:03

answered Mar 31 '14 at 20:36

Mark Baker

209,507
32
346
385

Thanks for your response. But I am getting Empty result for those columns(for small data getting correct value). – Kishore Mar 31 '14 at 20:41
Then you're going to have to tell me which Excel writing library you're using – Mark Baker Mar 31 '14 at 20:43
Sure. Please check the complete code at main Post. Just now I updated. – Kishore Mar 31 '14 at 20:51
Hi Babker, can you please provide me some reference for working libraries? – Kishore Apr 01 '14 at 15:52
In addition to my own [PHPExcel library](https://github.com/PHPOffice/PHPExcel) (also available from [codeplex](http://phpexcel.codeplex.com/)), there's a comprehensive list [here](http://stackoverflow.com/questions/3930975/alternative-for-php-excel/3931142#3931142) – Mark Baker Apr 01 '14 at 16:27
I tried with PHP Excel Library. Its working great. Thank you soo much for your cool response. – Kishore Apr 01 '14 at 19:35

Export to ExCel shows some columns empty

1 Answers1