ThinkPHP 数据导出EXCEL表格
super
2021-11-13 12:17
2951
composer require phpoffice/phpspreadsheet
- PHP版本: ^7.2 || ^8.0
https://packagist.org/packages/phpoffice/phpspreadsheet
文档:https://phpspreadsheet.readthedocs.io/en/latest/#getting-started.
Excel导出
tip: 修改数组,将$array[0]修改为表头标题
$array = [
[
'id' => 'ID',
'name' => '姓名',
'age' => '年龄'
],
[
'id' => 1,
'name' => 'r1',
'age' => 10
],
[
'id' => 2,
'name' => 'r2',
'age' => 10
],
[
'id' => 3,
'name' => 'r3',
'age' => 12
],
[
'id' => 4,
'name' => 'r4',
'age' => 9
],
[
'id' => 5,
'name' => 'r5',
'age' => 8
],
];
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$word = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$num = 1;
foreach ($array as $k => $v) {
$start = 0;
foreach ($v as $k1 => $v1) {
if ($start < 26) {
$sheet->setCellValue($word[$start] . $num, $v1);
} else {
$rw = (int)(($start + 1) / 26);
$sheet->setCellValue($word[$rw - 1] . $word[$start - ($rw * 26)] . $num, $v1);
}
$start += 1;
}
$num++;
}
$writer = new Xlsx($spreadsheet);
$writer->save('./xlsx/hello world.xlsx');
提示:以上代码仅支持导出每行数据不超过(26 * 26 + 26 = 702 A - ZZ)列的数据
由于时间关系,我仅用了29列数据测试,如果你的数据超过26列,请在导出后检查数据
导出文件在根目录xlsx目录下
补充(18:25):
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
if (!function_exists('export_data')) {
/**
* 数据导出
* @param array $list 数据列表
* @param string $file_name 文件名
* @param string $suffix 文件后缀: xlsx/xls
* @param string $save_path 保存路径
* @return void
*/
function export_data (array $list, $file_name = '', $suffix = 'xlsx', $save_path = '') {
try {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$word = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$num = 1;
foreach ($list as $k => $v) {
$start = 0;
foreach ($v as $k1 => $v1) {
if ($start < 26) {
$sheet->setCellValue($word[$start] . $num, $v1);
} else {
$rw = (int)(($start + 1) / 26);
$sheet->setCellValue($word[$rw - 1] . $word[$start - ($rw * 26)] . $num, $v1);
}
$start += 1;
}
$num++;
}
// 处理后缀
if ($suffix[0] === '.') {
$suffix = substr($suffix, 1);
}
$suffix = strtolower($suffix);
if ($save_path) {
// 处理路径
if (substr($save_path, -1, 1) !== '/') {
$save_path .= '/';
}
if (!file_exists('.' . $save_path)) {
mkdir('.' . $save_path, 0777, true);
}
switch ($suffix) {
case 'xlsx':
(new Xlsx($spreadsheet))->save('.' . $save_path . $file_name . '.xlsx');
break;
case 'xls':
(new Xls($spreadsheet))->save('.' . $save_path . $file_name . '.xls');
break;
}
} else {
switch ($suffix) {
case 'xlsx':
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
break;
case 'xls':
header('Content-Type: application/vnd.ms-excel');
break;
default:
exit('导出失败, 未定义的类型:' . $suffix);
}
header("Content-Disposition: attachment;filename=" . $file_name . '.' . $suffix);
header('Cache-Control: max-age=0'); //禁止缓存
$objWriter = IOFactory::createWriter($spreadsheet, ucfirst($suffix));
$objWriter->save('php://output');
}
} catch (\Exception $e) {
exit('导出失败: ' . $e->getMessage());
}
}
}
使用:
// $array[0]是表头(标题)
$array = [
[
'id' => 'ID',
'name' => '姓名',
'age' => '年龄',
'grade' => '年级',
],
[
'id' => 1,
'name' => '小王',
'age' => 4,
'grade' => 1,
],
[
'id' => 2,
'name' => '小李',
'age' => 3,
'grade' => 1,
],
[
'id' => 3,
'name' => '小赵',
'age' => 5,
'grade' => 1,
],
[
'id' => 4,
'name' => '小二',
'age' => 3,
'grade' => 1,
],
[
'id' => 5,
'name' => '小丽',
'age' => 5,
'grade' => 1
],
];
export_data($array, 'test', '.xlsl', '/xlsx/1/'); // 保存文件:根目录/xlsx/1/
export_data($array, 'test', '.xlsl'); // 下载文件
0 条讨论