PHPExcel操作

PHPEXCEL

 function readExcel($excelPath, $arrSheetNum=[0]){
        if(!is_array($arrSheetNum)){
            $arrSheetNum = [$arrSheetNum];
        }
        $inputFileType = \PHPExcel_IOFactory::identify($excelPath);
        $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($excelPath);
        $arrData = [];
        foreach ($arrSheetNum as $sheetNum) {
            $sheet = $objPHPExcel->getSheet($sheetNum);
            $sheetName = $sheet->getTitle();
            // 取得总行数
            $highestRow = $sheet->getHighestRow();
            // 取得总列数
            $highestColumn = $sheet->getHighestColumn();
            //从第一行开始读取数据
            $shared = new \PHPExcel_Shared_Date();
            //循环读取excel文件,读取一条,插入一条
            //从第一列开始读
            for ($j = 1; $j <= $highestRow; $j++) {
                //从A列读取数据
                for ($columnName = 'A'; $columnName <= $highestColumn; $columnName++) {
                    $cell = $sheet->getCell("$columnName$j");
                    $dataType = $cell->getDataType();
                    if ($dataType == \PHPExcel_Cell_DataType::TYPE_NUMERIC) {
                        $cellstyleformat = $cell->getStyle()->getNumberFormat();
                        $formatcode = $cellstyleformat->getFormatCode();
                        if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $formatcode)) {
                            $arrData[$sheetName][$j][$columnName] = gmdate("Y-m-d H:i:s", $shared->ExcelToPHP($objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue()));
                        } else {
                            $arrData[$sheetName][$j][$columnName] = is_object($objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue()) ? $objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue()->__toString() : $objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue();
                        }
                    } else {
                        // 读取单元格
                        $arrData[$sheetName][$j][$columnName] = is_object($objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue()) ? $objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue()->__toString() : $objPHPExcel->getActiveSheet()->getCell("$columnName$j")->getValue();
                    }
                }
            }
            $arrHead = $arrData[$sheetName][1];
            unset($arrData[$sheetName][1]);
        }
        return ['head' => $arrHead, 'data' => $arrData];

    }

function writeExcel($excelHead, $excelData, $excelPath=''){
        if(empty($excelPath)) {
            $excelPath = date("YmdHis") . mt_rand(1000, 9999) . ".xlsx" ;
        }
        $i = 0;
        $objPHPExcel = new \PHPExcel();
        foreach ($excelData as $sheet => $data){
            array_unshift($data, $excelHead);
            if($i>0) $objPHPExcel->createSheet();
            $objPHPExcel->setActiveSheetIndex($i);

            $objSheet = $objPHPExcel->getActiveSheet()->setTitle($sheet);
            $objSheet->fromArray($data);
            $i++;
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
        $objWriter->save($excelPath);
        return $excelPath;
    }