How to Convert Excel Xls to CSV Using PHP

How to convert Excel XLS to CSV using PHP

Probably you can start reading a XLS using PHP.

Then, using the main logic to output what you want (csv in your case).

Good luck,

PHPExcel convert XLS to CSV with special characters

<?php

/**
    Convert excel file to csv
*/

//Various excel formats supported by PHPExcel library
$excel_readers = array(
    'Excel5' , 
    'Excel2003XML' , 
    'Excel2007'
);

require_once('PHPExcel180/Classes/PHPExcel.php');
require_once('PHPExcel180/Classes/PHPExcel/Writer/CSV.php');

$reader = PHPExcel_IOFactory::createReader('Excel5');
$reader->setReadDataOnly(false);

$path = 'file.xls';
$excel = $reader->load($path);

$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->setUseBOM(true);
$writer->save('data.csv');

echo 'File saved to csv format';
?>

PHPExcel_1.8.0_doc
Library use code

Removes special characters when converting from one xls to csv, code end

phpExcel xls to csv conversion is changing the date to raw

Your problem is

$objReader->setReadDataOnly(true);  

which is explicitly telling PHPExcel to read the cell data as raw data, without any of the styling information (such as number format masking).

Simply comment out that line

Change date format during conversion from xlsx to csv with PHPExcel

I was looking for a way to set the format code for all cells that are dates without having to iterate through all the cells. I never found that solution. Here is what I ended up using.

$filePath = "fromFile.xlsx"; 
$csvPath = "toFile.csv";

PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$reader = PHPExcel_IOFactory::load( $filePath );

foreach( $reader->getActiveSheet()->getRowIterator(1) as $row ){
    foreach( $row->getCellIterator() as $cell ){
        if( PHPExcel_Shared_Date::isDateTime($cell)){
            $cell->getStyle()->getNumberFormat()->setFormatCode("mm/dd/yyyy" );
        }
    }
}

$writer = PHPExcel_IOFactory::createWriter( $reader, 'CSV' );
$writer->setDelimiter(",");
$writer->setEnclosure('"');
$writer->setSheetIndex(0);

$writer->save( $csvPath );

Using PHPExcel to convert more then 1 sheet from xls to csv

At the moment you only write the active sheet. You have to iterate over the different sheets in the excel-file and write them to seperate csv files.

foreach ($excel->getWorksheetIterator() as $workSheetIndex => $worksheet) {
    $excel->setActiveSheetIndex($workSheetIndex);    
    $writer->setSheetIndex($index);
    $writer->save("test123_" . $worksheet->getTitle() . ".csv");    
}

---

Related Topics