thinkphp_phpexcel导入导出数据demo
基于Thinkphp3.2+phpexcel1.7.7实现的数据导入导出功能
前台模板
<!doctype html> <html> <head> <meta charset="utf-8"> <title>thinkphp_phpexcel导入导出数据Demo</title> </head> <div class="formBox"> <form id="addform" action="{:U('Index/upload')}" method="post" enctype="multipart/form-data"> <div class="control-group"> <label>Excel表格：</label> <input type="file" name="excelData" value=""/> <span class="Validform_checktip"></span> </div> <div class="control-group"> <img style="display:none;" src="images/loading.gif"/> <input type="submit" class="btn btn-primary Sub" value="导入"/> </div> </form> <a href="{:U('Index/outport')}">导出</a> </div> </body> </html>
控制器导出
//导出到excel public function outport() { $model = D("Orders"); //查询数据得到$OrdersData二维数组 $OrdersData = $model->select(); //引入类库(PHPExcel的拓展名改为为PHPExcel.class.php) import('Vendor.Excel.PHPExcel'); // Create new PHPExcel object $objPHPExcel = new \PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("gimin") ->setLastModifiedBy("gimin") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); //set width $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(8); //设置行高度 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20); //set font size bold $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getAlignment() ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getBorders() ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); //设置水平居中 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //合并cell $objPHPExcel->getActiveSheet()->mergeCells('A1:K1'); // set table header content $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '订单数据汇总 时间:' . date('Y-m-d H:i:s')) ->setCellValue('A2', '订单ID') ->setCellValue('B2', '下单人') ->setCellValue('C2', '客户名称') ->setCellValue('D2', '下单时间') ->setCellValue('E2', '需求机型') ->setCellValue('F2', '需求数量') ->setCellValue('G2', '需求交期') ->setCellValue('H2', '确认BOM料号') ->setCellValue('I2', 'PMC确认交期') ->setCellValue('J2', 'PMC交货备注') ->setCellValue('K2', '质检'); // Miscellaneous glyphs, UTF-8 for ($i = 0; $i <= count($OrdersData) - 1; $i++) { $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 3), $OrdersData[$i]['id']); $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 3), $OrdersData[$i]['realname']); $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 3), $OrdersData[$i]['customer_name']); $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 3), date('Y-m-d', $OrdersData[$i]['create_time'])); $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 3), $OrdersData[$i]['require_product']); $objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($i + 3), $OrdersData[$i]['require_count']); $objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($i + 3), $OrdersData[$i]['require_time']); $objPHPExcel->getActiveSheet(0)->setCellValue('H' . ($i + 3), $OrdersData[$i]['product_bom_encoding']); $objPHPExcel->getActiveSheet(0)->setCellValue('I' . ($i + 3), $OrdersData[$i]['delivery_time']); $objPHPExcel->getActiveSheet(0)->setCellValue('J' . ($i + 3), $OrdersData[$i]['delivery_memo']); $objPHPExcel->getActiveSheet(0)->setCellValue('K' . ($i + 3), $OrdersData[$i]['quality']); $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':K' . ($i + 3))->getAlignment() ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':K' . ($i + 3))->getBorders() ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16); } //sheet命名 $objPHPExcel->getActiveSheet()->setTitle('订单汇总表'); //Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); //excel头参数 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="订单汇总表(' . date('Ymd-His') . ').xls"'); //日期为文件名后缀 header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式 $objWriter->save('php://output'); }
控制器导入
//展示模板 public function test() { $this->display(); } //上传方法 public function upload() { header("Content-Type:text/html;charset=utf-8"); $upload = new \Think\Upload(); //实例化上传类 $upload->maxSize = 3145728 ; //设置附件上传大小 $upload->exts = array('xls', 'xlsx'); //设置附件上传类 $upload->savePath = '/'; //设置附件上传目录 //上传文件 $info = $upload->uploadOne($_FILES['excelData']); $filename = './Uploads'.$info['savepath'].$info['savename']; //上传的文件保存的目录 $exts = $info['ext']; //dump($info);exit; if(!$info) {// 上传错误提示错误信息 $this->error($upload->getError()); }else{// 上传成功,执行导入数据操作 $this->goods_import($filename, $exts); } } //导入数据方法 protected function goods_import($filename, $exts='xls') { //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能import导入 import('Vendor.Excel.PHPExcel'); //创建PHPExcel对象，注意，不能少了\ $PHPExcel=new \PHPExcel(); //如果excel文件后缀名为.xls，导入这个类 if($exts == 'xls'){ import("Vendor.Excel.PHPExcel.Reader.Excel5"); $PHPReader=new \PHPExcel_Reader_Excel5(); }else if($exts == 'xlsx'){ import("Vendor.Excel.PHPExcel.Reader.Excel2007"); $PHPReader=new \PHPExcel_Reader_Excel2007(); } //载入文件 $PHPExcel=$PHPReader->load($filename); //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推 $currentSheet=$PHPExcel->getSheet(0); //获取总列数 $allColumn=$currentSheet->getHighestColumn(); //获取总行数 $allRow=$currentSheet->getHighestRow(); //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据， //索引值从0开始,0为空行，如果有表头，则需要根据表头来定义索引 for($currentRow=3;$currentRow<=$allRow;$currentRow++){ //从哪列开始，A表示第一列 for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){ //数据坐标 $address=$currentColumn.$currentRow; //读取到的数据，保存到数组$arr中 $data[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue(); } } $this->save_import($data); } //保存导入数据 protected function save_import($data) { //dump($data);exit; foreach ($data as $k=>$v){ //$date['id'] = $v['A']; //若id为主键，如果主键冲突，会导致导入失败 $date['realname'] = $v['B']; $date['customer_name'] = $v['C']; $date['create_time'] = $v['D']; $date['require_product'] = $v['E']; $date['require_count'] = $v['F']; $date['require_time'] = $v['G']; $date['product_bom_encoding'] = $v['H']; $date['delivery_time'] = $v['I']; $date['delivery_memo'] = $v['J']; $date['quality'] = $v['K']; $result = M('orders')->add($date); } if($result){ $this->success('导入成功',U('Index/test')); }else{ $this->error('导入失败'); } }
浙公网安备 33010602011771号