记录一次使用thinkphp使用PhpSpreadsheet扩展导出数据，解决身份证号码等信息科学计数法问题处理
// 如果将该扩展直接安装到thinkphp6项目中提示找不到Class 'PhpOffice\\PhpSpreadsheet\\Spreadsheet' not found，将该扩展放置项目根目录的extend文件夹中（参考下图）,随后打开下行代码
// require_once app()->getRootPath() . 'extend/PhpSpreadsheet/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
public function export_data($data){
// 获取所有要查询的字段和名称
$header_arr = [];
$fields_arr = [];
foreach($data['export_data'] as $key=>$val){
array_push($header_arr, $val['label']);
array_push($fields_arr, $val['field']);
}
// 文件存储目录
$public = app()->getRootPath().'public/';
$path = 'uploads/export_data/';
if(!file_exists($path)){
mkdir($path, 0777);
}
// 文件名
$res_file = $file_name . '_all.xlsx';
$finalFile = $public . $path . $res_file;
$currentRow = 1; // 当前写入行
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 3. 计算列范围 - 安全方法
$lastColumn = Coordinate::stringFromColumnIndex(count($header_arr));
$headerRange = 'A:' . $lastColumn;
// $sheet->setTitle('Sheet1');
// 如果是第一次运行，创建新文件并写入头部
if (!file_exists($finalFile)) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getStyle($headerRange)
->getNumberFormat()
->setFormatCode(NumberFormat::FORMAT_TEXT);
// 写入头部
$sheet->fromArray([$header_arr], null, 'A1');
$currentRow = 2;
// 设置表头样式
$styleArray = [
'alignment' => [
'horizontal' => 'center',
'vertical' => 'center',
],
// 'font' => [
// 'name' => '宋体',
// 'bold' => true,
// 'size' => 22
// ]
];
// $headerRange = 'A1:' . chr(ord('A') + count($header_arr) - 1) . '1';
$sheet->getStyle($headerRange)->applyFromArray($styleArray);
// 保存文件
$writer = new Xlsx($spreadsheet);
$writer->save($finalFile);
// echo "创建新文件并写入头部\n";
}
// 分批导出并追加数据
$totalBatches = 3; // 假设总共3批
for ($batch = 1; $batch <= $totalBatches; $batch++) {
// 加载现有文件
$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($finalFile);
$sheet = $spreadsheet->getActiveSheet();
// 获取当前最后一行
$currentRow = $sheet->getHighestRow() + 1;
// 生成测试数据 - 实际应用中从数据库获取
$data = [];
$perBatch = 10000;
$start = ($batch - 1) * $perBatch + 1;
$end = $batch * $perBatch;
for ($i = $start; $i <= $end; $i++) {
$data[] = [
$i,
"姓名{$i}",
($i % 2) ? '男' : '女',
"职业{$i}"
];
}
// 追加数据
$stringContent = array_map(function($row) use ($sheet, $currentRow) {
return array_map(function($cell) {
return (string)$cell; // 只返回值，不返回类型
}, $row);
}, $data);
// 先设置数据
$sheet->fromArray($stringContent, null, "A{$currentRow}");
// 部分列强制文本格式
$forceTextColumns = ['CI', 'AQ', 'AH']; // 需要强制文本的列
for ($row = $currentRow; $row <= $highestRow; $row++) {
foreach ($forceTextColumns as $col) {
$cell = $sheet->getCell($col.$row);
$cell->setValueExplicit(
$cell->getValue(),
DataType::TYPE_STRING
);
$sheet->getStyle($col.$row)->getNumberFormat()->setFormatCode('@');
}
}
// 保存文件
$writer = new Xlsx($spreadsheet);
$writer->save($finalFile);
echo "批次 {$batch} 数据已追加到 {$finalFile} (行 {$currentRow}-" . ($currentRow + count($data) - 1) . ")\n";
}
echo "所有数据已导出到 {$finalFile}\n";
}