Yii 等框架查詢結果默認返回關聯數組(字段名作鍵),但導出 Excel 時需要純索引數組(0,1,2...)對應列順序,且需單獨提取表頭。
/**
* 格式化數據用於 Excel 導出
* @param array $data 二維關聯數組 [ ['id'=>1, 'name'=>'A'], ['id'=>2, 'name'=>'B'] ]
* @return array ['excel_title' => ['id','name'], 'excel_cells' => [[1,'A'], [2,'B']] ]
*/
public function excelDataFormat($data) {
$new_arr = array();
$new_key = array();
for ($i = 0; $i < count($data); $i++) {
$each_arr = $data[$i];
$new_arr[] = array_values($each_arr); // 只取值,重置為 0,1,2...
}
// 表頭取第一行的鍵名(假設所有行鍵名一致)
if (!empty($data)) {
$new_key[] = array_keys($data[0]);
}
return array(
'excel_title' => $new_key[0] ?? array(),
'excel_cells' => $new_arr
);
}
$rawData = [
['goods_id' => 101, 'goods_name' => '蘋果', 'price' => 5.5],
['goods_id' => 102, 'goods_name' => '香蕉', 'price' => 3.2],
];
$formatted = excelDataFormat($rawData);
/*
$formatted = [
'excel_title' => ['goods_id', 'goods_name', 'price'],
'excel_cells' => [
[101, '蘋果', 5.5],
[102, '香蕉', 3.2],
]
];
*/
// 導出邏輯
$objPHPExcel = new PHPExcel();
// 寫入表頭
$objPHPExcel->getActiveSheet()->fromArray($formatted['excel_title'], null, 'A1');
// 寫入數據
$objPHPExcel->getActiveSheet()->fromArray($formatted['excel_cells'], null, 'A2');
實際導出常需調整列順序、中文表頭、過濾敏感字段。
function formatForExcel($data, $columnMap) {
// $columnMap = ['db_field' => '中文表頭', ...] 順序即導出順序
$titles = array_values($columnMap);
$fields = array_keys($columnMap);
$cells = array();
foreach ($data as $row) {
$line = array();
foreach ($fields as $field) {
$line[] = isset($row[$field]) ? $row[$field] : '';
}
$cells[] = $line;
}
return compact('titles', 'cells');
}
// 使用
$map = [
'goods_name' => '商品名稱',
'price' => '價格',
'stock' => '庫存',
];
$excelData = formatForExcel($rawData, $map);
fromArray() 批量寫入。PHPExcel_CachedObjectStorageFactory 設置緩存到磁盤/Redis,或改用 PhpSpreadsheet + CSV 流式輸出。