>[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); ~~~