Java导入、导出Excel（二）

一、上传Excel，并将其中的数据导入数据库：

1. //点击按钮进入此方法，将会弹出一个对话框@RequestMapping(value="/importFile",method= RequestMethod.GET)public String dialogImport(ModelMap map) {return "configuration/person/importFile";}

2.  对话框文件importFile.jsp<span style="white-space:pre"></span><div style="width: 90%" align="center"><jvform:form id="dialogPlanform" action="/person/importSave" method="post"><jvform:fileupload id="file" title="导入文件" required="true"></jvform:fileupload></jvform:form></div>

3.  //选择Excel文件，然后点击提交按钮，将会进入该方法@RequestMapping(value="/importSave", method = RequestMethod.POST)public String importExcel(MultipartHttpServletRequest request, HttpServletResponse response, Model model) throws IOException {String dest = FileUploadUtil.getCurrentPath(request) + ResourceUtils.getProperty(ResourceUtils.FILEUPLOAD_EXCEL);String path = FileUploadUtil.upload(dest, "uploadFile", request);String errorInfo = personService.importExcel(path);if ("".equals(errorInfo)) {//若无错误将会跳转到指定的页面return "configuration/person/finished";} else {//若有错误将会跳转到弹出的对话框，并提示错误信息model.addAttribute("errorInfo", errorInfo);return "configuration/person/importFile";}}

4.导入数据public String importExcel(String xlsPath) throws IOException{String error = "";   List<Person> list = new LinkedList<Person>();   FileInputStream inputStream = new FileInputStream(new File(xlsPath));      //根据指定的文件输入流导入Excel从而产生Workbook对象，并解决版本冲突   Workbook wb = null;try {wb = new XSSFWorkbook(inputStream);} catch (Exception e) {wb = new HSSFWorkbook(inputStream);}   //获取Excel文档中的第一个表单   Sheet sht0 = wb.getSheetAt(0);   //对Sheet中的每一行进行迭代   for (Row r : sht0) {   //如果当前行的行号（从0开始）未达到2（第三行）则从新循环   if (r.getRowNum() < 2) {   continue;   }   for (int i = 0; i < 24; i++) {if(r.getCell(i) == null){r.createCell(i).setCellValue("");}}   //创建实体类   Person person = new Person();   //取出当前行第1个单元格数据，并封装在person实体的相关属性上   person.setId(Double.valueOf(r.getCell(0).getNumericCellValue()).intValue());   person.setName(r.getCell(1).getStringCellValue());   person.setGender("男".equals(r.getCell(2).getStringCellValue()) ? "MALE" : "FEMALE");   person.setIdCardNo(String.valueOf(Double.valueOf(r.getCell(3).getNumericCellValue()).intValue()));   person.setDepartment(departmentService.findByDepartmentName(r.getCell(4).getStringCellValue()));   person.setLeader("是".equals(r.getCell(5).getStringCellValue()) ? true : false);   person.setPosition(r.getCell(6).getStringCellValue());   person.setPart(r.getCell(7).getStringCellValue());   person.setNumber(r.getCell(8).getStringCellValue());      list.add(person);   }      for (Person person : list) {   if(person.getId() != null && findOne(person.getId()) != null){   Person p =  findOne(person.getId());   BeanUtils.copyProperties(person, p);   savePerson(p);   }else{   Person p = findByNumber(person.getNumber());   if(p == null){   savePerson(person);   }else{   error += person.getName() + "的工号违反工号的唯一性约束！";   }   }}   return error; }

二、//导出Excel表格1. 入口函数    @RequestMapping(value="/download", method = RequestMethod.GET)    public String exportExcel(HttpServletResponse response) { personService.exportExcel(response); return "redirect:/person";     }

2. 数据准备public void exportExcel(HttpServletResponse response){String[] titles = {"ID","姓名","性别","身份证号","部门","是否部门领导","岗位","角色","工号","工作证号","用工性质","学历","技能/职称","线路","班组","入职日期","电子邮件","内部号码","手机号码","调离","是否离职","是否显示","排序","备注"};List<String[]> dataList = new LinkedList<String[]>();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");List<Person> list = new LinkedList<Person>();//获得有序的数据列表list = findAll(new Specification<Person>() {@Overridepublic Predicate toPredicate(Root<Person> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {return null;}      }, new Sort(new Order(Direction.ASC, "id")));for (Person person : list) {String departmentName="";String isLeader="";String dimission="";String transferType = "";String joinDateStr = "";String navStr = "";String posStr = "";String gender = "";if(person.getDepartment() != null){departmentName = person.getDepartment().getName();}if(person.getLeader() != null){isLeader = person.getLeader() ? "是" : "否";}if(person.getDimission() != null){dimission = person.getDimission() ? "是" : "否";}if(person.getTransferType() != null){transferType = person.getTransferType().getName();}if(person.getJoiningDate() != null ){joinDateStr = sdf.format(person.getJoiningDate());}if(person.getNav() != null){navStr = person.getNav() ? "是" : "否";}if(person.getPos() != null){posStr = String.valueOf(Integer.valueOf(person.getPos()).intValue());}if("MALE".equals(person.getGender())) {gender = "男";} else {gender = "女";}String[] strs = {String.valueOf(Integer.valueOf(person.getId()).intValue()), person.getName(), gender, person.getIdCardNo(), departmentName, isLeader, person.getPosition(), person.getPart(), person.getNumber(), person.getWorkNumber(), person.getNatureWork(), person.getEducation(), person.getSkill(),  person.getLine(), person.getDeck(),joinDateStr, person.getEmail(), person.getInnerNum(), person.getTelephoneNum(), transferType, dimission,navStr, posStr, person.getNote() }; dataList.add(strs);}//sort(dataList, 0);String date = sdf.format(new Date());//导出的Excel的名称String fileName = date + "人员信息表.xls";exportExcel(fileName, titles, dataList, response);    }

3. 导出到Excelpublic void exportExcel(String fileName, String[] titles, List<String[]> dataList, HttpServletResponse response) {HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("sheet1");HSSFCellStyle cellStyle = wb.createCellStyle();HSSFFont fontStyle = wb.createFont();//设置对齐方式cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);//设置字体样式fontStyle.setFontName("宋体");fontStyle.setFontHeightInPoints((short)20);fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//将字体设置到单元格样式对象cellStyle.setFont(fontStyle);HSSFRow row;HSSFCell cell;//表题row = sheet.createRow(0);cell = row.createCell(0);cell.setCellValue("人员信息情况表");//将单元格样式应用于单元格cell.setCellStyle(cellStyle);//合并单元格（起始行，截至行，起始列， 截至列）sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.length-1));//列宽sheet.setDefaultColumnWidth(15);//表头row = sheet.createRow(1);for (int i = 0; i < titles.length; i++) {cell = row.createCell(i);//将单元格样式应用于单元格cell.setCellStyle(cellStyle);cell.setCellValue(titles[i]);}//内容if(dataList != null){for (int i = 2; i < dataList.size()+2; i++) {row = sheet.createRow(i);for (int j = 0; j < titles.length; j++) {cell = row.createCell(j);cell.setCellValue(dataList.get(i-2)[j]);}}}try {OutputStream outputStream = response.getOutputStream();String name = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");//清除首部的空白行response.reset(); response.setContentType("application/vnd.ms-excel;charset=UTF-8");         response.setHeader("Content-Disposition", "attachment;filename=" + name);     wb.write(outputStream);outputStream.flush();outputStream.close();} catch (Exception e) {e.printStackTrace();}   }

出现Cannot get a numeric value from a text cell这样的错误，是由于Excel数据Cell有不同的类型，当读取数据并写入数据库时，如果数据类型不匹配，就会报相关异常，需要人工处理。

row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);  //将数据类型设置为numberic

然后在去读取响应的值。