PhpSpreadsheet 導出特定格式 — 廣告請款單
- 2019 年 10 月 21 日
- 筆記
需求說明
最近需要實現一個導出這種格式的Excel表單，之前都有用過導出Excel的功能，但大都是表頭+數據的形式，只用於獲取數據，沒有太多樣式要求，不用合併單元格、合併居中等，也不用對每一行數據特異化訂製的，所以對PhpSpreadsheet擴展庫進行進階理解。
解決過程
從github->官方文檔->api文檔->CSDN文章(文末有鏈接)，完了跟著操作都會一直報錯，csdn的那篇文章中一旦調用了樣式的方法後，返回對象就變成了樣式相關的，不能繼續指定工作單元了，所以仍然不是太實用，只能作為參考了。
最後通過看源碼 + api文檔實現了這部分的功能點。
需要用到的方法
- mergeCells()：合併單元格，參數為起始單元格到結束單元格
- getCellByColumnAndRow(): 獲取指定位置的單元格對象
- setValueExplicit()：設置單元格內容，並指定樣式
- getStyle()：獲取單元格樣式對象
- applyFromArray()：指定單元格樣式
程式碼片段解讀
1、填充單元格數據
$sheet->mergeCells('A1:K2')->getCellByColumnAndRow(1, 1)->setValueExplicit('廣告請款單', $contentType);mergeCells('A1:K2'): 從A1合併到K2getCellByColumnAndRow(1, 1): 獲取第一行第一列單元格對象setValueExplicit('廣告請款單', $contentType): 填充內容為廣告請款單, 並指定為字元串類型($contentType = DataType::TYPE_STRING)
2、指定樣式
$styleArray = [ 'alignment' => [ // 水平居中 'horizontal' => Alignment::HORIZONTAL_CENTER, // 垂直居中 'vertical' => Alignment::VERTICAL_CENTER, // 自動換行 'wrapText' => true ], ]; $sheet->getStyle('A1:K21')->applyFromArray($styleArray);'wrapText' => true指定單元格內容自動換行，會自動解析文本內容中的n,rn換行符applyFromArray()方法在PhpOfficePhpSpreadsheetStyleAlignment類中，可根據需要指定參數;- 注意：指定樣式要在所有單元格數據設置之後，因為設置樣式後返回的結果是
PhpOfficePhpSpreadsheetStyleAlignment對象，沒有mergeCells()等設置單元格的方法了
剩餘的麻煩點就是計算每個特定合併單元的位置了
暴力實現
起初在試驗過程，理論基礎不夠充足，所以就通過暴力計算每個樣式位置並進行設置了。
$contentType = DataType::TYPE_STRING; $sheet->mergeCells('A1:K2')->getCellByColumnAndRow(1, 1)->setValueExplicit('廣告請款單', $contentType); $sheet->mergeCells('A3:B4')->getCellByColumnAndRow(1, 3)->setValueExplicit('申請時間', $contentType); $sheet->mergeCells('C3:E4')->getCellByColumnAndRow(3, 3)->setValueExplicit(date('Y-m-d H:i:s', $data['apply_clear_time']), $contentType); $sheet->mergeCells('F3:G4')->getCellByColumnAndRow(6, 3)->setValueExplicit('廣告編號', $contentType); $sheet->mergeCells('H3:K4')->getCellByColumnAndRow(8, 3)->setValueExplicit($data['ad_no'], $contentType); $sheet->mergeCells('A5:B6')->getCellByColumnAndRow(1, 5)->setValueExplicit('收款單位', $contentType); $sheet->mergeCells('C5:K6')->getCellByColumnAndRow(3, 5)->setValueExplicit($data['real_name'], $contentType); $sheet->mergeCells('A7:B8')->getCellByColumnAndRow(1, 7)->setValueExplicit('銀行賬戶', $contentType); $sheet->mergeCells('C7:K8')->getCellByColumnAndRow(3, 7)->setValueExplicit($data['bank_no'], $contentType); $sheet->mergeCells('A9:B10')->getCellByColumnAndRow(1, 9)->setValueExplicit('開戶行', $contentType); $sheet->mergeCells('C9:K10')->getCellByColumnAndRow(3, 9)->setValueExplicit($data['bank_name'], $contentType); $sheet->mergeCells('A11:B12')->getCellByColumnAndRow(1, 11)->setValueExplicit('金額', $contentType); $sheet->mergeCells('C11:E12')->getCellByColumnAndRow(3, 11)->setValueExplicit($data['amount'], $contentType); $sheet->mergeCells('F11:G12')->getCellByColumnAndRow(6, 11)->setValueExplicit('大寫金額', $contentType); $sheet->mergeCells('H11:K12')->getCellByColumnAndRow(8, 11)->setValueExplicit($data['amount_desc'], $contentType); $sheet->mergeCells('A13:B14')->getCellByColumnAndRow(1, 13)->setValueExplicit('備註', $contentType); $sheet->mergeCells('C13:K14')->getCellByColumnAndRow(3, 13)->setValueExplicit($data['desc'], $contentType); $sheet->mergeCells('A15:D16')->getCellByColumnAndRow(1, 15)->setValueExplicit('廣告BD審核', $contentType); $sheet->mergeCells('E15:H16')->getCellByColumnAndRow(5, 15)->setValueExplicit('廣告主管審核', $contentType); $sheet->mergeCells('I15:K16')->getCellByColumnAndRow(9, 15)->setValueExplicit('總經理審核', $contentType); $sheet->mergeCells('A17:D19')->getCellByColumnAndRow(1, 17)->setValueExplicit($data['auth_log_list'][0]['auth_desc'], $contentType); $sheet->mergeCells('E17:H19')->getCellByColumnAndRow(5, 17)->setValueExplicit($data['auth_log_list'][1]['auth_desc'], $contentType); $sheet->mergeCells('I17:K19')->getCellByColumnAndRow(9, 17)->setValueExplicit($data['auth_log_list'][2] ? $data['auth_log_list'][2]['auth_desc'] : '無需審核', $contentType); $sheet->mergeCells('A20:B21')->getCellByColumnAndRow(1, 20)->setValueExplicit('財務', $contentType); $sheet->mergeCells('C20:K21')->getCellByColumnAndRow(3, 20)->setValueExplicit('', $contentType);封裝
複雜點
唯一需要計算一下的就是mergeCells()方法和getCellByColumnAndRow()方法的參數
$sheet->mergeCells($start.':'.$end)->getCellByColumnAndRow($column_start, $row_start)->setValueExplicit($column[2], $contentType);但是通過暴力破解的過程也很好得到規律。
通用方法
public static function exportSpecialExcel($data, $filename='', $post=false) { // 此處只需要導出單條數據，對記憶體沒要求， // ini_set('memory_limit', '3072M'); if (empty($filename)) { $filename = date('Y-m-d') . '導出表格'; } $pathinfo = pathinfo($filename); if (empty($pathinfo['extension']) || !in_array($pathinfo['extension'], ['xls', 'xlsx'])) { $filename = $filename . '.xlsx'; } $spreadsheet = new Spreadsheet(); $sheet= $spreadsheet->getActiveSheet(); $contentType = DataType::TYPE_STRING; /** * column * 0 - 行高 * 1 - 列寬 * 2 - 數據內容 */ $row_start = 1; foreach ($data as $row) { $column_start = 1; foreach ($row as $column) { $start = (static::$column_header[$column_start]) . $row_start; $end = (static::$column_header[$column_start + $column[1] - 1]).($row_start + $column[0] - 1); $sheet->mergeCells($start.':'.$end)->getCellByColumnAndRow($column_start, $row_start)->setValueExplicit($column[2], $contentType); $column_start += $column[1]; } $row_start += $row[0][0]; } $styleArray = [ 'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true ], ]; $sheet->getStyle('A1:K21')->applyFromArray($styleArray); header('Content-Description: File Transfer'); header('Expires: 0'); header('Pragma: public'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告訴瀏覽器輸出07Excel文件 header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); $writer = new Xlsx($spreadsheet); $writer->save('php://output'); //刪除清空： LogHelper::access(memory_get_peak_usage(), 'export_memory'); $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; }業務數據
每個子元素數組由行高、列寬、數據內容三者組成
$showData = [ [ [2, 11, '廣告請款單'], ], [ [2, 2, '申請時間'], [2, 3, date('Y-m-d H:i:s', $data['apply_clear_time'])], [2, 2, '廣告編號'], [2, 4, $data['ad_no']], ], [ [2, 2, '收款單位'], [2, 9, $data['real_name']], ], [ [2, 2, '銀行賬戶'], [2, 9, $data['bank_no']], ], [ [2, 2, '開戶行'], [2, 9, $data['bank_name']], ], [ [2, 2, '金額'], [2, 3, $data['amount']], [2, 2, '大寫金額'], [2, 4, $data['amount_desc']], ], [ [2, 2, '備註'], [2, 9, $data['desc']], ], [ [2, 4, '廣告BD審核'], [2, 4, '廣告主管審核'], [2, 3, '總經理審核'], ], [ [3, 4, $data['auth_log_list'][0]['auth_desc']], [3, 4, $data['auth_log_list'][1]['auth_desc']], [3, 3, isset($data['auth_log_list'][2]) ? $data['auth_log_list'][2]['auth_desc'] : '無需審核'], ], [ [2, 2, '財務'], [2, 9, ''], ], ];如此便實現了本次的業務需求，也便於今後相關業務功能的實現
參考資料
- github-PhpSpreadsheet: github源碼庫，可學習安裝
- phpspreadsheet 文檔 全英文文檔
- phpspreadsheet api 庫的類和方法說明
- CSDN-詳解PhpSpreadsheet設置單元格：由官方文檔整理出的部分使用案例，但過於簡單，直接使用仍然會出現問題
