导入excel

public function exportExcel($expTitle,$expTableData,$lis){

$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称

$fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定

vendor("PHPExcel.Classes.PHPExcel");

$objPHPExcel = new \PHPExcel();

// Miscellaneous glyphs, UTF-8

 

//2、设置表头

$objPHPExcel->getActiveSheet()->setCellValue('A1', '序号');

$objPHPExcel->getActiveSheet()->setCellValue('B1', '姓名');

$objPHPExcel->getActiveSheet()->setCellValue('C1', '学校');

$objPHPExcel->getActiveSheet()->setCellValue('D1', '成绩');

$objPHPExcel->getActiveSheet()->setCellValue('E1', '排名');

 

//3、填入数据

$count = count($expTableData);//$driver 为数据库表取出的数据

$count1 = count($lis);//$lis为数据库表取出的数据

for ($i = 2; $i <= $count + 1; $i++) {

$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i - 1);

$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $expTableData[$i - 2]['zname']);

$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $expTableData[$i - 2]['school']);

}

for ($i = 2; $i <= $count1 + 1; $i++) {

$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $lis[$i - 2]['score']);

$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $lis[$i - 2]['rank']);

}

 

 

 

header('pragma:public');

header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');

header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

 

$objWriter->save('php://output');

exit;

}

header('Content-type: text/html; charset=utf-8');

vendor('PHPExcel.Classes.PHPExcel');

$pic=$_FILES['file'];

$dirname="../application/admin/exc";//定义目录

$nam = substr($pic['name'],strrpos($pic['name'],'.'));

$filename=time().mt_rand(1000,9999).$nam;

$ss=$dirname;

if(!file_exists($ss)){

mkdir($ss);

}

$new=$dirname.'/'.$filename;

move_uploaded_file($pic['tmp_name'],$new);

$Excel = new \PHPExcel();

if($nam=='.xls'){

// 如果excel文件后缀名为.xls

vendor("PHPExcel.Classes.PHPExcel.Reader.Excel5");

$PHPReader = new \PHPExcel_Reader_Excel5();

}else{

// 如果excel文件后缀名为.xlsx

vendor("PHPExcel.Classes.PHPExcel.Reader.Excel2007");

$PHPReader = new \PHPExcel_Reader_Excel2007();

}

// 载入文件

$Excel = $PHPReader -> load($new);

//print_r($Excel);

//获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推

$currentSheet = $Excel -> getSheet(0);

//获取总列数

$allColumn = $currentSheet -> getHighestColumn();

//获取总行数

$allRow = $currentSheet -> getHighestRow();

//循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始

for($currentRow = 2; $currentRow <= $allRow; $currentRow++) {

//从哪列开始,A表示第一列

for($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {

//数据坐标

$address = $currentColumn.$currentRow;

//读取到的数据,保存到数组$arr

$arr[$currentRow][$currentColumn] = $currentSheet

-> getCell($address)

-> getValue();

}

}

 

posted @ 2020-07-07 14:39  Smile☆  阅读(103)  评论(0编辑  收藏  举报