PHP tp6 exce导入导出

安装

composer require phpoffice/phpspreadsheet

建立封装类
Excel.php

<?php
//根据自己的文件位置更改命名空间
namespace app\admin\library;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\exception\ValidateException;
use think\facade\Filesystem;

class Excel
{
/**
* @param string $filename
* @return array|string
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
*/
public static function importExcel($filename = "")
{
$file[] = $filename;

try {
// 验证文件大小，名称等是否正确
validate(['file' => 'fileSize:51200|fileExt:xls,xlsx'])
->check($file);
// 将文件保存到本地
$savename = Filesystem::disk('public')->putFile('file', $file[0]);
// 截取后缀
$fileExtendName = substr(strrchr($savename, '.'), 1);
// 有Xls和Xlsx格式两种
if ($fileExtendName == 'xlsx') {
$objReader = IOFactory::createReader('Xlsx');
} else {
$objReader = IOFactory::createReader('Xls');
}
// 设置文件为只读
$objReader->setReadDataOnly(TRUE);
// 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
$objPHPExcel = $objReader->load(public_path() . 'admin/' . $savename);
//excel中的第一张sheet
$sheet = $objPHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumn = $sheet->getHighestColumn();
Coordinate::columnIndexFromString($highestColumn);
$lines = $highestRow - 1;
if ($lines <= 0) {
return "数据为空数组";
}
// 直接取出excle中的数据
$data = $objPHPExcel->getActiveSheet()->toArray();
// 删除第一个元素（表头）
array_shift($data);
//删除文件
unlink(public_path() . 'admin/' . $savename);
// 返回结果
return $data;
} catch (ValidateException $e) {
return $e->getMessage();
}
}

// 导出
public static function export($header = [], $type = true, $data = [], $fileName = "1910")
{
// 实例化类
$preadsheet = new Spreadsheet();
// 创建sheet
$sheet = $preadsheet->getActiveSheet();
// 循环设置表头数据
foreach ($header as $k => $v) {
$sheet->setCellValue($k, $v);
}
// 生成数据
$sheet->fromArray($data, null, "A2");
// 样式设置
$sheet->getDefaultColumnDimension()->setWidth(12);
// 设置下载与后缀
if ($type) {
header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
$type = "Xlsx";
$suffix = "xlsx";
} else {
header("Content-Type:application/vnd.ms-excel");
$type = "Xls";
$suffix = "xls";
}
ob_end_clean();//清楚缓存区
// 激活浏览器窗口
header("Content-Disposition:attachment;filename=$fileName.$suffix");
//缓存控制
header("Cache-Control:max-age=0");
// 调用方法执行下载
$writer = IOFactory::createWriter($preadsheet, $type);
// 数据流
$writer->save("php://output");
}
}

控制器调用：

/导入
public function index(Request $request)
{
// 接收文件上传信息
$files = $request->file("myfile");
// 调用类库，读取excel中的内容
$dataArr = Excel::importExcel($files);

        unset($dataArr[0]);
        unset($dataArr[1]);

        $arry = array();
        //查询所有的订单号
        $orderList=Db::table('ei_invoice_order')->where(['serve_id'=>$this->serve_id])->field('order_no')->select()->toArray();
        //将订单号转换成一维数组
        $order_no_arr=array_column($orderList,'order_no');
       
        $repeat_num = $err_num = 0;
        foreach ($dataArr as $k => $v) {
            //查询订单是否重复存在
            if(in_array($v[1],$order_no_arr) == true){
                //重复的订单统计
                //  $repeat_num += 1;
                $msg = ['code' => '0','msg' => '导入的订单数据已重复'];
                throw new \think\exception\HttpResponseException(json($msg));
                //  continue;
            }
            //导入的数据存在合并单元格的方式，去除为空的数组
            if(!is_null($v[0]) && !is_null($v[1]) && !is_null($v[2])){
                //未开票金额小于等于数据统计，并且不插入数据库
                if(intval($v[56]) <= 0){
                    $err_num += 1;
                    continue;
                }

                $arry[] = [
                    'order_state' => $v[0],
                    'order_no' => $v[1],
                    'order_name' => $v[2],
                    'order_time' => $v[27],
                    'latest_delivery_time' => $v[28],
                    'order_amount' => $v[30],
                    'payment_number' => $v[45],
                    'create_time' => $v[46],
                    'update_time' => $v[47],
                    'not_invoiced_amount' => $v[56],
                    'serve_id'=>$this->serve_id,
                    'status'=>0,
                ];
            }
        }
        // insertAll 返回成功的条数
        $ok_num = Db::table('ei_invoice_order')->insertAll($arry);
        if ($ok_num){
            $msg = ['code' => '1','msg' => '导入excel数据成功！'.'成功:'.$ok_num.'条数据,失败:'.$err_num.'条数据'];
            throw new \think\exception\HttpResponseException(json($msg));
        }
}


//导出
public function get()
{
// 设置表格的表头数据
$header = ["A1" => "编号", "B1" => "姓名", "C1" => "年龄"];
// 假设下面这个数组从数据库查询出的二维数组
$data = [
[1,'张三',18],
[2,'李四',19],
[3,'王五',22],
[4,'赵六',19],
[5,'李梅',17]
];
//也可从数据库里查值
$data=Db::name('数据库名')->select()->toArray();
// 保存文件的类型
$type= true;
// 设置下载文件保存的名称
$fileName = '信息导出'.time();
// 调用方法导出excel
Excel::export($header,$type,$data,$fileName);
}