php - CSV 导出/导入 PHPExcel

Question

您能否指导我或提供一些使用 PHPExcel 库执行 CSV 导出和导入的示例代码？

Excel 导出和导入很好，但我也需要 CSV 导出/导入。我有其他的 CSV 导出和导入方式，但也可以通过 PHPExcel 完成吗？

Answer 1

将 CSV 文件导入 PHPExcel 对象

$inputFileType = 'CSV';
$inputFileName = 'testFile.csv';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

从 PHPExcel 对象导出 CSV 文件

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save('testExportFile.csv');

编辑

如何读取行和单元格:

$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";
        }
    }
}

如何写入 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++;
}