thinkphp6 + phpexcel 导入导出数据，设置特殊表格

第一步：安装excel，使用composer安装，切换到项目根目录下面，输入composer require phpoffice/phpexcel，然后就等待安装完成。

安装之后的目录：

第二步：引入相关类

第三步： 写导出、导入代码

<?php
 
namespace app\admin\controller;
 
use app\BaseController;
 
 
class Index extends BaseController
{
    public function index()
    {
    }
 
 
    /**
     * excel 读取导入
     * @return \think\response\Json|void
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function impExcel()
    {
        //读取excel文件
        $objPHPExcel = new \PHPExcel();
        $file_name = "C:\Users\EDZ\Downloads\智能匹配导入数据(1).xlsx";
        $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
        $excel_array = $obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
        array_shift($excel_array);  //删除第一个数组(标题);
        $data = [];
        $i = 0;
        foreach ($excel_array as $k => $v) {
            $data[$k]['id'] = $v[0];
            $data[$k]['name'] = $v[1];
            $data[$k]['image'] = $v[2];
            $i++;
        }
        return json($data);
    }
 
    /**
     * eccel导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
 
    public function expExcel()
    {
 
        // 1.选取表中要输出数据
        $con = array(
            [
                'id' => 1,
                'name' => '名字',
                'image' => '头像'
            ],
            [
                'id' => 2,
                'name' => '名字2',
                'image' => '头像2'
            ],
 
        );
 
        //没有第二部
 
        //3.实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //4.激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '姓名')
            ->setCellValue('C1', '性别')
            ->setCellValue('D1', '年龄')
            ->setCellValue('E1', '电话')
            ->setCellValue('F1', '地址')
            ->setCellValue('G1', '详细地址');
        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(30);
        //6.循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($con); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $con[$i]['id']);//ID
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $con[$i]['name']);//姓名
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $con[$i]['image']);//性别
        }
        //7.设置保存的Excel表格名称
        $filename = 'user' . date('ymd', time()) . '.xls';
        //8.设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('user');
        //9.设置浏览器窗口下载表格
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename . '"');
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        $objWriter->save('php://output');
        exit;
 
 
    }
 
    public function hello($name = 'ThinkPHP6')
    {
        return 'hello,' . $name;
    }
}

第四步： 如下报错解决办法：

解决直接简单粗暴 更改phpexcel包文件

continue;改成break;      //再去试试就可以了

Tags： PHP