Home > Blockchain >  PHP Spreadsheet leading zero in cell not working
PHP Spreadsheet leading zero in cell not working

Time:10-12

I copied this code from PhpSpreadsheet's documentation and it does not work. This example can be found on this page:
enter image description here


My code:

$sheet->getCell('A2')->setValue(19);
$sheet->getStyle('A2')->getNumberFormat()->setFormatCode('0000'); 
// will show as 0019 in Excel

But its not working and this is the result:

enter image description here

The expected value of cell $A2 should be 0019 but it shows 19.

Any idea on how to fix this?

CodePudding user response:

Dude,

I Tried and it's working for me.

Let me know it helps you.

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

$spreadsheet->getActiveSheet()->setCellValueExplicit(
    'A2',
    "01513789642",
    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);


// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('php://output');
exit;

CodePudding user response:

Maybe you use a CSV format try to use Xlsx, I think styling in CSV is not working.

I think this is already issued and there's no solution yet. I'm also looking for the solution but the solution i only found is not use CSV.

https://github.com/PHPOffice/PhpSpreadsheet/issues/2230

CodePudding user response:

You can try this:

$num = 19;
$str = substr('0000' . $num,-4);
$sheet->getCell('A2')->setValue($str);  

Can you read the cell format?

I have never used PhpSpreadsheet, but I would think it would be something like:

echo $sheet->getActiveSheet()->getStyle('A2');

You can try:

$sheet->getCell('A2')->setValueExplicit("'0019",\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
  • Related