导出数据
1.要先获取模板路径（模板放在项目的根目录下了）
2.获取到表里面的所有单元格
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(saveFile));
sheets = wb.iterator();//获取到表里面的所有单元格
3.遍历excel中每个sheet的数据
4.判断表格中标题内容，根据标题内容获取相对应模板sheet
5.之后根据此sheet中的单元格位置放相应的数据（此次模板中第一列放的是代号，代号是唯一的，可以根据代号查到相应的数据放入具体单元格中）
可以进行for循环进行放入数据，此表中有几行是对下面一些进行分类的小标题，所以要判断当前行中是否有数据，如果有的话在下一行放数据
6.放入完成之后按照格式导出表格
response.setContentType("application/binary;charset=UTF-8");
// 设置响应输出的头类型
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFilename + format);
response.setCharacterEncoding("UTF-8");
wb.write(response.getOutputStream());
//根据模板导出测评分数 @Override public void exportInfo(Map<String, Object> map, HttpServletResponse response) throws Exception { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String format = sdf.format(new Date()); String downloadFilename = ""; //excel模板路径 Workbook wb = null; String file = uploadDir + "/templates/uploadFile.xlsx"; if (StringUtils.isNotBlank(uploadDir)) { if (!uploadDir.endsWith("/")) { uploadDir = uploadDir + "/"; } } File saveFile = new File(file); Iterator<Sheet> sheets = null; List<List> returnlist = new ArrayList<List>(); try { wb = new XSSFWorkbook(new FileInputStream(saveFile)); sheets = wb.iterator(); if (sheets == null) { throw new Exception("excel中没有内容"); } //遍历excel中每个sheet的数据 while (sheets.hasNext()) { Sheet sheet = sheets.next(); String sheetName = sheet.getSheetName(); List<Map<String, Object>> list = null; if (sheetName.equals("健康")) { map.put("firstSymbol", "H"); map.put("type", 0); List<AssessmentInfoEntity> healList = assessmentInfoService.exportInfo(map); for (int i = 0; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row != null && !row.equals("")) { if (row.getCell(1) != null && row.getCell(1).getStringCellValue().replace(" ", "") != null && row.getCell(1).getStringCellValue().replace(" ", "") != "") { for (int z = 0; z < healList.size(); z++) { if (row.getCell(0).getStringCellValue().replace(" ", "").equals(healList.get(z).getSymbol())) { //判断数据库代号和excel中代号是否相等 List<Map<String, Object>> scoreJson = (List<Map<String, Object>>) JSON.parse(String.valueOf(healList.get(z).getFenShu())); if (scoreJson != null && scoreJson.size() > 0) { for (int j = 0; j < scoreJson.size(); j++) { //分数 if (null != scoreJson.get(j).get("value") && !scoreJson.get(j).get("value").equals("")) { row.getCell(j + 3).setCellValue(String.valueOf(scoreJson.get(j).get("value"))); } else { row.getCell(j + 3).setCellValue(""); } if (null != scoreJson.get(j).get("time") && !scoreJson.get(j).get("time").equals("")) { sheet.getRow(3).getCell(j+3).setCellValue(String.valueOf(scoreJson.get(j).get("time"))); } else { sheet.getRow(3).getCell(j+3).setCellValue(""); } } } row.setHeight((short) ((short) 30 * 20)); } } // } } } } else if (sheetName.equals("一日生活常规-高宽班")) { map.put("firstSymbol", "CL"); map.put("type", 2); List<AssessmentInfoEntity> healList = assessmentInfoService.exportInfo(map); for (int i = 0; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row!=null && !row.equals("")){ if (row.getCell(1) != null && row.getCell(1).getStringCellValue().replace(" ", "") != null && row.getCell(1).getStringCellValue().replace(" ", "") != ""){ for (int z = 0; z < healList.size(); z++) { if (row.getCell(0).getStringCellValue().replace(" ", "").equals(healList.get(z).getSymbol())){ //判断数据库代号和excel中代号是否相等 List<Map<String, Object>> scoreJson = (List<Map<String, Object>>) JSON.parse(String.valueOf(healList.get(z).getFenShu())); if (scoreJson != null && scoreJson.size()>0){ for (int j = 0; j < scoreJson.size(); j++) { if (null != scoreJson.get(j).get("value") && !scoreJson.get(j).get("value").equals("")){ row.getCell(j+3).setCellValue(String.valueOf(scoreJson.get(j).get("value"))); }else { row.getCell(j+3).setCellValue(""); } if (null != scoreJson.get(j).get("time") && !scoreJson.get(j).get("time").equals("")) { sheet.getRow(3).getCell(j+3).setCellValue(String.valueOf(scoreJson.get(j).get("time"))); } else { sheet.getRow(3).getCell(j+3).setCellValue(""); } } } row.setHeight((short)((short) 30*20)); } } } } } } } response.setContentType("application/binary;charset=UTF-8"); // 设置响应输出的头类型 response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + downloadFilename + format); response.setCharacterEncoding("UTF-8"); wb.write(response.getOutputStream()); } catch (Exception e) { throw e; } finally { if (wb != null) { wb.close(); } } }
导入表格：
1.根据模板导入数据时只能导入日期格式的时间，
但根据模板导出的时候会把时间输出为字符串格式的，所以这时需要对其进行转化（先要判断读到的数据是什么格式的，如果是字符串就进行转化，是日期格式就可以直接放进去）
Map<String, Object> birthday = null;
if (sheet.getRow(3).getCell(i+3).getCellType() == 1) { //1指的是String类型
birthday = DateUtils.getAgeYearMonth2(sdf.parse(String.valueOf(map.get("birthday"))), sdf.parse(sheet.getRow(3).getCell(i + 3).getStringCellValue()));
}else{
birthday = DateUtils.getAgeYearMonth2(sdf.parse(String.valueOf(map.get("birthday"))), sheet.getRow(3).getCell(i + 3).getDateCellValue());
}
listAge.add(birthday);
//导入获取数据 public List<Map<String, Object>> getHealCellValue(Sheet sheet, Map<String, Object> map) throws ParseException { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); List<AssessmentScoreEntity> list = new ArrayList<>(); List<AssessmentInfoEntity> listInfo = assessmentInfoService.list();//获取所有的题目信息 for (int i = 4; i < sheet.getPhysicalNumberOfRows(); i++) { List<Map<String, Object>> mapList = new ArrayList<>();//存score数据 list<map<String,Object>>形式 Row row = sheet.getRow(i); if (row != null && !row.equals("")) { if (row.getCell(1) != null && row.getCell(1).getStringCellValue().replace(" ", "") != null && row.getCell(1).getStringCellValue().replace(" ", "") != "") { AssessmentScoreEntity assessmentScoreEntity = new AssessmentScoreEntity(); for (int j = 0; j < listInfo.size(); j++) { // 获取分数表中对应的题目ID if ((row.getCell(0).getStringCellValue().replace(" ", "")).equals(listInfo.get(j).getSymbol())) { assessmentScoreEntity.setAssessmentId(listInfo.get(j).getId()); } } assessmentScoreEntity.setCreateTime(new Date()); assessmentScoreEntity.setChildrenId(Long.parseLong(String.valueOf(map.get("childrenId")))); assessmentScoreEntity.setSchoolId(Long.parseLong(String.valueOf(map.get("schoolId")))); assessmentScoreEntity.setGradeId(Long.parseLong(String.valueOf(map.get("gradeId")))); assessmentScoreEntity.setSymbol(row.getCell(0).getStringCellValue()); assessmentScoreEntity.setName(row.getCell(1).getStringCellValue()); for (int j = 0; j < 7; j++) { if (sheet.getRow(i).getCell(j + 3) != null) { sheet.getRow(i).getCell(j + 3).setCellType(CellType.STRING); DecimalFormat df = new DecimalFormat("#.#"); String format = ""; Date date = new Date(); if (sheet.getRow(3).getCell(j + 3) != null && !sheet.getRow(3).getCell(j + 3).equals("")){ //打印当前单元格内数据类型 log.info("sheet.getRow(3).getCell(j+3).getCellType()={}", sheet.getRow(3).getCell(j+3).getCellType() ); //判断当前单元格内数据类型是否是string类型的 if (sheet.getRow(3).getCell(j+3).getCellType() == 1) { date = sdf.parse(sheet.getRow(3).getCell(j + 3).getStringCellValue()); }else{ date = sheet.getRow(3).getCell(j + 3).getDateCellValue(); log.info("----------------"+date); } } Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$"); if (sheet.getRow(4).getCell(j + 3).getStringCellValue().replace(" ", "") != "" && sheet.getRow(4).getCell(j + 3).getStringCellValue().replace(" ", "") != null) { if (!pattern.matcher(sheet.getRow(i).getCell(j + 3).getStringCellValue()).matches() && sheet.getRow(i).getCell(j + 3).getStringCellValue() != null && sheet.getRow(i).getCell(j + 3).getStringCellValue() != "") { double dou = Double.valueOf(sheet.getRow(i).getCell(j + 3).getStringCellValue()); format = df.format(dou); } else { format = sheet.getRow(i).getCell(j + 3).getStringCellValue(); } Map<String, Object> objectMap = new HashMap<>(); objectMap.put("expect", ""); objectMap.put("final", ""); objectMap.put("time", sdf.format(date)); objectMap.put("value", format); mapList.add(objectMap); } } } assessmentScoreEntity.setScore(JSON.toJSONString(mapList)); list.add(assessmentScoreEntity); } } } List<Map<String, Object>> listAge = new ArrayList<>(); for (int i = 0; i < 7; i++) { //获取填报的时间 sheet.getRow(4).getCell(i + 3).setCellType(CellType.STRING); if (sheet.getRow(3).getCell(i + 3) != null && !sheet.getRow(3).getCell(i + 3).equals("") && sheet.getRow(4).getCell(i + 3).getStringCellValue().replace(" ", "") != null && sheet.getRow(4).getCell(i + 3).getStringCellValue().replace(" ", "") != "") { Map<String, Object> birthday = null; if (sheet.getRow(3).getCell(i+3).getCellType() == 1) { birthday = DateUtils.getAgeYearMonth2(sdf.parse(String.valueOf(map.get("birthday"))), sdf.parse(sheet.getRow(3).getCell(i + 3).getStringCellValue())); }else{ birthday = DateUtils.getAgeYearMonth2(sdf.parse(String.valueOf(map.get("birthday"))), sheet.getRow(3).getCell(i + 3).getDateCellValue()); } listAge.add(birthday); } } saveValue(list, map); saveProgress(listAge, map); return null; }
