/**
* 导入文件
* @param file
*/
@ResponseBody
@RequestMapping(value="/query/importrelie")
public String importReliefWorkers(@RequestParam("file") CommonsMultipartFile file,HttpServletRequest request,HttpServletResponse response,String type) throws InvalidFormatException{
List<ReliefWorkers> workers = new ArrayList<>();
ReliefWorkers worker = null;
List<StudentInfo> infos = new ArrayList<>();
StudentInfo info = null;
try {
Workbook workbook = WorkbookFactory.create(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
info = new StudentInfo();
//获得表头
Row rowHead = sheet.getRow(0);
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
for(int i = 1 ; i <= totalRowNum ; i++){
worker = new ReliefWorkers();
info = new StudentInfo();
//获得第i行对象
Row row = sheet.getRow(i);
Cell cell = row.getCell(0);
String stuNum = cell.getStringCellValue().toString();
Cell cell2 = row.getCell(1);
String name = cell2.getStringCellValue().toString();
String college = null;
if(row.getPhysicalNumberOfCells()>=3){
Cell cell3 = row.getCell(2);
college = cell3.getStringCellValue().toString();
}
Integer count = reduceService.ifThere(stuNum);
if(count>0){
worker.setSuId(BaseUntil.getUuid());
if(BaseUntil.validateStr(stuNum)){
worker.setSuStuId(stuNum);
}
worker.setSuReliefType(type);
worker.setSuYear(BaseUntil.formatTime(new Date(), "yyyy"));
workers.add(worker);
}else{
if(BaseUntil.validateStr(stuNum)){
info.setStudentNumber(stuNum);
}
if(BaseUntil.validateStr(name)){
info.setStudentName(name);
}
if(BaseUntil.validateStr(college)){
info.setCollege(college);
}
info.setMemo("未存在此学生信息");
infos.add(info);
}
}
if(workers.size()>0){
reduceService.addbatchs(workers);
}
return ReturnData.toData(100, null, null, "导入成功");
} catch (Exception e) {
e.printStackTrace();
return ReturnData.toData(101, null, null, "导入失败");
}
}
@RequestMapping("/upload/demo")
public void uploadDemo(HttpServletResponse response){
String sName = "";
List<StudentInfo> studentList = infoService.selectFind(sName);
//2.使用POI将数据写进excel表中
//2.1在内存中创建一个excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//2.2创建一个标签页
HSSFSheet hssfSheet = hssfWorkbook.createSheet("不存在的学生数据");
//2.3创建标题行
HSSFRow headRow = hssfSheet.createRow(0);
String headers[] = { "学号", "姓名", "学院" };
for (int i = 0; i < headers.length; i++) { // 先写表头
headRow.createCell(i).setCellValue(headers[i]);
}
for (StudentInfo studentInfo : studentList) {
HSSFRow dataRow = hssfSheet.createRow(hssfSheet.getLastRowNum()+1);
dataRow.createCell(0).setCellValue(studentInfo.getStudentNumber());
dataRow.createCell(1).setCellValue(studentInfo.getStudentName());
dataRow.createCell(2).setCellValue(studentInfo.getCollege());
}
try {
response.setHeader("Content-Disposition",
"attachment;filename=" + new String("手动导出excel.xls".getBytes("utf-8"), "iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out;
out = response.getOutputStream();
hssfWorkbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
//springmvc 下载
/**
* 下载
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
@RequestMapping(value ="/DownloadFile")
public ResponseEntity<byte[]> DownloadFile(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
//获取项目根目录
String filePath = req.getServletContext().getRealPath("/")+"uploadFile"+"/student.xlsx";
//获取文件名
String filename="学生错误数据汇总.xlsx";
File file = null;
HttpHeaders headers =null;
try {
file =new File(filePath);
//请求头
headers =new HttpHeaders();
String fileName1 =new String(filename.getBytes("UTF-8"),"iso-8859-1");//解决文件名乱码
headers.setContentDispositionFormData("attachment",fileName1);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
} catch (Exception e) {
throw new RuntimeException(e);
}
return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file),headers, HttpStatus.OK);
}
}