public static function convertCSVtoXLSX($csvFilePath)
{
$xlsxFileName = pathinfo($csvFilePath, PATHINFO_FILENAME) . ".xlsx";
$xlsxFilePath = pathinfo($csvFilePath, PATHINFO_DIRNAME) . "/{$xlsxFileName}";
CSVToExcelConverter::convert(str_replace("\\", "/", $csvFilePath), $xlsxFilePath);
return $xlsxFilePath;
}
public function testGuessDelimiter()
{
$csv_file = realpath('./file/State list -- dv1.csv');
$tsv_file = realpath('./file/State list_tsv -- dv1.csv');
$this->assertEquals(CSVToExcelConverter::guessDelimiter($csv_file), ',');
$this->assertEquals(CSVToExcelConverter::guessDelimiter($tsv_file), ' ');
}
/**
* Read given csv file and write all rows to given xls file
*
* @param string $csv_file Resource path of the csv file
* @param string $xls_file Resource path of the excel file
* @param string $csv_enc Encoding of the csv file, use utf8 if null
* @throws Exception
*/
public static function convert($csv_file, $xls_file, $csv_enc = null)
{
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
$cacheSettings = array('dir' => 'E:/PHPExcel_cache');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
} else {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
}
//open csv file
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(CSVToExcelConverter::guessDelimiter($csv_file));
if ($csv_enc != null) {
$objReader->setInputEncoding($csv_enc);
}
$objPHPExcel_CSV = $objReader->load($csv_file);
$in_sheet = $objPHPExcel_CSV->getActiveSheet();
//open excel file
$objPHPExcel_XLSX = new PHPExcel();
$out_sheet = $objPHPExcel_XLSX->getActiveSheet();
$out_sheet->setTitle('File Content');
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
//if($row_index==20)
// break;
$row_index++;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($cellIterator as $cell) {
$column_index++;
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell->getValue());
}
}
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel_XLSX);
$objWriter->save($xls_file);
}
