PHP CSV excel not show 0 before numbers

Question

I'm writing data from db to excel, and have one problem, if number have first symbol "0" excel not show. for example numer 0777 excel show 777, there is programming problem or excel ?

You will need to make sure the field is set to be a string and not an integer — John Conde
– John Conde, Commented Apr 30, 2013 at 13:14
I don't know at the moment. Which library are you using? will have a look. — hek2mgl
– hek2mgl, Commented Apr 30, 2013 at 13:17
excel does not catch start value 023 in int and convert to the 23 so make a string on exporting data to or create rule for 0 — Rakesh Sharma
– Rakesh Sharma, Commented Apr 30, 2013 at 13:22

hek2mgl · Accepted Answer · 2013-04-30 14:02:07Z

2

Here comes a basic example how to set the number style with PHPExcel. In Excel the values will be threatened as numbers 777 but will be formatted with a leading zero 0770 .

// create a new document
$xls = new PHPExcel();

// get the first worksheet
$sheet = $xls->getSheet(0);

// write example values and set number styles of the cells
// 0000 means that values lower than 1000 will be prepended by a 0
$sheet->getCell('A1')->setValue('0777');
$sheet->getStyle('A1')->getNumberFormat()
   ->setFormatCode('0000');

$sheet->getCell('A2')->setValue('0440');
$sheet->getStyle('A2')->getNumberFormat()
   ->setFormatCode('0000');

// write file in Excel2007 format
$writer = new PHPExcel_Writer_Excel2007($xls);
$writer->save('test.xlsx');

edited Apr 30, 2013 at 14:02

answered Apr 30, 2013 at 13:51

hek2mgl

159k31 gold badges263 silver badges280 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rakesh Sharma · Accepted Answer · 2013-04-30 13:24:52Z

my code on export clean data having some rules for strings

ob_end_clean();
  function cleanData(&$str) { 
     if($str == 't') 
       $str = 'TRUE'; 
     if($str == 'f') 
       $str = 'FALSE'; 
     if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) { 
       $str = "'$str"; 
     } 
     if(strstr($str, '"')) 
     $str = '"' . str_replace('"', '""', $str) . '"'; 
  } 
  // filename for download 
  $filename = date("Y-m-d").".csv";
  header('Content-Type: text/csv');
  header("Content-Disposition: attachment; filename=\"$filename\""); 
  $out = fopen("php://output", 'w'); 
  $flag = false;
   //$result = $orderDetails; 
   foreach($details as $row) {
     if(!$flag) { 
       // display field/column names as first row 
       fputcsv($out, array_keys($row), ',', '"');
       $flag = true; 
     } 
     array_walk($row, 'cleanData');
     fputcsv($out, array_values($row), ',', '"'); 
   } 
    fclose($out); 
    exit();

Collectives™ on Stack Overflow

PHP CSV excel not show 0 before numbers

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related