抠脚强 2017-06-09
/* 导入excel */ function importExcel($filepath){ require_once "/Libs/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php"; if (!file_exists($filepath)) { exit("not found {$filepath}.\n"); } $reader = PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel5格式(Excel97-2003工作簿) $PHPExcel = $reader->load($filepath); // 载入excel文件 $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表 $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumm = $sheet->getHighestColumn(); // 取得总列数 /*返回数据变量 */ $return=array( "rows"=>$highestRow-1, "cols"=>$highestColumm, "colindex"=>strpos("ABCDEFGHIJKLMNOPQRSTUVWXYZ",$highestColumm)+1, "data"=>array(), ); /** 循环读取每个单元格的数据 */ for ($row = 2; $row <= $highestRow; $row++){//行数是以第1行开始 for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始 $dataset[] = $sheet->getCell($column.$row)->getValue(); //echo $column.$row.":".$sheet->getCell($column.$row)->getValue()."<br />"; $return['data'][$row-2][$column]=$sheet->getCell($column.$row)->getValue(); } } return $return; } /* 导出excel $titles为一维数组 $data为多维数组 */ function exportExcel($titles=array(),$data=array(),$filename='icaodan'){ require_once "/Libs/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel.php"; $objPHPExcel = new PHPExcel(); #设置表头 $rownum=1; $settiele=$objPHPExcel->setActiveSheetIndex(0); for($i=0;$i<count($titles);$i++){ $colname=substr("ABCDEFGHIJKLMNOPQRSTUVWXYZ",$i,1); $settiele->setCellValue($colname.$rownum, $titles[$i]); } if(count($titles)>0){$rownum++;} // 设置行高度 $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); foreach($data as $k => $v){ $num=0; foreach($v as $key=>$val){ $colname=substr("ABCDEFGHIJKLMNOPQRSTUVWXYZ",$num++,1); $settiele->setCellValue($colname.$rownum, $val); } $rownum++; } $objPHPExcel->getActiveSheet()->setTitle('User'); $objPHPExcel->setActiveSheetIndex(0); 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; }