phpExcel简单导出/导入xlsx电子表格文件excel例子
使用PHPExcel库导出excel文件代码，Yii1.1框架下编写，相关代码需要自行调整
/**
* @param string $filepath
* @param array $titles
* @param int $startRow
* @return array|false
*/
public static function importExcelData($filepath, $titles, int $startRow)
{
$datas = [];
try {
Yii::import('application.vendors.phpexcel.*');
require_once 'PHPExcel.php';
//读取文件
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($filepath);
//读取表格数据存入$datas
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestDataRow();
$letters = range('A', 'Z');
$columns = array_keys($titles);
for ($row = $startRow; $row <= $highestRow; $row++) {
$_data = [];
foreach ($columns as $k => $key) {
$_data[$key] = $sheet->getCell("{$letters[$k]}{$row}")->getValue();
}
$datas[] = $_data;
}
return $datas;
} catch (\Exception $e) {
return false;
}
}
/**
* 导出Excel文件
* @param $titles
* @param $data
* @param $filename
* @return void
* @throws CException
*/
public static function exportExcel($titles, $data, $filename)
{
Yii::import('application.vendors.phpexcel.*');
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
try {
//生成A到AZ的列名称数组
$cols = range('A', 'Z');
array_push($cols, ...array_map(function ($v) {
return "A{$v}";
}, $cols));
$objSheet = $objPHPExcel->getActiveSheet();
//工作表标题
$objSheet->setTitle("外呼结果");
$rowIdx = 1;
$titleArr = array_values($titles);
//第一行内容，放列标题
foreach ($titleArr as $k=>$title) {
$objSheet->setCellValue($cols[$k] . $rowIdx, $title);
}
//设置第一行的背景颜色
$objSheet->getStyle("A{$rowIdx}:{$cols[$k]}1")
->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()
->setRGB('E8E8E8');
//后面行循环放内容
foreach ($data as $row) {
$rowIdx++;
foreach ($titleArr as $k=>$title) {
//如果值长度超过阈值，则只保存为字符型单元格格式，避免长数字显示为科学计数法
$value = $row[$k]??'';
if (is_numeric($value) && mb_strlen($value, 'UTF-8') > 10) {
$objSheet->setCellValueExplicit($cols[$k] . $rowIdx, $value);
} else {
$objSheet->setCellValue($cols[$k] . $rowIdx, $value);
}
}
}
// Rename worksheet
// 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="' . $filename . '.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
} catch (\PHPExcel_Exception $e) {
$line = __LINE__;
$file = __FILE__;
zmf::fp("导出文件 {$filename} 出错：{$e->getMessage()}：({$line}){$file}，args:".CJSON::encode(func_get_args()));
Yii::app()->controller->message(200, '导出文件出错');
}
}