PHPexcel 操作例子

判断文件夹是否存在

if(! file_exists('./error')) {
    mkdir('./error', 0775, true);
}

phpexcel操作一对多表格

 /*
     * 导出套餐列表（包含菜式信息）
     * @author llj<1063944289@qq.com>
     */
    public function packageExport() {
        try {
            $fields = ['search', 'type'];
            $param = $this->_apiParam($fields);
            $where = [];
            if (isset($param['search']) && $param['search'] != '') {
                $where['name'] = ['like', '%' . $param['search'] . '%'];
            }
            if (isset($param['type']) && $param['type'] != '') {
                if (in_array($param['type'], [0, 1, 2])) {
                    if ($param['type'] == 0) {
                        $where['status'] = ['in', '0'];
                    } elseif ($param['type'] == 1) {
                        $where['status'] = ['in', '1'];
                    } else {
                        $where['status'] = ['in', '0,1'];
                    }
                } else {
                    return $this->errorResponse('type取值范围：0、1、2');
                }
            }
            $res = Package::packageExport($where);
            if (empty($res['info'])) {
                return $this->errorResponse('导出目标无数据');
            } else {
                $this->exportExcelOnetoMany(exportPrefix() . '_套餐列表', ['套餐名称', '套餐价格', '状态', '菜式名称', '菜式id'], $res['info'], [20, 10, 10, 50, 10], ['name', 'total_price', 'status'], ['dishes_name', 'name', 'id']);
            }
            return $this->successResponse('导出数据成功');
        } catch (\Exception $e) {
            return $this->errorResponse($e->getMessage());
        }
    }

• 数据格式：

/**
 * 数据格式：
 *
 * data = [
 *   0 => [
 *      'name' => '菜花系列',
 *      'total_price' => '100',
 *      'status' => '启用',
 *      'dishes_name' => [
 *          0 => [
 *              'name' => ‘红菜花’,
 *              'id'   => '5',
 *          ],
 *       ],
 *    ],
 *  ];
 *
 * 参数： firstField = ['name', 'total_price', 'status'];
 *       secondField = ['dishes_name', 'name', 'id'];
 */

/*
     * @param string $file_name 文件名
     * @param array $header_data 表头（包括附属字段)
     * @param array $data       数据
     * @param array $width      列宽（所有列宽度，包括附属字段）
     * @param array $firstField 字段
     * @param array $secondField  附属字段（data中包含另外一组二维数组）
     * @throws \Exception
     * @author llj <1063944289@qq.com>
     */
    public function exportExcel($file_name = '', $header_data = [], $data = [], $width = [], $firstField = [], $secondField = []) {
        //检查
        if (empty($file_name)) {
            throw new \Exception('导出文件名不能为空');
        }
        if (empty($header_data) || !is_array($header_data)) {
            throw new \Exception('导出表头不能为空');
        }
        if (isset($secondField) && (count($secondField) < 2)){
            throw new \Exception('附属字段错误');
        }

        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();


        //设置边框和水平垂直居中，定义里一个$styleArray，方便在往Excel中写入数据时，同时对单元格进行格式的设置：水平、垂直居中、加边框
        $styleArray = [
            'alignment' => [
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allborders' => [
                    'style' => \PHPExcel_Style_Border::BORDER_THIN
                ]
            ]
        ];
        //设置临时变量，用于处理合并单元格的范围
        $oldTemp = 2;//列名下第一行
//        $newTemp = 0;
        //firstField所占位置
        $firstUnit = count($firstField);

        if (isset($secondField) && (count($secondField) >= 1)) {
            $secondUnit = count($secondField) - 1;
        } else {
            $secondUnit = 0;
        }

        for ($i = 0; $i < count($width); $i++) {
            $PHPSheet->getColumnDimension(chr(ord("A") + $i))->setWidth($width[$i]);

        }
        for ($i = 0; $i < count($header_data); $i++) {
            $PHPSheet->setCellValue(chr(ord("A") + $i) . '1', $header_data[$i]);
            $PHPSheet->getStyle(chr(ord("A") + $i) . '1', $header_data[$i])->applyFromArray($styleArray);
        }

        for ($i = 0; $i < sizeof($data); $i++) {
            //判断是否有附属字段
            if (isset($data[$i][$secondField[0]][0][$secondField[1]])) {
                $tempCount = count($data[$i][$secondField[0]]);
            } else {
                $tempCount = 1;
            }
            $newTemp = $oldTemp + $tempCount;
            for ($j = 0; $j < $tempCount; $j++) {
                if (isset($data[$i][$secondField[0]][0][$secondField[1]])) {
                    if ($secondUnit >= 1) {
                        for ($k = 1; $k <= $secondUnit; $k++) {
                            $PHPSheet->setCellvalue(chr(ord("A") + $firstUnit + $k - 1) . ($j + $oldTemp), $data[$i][$secondField[0]][$j][$secondField[$k]]);
                        }
                    }
                }
                //设置格式
                if ($secondUnit >= 1) {
                    for ($k = 1; $k <= $secondUnit; $k++) {
                        $PHPSheet->getStyle(chr(ord("A") + $firstUnit + $k - 1) . ($j + $oldTemp))->applyFromArray($styleArray);
                    }
                }
            }

            //设置firstField字段值
            for ($j = 0; $j < $firstUnit; $j++) {
                $PHPSheet->setCellValue(chr(ord("A") + $j) . $oldTemp, $data[$i][$firstField[$j]]);
            }

            //若附属字段数量大于1，合并firstField字段

            if (isset($secondField) && (count($data[$i][$secondField[0]]) > 1)) {
                for ($j = 0; $j < $firstUnit; $j++) {
                    $PHPSheet->mergeCells(chr(ord("A") + $j) . $oldTemp . ':' . chr(ord("A") + $j) . ($newTemp - 1));
                }
            }
            //设置单元格格式：水平、垂直居中、加边框
            for ($j = 0; $j < $firstUnit; $j++) {
                $PHPSheet->getStyle(chr(ord("A") + $j) . $oldTemp . ':' . chr(ord("A") + $j) . ($newTemp - 1))->applyFromArray($styleArray);
            }

            $oldTemp = $newTemp;
        }

//        $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');

        $PHPSheet->setTitle($file_name);
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        header('Content-Type:application/vnd.ms-excel');

        //中文名兼容各种浏览器
        $ua = $_SERVER["HTTP_USER_AGENT"];
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="' . $file_name . '.xls"');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\'' . $file_name . '.xls"');
        } else {
            header('Content-Disposition: attachment; filename="' . $file_name . '.xls"');
        }
//        header('Content-Disposition: attachment;filename="'.$file_name.'.xls"');

        header('Cache-Control: max-age=0');
        $PHPWriter->save("php://output");
    }

phpexcel 读文件

public function importExcel()
    {
        //只需要拿到上传临时文件不用做保存
        $upload = new \Think\Upload();
        $upload->exts = ['xlsx', 'xls'];
        $upload->rootPath = './Public/upload/';
        $upload->savePath = '';
        $upload->autoSub = false;
        $info = $upload->upload();
        if(!$info) {// 上传错误提示错误信息
            $this->error($upload->getError());
        }else{// 上传成功
            //获取文件路径
            $file_path = $upload->rootPath . $info['excel']['savepath'] . $info['excel']['savename'];
            $PHPReader = new \PHPExcel_Reader_Excel2007();
            if (!$PHPReader->canRead($file_path)) {
                $PHPReader = new \PHPExcel_Reader_Excel5();
                if (!$PHPReader->canRead($file_path)) {
                    $this->error('excel载入失败');
                }
            }
            $PhpExcel = $PHPReader->load($file_path);
            $sheet = $PhpExcel->getSheet(0);
            $row = $sheet->getHighestDataRow(); //获取总行数
            //注意列数转换
            $column = \PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); //获取最大的列数
            $data = [];
            //从第二行开始，去掉第一行表头
            for ($i = 2; $i <= $row; $i++) {  //获取要导入的数据
                for ($j = 0; $j < $column; $j++) {
                    $val = $sheet->getCell(\PHPExcel_Cell::stringFromColumnIndex($j) . $i)->getCalculatedValue();
                    if (is_object($val)) {
                        //富文本转换字符串
                        $val = $val->__toString();
                    }
//                    if($val instanceof PHPExcel_RichText){ //富文本转换字符串
//                        $val = $val->__toString();
//                    }
                    $data[$i][] = $val ?: '';
                }
            }
            $assetData = [];
            //处理空行
            for ($k = 0; $k < count($data); $k++) {
                $emptyFlag = 0;
                for ($m = 0; $m < $column; $m++) {
                    if (empty($data[$k][$m])) {
                        $emptyFlag++;
                    };
                }
                if ($emptyFlag != $column) {
                    $assetData[] = $data[$k];
                }
            }
            //重新整理索引
            $assetData = array_merge($assetData);
            if (empty($assetData)) {
                $this->error('导入的数据为空');
            }
//            for ($j = 0; $j < $column; $j++) {
//                $val = $sheet->getCell(\PHPExcel_Cell::stringFromColumnIndex($j) . 1)->getCalculatedValue();
//                $text[] = $val;
//            }
//            var_dump($text);
            $add_data = [];
            foreach ($assetData as $key => $value) {
                $add_data[$key] = [
                    'asset_num' => $value[1],
                    'year' => $value[2],
                    'item_number' => $value[3],
                    'add_number' => $value[4],
                    'name'=>$value[6],
                    'count'=>$value[31],
                    'purse_time'=>$value[21],
                    'use_time'=>$value[17],
                    'pattern'=>$value[11],
                    'worn_out'=> 1,
                    'buy_person'=>$value[20],
                    'address'=>$value[19],
                    'suppliur'=>$value[22],
                    'standard'=>$value[12],
                    'dept_number'=>$value[5],
                    'campus'=>$value[7],
                    'sort_number'=>$value[8],
                    'guobiao'=>$value[9],
                    'books'=>$value[10],
                    'ovalues'=>$value[13],
                    'nvalues'=>$value[14],
                    'body_number'=>$value[15],
                    'product'=>$value[16],
                    'country_number'=>$value[18],
                    'addway'=>$value[23],
                    'userway'=>$value[24],
                    'recived_people'=>$value[25],
                    'save_person'=>$value[26],
                    'item_money'=>$value[27],
                    'addtime'=>$value[28],
                    'phone_number'=>$value[29],
                    'munit'=>$value[32],
                    'precious'=>$value[33],
                    'train'=>$value[34],
                    'notes'=>$value[35],
                    'tips'=>$value[36],
                    'exit_date'=>$value[37],
                    'exit_people'=>$value[38],
                    'exit_year'=>$value[39],
                    'exit_add'=>$value[40],
                    'exit_number'=>$value[41],
                    'exit_cause'=>$value[42],
                    'tag'=>$value[44],
                    'tag_date'=>$value[45],
                    'tag_people'=>$value[46],
                    'card'=>$value[47],
                    'card_date'=>$value[48],
                    'card_person'=>$value[49],
                    'check'=>$value[50],
                    'check_person'=>$value[51],
                    'check_date'=>$value[52],
                    'operator'=>$value[43],
                    'is_delete'=>0,
                    'update_time' => time(),
                    'create_time' => time(),
                ];
            }
            $asset_results = M('asset')->addAll($add_data);
            unlink($file_path);
            if ($asset_results != false) {
                $this->success('导入成功');
            } else {
                $this->error('导入失败');
            }
        }
    }