欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

phpExcel数据导入

程序员文章站 2022-07-13 13:17:01
...

简介:PHPExcel 是用来操作Office Excel 文档的一个PHP类库,它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格,如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML等等。

版本:PHPExcel-1.8
需求描述:需要导出某某某表数据
优点:能缓解内存溢出问题
类库下载:https://download.csdn.net/download/angus_01/10503746

public function upload() {
        ini_set('memory_limit','1024M');
        if (!empty($_FILES))
        {
            $config = array(
                'exts' => array('xlsx','xls'),
                'maxSize' => 3145728000,
                'rootPath' =>"./Public/",
                'savePath' => '/Uploads/',
                'subName' => array('date','Ymd'),
            );
            $upload = new \Think\Upload($config);
            if (!$info = $upload->upload())
            {
                $this->error($upload->getError());
            }
            vendor("PHPExcel.PHPExcel.Classes.PHPExcel");
            $file_name=$upload->rootPath.$info['file_stu']['savepath'].$info['file_stu']['savename'];
            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
            if ($extension == 'xlsx') {
                $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
                $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
            }
            else if ($extension == 'xls')
            {
                $objReader =\PHPExcel_IOFactory::createReader('Excel5');
                $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
            }
            $sheet =$objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow();//取得总行数
            $highestColumn =$sheet->getHighestColumn(); //取得总列数
            for($i=2;$i<=$highestRow;$i++)
            {
                $data['id'] ='';
                $data['name'] =$objPHPExcel->getActiveSheet()->getCell("A" .$i)->getValue();
                $data['sex'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
                $data['mobile'] = $objPHPExcel->getActiveSheet()->getCell("C". $i)->getValue();
                $data['id_no'] =$objPHPExcel->getActiveSheet()->getCell("D" . $i)->getValue();
                $data['area'] =$objPHPExcel->getActiveSheet()->getCell("E" . $i)->getValue();
                $data['city'] =$objPHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
                $data['bx_type_cd'] ='0'.$objPHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();
                $data['pabxtype'] = '03';
                $data['birth'] = substr($data['id_no'], 6, 4).'-'.substr($data['id_no'], 10, 2).'-'.substr($data['id_no'], 12, 2);
                print_r($data);die;
                M('excel')->add($data);
            }
            $this->success('导入成功!');
        }else{
            $this->error("(⊙o⊙)~没传数据就导入?!你在逗我?!");
        }
    }