tp5 读取/下载 excel文件内容

小咪咪 2023-06-25 02:08 52阅读 0赞

## **tp5 读取/下载 excel文件内容** ##

1、下载一个excel模板  
导出函数

/**
     * 创建(导出)Excel数据表格
     * @param  array   $expTableData 		要导出的数组格式的数据
     * @param  string  $expTitle 	导出的Excel表格数据表的文件名
     * @param  array   $expCellName 	$list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
     */
    function exportExcel($expTitle,$expCellName,$expTableData){
        //文件引入
        vendor("PHPExcel.PHPExcel");  //  引入工具包
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
    
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
    
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        ob_end_clean();
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

函数写好，在进行导出

//excel模板
    public function excelModel()
    {
        $list = array();
        $head=array(array('username','姓名'),array('id_card','身份证号'),array('mobile','手机号码'),array('type','分类'),array('msg','留言'));
        $name ='批量导入模板表-'.date('Y-m-d H:i:s',time());
        exportExcel($name,$head,$list);
    }

当进行访问时，生成了一个excel文件。我们在里面加一些数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Njc5NjIw_size_16_color_FFFFFF_t_70]

2、读取excel内容

//读取excel文件内容
    public function do_upload(){
        vendor("PHPExcel.PHPExcel");
        //引入文件
        $objPHPExcel = new \PHPExcel();
        //获取表单上传文件
        $file = request()->file('file');
        $info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads');
    
        //数据为空返回错误
        if(empty($info)){
            $output['status'] = false;
            $output['info'] = '导入数据失败~';
            $this->ajaxReturn($output);
        }
        //获取文件名
        $exclePath = $info->getSaveName();
        //上传文件的地址
        $filename = ROOT_PATH . 'public' . DS . 'uploads'.DS . $exclePath;
        $extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );
        \think\Loader::import('PHPExcel.IOFactory.PHPExcel_IOFactory');
        if ($extension =='xlsx') {
            $objReader = new \PHPExcel_Reader_Excel2007();
            $objExcel = $objReader ->load($filename);
        } else if ($extension =='xls') {
            $objReader = new \PHPExcel_Reader_Excel5();
            $objExcel = $objReader->load($filename);
        }
        $excel_array=$objExcel->getsheet(0)->toArray();   //转换为数组格式
        array_shift($excel_array);  //删除第一个数组(标题);
        array_shift($excel_array);  //删除th
    
        $data=[];
        foreach ($excel_array as $k=>$v){
            $data[$k]["username"]=$v[0];//单号
            $data[$k]["id_card"]=$v[1];//类型名称
            $data[$k]["mobile"]=$v[2];
            $data[$k]["type"]=$v[3];
            $data[$k]["msg"]=$v[4];
        }
        $msg=[
            'Code' => '100000',
            'Msg'  => 'Success'
        ];
        $msg['Data']=$data;
        return json_encode($msg);
    }

我们使用postman测试一下。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Njc5NjIw_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Njc5NjIw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191224112200292.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Njc5NjIw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Njc5NjIw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191224112245980.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Njc5NjIw,size_16,color_FFFFFF,t_70