/**
* 报表导出
* @param response
*/
@RequestMapping("/stuExcel")
@LogAnno(value="对学生数据进行了excel表格导出",grade="info")
public void stuExcel(HttpServletResponse response){
//查询所有学生信息
List<Student> list = stuService.selectAll();
//创建输出流
OutputStream fileOut = null;
try {
// 导出
// 重置输出流
response.reset();
// 设置导出Excel报表的导出形式
response.setContentType("application/vnd.ms-excel");
// 自定义响应文件名
String fileName = new String("学生信息表".getBytes("utf-8"),
"ISO-8859-1");
response.setHeader("Content-disposition", "attachment;filename="
+ fileName + ".xls");
fileOut = response.getOutputStream();
// 创建工作空间
Workbook wb = new HSSFWorkbook();
// 创建sheet
Sheet sheet = wb.createSheet("sheet1");
// 设置列宽
sheet.setColumnWidth(0, 2300);
sheet.setColumnWidth(1, 2300);
sheet.setColumnWidth(2, 2300);
sheet.setColumnWidth(3, 3400);
CreationHelper creationHelper = wb.getCreationHelper();
// 创建行 从 0 开始为第一行
Row row = sheet.createRow((short) 0);
row.setHeight((short) 450);// 目的是想把行高设置成25px
// 创建列 从0 开始为第一列
// 第一行的数据
row.createCell(0).setCellValue(
creationHelper.createRichTextString("学生编号")
);
row.createCell(1).setCellValue(
creationHelper.createRichTextString("学生姓名"));
// 设置String
row.createCell(2).setCellValue(
creationHelper.createRichTextString("就业单位"));
row.createCell(3).setCellValue(
creationHelper.createRichTextString("学生图片"));
row.createCell(4).setCellValue(
creationHelper.createRichTextString("学生薪资"));
row.createCell(5).setCellValue(
creationHelper.createRichTextString("入职时间"));
row.createCell(6).setCellValue(
creationHelper.createRichTextString("培训时间"));
row.createCell(7).setCellValue(
creationHelper.createRichTextString("是否是明星学员"));
row.createCell(8).setCellValue(
creationHelper.createRichTextString("学校"));
row.createCell(9).setCellValue(
creationHelper.createRichTextString("学历"));
row.createCell(10).setCellValue(
creationHelper.createRichTextString("工作地址"));
row.createCell(11).setCellValue(
creationHelper.createRichTextString("学生感言"));
row.createCell(12).setCellValue(
creationHelper.createRichTextString("状态"));
row.createCell(13).setCellValue(
creationHelper.createRichTextString("备注"));
row.createCell(14).setCellValue(
creationHelper.createRichTextString("作者"));
int i=1;
for (Student stu : list) {
Row row1 = sheet.createRow((short) i);
row1.setHeight((short) 450);// 目的是想把行高设置成25px
String uid=String.valueOf(stu.getStuId());
// 第二行的数据
row1.createCell(0).setCellValue(
creationHelper.createRichTextString(uid));
row1.createCell(1).setCellValue(
creationHelper.createRichTextString(stu.getStuName()));
// 设置String
row1.createCell(2).setCellValue(
creationHelper.createRichTextString(stu.getStuCompany()));
row1.createCell(3).setCellValue(
creationHelper.createRichTextString(stu.getStuPicture()));
row1.createCell(4).setCellValue(
creationHelper.createRichTextString(String.valueOf(stu.getStuSalary())));
row1.createCell(5).setCellValue(
creationHelper.createRichTextString(String.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(stu.getStuEntrytime()))));
row1.createCell(6).setCellValue(
creationHelper.createRichTextString(String.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(stu.getStuTrainingtime()))));
String isStart="否";
if(stu.getStuIsstar()!=null){
if(stu.getStuIsstar()==1){
isStart="是";
}
}
row1.createCell(7).setCellValue(
creationHelper.createRichTextString(isStart));
row1.createCell(8).setCellValue(
creationHelper.createRichTextString(String.valueOf(stu.getStuSchool())));
row1.createCell(9).setCellValue(
creationHelper.createRichTextString(stu.getStuEducation()));
row1.createCell(10).setCellValue(
creationHelper.createRichTextString(stu.getStuWorkaddress()));
row1.createCell(11).setCellValue(
creationHelper.createRichTextString(stu.getStuRecollections()));
//1表示缉编中 2 待审核 3 待发布 4 取消发布 5 过期
String style="";
//判断状态
switch (stu.getStuState()) {
case 1:style="缉编中";break;
case 2:style="待审核";break;
case 3:style="待发布";break;
case 4:style="取消发布";break;
case 5:style="过期 ";break;
}
row1.createCell(12).setCellValue(
creationHelper.createRichTextString(style));
row1.createCell(13).setCellValue(
creationHelper.createRichTextString(stu.getStuNote()));
row1.createCell(14).setCellValue(
creationHelper.createRichTextString(stu.getStuWriter()));
i++;
}
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 报表导入
*/
@RequestMapping("/ExcelInfo")
@LogAnno(value="对学生数据进行了excel表格导入",grade="info")
public RespModel ExcelInfo(MultipartFile file,HttpServletRequest request){
RespModel rm=new RespModel();
InputStream fileIn=null;
try {
fileIn=file.getInputStream();
//根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = new HSSFWorkbook(fileIn);
//获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0);
//对Sheet中的每一行进行迭代
for (Row r : sht0) {
//如果当前行的行号（从0开始）未达到2（第三行）则从新循环
if(r.getRowNum()<1){
continue;
}
//创建实体类
Student stu=new Student();
//取出当前行第1个单元格数据，并封装在info实体stuName属性上
stu.setStuName(r.getCell(1)==null?null:r.getCell(1).getStringCellValue());
stu.setStuCompany(r.getCell(1)==null?null:r.getCell(2).getStringCellValue());
stu.setStuEducation(r.getCell(1)==null?null:r.getCell(9).getStringCellValue());
stu.setStuEntrytime(r.getCell(5)==null?null:new SimpleDateFormat("yyyy-MM-dd").parse(r.getCell(5).getStringCellValue()));
stu.setStuIsstar("是".equals(r.getCell(7))==true?1:0);
stu.setStuNote(r.getCell(13)==null?null:r.getCell(13).getStringCellValue());
stu.setStuPicture(r.getCell(3)==null?null:r.getCell(3).getStringCellValue());
stu.setStuRecollections(r.getCell(11)==null?null:r.getCell(11).getStringCellValue());
stu.setStuSalary(r.getCell(4)==null?null:Float.valueOf(r.getCell(4).getStringCellValue()));
stu.setStuSchool(r.getCell(8)==null?null:r.getCell(8).getStringCellValue());
String state = r.getCell(12)==null?"":r.getCell(12).getStringCellValue();
int sta=1;
switch (state) {
case "缉编中": sta=1;break;
case "待审核": sta=2;break;
case "待发布": sta=3;break;
case "取消发布": sta=4;break;
case "过期": sta=5;break;
}
stu.setStuState(sta);
stu.setStuTrainingtime(r.getCell(6)==null?null:new SimpleDateFormat("yyyy-MM-dd").parse(r.getCell(6).getStringCellValue()));
stu.setStuWiter(r.getCell(14)==null?null:r.getCell(14).getStringCellValue());
stu.setStuWorkaddress(r.getCell(10)==null?null:r.getCell(10).getStringCellValue());
//添加学生
stuService.addStuInfo(stu);
}
rm.setFlag(true);
rm.setMsg("数据导入成功!");
fileIn.close();
} catch (Exception e) {
System.out.println("异常");
rm.setFlag(false);
rm.setMsg("数据导入失败!");
e.printStackTrace();
}
return rm;
}
