PHP + CI4 的 PhpSpreadsheet 的處理

0、需要用到的 Library

use \PhpOffice\PhpSpreadsheet\Spreadsheet;

use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use \PhpOffice\PhpSpreadsheet\Reader\Xlsx;

use \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

參考資料：Welcome to PhpSpreadsheet's documentation - PhpSpreadsheet Documentation

 1、上傳 excel 檔案並讀取內容

//處理上傳

try {

            $thefile = $this->request->getFile('theuploadfile');

} catch (\Exception $e) {

echo $e;

exit;

}

try {

$path = $thefile->store();

} catch (\Exception $e) {

echo $e;

exit;

}

//確認檔案格式並讀取內容

$filename = WRITEPATH . 'uploads/' . $path;

$fileext = ucfirst($thefile->getClientExtension());

if (in_array($fileext, array('Xls', 'Xlsx'))) {

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($fileext);

$spreadsheet = $reader->load($filename);

$sheet = $spreadsheet->getSheet(0);

$data = $sheet->getCell('A1')->getValue();

}

2、產生 Excel 檔案

//生成試算表並設定格式

$spreadsheet = new Spreadsheet();

$spreadsheet->getDefaultStyle()->getFont()->setName('標楷體');

$spreadsheet->getDefaultStyle()->getFont()->setSize(10);

$sheet = $spreadsheet->getActiveSheet();

//填入內容

$sheet->setCellValue('B1', $pagetitle);

$sheet->mergeCells('B1:AO1');

$sheet->setCellValue('B2', $subdata);

$sheet->mergeCells('B2:AH2');

$sheet->setCellValueByColumnAndRow(2, 3, $data);

//輸出

$writer = new Xlsx($spreadsheet);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment; filename="' . urlencode($filename) . '"');

$writer->save('php://output');

3、讀取範本檔案並填入資料後輸出

//讀取範本

$path = WRITEPATH . 'uploads/' . $filename;

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');

$spreadsheet = $reader->load($path);

$sheet = $spreadsheet->getSheet(0);

//填入內容

$sheet->getCell('A1')->setValue($data);

//輸出

$writer = new Xlsx($spreadsheet);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment; filename="' . urlencode($outputfilename) . '"');

$writer->save('php://output');