TP5.1+PHPExcel导入导出excel

一、导入excel

1.读取文件

     //信息导入
    public function upload() {
        // 获取表单上传文件
        $request = new Request();
        $file = $request->file('excelData');
        //文件保存路径
        $rootPath = App::getRootPath();
        $filepath = $rootPath."public/excel";

        $info = $file->validate(['size' => 1024 * 1024 * 80, 'ext' => 'xls,xlsx'])->move($filepath,false);
        if ($info) {
            return $this->import($filepath . '/' . $info->getSaveName());
        } else {
            // 上传失败获取错误信息
            return $file->getError();
        }
    }

2.导入数据的处理

 //导入数据方法
    public function import($filename)
    {
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        //$PHPExcel = new \PHPExcel();
        //文件格式
        $exts = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );
        if ($exts == 'xls') {
            include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php";
            $PHPReader = new \PHPExcel_Reader_Excel5();
        } else if ($exts == 'xlsx') {
            include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel2007.php";
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        } else if ($exts == 'csv') {
            include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/CSV.php";
            $PHPReader = new \PHPExcel_Reader_CSV();
        } else {
            die('文件格式不正确');
        }
        //载入文件
        $PHPExcel = $PHPReader->load($filename);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet = $PHPExcel->getSheet(0);
        //获取总列数
        $allColumn = $currentSheet->getHighestColumn();
        //获取总行数
        $allRow = $currentSheet->getHighestRow();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        $data = [];
        //从哪列开始，2表示第2行
        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            //从哪列开始，A表示第一列
            for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                //数据坐标
                $address = $currentColumn . $currentRow;
                if($currentRow > $allRow){
                    if($currentColumn == 'A'){
                        $data[$currentRow]["phonecard_number"] =  \PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell($address)->getValue());
                    }else if($currentColumn == 'B'){
                        $data[$currentRow]["phonecard_refill"] =  \PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell($address)->getValue());
                    }else if($currentColumn == 'C'){
                        //Excel 已经帮我们内置了一些处理时间格式的方法的
                        $data[$currentRow]["recharge_time"] = (string)$this->excelTime(  \PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell($address)->getValue()));

                    }else if($currentColumn == 'D'){
                        $data[$currentRow]["rechargeable_person"] =  \PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell($address)->getValue());
                    }
                }else{
                    //读取到的数据，保存到数组$arr中
                    if($currentColumn == 'A'){
                        $data[$currentRow]["phonecard_number"] = $currentSheet->getCell($address)->getValue();
                    }else if($currentColumn == 'B'){
                        $data[$currentRow]["phonecard_refill"] = $currentSheet->getCell($address)->getValue();
                    }else if($currentColumn == 'C'){

                        $data[$currentRow]["recharge_time"] =(string)$this->excelTime( $currentSheet->getCell($address)->getValue());
                    }else if($currentColumn == 'D'){
                        $data[$currentRow]["rechargeable_person"] = $currentSheet->getCell($address)->getValue();
                    }
                }
            }
        }
        /*$phonecardrefill = new PhonecardRefill();
        $phonecardrefill->saveAll($data);*/
        Db::name('phonecard_refill')->insertAll($data);
    }

3.中间可能会出现excel中填写的时间导入之后会为一个float型的五位数

//上传的时间是变为五位数解决方法
    function excelTime($date, $time = false) {
        if(function_exists('GregorianToJD')){
            if (is_numeric( $date )) {
                $jd = GregorianToJD( 1, 1, 1970 );
                $gregorian = JDToGregorian( $jd + intval ( $date ) - 25569 );
                $date = explode( '/', $gregorian );
                $date_str = str_pad( $date [2], 4, '0', STR_PAD_LEFT )
                    ."-". str_pad( $date [0], 2, '0', STR_PAD_LEFT )
                    ."-". str_pad( $date [1], 2, '0', STR_PAD_LEFT )
                    . ($time ? " 00:00:00" : '');
                return $date_str;
            }
        }else{
            $date=$date>25568?$date+1:25569;
            /*There was a bug if Converting date before 1-1-1970 (tstamp 0)*/
            $ofs=(70 * 365 + 17+2) * 86400;
            $date = date("Y-m-d",($date * 86400) - $ofs).($time ? " 00:00:00" : '');
        }
        return $date;
    }

二、excel导出数据

 

 function exportPhonecard(){
        $list = array(array('id'=>1,'phonecard_number'=>'15573201650','phonecard_refill'=>'100','recharge_time'=>'2019-08-22 09:56:19','rechargeable_person'=>'李默然'));
        $indexKey = array('id','phonecard_number','phonecard_refill','recharge_time','rechargeable_person');
        $filename ="20190822";
        return $this->exportExcel($list,$filename,$indexKey);
    }



    /**
     * 创建(导出)Excel数据表格
     * @param  array   $list        要导出的数组格式的数据
     * @param  string  $filename    导出的Excel表格数据表的文件名
     * @param  array   $indexKey    $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
     * @param  array   $startRow    第一条数据在Excel表格中起始行
     * @param  [bool]  $excel2007   是否生成Excel2007(.xlsx)以上兼容的数据表
     * 比如: $indexKey与$list数组对应关系如下:
     *     $indexKey = array('id','username','sex','age');
     *     $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));
     */
    function exportExcel($list,$filename,$indexKey,$startRow=1,$excel2007=false){
        //文件引入
        include "../vendor/phpoffice/phpexcel/Classes/PHPExcel.php";

        if(empty($filename)) $filename = time();

        if( !is_array($indexKey)) return false;

        $header_arr = 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');
        //初始化PHPExcel()
        $objPHPExcel = new \PHPExcel();

        //设置保存版本格式
        if($excel2007){
            include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel2007.php";
            $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
            $filename = $filename.'.xlsx';
        }else{
            include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5.php";
            $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
            $filename = $filename.'.xls';
        }


        $objActSheet = $objPHPExcel->getActiveSheet();

        //设置表格格式
        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->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->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);


        //设置行高度
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

        //set font size bold
        $objPHPExcel->getActiveSheet()->getStyle()->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getFont()->setBold(true);

        //合并cell
        $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');

        //设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


  /*      $objActSheet->setCellValue('A2',  "id");
        $objActSheet->setCellValue('B2',  "手机号");
        $objActSheet->setCellValue('C2',  "充值金额");
        $objActSheet->setCellValue('D2',  "充值时间".date('Y-m-d H:i:s'));
        $objActSheet->setCellValue('E2',  "充值负责人");*/

        //接下来就是写数据到表格里面去
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '充值信息数据汇总  时间:'.date('Y-m-d H:i:s'))
            ->setCellValue('A2', 'id')
            ->setCellValue('B2', '手机号')
            ->setCellValue('C2', '充值金额')
            ->setCellValue('D2', '充值时间')
            ->setCellValue('E2', '充值负责人');


        $startRow = 3;
        foreach ($list as $row) {
            foreach ($indexKey as $key => $value){
                //这里是设置单元格的内容
                $objActSheet->setCellValue($header_arr[$key].$startRow,$row[$value]);
            }
            $startRow++;
        }

        // 下载这个表格，在浏览器输出
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$filename.'');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }