>[info] PHPExcel導入導出 ~~~ /** * 導入Excel * @author 牧羊人 * @since 2021/5/24 */ public function importExcel() { $error = ""; // 上傳文件(非圖片) $result = upload_file('file', '', $error); if (!$result) { return message($error, false); } // 文件路徑 $filePath = ATTACHMENT_PATH . $result['filePath']; if (!file_exists($filePath)) { return message("文件不存在", false); } // 讀取文件 $objPHPExcel = \PHPExcel_IOFactory::load($filePath); //獲取sheet表格數目 $sheetCount = $objPHPExcel->getSheetCount(); //默認選中sheet0表 $sheetSelected = 0; $objPHPExcel->setActiveSheetIndex($sheetSelected); //獲取表格行數 $rowCount = $objPHPExcel->getActiveSheet()->getHighestRow(); //獲取表格列數 $columnCount = $objPHPExcel->getActiveSheet()->getHighestColumn(); // 計數器 $totalNum = 0; // 循環讀取行數據 for ($row = 2; $row <= $rowCount; $row++) { // 獲取列值 $dataArr = array(); // 名稱 $dataArr['name'] = $objPHPExcel->getActiveSheet()->getCell("A" . $row)->getValue(); // 狀態 $status = $objPHPExcel->getActiveSheet()->getCell("B" . $row)->getValue(); $dataArr['status'] = $status == "正常" ? 1 : 2; // 排序 $dataArr['sort'] = $objPHPExcel->getActiveSheet()->getCell("C" . $row)->getValue(); // 插入數據 $levemModel = new \app\admin\model\Level(); $result = $levemModel->edit($dataArr); if ($result) { $totalNum++; } } return message("本次共導入{$totalNum}條數據", true); } ~~~ ~~~ /** * 導出Excel * * @return mixed */ public function exportExcel(){ // 參數 $param = request()->param(); // 取出參數 $keyword = getter($param, "keyword"); // 獲取導出的數據源 $list = $this->commend_commiserate::with(['user'])->where(function ($query) use ($keyword) { // 用戶/手機號 $query->whereIn('admin_id',function($query) use ($keyword){ $query->table('zg_user')->where('nickname|mobile', 'like', "%".$keyword."%")->field('id'); }); })->where(['mark' => 1])->select()->toArray(); // sheet工作表 $sheetIndex = 0; $setWidth = 20; // 實例化PHPExcel類 $objPHPExcel = new \PHPExcel(); // 激活當前的sheet表 $objPHPExcel->setActiveSheetIndex($sheetIndex); // 設置表格頭（即excel表格的第一行） $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('A1', '姓名'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('B1', '手機號'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('C1', '表彰級別'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('D1', '表彰時間'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('E1', '表彰原因'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('F1', '頒發單位'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('G1', '頒發時間'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('H1', '備注'); $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue('I1', '創建時間'); // 設置單元格寬度 $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('A')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('B')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('C')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('D')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('E')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('F')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('G')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('H')->setWidth($setWidth); $objPHPExcel->setActiveSheetIndex($sheetIndex)->getColumnDimension('I')->setWidth($setWidth); // 設置單元格加粗 $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true); // 設置表格頭水平居中 $objPHPExcel->setActiveSheetIndex($sheetIndex)->getStyle('A1:I1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 垂直居中 $objPHPExcel->setActiveSheetIndex($sheetIndex)->getStyle('A:I')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); // 水平居中 $objPHPExcel->setActiveSheetIndex($sheetIndex)->getStyle('A:I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 循環剛取出來的數組，將數據逐一添加到excel表格。 for ($i = 0; $i < count($list); $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $list[$i]['id']);// 職級ID $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $list[$i]['name']);// 職級名稱 $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $list[$i]['status']);// 職級狀態 $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $list[$i]['sort'] == 1 ? "正常" : "停用");// 職級排序 } // 設置保存的Excel表格名稱 $filename = '表彰慰問' . '_' . date('YmdHis', time()) . ".xlsx"; // 設置當前激活的sheet表格名稱 $objPHPExcel->getActiveSheet()->setTitle('表彰慰問'); // 保存本地文件 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(UPLOAD_TEMP_PATH . "/" . $filename); // 文件地址 $filePath = get_image_url(str_replace(ATTACHMENT_PATH, "", UPLOAD_TEMP_PATH . "/" . $filename)); return message("操作成功", true, $filePath); } ~~~ * **其他：** ~~~ // 設置默認行高 $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); // 第一行行高 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); // 字體 $objPHPExcel->setActiveSheetIndex($sheetSelectedIndex)->getStyle('A2:AH2')->getFont()->setName('宋體'); // 字體大小 $objPHPExcel->setActiveSheetIndex($sheetSelectedIndex)->getStyle('A2:AH2')->getFont()->setSize(9); // 背景顏色為黃色 $objPHPExcel->setActiveSheetIndex($sheetSelectedIndex)->getStyle('A2:AH2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('FFFF00'); // 邊框，顏色前面兩位00是透明度，后面6位上線顏色，必須加前面兩位透明度，不然顏色你會發現不是你的顏色 $styleArray = array( 'borders' => array( 'allborders' => array( 'style' => \PHPExcel_Style_Border::BORDER_THIN,//細邊框 // 'color' => array('argb' => '00c8cfd9'), ), ), ); $objPHPExcel->setActiveSheetIndex($sheetSelectedIndex)->getStyle('A2:AH2')->applyFromArray($styleArray); ~~~