tp6 导出 excel

thinkphp6 PhpOffice/PhpSpreadsheet 将数据导出为 Excel

项目需求：将订单数据到出到excel

PHP版本：php7.4

• composer 安装 phpoffice

composer require phpoffice/phpspreadsheet

• 在控制器代码

// 读取需要导出的数据
$list = $this->model->where($where)->order('id desc')->select();	

// excel 的数据
$xlsData = [];
foreach ($list as $k => $v) {
	$xlsData[] = [
		'ID' => $v['id'],
		'订单号' => $v['order_sn'],
		'订单状态' => $v['status'],
        '店铺ID' => $v['store_id'],
        '店铺名称' => $v['store_name'],
        '商品名称' => $v['goods_title'],
       	'商品价格' => $v['goods_price'],
		'购买数量' => $v['good_num'],
        '用户ID' => $v['user_id'],
        '用户昵称' => $v['user_nickname'],
        '用户手机' => $v['user_phone'],
        '下单时间' => $v['order_time'],
        '订单金额' => $v['pay_price'],
        '付款时间' => $v['pay_time'],
        '支付方式' => $v['pay_type'],
	];
}
if (count($xlsData) == 0) {
	return '暂无订单数据';
}

// 编辑表格的title，返回的数据格式是：$xlsCell = ['ID', '订单号', '订单状态', ...] 
$length = count($xlsData);
foreach ($xlsData[$length - 1] as $k => $v) {
	$xlsCell[] = $k;
}

// 你的表格一共有多少字段信息需要导出，设置对应的excel坐标
$setWidth = ['A', 'B', 'c', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O'];

// 调用自定义方法
exportExcel('订单表', $xlsCell, $xlsData, $setWidth);

• 自定义方法

use PhpOffice\PhpSpreadsheet\Spreadsheet;

function exportExcel($expTitle, $expCellName, $expTableData, $setWidth = [])
{
    $spreadsheet = new Spreadsheet();
    $spreadsheet->getActiveSheet()->setTitle($expTitle . date('Ymd'));
    $cellNum = count($expCellName);
    $len = count($expTableData);

    // 设置表头字段
    foreach ($expCellName as $k => $v) {
        $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($k + 1, 1, $v);
    }

    // 添加数据
    foreach ($expTableData as $k => $v) {
        for ($i = 0; $i < $cellNum; $i++) {
            $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($i + 1, $k + 2, $expTableData[$k][$expCellName[$i]]);
        }
    }

    // 添加所有边框/居中
    $styleArrayBody = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => '333333'],
            ],
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
    $total_rows = $len + 1;		// 表格总行数要加上表头一栏
    $spreadsheet->getActiveSheet()->getStyle('A1:'.end($setWidth) . $total_rows)->applyFromArray($styleArrayBody);

    // 设置列宽
    if ($setWidth) {
        foreach ($setWidth as $k => $v) {
            $spreadsheet->getActiveSheet()->getColumnDimension($v)->setAutoSize(true); // 列宽自定义
        }
    } else {
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(14);
    }

    $filename = $expTitle . date('Ymd') . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    die();
}