PHPexcel笔记

原创

kangjunfei 2017-12-20 13:01:31 ©著作权

©著作权归作者所有：来自51CTO博客作者kangjunfei的原创作品，请联系作者获取转载授权，否则将追究法律责任

首选去下载phpexcel文件包

1.phpexcel把excel表格数据导入mysql‘数据库中规定的表中前端： <tr> <td><table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="10%" height="30" align="right" bgcolor="#f2f2f2" class="left_txt2">上传文件</td> <td width="1%" bgcolor="#f2f2f2"> </td> <td width="32%" height="30" bgcolor="#f2f2f2"> <input type="file" name="excelPacket" class="file"> </td>

                                </tr>
                                <tr>
                                    <td height="30" colspan="4" align="center" class="left_txt"><input type="submit" name="button" id="button" value="创建" />
                                        &nbsp;
                                        <input type="reset" name="button2" id="button2" value="重置" /></td>
                                </tr>

                            </table></td>
                        </td>
                    </tr>

注：mysql表里的字段要和excel表格的表头一致方便导入 header("Content-type:text/html;charset=utf-8"); require_once 'backend_islogin.php'; require_once '../db/Db.php'; require_once '../backend/PHPExcel/PHPExcel.php'; //实例化类 $PHPReader = new PHPExcel_Reader_Excel2007(); //接收前端传过来的file文件 $fileExtArr = explode(".",$_FILES['excelPacket']['name']); $fileExt = ".".$fileExtArr[1]; //加密存放到指定文件夹下 $path=$_SERVER['DOCUMENT_ROOT'].'/excelupload/'.time().md5($_FILES['excelPacket']['name'].rand()).$fileExt; move_uploaded_file($_FILES['excelPacket']['tmp_name'],$path); if( ! $PHPReader->canRead($path)) { $PHPReader = new PHPExcel_Reader_Excel5(); if( ! $PHPReader->canRead($path)){ echo 'no Excel'; return ; } }

$PHPExcel = $PHPReader->load($path); //读取文件 $currentSheet = $PHPExcel->getSheet(0); //读取第一个工作簿 $allColumn = $currentSheet->getHighestColumn(); // 所有列数 $allRow = $currentSheet->getHighestRow(); // 所有行数

$data = array(); //下面是读取想要获取的列的内容

for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) { $data[] = array( 'name' => $currentSheet->getCell('A'.$rowIndex)->getValue(), 'brand' => $currentSheet->getCell('B'.$rowIndex)->getValue(), 'goods_code' => $currentSheet->getCell('C'.$rowIndex)->getValue(), 'credit_code' => $currentSheet->getCell('D'.$rowIndex)->getValue(), 'f_code' => $currentSheet->getCell('E'.$rowIndex)->getValue(), 'c_code' => $currentSheet->getCell('F'.$rowIndex)->getValue(), 'adapt_mod' => $currentSheet->getCell('G'.$rowIndex)->getValue(), 'oe_code' => $currentSheet->getCell('H'.$rowIndex)->getValue(), 'img_code' => $currentSheet->getCell('I'.$rowIndex)->getValue(), 'acc_info' => $currentSheet->getCell('J'.$rowIndex)->getValue(), 'user_id' => $_SESSION['session_id'], 'audit' => 0, ); } //执行数据库操作 $dbObj = new DB(); foreach($data as $row){ $oe_code = $row["oe_code"]; $oe_code = $dbObj->get_one("select 'oe_code' from tb_goods where oe_code = '$oe_code'"); if(!empty($oe_code)){ echo "<script> alert('商品重复请重新上传'); location.href = 'backend_product.php'; </script>"; break; }else{ $re = $dbObj -> insert("tb_goods",$row); if($re){ echo "<script>alert('编辑成功');location.href='backend_product.php';</script>"; } } }

2.phpexcel把excel表格数据修改，保存新的表格需求：修改当前excel表格的表头，表文件名换成新品牌的名字，sheet换成品牌源表头字段 id brand series car_name manufacturer model_year level engine case specifications structure max_speed integrated_fuel wheelbase number_doors number_seats fuel_volume fuel_volume inlet drive car_body parking_brake_type tyre_spec tire_spec 替换后的表头字段 id 品牌车系车型名称厂商年款级别发动机变速箱长宽高(mm) 车身结构最高车速(km/h) 工信部综合油耗(L/100km) 轴距(mm) 车门数(个) 座位数(个) 油箱容积(L) 发动机型号进气形式驱动方式车体结构驻车制动类型前轮胎规格后轮胎规格就是把因为除了id之外其它的换成汉字 sheet里的内容换成对应的品牌：修改后的样子：具体实现代码如下： <?php /**

Created by PhpStorm.
User: a-4
Date: 2017/12/19
Time: 15:04 */ require_once './PHPExcel/PHPExcel.php'; require_once './PHPExcel/PHPExcel/IOFactory.php'; //$PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); $fileExtArr = explode(".",$_FILES['excelPacket']['name']); $fileExt = ".".$fileExtArr[1]; $path=$_SERVER['DOCUMENT_ROOT'].'/excelupload/'.$_FILES['excelPacket']['name']; move_uploaded_file($_FILES['excelPacket']['tmp_name'],$path);//函数将上传的文件移动到新位置 $PHPExcel = $PHPReader->load($path); //读取文件 $file_one =$PHPExcel->getSheet(0); //读取第一个工作簿 $highestRow =$file_one->getHighestRow();// 取得总行数 $highestColumm = $file_one->getHighestColumn(); // 取得总列数 $dataset=array(); $objWriter = new PHPExcel_Writer_Excel2007($PHPExcel);//写入excel类 //print_r($objWriter);exit; for ($i=1;$i<=$highestRow;$i++){ $dataset[] = array( 'id' => $file_one->getCell('A'.$i)->getValue(), 'brand' => $file_one->getCell('B'.$i)->getValue(), 'series' => $file_one->getCell('C'.$i)->getValue(), 'car_name' => $file_one->getCell('D'.$i)->getValue(), 'manufacturer' => $file_one->getCell('E'.$i)->getValue(), 'model_year' => $file_one->getCell('F'.$i)->getValue(), 'level' => $file_one->getCell('G'.$i)->getValue(), 'engine' => $file_one->getCell('H'.$i)->getValue(), 'case' => $file_one->getCell('I'.$i)->getValue(), 'specifications' => $file_one->getCell('J'.$i)->getValue(), 'structure' => $file_one->getCell('K'.$i)->getValue(), 'max_speed' => $file_one->getCell('L'.$i)->getValue(), 'integrated_fuel' => $file_one->getCell('M'.$i)->getValue(), 'wheelbase' => $file_one->getCell('N'.$i)->getValue(), 'number_doors' => $file_one->getCell('O'.$i)->getValue(), 'number_seats' => $file_one->getCell('P'.$i)->getValue(), 'fuel_volume' => $file_one->getCell('Q'.$i)->getValue(), 'engine_type' => $file_one->getCell('R'.$i)->getValue(), 'inlet' => $file_one->getCell('S'.$i)->getValue(), 'drive' => $file_one->getCell('T'.$i)->getValue(), 'car_body' => $file_one->getCell('U'.$i)->getValue(), 'parking_brake_type' => $file_one->getCell('V'.$i)->getValue(), 'tyre_spec' => $file_one->getCell('W'.$i)->getValue(), 'tire_spec' => $file_one->getCell('X'.$i)->getValue(), );

} foreach ($dataset as $value){ $bdname = $value['brand']; } //print_r($bdname);exit; $PHPExcel->getActiveSheet()->setTitle("$bdname"); $PHPExcel->setActiveSheetIndex(0); $file_one->setCellValue('A1', 'id') ->setCellValue('B1', '品牌') ->setCellValue('C1', '车系') ->setCellValue('D1', '车型名称') ->setCellValue('E1', '厂商') ->setCellValue('F1', '年款') ->setCellValue('G1', '级别') ->setCellValue('H1', '发动机') ->setCellValue('I1', '变速箱') ->setCellValue('J1', '长宽高(mm)') ->setCellValue('K1', '车身结构') ->setCellValue('L1', '最高车速(km/h)') ->setCellValue('M1', '工信部综合油耗(L/100km)') ->setCellValue('N1', '轴距(mm)') ->setCellValue('O1', '车门数(个)') ->setCellValue('P1', '座位数(个)') ->setCellValue('Q1', '油箱容积(L)') ->setCellValue('R1', '发动机型号') ->setCellValue('S1', '进气形式') ->setCellValue('T1', '驱动方式') ->setCellValue('U1', '车体结构') ->setCellValue('V1', '驻车制动类型') ->setCellValue('W1', '前轮胎规格') ->setCellValue('X1', '后轮胎规格'); $path =iconv("utf-8","gb2312",$bdname.$fileExt);//因为win是支持excelgbk的索引要转换一下编码格式 $objWriter->save("../excelupload/$path");