ThinkPHP5调用PHPExcel类实现导入导出

<?phpnamespace app\index\controller;use think\Controller;use think\Db;use think\Loader;use think\Request;//use lib\PExcel;class Four extends Controller{ ???//上传页面展示 ???public function index(){ ???????return view(); ???} ???//实现excel的导入操作 ???public function uploadExcel(){// ???????//接收文件// ???????$file = request()->file(‘excel‘);// ???????$info = $file->validate([‘size‘=>3145728,‘ext‘=>‘xlsx,xls,csv‘])->move(ROOT_PATH . ‘public‘ . DS . ‘static‘ . DS . ‘excels‘);// ???????if($info){// ???????????$excel_path = ROOT_PATH.‘public‘.DS.‘static‘.DS."excels".DS.$info->getSaveName();// ???????}else{// ???????????$excel_path = null;// ???????} ???????//防止乱码 ???????ini_set(‘memory_limit‘, ‘1024M‘);//设置php允许的文件大小最大值 ???????Loader::import(‘PHPExcel.Classes.PHPExcel‘);//必须手动导入，否则会报PHPExcel类找不到 ???????header("Content-type:text/html;charset=utf-8"); ???????//接收文件 ???????$file = $_FILES[‘excel‘]; ???????$extension = strtolower( pathinfo($file[‘name‘], PATHINFO_EXTENSION) ); ???????//实例化主文件 ???????$phpExcel = new \PHPExcel(); ???????//创建读入器 ???????if($extension==‘xlsx‘){ ???????????$objRender = \PHPExcel_IOFactory::createReader(‘excel2007‘); ???????}else{ ???????????$objRender = \PHPExcel_IOFactory::createReader(‘Excel5‘); ???????} ???????//读取excel文件 ???????$ExcelObj = $objRender->load($file[‘tmp_name‘]); ???????//查看有几个sheet ???????$sheetContent = $ExcelObj->getSheet(0)->toArray(); ???????unset($sheetContent[0]); ???????//取出文件的内容描述信息 ???????//$sheetColumn = $objRender->listWorksheetInfo($file[‘tmp_name‘]); ???????foreach ($sheetContent as $k => $v){ ???????????$arr[‘uname‘] = $v[0]; ???????????$arr[‘sex‘] = $v[1]; ???????????$arr[‘age‘] = $v[2]; ???????????$arr[‘class_name‘] = $v[3]; ???????????$res[] = $arr; ???????} ???????if(Db::name(‘excel_upload‘)->insertAll($res)){ ???????????$this->success("导入成功"); ???????}else{ ???????????$this->error("导入失败"); ???????} ???????//echo $sheetColumn[0][‘totalColumns‘];die; ???}

/************************************************************************************/
 ???//实现excel的导出操作 ???public function excel_download(){ ???????ini_set(‘memory_limit‘, ‘1024M‘);//设置php允许的文件大小最大值 ???????Loader::import(‘PHPExcel.Classes.PHPExcel‘);//必须手动导入，否则会报PHPExcel类找不到 ???????$data = Db::table("excel_upload")->select(); ???????//实例化excel(相当于创建了一个excel) ???????$objPHPExcel = new \PHPExcel(); ???????//获得当前活动的sheet ???????$objSheet = $objPHPExcel->getActiveSheet(); ???????//给当前的sheet修改名称 ???????$objSheet->setTitle("测试数据导出"); ???????//设置单元格垂直居中、水平居中 ???????$objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ???????//设置单元格格式范围的字体、字体大小、加粗 ???????$objSheet->getStyle("A1:Z1")->getFont()->setName("微软雅黑")->setSize(10)->setBold(true); ???????//给单元格填充背景色 ???????$objSheet->getStyle("A1:Z1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(‘#00FF00‘); ???????//填充边框 ???????$styleArray = [ ???????????‘borders‘=>[ ???????????????‘outline‘=>[ ???????????????????‘style‘=>\PHPExcel_Style_Border::BORDER_THICK, ???????????????????‘color‘ => [‘argb‘ => ‘#F0F8FF‘], ???????????????], ???????????], ???????]; ???????//填充样式 ???????$objSheet->getStyle("A1")->applyFromArray($styleArray); ???????$objSheet->getStyle("B1")->applyFromArray($styleArray); ???????$objSheet->getStyle("C1")->applyFromArray($styleArray); ???????$objSheet->getStyle("D1")->applyFromArray($styleArray); ???????$objSheet->getStyle("E1")->applyFromArray($styleArray); ???????//填充数据 ???????$objSheet->setCellValue("A1","ID") ???????????->setCellValue("B1","姓名") ???????????->setCellValue("C1","性别") ???????????->setCellValue("D1","年龄") ???????????->setCellValue("E1","班级"); ???????//到这里第一行就被占用了，所以要从第二行开始循环 ???????$j = 2; ???????foreach ($data as $k => $v){ ???????????$objSheet->setCellValue("A".$j,$v[‘id‘]) ???????????????->setCellValue("B".$j,$v[‘uname‘]) ???????????????->setCellValue("C".$j,$v[‘sex‘]) ???????????????->setCellValue("D".$j,$v[‘age‘]) ???????????????->setCellValue("E".$j,$v[‘class_name‘]); ???????????$j++; ???????}// ???????header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘);// ???????header(‘Content-Disposition: attachment;filename="‘ . date(‘Ymd‘) . ‘.xlsx"‘);// ???????header(‘Cache-Control: max-age=0‘);//// If you‘re serving to IE 9, then the following may be needed// ???????header(‘Cache-Control: max-age=1‘);//// If you‘re serving to IE over SSL, then the following may be needed// ???????header(‘Expires: Mon, 26 Jul 1997 05:00:00 GMT‘); // Date in the past// ???????header(‘Last-Modified: ‘ . gmdate(‘D, d M Y H:i:s‘) . ‘ GMT‘); // always modified// ???????header(‘Cache-Control: cache, must-revalidate‘); // HTTP/1.1// ???????header(‘Pragma: public‘); // HTTP/1.0// ???????$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007‘);// ???????$objWriter->save(‘php://output‘);// ???????exit; ???????//设置文件保存的命名、编码、同时开放保存路径的权限 ???????//命名 ???????$file_name = "上海软工专业高级成材率汇总 ".date("Y-m-d",time()).".xlsx"; ???????//编码 ???????header(‘Content-Type:application/vnd.ms-excel;charset=utf-8‘); ???????//告诉浏览器要输出的名称 ???????header("Content-Disposition:attachment;filename=$file_name"); ???????//禁止浏览器缓存 ???????header(‘Cache-Control:max-age=0‘); ???????//生成excel文件 ???????$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,‘Excel2007‘); ???????//彻底开放保存路径 ???????$objWriter->save(‘php://output‘); ???????exit(); ???} ???public function te(){ ???????$a = "<table><thead><tr><th style=‘color: #ffb454‘>adsf</th><th>中文</th></tr></thead><tbody><tr><td>123</td><td>123</td></tr></tbody></table>"; ???????$a = "\xEF\xBB\xBF" . $a; ???????$filename = date(‘Ymd‘).‘.xls‘; //设置文件名 ???????header( "Content-Type: application/vnd.ms-excel; name=‘excel‘" ); ???????header( "Content-type: application/octet-stream" ); ???????header( "Content-Disposition: attachment; filename=".$filename );//header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );////header( "Pragma: no-cache" );////header( "Expires: 0" ); ???????exit( $a ); ???} ???public function ttt(){ ???????ini_set(‘memory_limit‘, ‘1024M‘);//设置php允许的文件大小最大值 ???????Loader::import(‘PHPExcel.Classes.PHPExcel‘);//必须手动导入，否则会报PHPExcel类找不到 ???????$objPHPExcel = new \PHPExcel(); ???????$worksheet = $objPHPExcel->getActiveSheet(); ???????$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ???????$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER);// Set document properties ???????$objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ???????????->setLastModifiedBy("Maarten Balliauw") ???????????->setTitle("Office 2007 XLSX Test Document") ???????????->setSubject("Office 2007 XLSX Test Document") ???????????->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ???????????->setKeywords("office 2007 openxml php") ???????????->setCategory("Test result file"); ???????$objPHPExcel->setActiveSheetIndex(0) ???????????->setCellValue(‘A1‘, ‘昵称‘) ???????????->setCellValue(‘B1‘, ‘链接‘) ???????????->setCellValue(‘C1‘, ‘房间号‘) ???????????->setCellValue(‘D1‘, ‘分组‘);// Rename worksheet ???????$objPHPExcel->getActiveSheet()->setTitle(‘Simple‘);// Set active sheet index to the first sheet, so Excel opens this as the first sheet ???????$objPHPExcel->setActiveSheetIndex(0);// Redirect output to a client’s web browser (Excel2007) ???????header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘); ???????header(‘Content-Disposition: attachment;filename="‘ . date(‘Ymd‘) . ‘.xlsx"‘); ???????header(‘Cache-Control: max-age=0‘);// If you‘re serving to IE 9, then the following may be needed ???????header(‘Cache-Control: max-age=1‘);// If you‘re serving to IE over SSL, then the following may be needed ???????header(‘Expires: Mon, 26 Jul 1997 05:00:00 GMT‘); // Date in the past ???????header(‘Last-Modified: ‘ . gmdate(‘D, d M Y H:i:s‘) . ‘ GMT‘); // always modified ???????header(‘Cache-Control: cache, must-revalidate‘); // HTTP/1.1 ???????header(‘Pragma: public‘); // HTTP/1.0 ???????$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007‘); ???????$objWriter->save(‘php://output‘); ???????exit; ???}}

ThinkPHP5调用PHPExcel类实现导入导出

原文地址：https://www.cnblogs.com/afeige/p/9719956.html