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

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 .

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?

CodePudding user response：

Solution: As PhpOffice\PhpSpreadsheet\Cell\Cell::getValue() returns the actual value of the cell (1234.56 instead of 1 234,56 €), I decided to iterate over the entire excel sheet, and to write row by row my CSV file. This code worked for me:

function convertExcelToCsv(string $excel, string $csv, string $delimiter = ';', string $line_ending = "\n", int $sheet_index = 0)
{
    // Load Excel and open CSV
    $reader = new Xlsx();
    $spreadsheet = $reader->load($excel);
    $sheet = $spreadsheet->getSheet($sheet_index);
    $file = fopen($csv, "w");

    // Iterate over the rows and columns
    for ($row = 1; $row <= $sheet->getHighestDataRow(); $row  ) { // Used <= to include the last row

        // get the highest column for current row
        $highest_column = Coordinate::columnIndexFromString($sheet->getHighestDataColumn($row));

        for ($column = 1; $column <= $highest_column; $column  ) { // Used <= to include the last column
            // write the data
            fwrite($file, $sheet->getCellByColumnAndRow($column, $row)->getValue());
            // if there's still data to be written after, place a delimiter
            if ($column   1 <= $highest_column)
                fwrite($file, $delimiter);
        }

        // The current row is finished, place a line break
        fwrite($file, $line_ending);
    }
    // Don't forget to close the file
    fclose($file);
}

This code may be optimizable, perhaps using $sheet->getRowIterator() and $sheet->getColumnIterator(), but I'm not sure how to use them nor if they would give the actual value of the cell (1234.56).

CodePudding user response：

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