Excel文件导出
$excel = new \PHPExcel();
//Excel表格式,这里简略写了8列
//表头数组
$letter = array('A','B','C','D');
//设置表头宽度
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
//设置水平居中
$excel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$excel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
//填充表头信息
$excel->getActiveSheet()
->setCellValue('A1','体育')
->setCellValue('B1','文学')
->setCellValue('C1','画画')
->setCellValue('D1','音乐');
$a = 0;
$excel->getActiveSheet()->setCellValue("A"."3","共计:".5."本");
$excel->getActiveSheet()->setCellValue("B"."3","共计:".6."本");
$excel->getActiveSheet()->setCellValue("C"."3","共计:".33."本");
$excel->getActiveSheet()->setCellValue("D"."3","共计:".21."本");
//创建Excel输入对象
$write = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-excel");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header('Content-Disposition:attachment;filename="信息.xls"');
header("Content-Transfer-Encoding:binary");
$write->save('php://output');
exit;
代码执行后，会直接生成Excel，并提示下载或打开
Excel文件导入
$phpexcel = new \PHPExcel;
$excelReader = \PHPExcel_IOFactory::createReader('Excel2007');
$phpexcel = $excelReader->load($rootPath . $randName)->getSheet(0);//载入文件并获取第一个sheet
$total_line = $phpexcel->getHighestRow(); //多少行
$total_column = $phpexcel->getHighestColumn(); //多少列
for($row = 2; $row <= $total_line; $row++) {
$oneUser = array();
for($column = 'A'; $column <= 'K'; $column++) {
$oneUser[] = trim($phpexcel->getCell($column.$row)->getValue());
}
$id = $oneUser[0]; //id
$name = $oneUser[1]; //姓名
$existUser = Student::findOne(['id' => $id]);
if($existUser){
$record = 'ID:'.$id. '姓名:'.$name.'已存在';
Yii::$app->db->createCommand()->insert('excel_record', [
'record' => $record,
'type' => 2,
'timeline' => time(),
])->execute();
}else{
$Model = new Relese;
$Model->id = $id;
$Model->name = $name;
$Model->isNewRecord = true;
if(!$Model->save()){
$record = 'ID:'.$id.' 姓名:'.$name.'导入失败';
Yii::$app->db->createCommand()->insert('excel_record', [
'record' => $record,
'type' => 2,
'timeline' => time(),
])->execute();
}else{
$record = 'ID:'.$id.' 姓名:'.$name.'导入成功';
Yii::$app->db->createCommand()->insert('excel_record', [
'record' => $record,
'type' => 2,
'timeline' => time(),
])->execute();
}
}
}
