注意：extend是放置第三方类的地方，不要乱配置命名空间那些，引起不必要的类错误
代码如下
<?php
namespace 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;
}
}
重点看前两个方法
uploadExcel
excel_download 引自：小郭同志：https://www.cnblogs.com/hopelooking/default.html?page=1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
相信自己，终会成功！