$file = '20240606094136.xls';
include 'PHPExcel/IOFactory.php';
$reader = PHPExcel_IOFactory::createReaderForFile($file); // 读取 excel 文档
$PHPExcel = $reader->load($file); // 文档名称
$sheet = $PHPExcel->getSheet(); // 读取第一个工作表(编号从 0 开始)
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$arr = array(1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M', 14=>'N',15=>'O',16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z');
// 一次读取一列
$valArr = array();
for ($row = 2; $row <= $highestRow; $row++) {
$val = array();
for ($column = 0; $arr[$column] != 'L'; $column++) {
$val[] = $sheet->getCellByColumnAndRow($column, $row)->getValue();
}
$valArr[]= $val;
}
//业务逻辑
$data = array();
foreach ($valArr as $item)
{
if (!isset($data[$item[1]]['allNum']))
{
$data[$item[1]]['allNum'] = 0;
$data[$item[1]]['num'] = 0;
$data[$item[1]]['noNum'] = 0;
}
$data[$item[1]]['allNum']++;
if ($item[7]/16>=0.5 && $item[8]/8>=0.25 && $item[9]/8>=0.25 && $item[10]/8>=0.25 && $item[11]/8>=0.25)
{
$data[$item[1]]['num']++;
}else{
$data[$item[1]]['noNum']++;
}
}
$dataArr = array();
foreach ($data as $key => $datum)
{
$datum['ratio'] = round(($datum['num'] / $datum['allNum'])*100,2).'%';
$dataArr[] = [
'name' => $key,
'allNum' => $datum['allNum'],
'num' => $datum['num'],
'noNum' => $datum['noNum'],
'ratio' => $datum['ratio'],
];
}
//导出
// 实例化excel类
$objPHPExcel = new \PHPExcel();
//输出表头
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','学院');
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1','总人数');
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1','达标人数');
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1','未达标人数');
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1','达标率');
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
// 数据起始行
$row_num = 2;
// 向每行单元格插入数据
foreach($dataArr as $value)
{
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('A'.$row_num,$value['name'],\PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('B'.$row_num,$value['allNum'],\PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('C'.$row_num,$value['num'],\PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('D'.$row_num,$value['noNum'],\PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('E'.$row_num,$value['ratio'],\PHPExcel_Cell_DataType::TYPE_STRING);
$row_num++;
}
$dirName = date('Ymd');//目录名
$fileName = date('YmdHis');//文件名
$objPHPExcel->getActiveSheet()->setTitle($fileName);
$objPHPExcel->setActiveSheetIndex(0);
$callStartTime = microtime(true);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($fileName.'.xlsx');
$strUrl='Location:'.$fileName.'.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header($strUrl);
php本地导入表格,整理数据之后再导出表格
于 2024-06-06 11:07:51 首次发布