php 使用 PHPExcel 导出/导入 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3895819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CSV Export/Import with PHPExcel
提问by Kunal
Could you please guide me or provide me with some sample codes for performing CSV export and import using the PHPExcel library?
您能否指导我或向我提供一些使用 PHPExcel 库执行 CSV 导出和导入的示例代码？
Excel export and import is fine but I need CSV export/import as well. I have other means of CSV export and import, but can it be done via PHPExcel also?
Excel 导出和导入很好，但我也需要 CSV 导出/导入。我有其他的 CSV 导出和导入方法，但也可以通过 PHPExcel 完成吗？
回答by Mark Baker
To import a CSV file into a PHPExcel object
将 CSV 文件导入 PHPExcel 对象
$inputFileType = 'CSV';
$inputFileName = 'testFile.csv';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
To export a CSV file from a PHPExcel object
从 PHPExcel 对象导出 CSV 文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save('testExportFile.csv');
EDIT
编辑
How to read through the rows and cells:
如何通读行和单元格：
$worksheet = $objPHPExcel->getActiveSheet();
foreach ($worksheet->getRowIterator() as $row) {
echo 'Row number: ' . $row->getRowIndex() . "\r\n";
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {
echo 'Cell: ' . $cell->getCoordinate() . ' - ' . $cell->getValue() . "\r\n";
}
}
}
How to write to a PHPExcel object: You don't say where your data comes from: here's how to do it from a MySQL Query
如何写入 PHPExcel 对象：你没有说你的数据来自哪里：这里是如何从 MySQL 查询中做到这一点
$query = sprintf("SELECT firstname, lastname, age, date_of_birth, salary FROM employees WHERE firstname='%s' AND lastname='%s'",
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
$result = mysql_query($query);
$row = 1;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'First Name')
->setCellValue('B'.$row, 'Last Name')
->setCellValue('C'.$row, 'Age')
->setCellValue('D'.$row, 'Date of birth')
->setCellValue('E'.$row, 'Salary');
$row++;
while ($rec = mysql_fetch_assoc($result)) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $rec['firstname'])
->setCellValue('B'.$row, $rec['lastname'])
->setCellValue('C'.$row, $rec['age'])
->setCellValue('D'.$row, PHPExcel_Shared_Date::stringToExcel($rec['date_of_birth']))
->setCellValue('E'.$row, $rec['salary']);
$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getNumberFormat()->setFormatCode('￡#,##0.00');
$row++;
}
回答by HELPFUL_SHADOW
I been seeking the same thing. Excel CSV dosn't always use the quote separators and escapes the quotes using "" because the algorithm was probably made back the 80's or something. After looking at several .csv parsers in the comments section on PHP.NET, I seen ones that even used callbacks or eval'd code and they either didnt work like needed or simply didnt work at all. So, I wrote my own routines for this and they work in the most basic PHP configuration. The array keys can either be numeric or named as the fields given in the header row. Hope this helps.
我一直在寻找同样的东西。Excel CSV 并不总是使用引号分隔符并使用 "" 对引号进行转义，因为该算法可能是 80 年代或其他东西。在 PHP.NET 的评论部分中查看了几个 .csv 解析器后，我看到了一些甚至使用回调或 eval'd 代码的解析器，但它们要么无法正常工作，要么根本无法正常工作。所以，我为此编写了自己的例程，它们在最基本的 PHP 配置中工作。数组键可以是数字或命名为标题行中给出的字段。希望这可以帮助。
function SW_ImplodeCSV(array $rows, $headerrow=true, $mode='EXCEL', $fmt='2D_FIELDNAME_ARRAY')
// SW_ImplodeCSV - returns 2D array as string of csv(MS Excel .CSV supported)
// AUTHOR: [email protected]
// RELEASED: 9/21/13 BETA
{ $r=1; $row=array(); $fields=array(); $csv="";
$escapes=array('\r', '\n', '\t', '\', '\"'); //two byte escape codes
$escapes2=array("\r", "\n", "\t", "\", "\""); //actual code
if($mode=='EXCEL')// escape code = ""
{ $delim=','; $enclos='"'; $rowbr="\r\n"; }
else //mode=STANDARD all fields enclosed
{ $delim=','; $enclos='"'; $rowbr="\r\n"; }
$csv=""; $i=-1; $i2=0; $imax=count($rows);
while( $i < $imax )
{
// get field names
if($i == -1)
{ $row=$rows[0];
if($fmt=='2D_FIELDNAME_ARRAY')
{ $i2=0; $i2max=count($row);
while( list($k, $v) = each($row) )
{ $fields[$i2]=$k;
$i2++;
}
}
else //if($fmt='2D_NUMBERED_ARRAY')
{ $i2=0; $i2max=(count($rows[0]));
while($i2<$i2max)
{ $fields[$i2]=$i2;
$i2++;
}
}
if($headerrow==true) { $row=$fields; }
else { $i=0; $row=$rows[0];}
}
else
{ $row=$rows[$i];
}
$i2=0; $i2max=count($row);
while($i2 < $i2max)// numeric loop (order really matters here)
//while( list($k, $v) = each($row) )
{ if($i2 != 0) $csv=$csv.$delim;
$v=$row[$fields[$i2]];
if($mode=='EXCEL') //EXCEL 2quote escapes
{ $newv = '"'.(str_replace('"', '""', $v)).'"'; }
else //STANDARD
{ $newv = '"'.(str_replace($escapes2, $escapes, $v)).'"'; }
$csv=$csv.$newv;
$i2++;
}
$csv=$csv."\r\n";
$i++;
}
return $csv;
}
function SW_ExplodeCSV($csv, $headerrow=true, $mode='EXCEL', $fmt='2D_FIELDNAME_ARRAY')
{ // SW_ExplodeCSV - parses CSV into 2D array(MS Excel .CSV supported)
// AUTHOR: [email protected]
// RELEASED: 9/21/13 BETA
//SWMessage("SW_ExplodeCSV() - CALLED HERE -");
$rows=array(); $row=array(); $fields=array();// rows = array of arrays
//escape code = '\'
$escapes=array('\r', '\n', '\t', '\', '\"'); //two byte escape codes
$escapes2=array("\r", "\n", "\t", "\", "\""); //actual code
if($mode=='EXCEL')
{// escape code = ""
$delim=','; $enclos='"'; $esc_enclos='""'; $rowbr="\r\n";
}
else //mode=STANDARD
{// all fields enclosed
$delim=','; $enclos='"'; $rowbr="\r\n";
}
$indxf=0; $indxl=0; $encindxf=0; $encindxl=0; $enc=0; $enc1=0; $enc2=0; $brk1=0; $rowindxf=0; $rowindxl=0; $encflg=0;
$rowcnt=0; $colcnt=0; $rowflg=0; $colflg=0; $cell="";
$headerflg=0; $quotedflg=0;
$i=0; $i2=0; $imax=strlen($csv);
while($indxf < $imax)
{
//find first *possible* cell delimiters
$indxl=strpos($csv, $delim, $indxf); if($indxl===false) { $indxl=$imax; }
$encindxf=strpos($csv, $enclos, $indxf); if($encindxf===false) { $encindxf=$imax; }//first open quote
$rowindxl=strpos($csv, $rowbr, $indxf); if($rowindxl===false) { $rowindxl=$imax; }
if(($encindxf>$indxl)||($encindxf>$rowindxl))
{ $quoteflg=0; $encindxf=$imax; $encindxl=$imax;
if($rowindxl<$indxl) { $indxl=$rowindxl; $rowflg=1; }
}
else
{ //find cell enclosure area (and real cell delimiter)
$quoteflg=1;
$enc=$encindxf;
while($enc<$indxl) //$enc = next open quote
{// loop till unquoted delim. is found
$enc=strpos($csv, $enclos, $enc+1); if($enc===false) { $enc=$imax; }//close quote
$encindxl=$enc; //last close quote
$indxl=strpos($csv, $delim, $enc+1); if($indxl===false) { $indxl=$imax; }//last delim.
$enc=strpos($csv, $enclos, $enc+1); if($enc===false) { $enc=$imax; }//open quote
if(($indxl==$imax)||($enc==$imax)) break;
}
$rowindxl=strpos($csv, $rowbr, $enc+1); if($rowindxl===false) { $rowindxl=$imax; }
if($rowindxl<$indxl) { $indxl=$rowindxl; $rowflg=1; }
}
if($quoteflg==0)
{ //no enclosured content - take as is
$colflg=1;
//get cell
// $cell=substr($csv, $indxf, ($indxl-$indxf)-1);
$cell=substr($csv, $indxf, ($indxl-$indxf));
}
else// if($rowindxl > $encindxf)
{ // cell enclosed
$colflg=1;
//get cell - decode cell content
$cell=substr($csv, $encindxf+1, ($encindxl-$encindxf)-1);
if($mode=='EXCEL') //remove EXCEL 2quote escapes
{ $cell=str_replace($esc_enclos, $enclos, $cell);
}
else //remove STANDARD esc. sceme
{ $cell=str_replace($escapes, $escapes2, $cell);
}
}
if($colflg)
{// read cell into array
if( ($fmt=='2D_FIELDNAME_ARRAY') && ($headerflg==1) )
{ $row[$fields[$colcnt]]=$cell; }
else if(($fmt=='2D_NUMBERED_ARRAY')||($headerflg==0))
{ $row[$colcnt]=$cell; } //$rows[$rowcnt][$colcnt] = $cell;
$colcnt++; $colflg=0; $cell="";
$indxf=$indxl+1;//strlen($delim);
}
if($rowflg)
{// read row into big array
if(($headerrow) && ($headerflg==0))
{ $fields=$row;
$row=array();
$headerflg=1;
}
else
{ $rows[$rowcnt]=$row;
$row=array();
$rowcnt++;
}
$colcnt=0; $rowflg=0; $cell="";
$rowindxf=$rowindxl+2;//strlen($rowbr);
$indxf=$rowindxf;
}
$i++;
//SWMessage("SW_ExplodeCSV() - colcnt = ".$colcnt." rowcnt = ".$rowcnt." indxf = ".$indxf." indxl = ".$indxl." rowindxf = ".$rowindxf);
//if($i>20) break;
}
return $rows;
}
...Now bob can do his spreadsheets
...现在鲍勃可以做他的电子表格
回答by Ajna
<?php
if(isset($_POST['Upload'])) {
$fname = $_FILES['upfile']['name'];
$chk_ext = explode(".",$fname);
if(strtolower($chk_ext[1]) == "csv" || strtolower($chk_ext[1]) == "xls" || strtolower($chk_ext[1]) == "xlsx") {
$filename = $_FILES['upfile']['tmp_name'];
$handle = fopen($filename, "r");
while (($data = fgetcsv($handle, 1000, "''09'")) !== FALSE)
{
$sql = "INSERT INTO tbl_upload(up_proj,up_loc,up_sys,up_dev,up_devtype,up_mnf,up_devtag,up_conn,up_readtag,up_ipaddr,up_warr,up_rem) VALUES('" .mysql_real_escape_string($data[1]). "','" .mysql_real_escape_string($data[2]). "','" .mysql_real_escape_string($data[3]). "','" .mysql_real_escape_string($data[4]). "','" .mysql_real_escape_string($data[5]). "','" .mysql_real_escape_string($data[6]). "','" .mysql_real_escape_string($data[7]). "','" .mysql_real_escape_string($data[8]). "','" .mysql_real_escape_string($data[9]). "','" .mysql_real_escape_string($data[10]). "','" .mysql_real_escape_string($data[11]). "','" .mysql_real_escape_string($data[12]). "')";
mysql_query($sql) or die(mysql_error());
} //while
fclose($handle);
echo "Successfully Uploaded";
} //if
else
{
echo "Invalid File";
}
}//submit
?>
