Converting XLS to CSV (but multiple sheets)

May 10, 2011 at 9:35 PM

Hey guys,

Firstly I'd like to thank the developers for working on this wonderful PHP library.  It saves me so much headache in having to fall back onto a perl alternative.

 

I'm having trouble turning one XLS file with 4 worksheets into 4 individual CSV files.

 

<?php
/** SCRIPT SETTINGS */
error_reporting(E_ALL);

/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::load('FILE.xls');
$objReader->setLoadSheetsOnly('Sheet1');

$writer = PHPExcel_IOFactory::createWriter($objReader);
$writer->save('sheet1.csv');

$objReader->disconnectWorksheets();
unset($objReader);

?>
This should work for one sheet, but it doesn't even do that.
Any ideas what I'm doing wrong?
May 10, 2011 at 10:08 PM

something like this should work...

<?
/** SCRIPT SETTINGS */
error_reporting(E_ALL);

/** PHPExcel */
require_once '../Classes/PHPExcel.php'; // (this should include the autoloader)


/**
 * Save Excel to CSV files
 * may want to clean up the worksheet title used for filename???
 *
 * @param	string		$xlsfile (full file path/name)
 * @param	boolean		$precalculate
 * @param	array		$use_states (array of states to save to file)
 * @throws	Exception
 */
function xls_to_csv_files($xlsfile,$precalculate=true,$use_states=array('visible')){
    $objReader = new PHPExcel_Reader_Excel2007;
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($xlsfile);
    // save csv files to same path as the xls.
    $dir = dirname($xlsfile);
    $base = basename($xlsfile,".xls");

    foreach ($objReader->getWorksheetIterator() as $i=>$worksheet) {
		if(in_array($wstate,$use_states)){
            $wtitle = $worksheet->getTitle();
            $objReader->setLoadSheetsOnly($wtitle);

            $objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
            $objWriter->setPreCalculateFormulas($precalculate);
            $writer->save($dir.'/'.$base.'-'.$wtitle.'.csv');
        }
    }
}

xls_to_csv_files('./file.xls');
?>

May 11, 2011 at 4:28 AM
Edited May 11, 2011 at 10:02 PM

It's an Excel5 file, sorry should have mentionned that.

 

getWorksheetIterator doesnt work on Excel5

Jul 13, 2012 at 5:54 PM

This works perfectly for me and an Excel5 spreadsheet.

<?php
error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Europe/London');

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$inputFileType = 'Excel5';
$inputFileName = './sampleData/PR.xls';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcelReader = $objReader->load($inputFileName);

$loadedSheetNames = $objPHPExcelReader->getSheetNames();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcelReader, 'CSV');

foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
	$objWriter->setSheetIndex($sheetIndex);
	$objWriter->save('E:/autoPTLD/uploads/IPSpreadsheet/'.$loadedSheetName.'.csv');

}

?>