phpexcel 导入导出

女爷i 2022-01-29 09:45 359阅读 0赞

//导出
    function export_Excel($data,$title){
        $path = dirname(__FILE__); //找到当前脚本所在路径
        $PHPExcel = new PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        $PHPSheet->setTitle($title); //给当前活动sheet设置名称
        //循环给表格赋值(包括标题)
        foreach ($data as $key=>$val){
            foreach ($val as $k=>$v){
                $PHPSheet->setCellValue($k,$v);
            }
        }
        $PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');//按照指定格式生成Excel文件，‘Excel2007’表示生成2007版本的xlsx，‘Excel5’表示生成2003版本Excel文件
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        //header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
        header('Content-Disposition: attachment;filename='.$title.'.xlsx');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $PHPWriter->save("php://output");
    }

/**
    
     *  数据导入
    
     * @param string $file excel文件
    
     * @param string $sheet
    
     * @return string   返回解析数据
    
     * @throws PHPExcel_Exception
    
     * @throws PHPExcel_Reader_Exception
    
     */
    
    function importExecl($filename='', $filedList = [], $isCutting = false, $size = 2000){
        $ext = pathinfo($filename, PATHINFO_EXTENSION);
        switch ($ext) {
            case 'xlsx':
                $objReader = PHPExcel_IOFactory::createReader('Excel2007');
                $objPHPExcel = $objReader->load($filename);//加载文件
                break;
            case 'xls':
                $objReader = PHPExcel_IOFactory::createReader('Excel5');
                $objPHPExcel = $objReader->load($filename);//加载文件
                break;
            case 'csv':
                $objReader = PHPExcel_IOFactory::createReader('CSV');
                $objPHPExcel = $objReader->setInputEncoding('GBK')->load($filename);//加载文件
                break;
            default:
                return [];
                break;
        }
        $highestRow = $objPHPExcel->getSheet()->getHighestRow();//取得总行数
        //$highestColumn = $objPHPExcel->getSheet()->getHighestColumn();//取得总列数
        $cellList = [];
        foreach ($filedList as $k => $v){
            $cellList[] = IntToChr($k);
        }
    
    //    if ($cellList[count($filedList) - 1] != $highestColumn){
    //        return [];
    //    }
        $data = [];
        for ($i = 0; $i < $highestRow - 1; $i++) {
            for ($j = 0; $j < count($cellList); $j++) {
                $data[$i][$filedList[$j]] = $objPHPExcel->getActiveSheet()->getCell($cellList[$j] . ($i + 2))->getFormattedValue();
                if (is_object($data[$i][$filedList[$j]])) $data[$i][$filedList[$j]] = $data[$i][$filedList[$j]]->__toString();
                $data[$i][$filedList[$j]] = preg_replace("/(\s|\ \;|　|\xc2\xa0)/", "", $data[$i][$filedList[$j]]);
            }
        }
        if ($isCutting) return array_chunk($data, $size);
        return $data;
    }

/**
     * 生成Excel列标
     * @param int $index 索引值
     * @param int $start 字母起始值
     * @return string 返回字母
     */
    function IntToChr($index, $start = 65)
    {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= IntToChr(floor($index / 26) - 1);
        }
        return $str . chr($index % 26 + $start);
    }