exporting php data into excel in text data type

Question

I want to export my php web page to excel.

For that, I have found the following code which works but it has an issue.

Values in a column start with something like '00003421' in php but when it is exported in excel, it shows only '3421' in the cell. It ignores the zero values. Moreover, I want them in text data type.

How can I export the data in plain text format as it is (including zeroes)?

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=export.xls");
header("Content-Transfer-Encoding: BINARY");

It's tabular data with 4 columns and around 20,000 rows.

Ah, the automagic extension solution... so what are you actually echoing to output with these headers? Let me guess, either CSV or HTML — Mark Baker, Sep 24 '12 at 20:59
Im exporting a table like data with 4 columns and 20,000 rows — user1449596, Sep 24 '12 at 20:59
@user1449596 - so I'm guessing that answer means HTML... size is less important to help you than the format of your output — Mark Baker, Sep 24 '12 at 21:00
I'd suggest writing either a real BIFF or OfficeOpenXML format Excel file, or a CSV file (treating your formatted numbers as strings) — Mark Baker, Sep 24 '12 at 21:19
Love it - a bounty on this question - has the OP actually looked at the possibility and options for creating a real BIFF file? — Mark Baker, Sep 27 '12 at 13:45

score 8 · Accepted Answer · edited May 23 '17 at 12:14

A possible solution is to create a new value Binder method to override the PHPExcel method. (PHPExcel tries to guess the datatype we insert in the cells. So, following the advice of markBaker, I created this class to override this method:

class PHPExcel_Cell_MyValueBinder extends PHPExcel_Cell_DefaultValueBinder
implements PHPExcel_Cell_IValueBinder 
{ 
    public function bindValue(PHPExcel_Cell $cell, $value = null) { 
        // sanitize UTF-8 strings 
        if (is_string($value)) { 
            $value = PHPExcel_Shared_String::SanitizeUTF8($value); 
        } 

        // if it is a string and starts with 0, the value will be converted into string 
        if (is_string($value) && $value[0] == '0') { 
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 
            return true; 
        } 
        return parent::bindValue($cell, $value); 
    } 
}

this was a tip that markbaker recommended to me in this question.

score 2 · Answer 2 · answered Sep 27 '12 at 08:54

2

The 0 is just hide if your data type of the cell is not text, select all the cells in excel, and set the data type to text.

Or try add a single quote to every value in php, '00003421' to '\'00003421', excel will know that this is text data type if the number begin with a single quote.

answered Sep 27 '12 at 08:54

xdazz

158,678
38
247
274

The value in php is coming from sql server stored procedure and just executed in php using mssql_fetch_array Your first option is not working. It converst to text if I select the column and convert to text in excel, but it doesn't add the hidden zero yet. – user1449596 Sep 27 '12 at 09:20

score 2 · Answer 3 · answered Dec 01 '17 at 15:01

2

I have solved this by the below way.

You can try this

<?php
$number='00003421';
echo "<td>=\"$number\"</td>";
?>

answered Dec 01 '17 at 15:01

Siva

1,481
1
18
29

1

This did work. Thanks a ton! The key thing here is to use the equal to sign and the backslashes. In my case, I had to do this: `$str = "=\"$str\"";` – Devner May 29 '23 at 16:42

exporting php data into excel in text data type

3 Answers3