thinkphp 使用phpExcel 导入和导出
一.导入
html部分
<div class="upexcel"> <form class="form-horizontal" method="post" action="{:url('detail/upexcel',['com_id'=>$company_id,'cty_id'=>$country_id])}" enctype="multipart/form-data"> <input type="file" name="excel" /> <input type="submit" value="立即导入" class="drexcel" /> </form> </div>
php部分:
//导入excel表格 public function upexcel() { $request = request()->param();//tp获取数据 $company_id = $request['com_id']; $country_id = $request['cty_id']; if (!empty($_FILES['excel']['name'])) { $fileName = $_FILES['excel']['name']; //得到文件全名 $dotArray = explode('.', $fileName); //把文件名安.区分，拆分成数组 $type = end($dotArray);//取出.后面的文件类型 if ($type != "xls" && $type != "xlsx") { return "不是Excel文件，请重新上传!"; } //取数组最后一个元素，得到文件类型 $uploaddir = "uploads2/" . date("Y-m-d") . '/';//设置文件保存目录 注意包含 '/' if (!file_exists($uploaddir)) { mkdir($uploaddir, 0777, true); } $path = $uploaddir . md5(uniqid(rand())) . '.' . $type; //产生随机文件名 //$path = "images/".$fileName; //客户端上传的文件名； //下面必须是tmp_name 因为是从临时文件夹中移动 move_uploaded_file($_FILES['excel']['tmp_name'], $path); //从服务器临时文件拷贝到相应的文件夹下 // unset($path); $file_path = $path; if (!file_exists($path)) { return '上传文件丢失!'; } Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的 //文件的扩展名 $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION)); if ($ext == 'xlsx') { $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = $objReader->load($file_path, 'utf-8'); } elseif ($ext == 'xls') { $objReader = \PHPExcel_IOFactory::createReader('Excel5'); $objPHPExcel = $objReader->load($file_path, 'utf-8'); } $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 $ar = array(); $i = 0; $importRows = 0; for ($j = 2; $j <= $highestRow; $j++) { $importRows++; $c_time = (string)$objPHPExcel->getActiveSheet()->getCell("A$j")->getValue();//需要导入的c_time $is_ok = (string)$objPHPExcel->getActiveSheet()->getCell("B$j")->getValue(); //需要导入的is_ok $order_num = (string)$objPHPExcel->getActiveSheet()->getCell("C$j")->getValue(); //需要导入的order_num $products = (string)$objPHPExcel->getActiveSheet()->getCell("D$j")->getValue(); //需要导入的products $sku = (string)$objPHPExcel->getActiveSheet()->getCell("E$j")->getValue(); //需要导入的字段sku $platform_income = (string)$objPHPExcel->getActiveSheet()->getCell("F$j")->getValue(); $cost = (string)$objPHPExcel->getActiveSheet()->getCell("G$j")->getValue(); $international_freight = (string)$objPHPExcel->getActiveSheet()->getCell("H$j")->getValue(); $freight_forwarding = (string)$objPHPExcel->getActiveSheet()->getCell("I$j")->getValue(); $platform_deduction = (string)$objPHPExcel->getActiveSheet()->getCell("J$j")->getValue(); $other1 = (string)$objPHPExcel->getActiveSheet()->getCell("K$j")->getValue(); $net_profit = (string)$objPHPExcel->getActiveSheet()->getCell("L$j")->getValue(); $ret['mdata'] = DetailModel::create(['c_time'=>$c_time,'is_ok'=> $is_ok, 'order_num'=> $order_num, 'products'=>$products,'sku'=> $sku, 'platform_income'=> $platform_income,'cost'=> $cost,'international_freight'=> $international_freight,'freight_forwarding'=> $freight_forwarding,'platform_deduction'=> $platform_deduction,'other1'=> $other1,'net_profit'=> $net_profit, 'company_id'=>$company_id,'country_id'=>$country_id,'c_time_month'=>$c_time,//额外用到的字段 ]);//这里就是我的数据库添加操作定义的一个方法啦,对应替换为自己的 if ($ret['mdata'] && !is_Bool($ret['mdata'])) { $ar[$i] = $ret['mdata']; $i++; } } unlink(ROOT_PATH .'public/'.$path);//导入数据库成功的时候,删除excel文件 if ($i > 0) { return "导入完毕!请勿重复刷新"; } return "导入成功!"; } else { return "上传文件失败!"; } }
二.导出
直接上php代码
public function daochu(){ $request = request()->param(); $company_id = $request['com_id']; $country_id = $request['cty_id']; $month = $request['month']; $xlsData = DetailModel::where(['company_id'=>$company_id,'country_id'=>$country_id,'c_time_month'=>$month])->order('id', 'asc')->select();//获取全部对应内容 按照自己的查询条件写 肯定要改的 // halt($xlsData); $company_name = CompanyModel::where('id',$company_id)->value('company_name');//获取公司名 $country_name = CountryModel::where(['company_id'=>$company_id,'id'=>$country_id])->value('c_name');//获取对应正确国家名 $notice = $company_name.'_'.$country_name; $sum = round(DetailModel::where(['company_id'=>$company_id,'country_id'=>$country_id,'c_time_month'=>$month])->sum('net_profit'),2); // 总净利润 总和 // halt($country['c_name']); Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的 Vendor('PHPExcel.PHPExcel.Worksheet.Drawing'); Vendor('PHPExcel.PHPExcel.Writer.Excel2007'); $objExcel = new \PHPExcel(); //set document Property $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007'); $objActSheet = $objExcel->getActiveSheet(); $key = ord("A"); $letter =explode(',',"A,B,C,D,E,F,G,H,I,J,K,L,M,N"); $arrHeader = array('日期','订单状态','订单编号','产品名称','SKU','平台售价','进货成本','国际运费','货代','平台扣费','其他','净利润','总利润'); //填充表头信息 $lenth = count($arrHeader); for($i = 0;$i < $lenth;$i++) { $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]"); }; //填充表格信息 foreach($xlsData as $k=>$v){ $k +=2; $objActSheet->setCellValue('A'.$k,$v['c_time']); $objActSheet->setCellValue('B'.$k, $v['is_ok']); $objActSheet->setCellValue('C'.$k, $v['order_num']); $objActSheet->setCellValue('D'.$k, $v['products']); $objActSheet->setCellValue('E'.$k, $v['sku']); $objActSheet->setCellValue('F'.$k, $v['platform_income']); $objActSheet->setCellValue('G'.$k, $v['cost']); $objActSheet->setCellValue('H'.$k, $v['international_freight']); $objActSheet->setCellValue('I'.$k, $v['freight_forwarding']); $objActSheet->setCellValue('J'.$k, $v['platform_deduction']); $objActSheet->setCellValue('K'.$k, $v['other1']); $objActSheet->setCellValue('L'.$k, $v['net_profit']); // 表格高度 $objActSheet->getRowDimension($k)->setRowHeight(20); } if(@$k){ $objActSheet->setCellValue('M'.$k, $sum);//算出的总和,就一个值所以不能循环 } $width = array(10,15,20,25,30); //设置表格的宽度 $objActSheet->getColumnDimension('A')->setWidth($width[1]); $objActSheet->getColumnDimension('B')->setWidth($width[2]); $objActSheet->getColumnDimension('C')->setWidth($width[3]); $objActSheet->getColumnDimension('D')->setWidth($width[4]); $objActSheet->getColumnDimension('E')->setWidth($width[1]); $objActSheet->getColumnDimension('F')->setWidth($width[1]); $objActSheet->getColumnDimension('G')->setWidth($width[1]); $objActSheet->getColumnDimension('H')->setWidth($width[1]); $objActSheet->getColumnDimension('I')->setWidth($width[1]); $objActSheet->getColumnDimension('J')->setWidth($width[1]); $objActSheet->getColumnDimension('K')->setWidth($width[1]); $objActSheet->getColumnDimension('L')->setWidth($width[1]); $objActSheet->getColumnDimension('M')->setWidth($width[1]); $objActSheet->getColumnDimension('N')->setWidth($width[1]); $outfile = $notice."_".$month."月订单信息列表.xlsx"; ob_end_clean(); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$outfile.'"'); header("Content-Transfer-Encoding: binary"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $objWriter->save('php://output'); }
完成.
