在项目中，有很多对excel的操作，大都数时候我们都会使用poi工具类，本文将介绍poi的一些使用方法。
1.poi导入excel，并展示数据
使用poi导入excel，解析后返回List数据到前台展示。
/** * * (读入excel文件，解析后返回) * @param file(文件类型) * @throws IOException * @return List<String[]> */ public static List<String[]> readExcel(MultipartFile file) throws IOException { // 检查文件 checkFile(file); // 获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回 List<String[]> list = new ArrayList<String[]>(); if (workbook != null) { for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++ ) { // 获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); if (sheet == null) { continue; } // 获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); // 获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); // 循环除了第一行的所有行 for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++ ) { // 获得当前行 Row row = sheet.getRow(rowNum); if (row == null) { continue; } // 获得当前行的开始列 int firstCellNum = row.getFirstCellNum(); // 获得当前行的列数 int lastCellNum = row.getLastCellNum(); String[] cells = new String[row.getLastCellNum()]; // 循环当前行 for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++ ) { Cell cell=row.getCell(cellNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL); cells[cellNum] = getCellValue(cell); } list.add(cells); } } } return list; } /** * * (判断文件) * @param file * @throws IOException * @return void */ public static void checkFile(MultipartFile file) throws IOException { // 判断文件是否存在 if (null == file) { throw new FileNotFoundException("文件不存在！"); } // 获得文件名 String fileName = file.getOriginalFilename(); // 判断文件是否是excel文件 if (!fileName.endsWith(XLS_TYPE) && !fileName.endsWith(XLSX_TYPE)) { throw new IOException(fileName + "不是excel文件"); } } public static String getCellValue(Cell cell) { String cellValue = ""; if (cell == null) { return cellValue; } // 把数字当成String来读，避免出现1读成1.0的情况 if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cell.setCellType(Cell.CELL_TYPE_STRING); } // 判断数据的类型 switch (cell.getCellType()) { // 数字 case Cell.CELL_TYPE_NUMERIC: cellValue = String.valueOf(cell.getNumericCellValue()); break; // 字符串 case Cell.CELL_TYPE_STRING: cellValue = String.valueOf(cell.getStringCellValue()); break; // Boolean case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; // 公式 case Cell.CELL_TYPE_FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; // 空值 case Cell.CELL_TYPE_BLANK: cellValue = ""; break; // 故障 case Cell.CELL_TYPE_ERROR: cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } /** * * (getWorkBook:(创建WorkBook对象)) * @param file * @return * @return Workbook */ public static Workbook getWorkBook(MultipartFile file) { // 获得文件名 String fileName = file.getOriginalFilename(); // 创建Workbook工作薄对象，表示整个excel Workbook workbook = null; try { // 获取excel文件的io流 InputStream is = file.getInputStream(); // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 if (fileName.endsWith(XLS_TYPE)) { // 2003 workbook = new HSSFWorkbook(is); } else if (fileName.endsWith(XLSX_TYPE)) { // 2007 workbook = new XSSFWorkbook(is); } } catch (IOException e) { e.printStackTrace(); } return workbook; }
2.poi导出excel并以web下载方式保存excel
将数据库中的数据组装成excel并导出，并在web下载栏中直接下载。
/** * 人员导出 * * @param sysSysUserVO * @throws IOException */ @PostMapping("/exportUser") @ApiOperation(value = "导出人员excel", notes = "导出人员excel") public void exportUser(@ApiParam(name = "人员id", value = "人员id", required = false) @RequestBody List<SysUserVO> sysSysUserVO) throws IOException { List<SysUserVO> middleList = new ArrayList<>(); // 查询用户详细信息 for (SysUserVO sysUserVO : sysSysUserVO) { List<SysUserVO> resultListSysUser = sysUserService.querySysUserAll(sysUserVO); userInfoUtil.completionInformation(resultListSysUser.get(0)); middleList.add(resultListSysUser.get(0)); } // excle格式 String[] headers = {"用户名", "姓名", "密码", "启动状态", "岗位", "角色", "所属部门", "手机", "邮箱", "身份证号"}; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // 设置列宽 sheet.setDefaultColumnWidth((short)18); HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { // 创建单元格，每行多少数据就创建多少个单元格 HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); // 给单元格设置内容 cell.setCellValue(text); } for (int j = 0; j < middleList.size(); j++) { SysUserVO export = middleList.get(j); // 从第二行开始填充数据 row = sheet.createRow(j + 1); List<String> datas = new ArrayList<>(); String userName = export.getUsername(); String trueName = export.getTureName(); String password = export.getPassword(); String status = String.valueOf(export.getIsEnabled()); String postName = export.getPostName(); String roleName = export.getRoleName(); String organName = export.getOrganizationName(); String phone = export.getMobile(); String email = export.getEmail(); String identityCard = export.getIdentityCard(); datas.add(userName); datas.add(trueName); datas.add(password); datas.add(status); datas.add(postName); datas.add(roleName); datas.add(organName); datas.add(phone); datas.add(email); datas.add(identityCard); for (int k = 0; k < datas.size(); k++) { String string = datas.get(k); HSSFCell cell = row.createCell(k); HSSFRichTextString richString = new HSSFRichTextString(string); HSSFFont font3 = workbook.createFont(); // 定义Excel数据颜色，这里设置为蓝色 font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } String fileName = "人员导出.xls"; // 导出 HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse(); RequestAttributes requsetAttributes = RequestContextHolder.currentRequestAttributes(); HttpServletRequest request = ((ServletRequestAttributes)requsetAttributes).getRequest(); // 获得浏览器代理信息 final String userAgent = request.getHeader("USER-AGENT"); // 判断浏览器代理并分别设置响应给浏览器的编码格式 if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident")) { // IE浏览器 fileName = URLEncoder.encode(fileName, "UTF-8"); } else if (StringUtils.contains(userAgent, "Mozilla")) { // google,火狐浏览器 fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); } else { // 其他浏览器 fileName = URLEncoder.encode(fileName, "UTF-8");// 其他浏览器 } // 设置HTTP响应头 response.reset(); // 重置 如果不在页面上显示而是下载下来 则放开注释 response.setContentType("application/octet-stream"); response.addHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\""); OutputStream os = response.getOutputStream(); workbook.write(os); os.close(); }
3.poi导出并以流方式保存excel
将数据库中的数据组装成excel并导出，并以数据流方式存在指定的路径。
public class BarcodeExportlFlow implements IBarcodeExport { @Autowired BarcodeManageBatchSerivce barcodeManageBatchSerivce; @Override public OutputStream exportData() throws IOException { //查询批次码 BarcodeBatchManageBo input = new BarcodeBatchManageBo(); List<BarcodeBatchManageBo> middleList = barcodeManageBatchSerivce.selectBatch(input); String[] headers = {"id", "条码批次码", "激活状态", "有效状态", "导入人", "导入时间", "激活人", "激活时间", "作废人", "作废时间", "本批次条数"}; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); //设置列宽 sheet.setDefaultColumnWidth((short) 18); HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { //创建单元格，每行多少数据就创建多少个单元格 HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); //给单元格设置内容 cell.setCellValue(text); } for (int j = 0; j < middleList.size(); j++) { BarcodeBatchManageBo export = middleList.get(j); //从第二行开始填充数据 row = sheet.createRow(j + 1); List<String> datas = new ArrayList<>(); String id = export.getId().toString(); String batchCode = export.getBatchCode(); String activationStatus = export.getActivationStatus(); String effectiveStatus = export.getEffectiveStatus(); datas.add(id); datas.add(batchCode); datas.add(activationStatus); datas.add(effectiveStatus); for (int k = 0; k < datas.size(); k++) { String string = datas.get(k); HSSFCell cell = row.createCell(k); HSSFRichTextString richString = new HSSFRichTextString(string); HSSFFont font3 = workbook.createFont(); //定义Excel数据颜色，这里设置为蓝色 font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } FileOutputStream fos = new FileOutputStream("D:/wb.xls"); workbook.write(fos); fos.close(); return null; } }
