PHPExcel-1.8.1导入、导出Excel表格-白红宇

PHPExcel-1.8.1导入、导出Excel表格

阅读量：5888 次

发布时间：2019-06-19

本文共 10713 字，大约阅读时间需要 35 分钟。

仅留作记录使用：

/**     * 读取文件方案一     */    public function readExcel()    {                require_once VENDOR_PATH . 'PhpExcel/PHPExcel/IOFactory.php';                // 循环遍历数据        $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); // 创建一个2007的读取对象        $file = getcwd() . '/internalAPNAndMCC.xlsx';        $objPHPExcel = $objReader->load($file); // 读取一个xlsx文件        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { // 遍历工作表            echo 'Worksheet - ' , $worksheet->getTitle() , PHP_EOL;            foreach ($worksheet->getRowIterator() as $row) { // 遍历行                echo 'Row number - ' , $row->getRowIndex(), PHP_EOL;                $cellIterator = $row->getCellIterator(); // 得到所有列                $cellIterator->setIterateOnlyExistingCells(false); // 让迭代器只对现有的单元进行循环                foreach ($cellIterator as $cell) { // 遍历列                    if (!is_null($cell)) { //如果列不是空就得到它的坐标和计算的值                        echo 'Cell - ', $cell->getCoordinate(), '-', $cell->getCalculatedValue() , PHP_EOL;                    }                }            }                        }    }

使用TP3.2.3上传&导入数据库：

/**     * 批量导入产品     */    public function p_bulk_import(){        $file = $_FILES['excel'];        if ($file['name']) {            $upload = new \Think\Upload();            $upload->maxSize   =     3145728 ;// 设置附件上传大小            $upload->exts      =     array('xls', 'xlsx', 'csv');// 设置附件上传类型            $upload->rootPath  =     UPLOAD_DIR; // 设置附件上传根目录            $upload->savePath  =     'excel/'; // 设置附件上传（子）目录            //$upload->autoSub   = 	 false;//是否使用子目录保存上传文件            $info   =   $upload->upload();            if ( !$info ) {// 上传错误提示错误信息                $this->error($upload->getError());            } else {                foreach ($info as $file) {                    $file_path = $file['savepath'].$file['savename'];                }            }        }            // 引入PHPEXCEL类        require_once VENDOR_PATH.'PhpExcel/PHPExcel/IOFactory.php';        require_once VENDOR_PATH.'PhpExcel/PHPExcel.php';        // 判断文件版本，选择对应的解析文件        if ('xlsx' == $info['excel']['ext']) {            require_once VENDOR_PATH.'PhpExcel/PHPExcel/Reader/Excel2007.php';            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');        } else {            require_once VENDOR_PATH.'PhpExcel/PHPExcel/Reader/Excel5.php';            $objReader = \PHPExcel_IOFactory::createReader('Excel5');        }            // 解析Excel文件        $objPHPExcel = $objReader->load(getcwd() . "/" . UPLOAD_DIR . $file_path);        // 读取第一个工作表(编号从 0 开始)        $sheet = $objPHPExcel->getSheet(0);        // 取得总行数        $highestRow = $sheet->getHighestRow();        // 取得总列数        $highestColumn = $sheet->getHighestColumn();            // 循环读取excel文件,读取一条,插入数组一条        for ($j=2;$j<=$highestRow;$j++) {            for ($k='A';$k<=$highestColumn;$k++) {                // 读取单元格                $examPaper_arr[$j][$k] = $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();            }        }            $sum = 0;        // 重复总数        $user_repeat = 0;        $user = M("apn_new");        $tour_level = M("product_new");        // 开启事务        $user->startTrans();        foreach ($examPaper_arr as $key=>$value) {//             print_r($key . '==>' . $value[A] . '\r\n' . $value[B]);            if ($user->where("tel='$value[C]'")->find()) {                $user_repeat++;            } else {                $data['name'] = empty($value['C']) ? 0 : $value['C'];                $data['password'] = empty($value['C']) ? 0 : md5($value['C']);                $data['tel'] = empty($value['C']) ? 0 : $value['C'];                $data['create_time'] = time();                $uid = $user->add($data);                if ($uid) {                    // 判断导游的类型，0：头目                    if (0 == $value['E']) {                        $tid = $tour_level->where("id_card='$value[D]'")->find();                        if (!$tid) { // 未找到导游头目                            $t_data['name'] = empty($value['B']) ? 0 : $value['B'];                            $t_data['id_card'] = empty($value['D']) ? 0 : $value['D'];                            $t_data['device_id'] = $uid;                            $t_data['created_time'] = date("Y.m.d");                            $t_data['level'] = 0;                            $t_data['fid'] = 0;                            $fid = $tour_level->add($t_data);                        } else {                            $fid = $tid;                        }                    } else {                        $t_data['name'] = empty($value['B']) ? 0 : $value['B'];                        $t_data['id_card'] = empty($value['D']) ? 0 : $value['D'];                        $t_data['device_id'] = $uid;                        $t_data['created_time'] = date("Y.m.d");                        $t_data['level'] = 1;                        $t_data['fid'] = !isset($fid) ? 3 : $fid;                        $tour_level->add($t_data);                    }                    // 提交事务                    $user->commit();                } else {                    // 事务回滚                    $user->rollback();                }                $sum++;            }        }            echo "上传结束
插入总数：".$sum.";重复总数：".$user_repeat;die;    }

/**     * 生成Excel、csv、pdf表格     */    public function generalExcel()    {        // 引入PHPEXCEL类        require_once VENDOR_PATH.'PhpExcel/PHPExcel/IOFactory.php';        require_once VENDOR_PATH.'PhpExcel/PHPExcel.php';        // 判断文件版本，选择对应的解析文件        require_once VENDOR_PATH.'PhpExcel/PHPExcel/Reader/Excel2007.php';                $objPHPExcel = new \PHPExcel();        // 设置Excel表格参数        $objPHPExcel->getProperties()                    ->setCreator("Maarten Balliauw")                    ->setLastModifiedBy("Maarten Balliauw")                    ->setTitle("Office 2007 XLSX Document")                    ->setSubject("Office 2007 XLSX Document")                    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")                    ->setKeywords("office 2007 openxml php")                    ->setCategory("Test result file");                // 给表格添加数据        $objPHPExcel->setActiveSheetIndex(0)             //设置第一个内置表（一个xls文件里可以有多个表）为活动的                    ->setCellValue( 'A1', 'Hello' )         //给表的单元格设置数据                    ->setCellValue( 'B2', 'world!' )      //数据格式可以为字符串                    ->setCellValue( 'C1', 12)            //数字型                    ->setCellValue( 'D2', 12)            //                    ->setCellValue( 'D3', true )           //布尔型                    ->setCellValue( 'D4', '=SUM(C1:D2)' );//公式                //得到当前活动的表,注意下文教程中会经常用到$objActSheet        $objActSheet = $objPHPExcel->getActiveSheet();        // 位置bbb  *为下文代码位置提供锚        // 给当前活动的表设置名称        $objActSheet->setTitle('Simple2222');        //         日期格式      锚：bbb        //获得秒值变量        $dateTimeNow = time();        //三个表格分别设置为当前实际的日期格式、时间格式、日期和时间格式        //首先将单元格的值设置为由PHPExcel_Shared_Date::PHPToExcel方法转换后的excel格式的值，然后用过得到该单元格的样式里面数字样式再设置显示格式        $objActSheet->setCellValue( 'C9', \PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));        $objActSheet->getStyle( 'C9')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);        $objActSheet->setCellValue( 'C10', \PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));        $objActSheet->getStyle( 'C10')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);        $objActSheet->setCellValue( 'C10', \PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));        $objActSheet->getStyle( 'C10')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);        //将E4到E13的数字格式设置为EUR        $objPHPExcel->getActiveSheet()->getStyle( 'E4:E13')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);        //         设置列的宽度        $objActSheet->getColumnDimension( 'B')->setAutoSize(true);   //内容自适应        $objActSheet->getColumnDimension( 'A')->setWidth(30);         //30宽                // 创建一个富文本框(实例)        $objRichText = new \PHPExcel_RichText();        $objRichText->createText("unless specified otherwise on the invoice.");        // 将文字写入到A18单元格中        $objPHPExcel->getActiveSheet()->getCell("A28")->setValue("$objRichText");        // 合并、拆分单元格        $objPHPExcel->getActiveSheet()->mergeCells('A28:B28'); // A28:B28合并        $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); // A28:B28再拆分        // object        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');        $objWriter->save('myexcel.xlsx');                //         设置表格样式和数据格式//         设置默认的字体和文字大小        $objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');        $objPHPExcel->getDefaultStyle()->getFont()->setSize(20);        //         2、提示下载文件        // 生成2003excel格式的xls文件//         header('Content-Type: application/vnd.ms-excel');//         header('Content-Disposition: p_w_upload;filename="01simple.xls"');//         header('Cache-Control: max-age=0');        //         $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//         $objWriter->save('php://output');//         exit;                //         生成2007excel格式的xlsx文件        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');        header('Content-Disposition: p_w_upload;filename="01simple.xlsx"');        header('Cache-Control: max-age=0');                $objWriter = \PHPExcel_IOFactory:: createWriter($objPHPExcel, 'Excel2007');        $objWriter->save( 'php://output');        exit;                // 下载一个pdf文件//         header('Content-Type: application/pdf');//         header('Content-Disposition: p_w_upload;filename="01simple.pdf"');//         header('Cache-Control: max-age=0');        //         $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');//         $objWriter->save('php://output');//         exit;                // 生成一个pdf文件//         $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');//         $objWriter->save('a.pdf');                // CSV 文件        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')->setDelimiter(',' )  //设置分隔符                                                ->setEnclosure('"' )    //设置包围符                                                ->setLineEnding("\r\n" )//设置行分隔符                                                ->setSheetIndex(0)      //设置活动表                                                ->save(str_replace('.php' , '.csv' , __FILE__));                var_dump($objWriter);    }

关于PHPExcel的详细操作方法可以参考

转载于:https://blog.51cto.com/laok8/1933911

你可能感兴趣的文章

IE8 中 session 管理的变化

查看>>

学习Unix怎么安装机器？《精通Unix下C语言与项目实践》读书笔记（14）

查看>>

《从零开始学Swift》学习笔记（Day 21）——函数返回值

查看>>

AD的备份与标准还原：深入浅出Active Directory系列(四)

查看>>

编译可在Android上运行的可执行文件：C/C++交叉编译环境