PHPExcel CSV到XLSX [英] PHPExcel CSV to XLSX
问题描述
我在将文件feom CSV转换为XLSX格式时遇到问题:
I have a problem converting file feom CSV to XLSX format:
Index.php
Index.php
<?php
if (!isset($_FILES["file"]))
{
?>
<html>
<body>
<h1>Convert CSV to XLSX</h1>
<form action="index.php" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit"/>
</form>
</body>
</html>
<?php
exit;
}
//obtain PHPExcel from http://phpexcel.codeplex.com
require_once('Classes\PHPExcel.php');
require_once('CSVToExcelConverter.php');
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"];
exit;
}
try
{
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.'example.xlsx');
CSVToExcelConverter::convert($_FILES['file']['tmp_name'], 'php://output');
} catch(Exception $e) {
echo $e->getMessage();
}
CSVToExcelConverter.php
CSVToExcelConverter.php
class CSVToExcelConverter
{
/**
* 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) {
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open csv file
$objReader = new PHPExcel_Reader_CSV();
if ($csv_enc != null)
$objReader->setInputEncoding($csv_enc);
$objPHPExcel = $objReader->load($csv_file);
$in_sheet = $objPHPExcel->getActiveSheet();
//open excel file
$objPHPExcel = new PHPExcel();
$out_sheet = $objPHPExcel->getActiveSheet();
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
$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);
$objWriter->save($xls_file);
}
}
CSV文件格式:使用Excel打开的CSV文件
基本，我想获得类似于原始csv文件的输出，但是以xmlx格式，该怎么做?
Basicaly I would like to get output similar to original csv file, but in xmlx format, how to do that?
推荐答案
在PHPExcel中读取CSV文件的默认"分隔符是逗号(,).您的CSV文件使用的不是逗号，可能是制表符("\t")，也经常用于此类文件.
The "default" separator for reading a CSV file in PHPExcel is a comma (,). Your CSV file is using something other than a comma - perhaps a tab ("\t"), which is also commonly used for such files).
如果值不是逗号(并且我们无法从MS Excel中查看的文件的图像中分辨出来)，则必须在加载之前明确告诉PHPExcel该分隔符是什么.
If the values isn't a comma (and we can't tell from an image of the file viewed in MS Excel) then you have to tell PHPExcel explicitly what that separator is before loading.
例如
$objReader->setDelimiter("\t");
这篇关于PHPExcel CSV到XLSX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
