β

用 PHPExcel 导入导出 Excel 和 csv 文件

刘勇的BLOG 295 阅读

下载最新的 PHPExcel
在项目中引入 PHPExcel:

require('PHPExcel/PHPExcel.php');

导出:

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Yong Liu")
->setLastModifiedBy("Yong Liu")
->setTitle('export')
->setSubject('export demo')
->setDescription('export ')
->setKeywords("excel csv")
->setCategory('file');

// Add data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'column1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'column2');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', 'column3');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', 'column4');
...

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('export demo');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export demo.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

// Redirect output to a client’s web browser (Excel5)
/*
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
*/

// Redirect output to a client’s web browser (csv)
/*
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename="' . $filename . '.csv"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')
->setDelimiter(',')
->setEnclosure('"')
->setLineEnding("\r\n")
->setSheetIndex(0);
*/

$objWriter->save('php://output');
exit;

导入:


// fox xlsx
$inputFileType = 'Excel2007';
// fox xls
// $inputFileType = 'Excel5';
// fox csv
// $inputFileType = 'CSV';
$path = $_FILES['upload_file']['tmp_name'];
$reader = PHPExcel_IOFactory::createReader($inputFileType);
$excelObj = $reader->load($path);
// $result 为导入内容的数组
$result = $excelObj->getActiveSheet()->toArray(null,true,true,true);

操作过程中可能会出现如下错误:

Unknown codepage: 10008
PHPExcel_Shared_CodePage::NumberToName(%d)
PHPExcel/Shared/CodePage.php 98 break()

原因是 NumberToName() 方法中没有 10008 对应的情况处理,所以会抛出“Unknown codepage: 10008”异常。
解决方法:
在 NumberToName() 方法中加入 10008 对应的处理:

case 10008: return 'MAC';

下载最新的 PHPExcel
在项目中引入 PHPExcel:

1

作者:刘勇的BLOG
走走看看,寻寻觅觅
原文地址:用 PHPExcel 导入导出 Excel 和 csv 文件, 感谢原作者分享。

发表评论