PHPEXCEL导入导出

//导入 ???public function excelImport(){ ???????$mimes = array( ???????????‘application/vnd.ms-excel‘, ???????????‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘ ???????); ???????$exts = array( ???????????‘xls‘, ???????????‘xlsx‘ ???????); ???????$upload = new Upload(array( ???????????‘mimes‘ => $mimes, ???????????‘exts‘ => $exts, ???????????‘rootPath‘ => ‘./Public/‘, ???????????‘savePath‘ => ‘abcExcel/‘.date(‘Ym‘)."/", ???????????‘subName‘ ?=> ?array(‘date‘, ‘d‘), ???????)); ???????$info = $upload->upload($_FILES); ???????if(!$info) {// 上传错误提示错误信息 ???????????$error = $upload->getError(); ???????????echo "<script>alert(‘{$error}‘);window.history.go(-1)</script>"; ???????}else{// 上传成功 ???????????foreach ($info as $item) { ???????????????$filePath[] = __ROOT__."/Public/".$item[‘savepath‘].$item[‘savename‘]; ???????????} ???????????$ImgStr = implode("|", $filePath); ???????????$data = $this->excel(‘.‘.$ImgStr); ???????????if(!$data){ ???????????????$this->success(‘导入失败！‘,‘index‘); ???????????} ???????????$res = M(‘assets‘)->data($data)->addAll($data); ???????????if(!$res){ ???????????????$this->success(‘导入失败！‘,‘index‘); ???????????} ???????????$this->success(‘导入成功！‘,‘index‘); ???????} ???} ???public function excel($files){ ???????//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入 ???????import("Common.Vendor.Excel.PHPExcel"); ???????//创建PHPExcel对象，注意，不能少了\ ???????$PHPExcel=new \PHPExcel(); ???????$name = ?substr(strrchr($files, ‘.‘), 1); ???????if ($name == ‘xls‘) { ???????????//如果excel文件后缀名为.xls，导入这个类 ???????????import("Common.Vendor.Excel.PHPExcel.Reader.Excel5"); ???????????$PHPReader=new \PHPExcel_Reader_Excel5(); ???????} ???????if ($name == ‘xlsx‘) { ???????????//如果excel文件后缀名为.xlsx，导入这下类 ???????????import("Common.Vendor.Excel.PHPExcel.Reader.Excel2007"); ???????????$PHPReader=new \PHPExcel_Reader_Excel2007(); ???????} ???????//载入文件 ???????$PHPExcel=$PHPReader->load($files); ???????$currentSheet=$PHPExcel->getSheet(0); ???????$allColumn=$currentSheet->getHighestColumn(); ???????$allRow=$currentSheet->getHighestRow(); ???????//循环读取数据 ???????for($currentRow=2;$currentRow<=$allRow;$currentRow++){ ???????????$arr[‘a‘] = $PHPExcel->getActiveSheet()->getCell(‘A‘.$currentRow)->getValue(); ???????????$arr[‘b‘] = $PHPExcel->getActiveSheet()->getCell(‘B‘.$currentRow)->getValue(); ???????????$arr[‘type‘] = $PHPExcel->getActiveSheet()->getCell(‘C‘.$currentRow)->getValue(); ???????????$arr[‘name‘] = $PHPExcel->getActiveSheet()->getCell(‘D‘.$currentRow)->getValue(); ???????????$arr[‘model‘] = $PHPExcel->getActiveSheet()->getCell(‘E‘.$currentRow)->getValue(); ???????????$arr[‘time‘] = gmdate("Y-m-d H:i:s",\PHPExcel_Shared_Date::ExcelToPHP($PHPExcel->getActiveSheet()->getCell(‘F‘.$currentRow)->getValue()));$arr[‘created_time‘] = date(‘Y-m-d H:i:s‘,time());; ???????????$data[] =$arr; ???????} ???????return $data; ???} ???//导出 ???public function excelExport(){ ???????$ids = I(‘param.ids‘); ???????if($ids){ ???????????$where[‘a.id‘] = [‘in‘,$ids]; ???????} ???????????????$data = M(‘abc‘)->where($where)->select(); ???????$title = array(‘ID‘,‘类别‘,‘名称‘,‘型号‘,‘供应商‘); ???????$this->dataExport($data,$title,‘这是表名‘.date("Y-m-d",time())); ???} ???/** ????* 数据导出 ????* @param string $fileName ?文件名 ????* @param array $headArr ???表头数据（一维） ????* @param array $data ??????列表数据（二维） ????* @return bool ????*/ ???public function dataExport($data=array(),$headArr=array(),$fileName="") { ???????import(‘Common.Vendor.Excel.PHPExcel‘); ???????import(‘Common.Vendor.Excel.PHPExcel.IOFactory‘); ???????import(‘Common.Vendor.Excel.PHPExcel.Writer.Excel5‘); ???????import(‘Common.Vendor.Excel.PHPExcel.Writer.Excel2007‘); ???????if (empty($headArr) && !is_array($headArr) && empty($data) && !is_array($data)) { ???????????return false; ???????} ???????$objPHPExcel = new \PHPExcel(); ???????//设置表头 ???????$tem_key = "A"; ???????foreach($headArr as $v){ ???????????if (strlen($tem_key) > 1) { ???????????????$arr_key = str_split($tem_key); ???????????????$colum = ‘‘; ???????????????foreach ($arr_key as $ke=>$va) { ???????????????????$colum .= chr(ord($va)); ???????????????} ???????????} else { ???????????????$key = ord($tem_key); ???????????????$colum = chr($key); ???????????} ???????????$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.‘1‘, $v); ???????????$tem_key++; ???????} ???????$objActSheet = $objPHPExcel->getActiveSheet(); ???????$border_end = ‘A1‘; // 边框结束位置初始化 ???????// 写入内容 ???????$column = 2; ???????foreach($data as $key => $rows){ //获取一行数据 ???????????$tem_span = "A"; ???????????foreach($rows as $keyName=>$value){// 写入一行数据 ???????????????if (strlen($tem_span) > 1) { ???????????????????$arr_span = str_split($tem_span); ???????????????????$j = ‘‘; ???????????????????foreach ($arr_span as $ke=>$va) { ???????????????????????$j .= chr(ord($va)); ???????????????????} ???????????????} else { ???????????????????$span = ord($tem_span); ???????????????????$j = chr($span); ???????????????} ???????????????$objActSheet->setCellValue($j.$column, $value); ???????????????$border_end = $j.$column; ???????????????$tem_span++; ???????????} ???????????$column++; ???????} ???????$fileName = iconv("utf-8", "gb2312", $fileName); ???????//设置活动单指数到第一个表 ???????$objPHPExcel->setActiveSheetIndex(0); ???????ob_end_clean();//清除缓冲区,避免乱码 ???????header(‘Content-Type: application/vnd.ms-excel‘); ???????header("Content-Disposition: attachment;filename=$fileName.xls"); ???????header(‘Cache-Control: max-age=0‘); ???????$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘); ???????$objWriter->save(‘php://output‘); //文件通过浏览器下载 ???????exit; ???}