POI通用型excel上传下载

今天是2020.07.01先记录一下时间，因为后期我会逐步完善这个通用型poi包excel的上传下载。因为excel上传下载操作很多，每次都重新造轮子太麻烦也没必要，因此一个项目有一个通用的上传下载excel就好了。但是我这里的只能适应最普通版的excel上传读取即数据导出，设计exel格式，多行合并等等复杂的暂时没法使用。 开干： 第一步：导包

<!--poi--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.1</version> </dependency>

第二步： excel的上传读取：

import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFRow; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; public class ImortExcelUtil { public static List<List<Object>> getBlankListByExcel(InputStream in, int startRow, int headerRow) throws Exception { List<List<Object>> list = new ArrayList<>(); Workbook workbook = WorkbookFactory.create(in); if (null == workbook) { throw new Exception("创建excel工作簿为空！"); } Sheet sheet = null; Row row = null; Cell cell = null; for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheet = workbook.getSheetAt(i); if (null == sheet) { continue; } Row header = getHeader(sheet, headerRow); System.out.println(header); List<Object> oo = new ArrayList<>(); for (Cell ce : header) { oo.add(ce.getStringCellValue()); } //遍历当前sheet的所有行 int rowNum = sheet.getLastRowNum(); for (int j = startRow; j < rowNum; j++) { row = sheet.getRow(j); if (null == row) { continue; } else { List<Object> obj = new ArrayList<>(0); for (int y = header.getFirstCellNum(); y < header.getLastCellNum(); y++) { //把excel头放在第一行（不需要exel头部的不放就是了） cell = row.getCell(y); obj.add(getCellValue(cell)); } list.add(oo); list.add(obj); } } } return list; } private static Object getCellValue(Cell cell) { Object value = ""; if (null != cell) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); switch (cell.getCellType()) { case STRING: value = cell.getStringCellValue(); break; case NUMERIC: if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = cell.getNumericCellValue(); } else if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); value = sdf.format(date); } else { value = cell.getNumericCellValue(); } if (value instanceof Number) { if (((Number) value).longValue() == ((Number) value).doubleValue()) { value = ((Number) value).longValue(); } } break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case BLANK: value = ""; break; default: break; } } return value; } private static Row getHeader(Sheet sheet, int headerRow) { Row header = sheet.getRow(headerRow); return header; } }

示例一个调用方法：

@PostMapping("/import") @ApiOperation(value = "导入读取excel文件") public R importExcel(@RequestBody MultipartFile file) { /** getBlankListByExcel(InputStream in, int startRow, int headerRow)**/ try { List<List<Object>> blankListByExcel = ImortExcelUtil.getBlankListByExcel(file.getInputStream(), 1, 0); return R.ok("读取成功").put("blankListByExcel", blankListByExcel); } catch (Exception e) { e.printStackTrace(); return R.error("读取失败"); } }

excel 文件的下载： 第一步，先定义一个excel头部信息的实体类：

@Data public class ExcelExportHead { private String headName; private String headKey; private String value; }

数据的exel导出：

import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; import java.util.Map; public class ExportExcelUtil { public static void createBook(List<ExcelExportHead> heads, List<Map<String, Object>> rows, String fileName, HttpServletResponse response) { ServletOutputStream outputStream = null; Sheet sheet = null; Workbook work = null; ExcelExportHead head = null; Cell cell = null; Map<String, Object> map = null; try { if (heads == null || heads.isEmpty()) { throw new Exception("导出文件缺失必要参数heads"); } work = new HSSFWorkbook(); sheet = work.createSheet("1"); Row row = sheet.createRow(0); for (int i = 0; i < heads.size(); i++) { head = heads.get(i); cell = row.createCell(i); cell.setCellValue(head.getHeadName()); } for (int i = 0; i < rows.size(); i++) { row = sheet.createRow(i + 1); map = rows.get(i); for (int j = 0; j < heads.size(); j++) { head = heads.get(j); cell = row.createCell(j); if (null != map) { cell.setCellValue(map.get(head.getHeadKey()) == null ? "" : (String) map.get(head.getHeadKey())); } } } response.reset(); response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1")); response.setContentType("application/octet-stream"); response.setCharacterEncoding("utf-8"); outputStream = response.getOutputStream(); work.write(outputStream); outputStream.flush(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (null != outputStream) { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } } }

提供一个下载的调用示例：

@GetMapping("/download") @ApiOperation(value = "下载excel") public void downloadExcel(HttpServletResponse response) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss"); List<ExcelExportHead> heads = new ArrayList<>(); ExcelExportHead eeh = new ExcelExportHead(); eeh.setHeadKey("name"); eeh.setHeadName("姓名"); heads.add(eeh); ExcelExportHead eem = new ExcelExportHead(); eem.setHeadKey("position"); eem.setHeadName("职位"); heads.add(eem); List<Map<String, Object>> rows = new ArrayList<>(); Map<String, Object> map = new HashMap<>(); map.put("name", "张三"); map.put("position", "几女"); Map<String, Object> map2 = new HashMap<>(); map2.put("name", "李四"); map2.put("position", "吉吉"); rows.add(map); rows.add(map2); String fileName = sdf.format(new Date()) + ".xls"; ExportExcelUtil.createBook(heads, rows, fileName, response); }

注意这里下载使用get请求