java通过POI解析Excel表格内容

为了使用SpringMVC读取参数使用MultipartFile接收参数，但MultipartFile与File不同，传参时经常会出现错误，可以通过getInputStream讲其转换为IO流做进一步处理。

public ModelAndView readExcel(MultipartFile aaa){ ModelAndView mav=new ModelAndView(); BufferedInputStream bf=null; try { bf=new BufferedInputStream(aaa.getInputStream()); } catch (Exception e) { e.printStackTrace(); } XSSFWorkbook workbook=null; try { workbook=new XSSFWorkbook(bf); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("___workbook__"+workbook); XSSFSheet sheet = workbook.getSheetAt(0); for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); if (row == null) { continue; } XSSFCell cell = null; for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) { String value = ""; cell = row.getCell(columnIndex); if (cell != null) { // 注意：一定要设成这个，否则可能会出现乱码 // cell.setEncoding(HSSFCell.ENCODING_UTF_16); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd") .format(date); } else { value = ""; } } else { value = new DecimalFormat("0").format(cell .getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } break; case XSSFCell.CELL_TYPE_BLANK: break; case XSSFCell.CELL_TYPE_ERROR: value = ""; break; case XSSFCell.CELL_TYPE_BOOLEAN: value = (cell.getBooleanCellValue() == true ? "Y" : "N"); break; default: value = ""; } } if (columnIndex == 0 && value.trim().equals("")) { break; } //excel表格中字段顺序为:用户名,密码,电话和地址,为方便起见假设字段一一对应 if (columnIndex == 0) { String n = value; System.out.print(n+" "); } else if (columnIndex == 1) { String v = value; System.out.print(v+" "); } else if (columnIndex == 2) { String v = value; System.out.print(v+" "); } else if (columnIndex == 3) { String v = value; System.out.println(v+" "); } } } mav.setViewName("wm/wmTcDaily/edit"); return mav; }

通过测试可以把excel文件中的内容输入至控制台，后续操作可将取得的字段封入实体类，进一步存入List，最终存入数据库

导出excel表文件：

@RequestMapping("/download") public ModelAndView transferExcel(HttpServletResponse response){ ModelAndView mav=new ModelAndView(); //创建HSSFWorkbook对象(excel的文档对象) HSSFWorkbook wkb = new HSSFWorkbook(); //建立新的sheet对象（excel的表单） HSSFSheet sheet=wkb.createSheet("成绩表"); //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个 HSSFRow row1=sheet.createRow(0); //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个 HSSFCell cell=row1.createCell(0); //设置单元格内容 cell.setCellValue("学员考试成绩一览表"); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 sheet.addMergedRegion(new CellRangeAddress(0,0,0,3)); //在sheet里创建第二行 HSSFRow row2=sheet.createRow(1); //创建单元格并设置单元格内容 row2.createCell(0).setCellValue("姓名"); row2.createCell(1).setCellValue("班级"); row2.createCell(2).setCellValue("笔试成绩"); row2.createCell(3).setCellValue("机试成绩"); //在sheet里创建第三行 HSSFRow row3=sheet.createRow(2); row3.createCell(0).setCellValue("李明"); row3.createCell(1).setCellValue("As178"); row3.createCell(2).setCellValue(87); row3.createCell(3).setCellValue(78); //输出Excel文件 OutputStream output; try { output = response.getOutputStream(); response.reset(); response.setHeader("Content-disposition", "attachment; filename=details.xls"); response.setContentType("application/msexcel"); wkb.write(output); output.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } mav.setViewName("wm/wmTcDaily/edit"); return mav; }