I’m using a PHP script to generate an excel CSV file from a result-set query. All works fine but when I read my excel file, I can not display leading zeros.
This is my code:
$rows = $this->Query($sql); $filename = "/www/zendsvr/htdocs/Project/public/report.xls"; $realPath = realpath( $filename ); $filename = realpath( $filename ); $handle = fopen( $filename, "w" ); $finalData = array(); for( $i = 0; $i < count( $rows ); $i++ ) { $finalData[] = array( utf8_decode( $rows[$i]->CODE ) ); } foreach ( $finalData AS $finalRow ) { fputcsv( $handle, $finalRow, "t" ); } fclose( $handle );
If I make a var_dump()
of $finalData[]
I see the correct value, for example ‘000198’, ‘000199’, ‘000200’ but the same value in my XLS file is 198,199,200
How can I also display leading zeros in the XLS file?
Advertisement
Answer
Your data is saved correctly, it is Excel who is trying to be smart.
Use the Excel import data wizard instead of opening the file directly (rename the .csv file to .txt if necessary). On the import wizard, choose the data type of the column as “text” instead of “general”: