Search code examples
phpexcelcsvphpexcel

PHP: Convert Excel to CSV, but with actual cell value instead of displayed value

I have an Excel file, which I'd like to convert to CSV. My Excel file has some of its cells formatted, such that 1234.56 is displayed enter image description here.

Problem is, when I convert the Excel file to CSV, using PHPSpreadSheet:

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

$reader = new Xlsx();
$spreadsheet = $reader->load('path_to_excel_file.xlsx');

$writer = (new Csv($spreadsheet))
    ->setEnclosure('')
    ->setLineEnding("\n")
    ->setDelimiter(';');
$writer->setSheetIndex(0);
$writer->save('path_to_csv.csv');

The values in the CSV file are stored as 1234.56 € (spaces and symbols included), while I want them to be stored as 1234.56.

I know that in LibreOffice, when converting Excel to CSV, you can specify if you want to store the values as is or to keep them formatted.

Any idea on how I can perform this with PHP, ideally using PHPSpreadSheet?

Solution

  • According to the manual, all you need is to set the Read data only option before loading the excel sheet.

    Check out the updated code below:

    use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

$reader = new Xlsx();
// set the Read data only option
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('path_to_excel_file.xlsx');

$writer = (new Csv($spreadsheet))
    ->setEnclosure('')
    ->setLineEnding("\n")
    ->setDelimiter(';');
$writer->setSheetIndex(0);
$writer->save('path_to_csv.csv');

    Reference: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file/#reading-and-writing-to-file