一、composer 安装 PHPExcel
以ThinkPHP5.1 为例,在根目录使用 composer 得到 phpexcel 扩展类库(自动保存至 vendor 文件夹):
composer require phpoffice/phpexcel
二、导出示例:
public function testExcel(){
$data = [
["id" => 1, "username" => "给你最好的我丶", "score" => 92],
["id" => 2, "username" => "抬首轻笑", "score" => 95],
["id" => 3, "username" => "嘉恒帅比等你王者归来", "score" => 74]
];
$objPHPExcel = new \PHPExcel();
// 所有单元格设置为水平居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal("center");
// 所有单元格设置为垂直居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical("center");
// A1:C1 合并并插入比赛名称
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:C1')->setCellValue('A1', "我是标题");
// A1:C2 加粗
$objPHPExcel->getActiveSheet()->getStyle('A1:C2')->getFont()->setBold(true);
// 第二行插入表头
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', 'ID')->setCellValue('B2', '用户名')->setCellValue('C2', '得分');
$a_strlen = $b_strlen = $c_strlen = 20;
$count = count($data);
for ($i = 3; $i <= $count+1; $i++) {
// 设置内容
$v = $data[$i-3];
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $v["id"]);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $v["username"]);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $v["score"]);
// 计算最长文本长度
$a_strlen = $a_strlen < strlen($v["id"]) ? strlen($v["id"]) : $a_strlen;
$b_strlen = $b_strlen < strlen($v["username"]) ? strlen($v["username"]) : $b_strlen;
$c_strlen = $c_strlen < strlen($v["score"]) ? strlen($v["score"]) : $c_strlen;
}
// 自适应单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth($a_strlen);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($b_strlen);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth($c_strlen);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$test_path = Env::get('ROOT_PATH') . "test.xls";
$objWriter->save($test_path);
echo "保存位置:" . $test_path;
}
导出结果:
三、导入示例:
略
四、更多excel操作参考