--- highlight: an-old-hope theme: cyanosis --- # []()一、Excel与POI概述目前世面上的Excel分为两个大版本Excel2003和Excel2007 | 区别点 | Excel2003 | Excel2007 | | --------- | --------------------- | ----------------- | | 后缀 | xls | xlsx | | 结构 | 二进制格式,其核心结构是复合文档类型的结构 | XML类型结构 | | 单sheet数据量 | 行:65535 列:256 | 行:1048576:列:16384 | | 特点 | 存储容量有限 | 基于xm压缩,占用空间操作效率 | > Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java语言操作Microsoft Office的功能。 # []()二、POI的核心类与常用API ## []()核心类 | 核心类 | 描述 | | ---- | ---------------------------------- | | HSSF | 操作Microsoft Excel XLS格式类型文件 | | XSSF | 操作Microsoft Excel OOXML XLSX格式类型文件 | | HWPF | 操作Microsoft Word DOC格式类型文件 | | HSLF | 操作Microsoft PowerPoint格式类型文件 | | HDGF | 操作Microsoft Visio格式类型文件 | | HPBF | 操作Microsoft Publisher格式类型文件 | | HSMF | 操作Microsoft Outlook格式类型文件 | ## []()常用API | API | 描述 | | --------- | ------------------------------------------------- | | Workbook | Excel的文档对象【HSSFWorkbook(2003)和XSSFWorkbool(2007)】 | | Sheet | Excel的表单 | | Row | Excel的行 | | Cell | Excel的格子单元 | | Font | Excel字体 | | CellStyle | 格子单元样式 | # []()三、POI的基本使用 ## []()添加依赖 ```java org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml 4.1.2 org.apache.poi poi-ooxml-schemas 4.1.2 ``` ## []()创建excel文件 ```java public static void main(String[] args) throws Exception { //创建workbook工作簿 Workbook wb = new XSSFWorkbook(); //创建表单Sheet Sheet sheet = wb.createSheet("test"); //文件流 FileOutputStream fos = new FileOutputStream("D:\\test.xlsx"); //写入文件 wb.write(fos); fos.close(); } ``` ## []()创建单元格 ```java public static void main(String[] args) throws Exception { //创建workbook工作簿 Workbook wb = new XSSFWorkbook(); //创建表单Sheet Sheet sheet = wb.createSheet("test"); //创建行对象，从0开始 Row row = sheet.createRow(5); //创建单元格，从0开始 Cell cell = row.createCell(5); //单元格写入数据 cell.setCellValue("hello world"); //文件流 FileOutputStream fos = new FileOutputStream("D:\\test.xlsx"); //写入文件 wb.write(fos); fos.close(); } ``` ## []()设置格式 ```java public static void main(String[] args) throws Exception { //创建workbook工作簿 Workbook wb = new XSSFWorkbook(); //创建表单Sheet Sheet sheet = wb.createSheet("test"); //创建行对象，从0开始 Row row = sheet.createRow(5); //创建单元格，从0开始 Cell cell = row.createCell(5); //单元格写入数据 cell.setCellValue("hello world"); //创建单元格样式对象 CellStyle cellStyle = wb.createCellStyle(); //设置边框 cellStyle.setBorderBottom(BorderStyle.DASH_DOT);//下边框 cellStyle.setBorderTop(BorderStyle.HAIR);//上边框 //设置字体 Font font = wb.createFont();//创建字体对象 font.setFontName("华文行楷");//设置字体 font.setFontHeightInPoints((short) 28);//设置字号 cellStyle.setFont(font); //设置宽高 sheet.setColumnWidth(0, 31 * 256);//设置第一列的宽度是31个字符宽度 row.setHeightInPoints(50);//设置行的高度是50个点 //设置居中显示 cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //设置单元格样式 cell.setCellStyle(cellStyle); //合并单元格 CellRangeAddress region = new CellRangeAddress(0, 3, 0, 2); sheet.addMergedRegion(region); //文件流 FileOutputStream fos = new FileOutputStream("D:\\test.xlsx"); //写入文件 wb.write(fos); fos.close(); } ``` ## []()绘制图形 ```java public static void main(String[] args) throws Exception { //创建workbook工作簿 Workbook wb = new XSSFWorkbook(); //创建表单Sheet Sheet sheet = wb.createSheet("test"); //读取图片流 FileInputStream stream=new FileInputStream("D:\\test.jpg"); byte[] bytes= IOUtils.toByteArray(stream); //读取图片到二进制数组 stream.read(bytes); //向Excel添加一张图片,并返回该图片在Excel中的图片集合中的下标 int pictureIdx = wb.addPicture(bytes,Workbook.PICTURE_TYPE_JPEG); //绘图工具类 CreationHelper helper = wb.getCreationHelper(); //创建一个绘图对象 Drawing patriarch = sheet.createDrawingPatriarch(); //创建锚点,设置图片坐标 ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(0);//从0开始 anchor.setRow1(0);//从0开始 //创建图片 Picture picture = patriarch.createPicture(anchor, pictureIdx); picture.resize(); //文件流 FileOutputStream fos = new FileOutputStream("D:\\test.xlsx"); //写入文件 wb.write(fos); fos.close(); } ``` ## []()读取Excel ```java public static void main(String[] args) throws Exception { //创建workbook工作簿 Workbook wb = new XSSFWorkbook("D:\\test.xlsx"); //获取sheet 从0开始 Sheet sheet = wb.getSheetAt(0); int totalRowNum = sheet.getLastRowNum(); Row row = null; Cell cell = null; //循环所有行 for (int rowNum = 3; rowNum < sheet.getLastRowNum(); rowNum++) { row = sheet.getRow(rowNum); StringBuilder sb = new StringBuilder(); //循环每行中的所有单元格 for (int cellNum = 2; cellNum < row.getLastCellNum(); cellNum++) { cell = row.getCell(cellNum); sb.append(getValue(cell)).append("-"); } System.out.println(sb.toString()); } } /** * 获取数据 * @param cell * @return */ private static Object getValue(Cell cell) { Object value = null; switch (cell.getCellType()) { case STRING: //字符串类型 value = cell.getStringCellValue(); break; case BOOLEAN: //boolean类型 value = cell.getBooleanCellValue(); break; case NUMERIC: //数字类型（包含日期和普通数字） if (DateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } break; case FORMULA: //公式类型 value = cell.getCellFormula(); break; default: break; } return value; } ``` # []()四、Excel导入与导出 ## []()导入Excel ```java /** * 导入Excel */ @RequestMapping(value="/import",method = RequestMethod.POST) public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception { //创建工作簿 Workbook wb = new XSSFWorkbook(file.getInputStream()); //获取Sheet 参数：索引 Sheet sheet = wb.getSheetAt(0); //获取Sheet中的每一行，和每一个单元格 //获取用户数据列表 List list = new ArrayList<>(); //从第二行开始获取数据 for (int rowNum = 1; rowNum<= sheet.getLastRowNum() ;rowNum ++) { //根据索引获取每一个行 Row row = sheet.getRow(rowNum); Object [] values = new Object[row.getLastCellNum()]; //从第一列获取数据 for(int cellNum=0;cellNum< row.getLastCellNum(); cellNum ++) { Cell cell = row.getCell(cellNum); Object value = getCellValue(cell); values[cellNum] = value; } User user = new User(values); list.add(user); } //批量保存用户 userService.saveAll(list); return new Result(ResultCode.SUCCESS); } public static Object getCellValue(Cell cell) { //获取到单元格的属性类型 CellType cellType = cell.getCellType(); //根据单元格数据类型获取数据 Object value = null; switch (cellType) { case STRING: value = cell.getStringCellValue(); break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case NUMERIC: if(DateUtil.isCellDateFormatted(cell)) { //日期格式 value = cell.getDateCellValue(); }else{ //数字 value = cell.getNumericCellValue(); } break; case FORMULA: //公式 value = cell.getCellFormula(); break; default: break; } return value; } ``` ## []()导出Excel ```java @RequestMapping(value = "/export", method = RequestMethod.GET) public void export() throws Exception { //获取导出数据 List list = userService.getAll(); //创建工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); //构造sheet String[] titles = {"编号", "姓名", "手机", "性别"}; Sheet sheet = workbook.createSheet(); Row row = sheet.createRow(0); // AtomicInteger具有原子性，安全 AtomicInteger headersAi = new AtomicInteger(); // 设置表头 for (String title : titles) { Cell cell = row.createCell(headersAi.getAndIncrement()); cell.setCellValue(title); } // 从第二行开始 AtomicInteger datasAi = new AtomicInteger(1); Cell cell = null; for (User report : list) { Row dataRow = sheet.createRow(datasAi.getAndIncrement()); //编号 cell = dataRow.createCell(0); cell.setCellValue(report.getId()); //姓名 cell = dataRow.createCell(1); cell.setCellValue(report.getUsername()); //手机 cell = dataRow.createCell(2); cell.setCellValue(report.getMobile()); //性别 cell = dataRow.createCell(3); cell.setCellValue(report.getSex()); } String fileName = URLEncoder.encode(month + "POI.xlsx", "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); workbook.write(response.getOutputStream()); } ``` # []()五、模板打印针对复杂Excel表头，单元格样式，字体等操作，使用准备好的Excel模板，只需要关注模板中的数据即可。 ```java @RequestMapping(value = "/export", method = RequestMethod.GET) public void export() throws Exception { //获取导出数据 List list = userService.getAll(); //加载模板 Resource resource = new ClassPathResource("excel-template/模板.xlsx"); FileInputStream fis = new FileInputStream(resource.getFile()); //根据模板创建工作簿 Workbook wb = new XSSFWorkbook(fis); //读取工作表 Sheet sheet = wb.getSheetAt(0); //抽取公共样式 Row row = sheet.getRow(2); // 创建样式数组，存放每一列单元格的样式 CellStyle styles[] = new CellStyle[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i); styles[i] = cell.getCellStyle(); } //循环数据，构造单元格，设置值与样式 int rowIndex = 2; Cell cell = null; for (User user : list) { row = sheet.createRow(rowIndex++); // 编号 cell = row.createCell(0); cell.setCellValue(user.getUserId()); cell.setCellStyle(styles[0]); // 姓名 cell = row.createCell(1); cell.setCellValue(user.getUsername()); cell.setCellStyle(styles[1]); // 手机 cell = row.createCell(2); cell.setCellValue(user.getMobile()); cell.setCellStyle(styles[2]); // 性别 cell = row.createCell(3); cell.setCellValue(user.getSex()); cell.setCellStyle(styles[3]); } // 完成下载 ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); new DownloadUtils().download(os, response, month + "POI.xlsx"); } ``` # []()六、自定义工具类将Excel导入、导出、以及模板打印相关的公共操作代码进一步封装成工具类使用。 ## []()自定义注解自定义ExcelAttribute注解 ```java @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD) public @interface ExcelAttribute { /** * 列名称 */ String name() default ""; /** * 列的索引 */ int sort(); /** * 字段类型对应的格式 */ String format() default ""; } ``` ## []()导入工具类 ```java public class ExcelImportUtil { /** * 导出数据对象字节码 */ private Class clazz; /** * 导出数据对象字段 */ private Field fields[]; public ExcelImportUtil(Class clazz) { this.clazz = clazz; fields = clazz.getDeclaredFields(); } /** * 基于注解读取excel * * @param inputStream 导入文件流 * @param rowIndex 读取数据的起始行 * @param cellIndex 读取数据的起始单元格位置 * @return */ public List readExcel(InputStream inputStream, int rowIndex, int cellIndex) { List list = new ArrayList(); T entity = null; try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); // 读取sheet每一行数据，转换处理填充到数据对象中 for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) { Row row = sheet.getRow(rowNum); entity = (T) clazz.newInstance(); // 取每一行每一列单元格的值 for (int j = cellIndex; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); for (Field field : fields) { // 循环数据对象字段，有ExcelAttribute注解标识则取之，Excel列号与注解sort属性匹配则为字段设值 if (field.isAnnotationPresent(ExcelAttribute.class)) { field.setAccessible(true); ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class); if (j == ea.sort()) { // 转换处理单元格格式为字段应有格式 field.set(entity, covertAttrType(field, cell)); } } } } list.add(entity); } } catch (Exception e) { e.printStackTrace(); } return list; } /** * 类型转换,将cell单元格的格式转换成子段的类型 * * @param field 字段类型 * @param cell 单元格格 * @return * @throws Exception */ private Object covertAttrType(Field field, Cell cell) throws Exception { String fieldType = field.getType().getSimpleName(); if ("String".equals(fieldType)) { return getValue(cell); } else if ("Date".equals(fieldType)) { return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(getValue(cell)); } else if ("int".equals(fieldType) || "Integer".equals(fieldType)) { return Integer.parseInt(getValue(cell)); } else if ("double".equals(fieldType) || "Double".equals(fieldType)) { return Double.parseDouble(getValue(cell)); } else { return null; } } /** * 格式转为String * * @param cell * @return */ public String getValue(Cell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case STRING: return cell.getRichStringCellValue().getString().trim(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date dt = DateUtil.getJavaDate(cell.getNumericCellValue()); return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dt); } else { // 防止数值变成科学计数法 String strCell = ""; Double num = cell.getNumericCellValue(); BigDecimal bd = new BigDecimal(num.toString()); if (bd != null) { strCell = bd.toPlainString(); } // 去除浮点型自动加的 .0 if (strCell.endsWith(".0")) { strCell = strCell.substring(0, strCell.indexOf(".")); } return strCell; } case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); default: return ""; } } } ``` ## []()导出工具类 ```java /** * 基于模板打印导出Excel工具类 */ @Getter @Setter public class ExcelExportUtil { /** * 写入数据的起始行 */ private int rowIndex; /** * 需要提取的样式所在的行号 */ private int styleIndex; /** * 对象的字节码 */ private Class clazz; /** * 对象中的所有属性 */ private Field fields[]; public ExcelExportUtil(Class clazz, int rowIndex, int styleIndex) { this.clazz = clazz; this.rowIndex = rowIndex; this.styleIndex = styleIndex; fields = clazz.getDeclaredFields(); } /** * 基于注解导出 * * @param response * @param inputStream 模板的输入流 * @param objectList 导出数据 * @param fileName 生成的文件名 * @throws Exception */ public void export(HttpServletResponse response, InputStream inputStream, List objectList, String fileName) throws Exception { //根据模板创建工作簿 XSSFWorkbook workbook = new XSSFWorkbook(inputStream); //读取工作表 Sheet sheet = workbook.getSheetAt(0); //提取公共的样式 Row styleRow = sheet.getRow(styleIndex); CellStyle[] styles = new CellStyle[styleRow.getLastCellNum()]; for (int i = 0; i < styleRow.getLastCellNum(); i++) { styles[i] = styleRow.getCell(i).getCellStyle(); } // AtomicInteger原子类，具有原子性，保证线程安全 AtomicInteger datasAi = new AtomicInteger(rowIndex); //循环创建每一行和每一个单元格的数据 for (T t : objectList) { // 自增获取每一行 Row row = sheet.createRow(datasAi.getAndIncrement()); // 为每一行每一列单元格赋值与设置样式 for (int i = 0; i < styles.length; i++) { Cell cell = row.createCell(i); cell.setCellStyle(styles[i]); // 循环判断数据对象 for (Field field : fields) { // 如果有ExcelAttribute注解标识则进一步处理 if (field.isAnnotationPresent(ExcelAttribute.class)) { field.setAccessible(true); // 获取字段上的注解 ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class); // 判断该字段与模板Excel那列对应并设置样式、值 if (i == ea.sort()) { if (field.get(t) != null) { cell.setCellValue(field.get(t).toString()); } } } } } } fileName = URLEncoder.encode(fileName, "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); workbook.write(response.getOutputStream()); } } ``` ## []()数据对象数据对象使用自定义注解 ```java @Getter @Setter @NoArgsConstructor @ToString public class User { @ExcelAttribute(sort = 0) private String id; @ExcelAttribute(sort = 1) private String username; @ExcelAttribute(sort = 2) private String mobile; @ExcelAttribute(sort = 3) private String sex; } ``` ## []()导入导出工具类测试导出测试 ```java @RequestMapping(value = "/export", method = RequestMethod.GET) public void export() throws Exception { //数据 List list = userService.getAll(); // 加载模板 Resource resource = new ClassPathResource("excel-template/模板.xlsx"); FileInputStream fis = new FileInputStream(resource.getFile()); new ExcelExportUtil(User.class, 2, 2).export(response, fis, list, "POI.xlsx"); } ``` 导入测试 ```java @RequestMapping(value = "/import", method = RequestMethod.POST) public Result importUser(@RequestParam(name = "file") MultipartFile file) throws Exception { List list = new ExcelImportUtil(User.class).readExcel(file.getInputStream(), 1, 1); userService.saveAll(list); return new Result(ResultCode.SUCCESS); } ``` # []()七、百万数据导入与导出 > Excel2003使用HSSF对象时，最多允许存储65536条数据，一般用来处理较少的数据量。 > Excel2007使用XSSF对象时，因为它采用ooxml格式，最多可支持1048576条数据，单个sheet表就支持近百万条数据。 > 在实际运行时，由于执行POI报表所产生的行对象，单元格对象，字体对象等都不会销毁，就会导致OOM的风险。 ## []()解决方案 Apache Poi官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：用户模式： > 用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存事件模式： > 基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。 SXSSF对象： > 用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel ## []()导入Excel > Excel2007实质是一种特殊的XML存储数据，可以使用基于SAX的方式解析XML完成Excel的读取。SAX提供了一种从XML文档中读取数据的机制,不同于将数据一次性读取到内存中。它逐行扫描文档，一边扫描一边解析。 **自定义处理器** ```java /** * 自定义Sheet基于Sax的解析处理器 */ public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler { private User user; private List userList = new ArrayList(); /** * 解析行开始 * * @param rowNum 真正数据起始行号 */ @Override public void startRow(int rowNum) { if (rowNum > 2) { user = new User(); } } /** * 自定义解析,解析每一个单元格 * * @param cellReference * @param value * @param comment */ @Override public void cell(String cellReference, String value, XSSFComment comment) { if (user != null) { switch (cellReference.substring(0, 1)) { case "A": user.setId(value); break; case "B": user.setUserName(value); break; case "C": user.setMobile(value); break; case "D": user.setSex(value); break; default: break; } } } /** * 解析行结束 * * @param rowNum */ @Override public void endRow(int rowNum) { if (user != null) { userList.add(user); } } /** * 处理头尾 * * @param text * @param isHeader * @param tagName */ @Override public void headerFooter(String text, boolean isHeader, String tagName) { if (!isHeader) { System.out.println(userList.size()); } } } ``` 上传时自定义解析 ```java @RequestMapping(value = "/import", method = RequestMethod.POST) public Result importUser(@RequestParam(name = "file") MultipartFile file) throws Exception { //获取OPCPackage对象 OPCPackage pkg = OPCPackage.open(file.getInputStream()); try { //创建XSSFReader对象 XSSFReader reader = new XSSFReader(pkg); //获取SharedStringsTable对象 SharedStringsTable sst = reader.getSharedStringsTable(); //获取StylesTable对象 StylesTable styles = reader.getStylesTable(); //创建Sax的XmlReader对象 XMLReader parser = XMLReaderFactory.createXMLReader(); //设置处理器 parser.setContentHandler(new XSSFSheetXMLHandler(styles, sst, new SheetHandler(), false)); XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData(); //逐行读取 while (sheets.hasNext()) { InputStream sheetstream = sheets.next(); InputSource sheetSource = new InputSource(sheetstream); try { parser.parse(sheetSource); } finally { sheetstream.close(); } } } finally { pkg.close(); } return new Result(ResultCode.SUCCESS); } ``` ## []()导出Excel > 使用SXSSFWork对象，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中\ > 的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。替换XSSFWorkbook对象，使用 ```java @RequestMapping(value = "/export", method = RequestMethod.GET) public void export() throws Exception { //获取导出数据 List list = userService.getAll(); //创建工作簿,同时指定内存中存在的最大对象数量 SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(200); //构造sheet String[] titles = {"编号", "姓名", "手机", "性别"}; Sheet sheet = sxssfWorkbook.createSheet(); Row row = sheet.createRow(0); // AtomicInteger具有原子性，安全 AtomicInteger headersAi = new AtomicInteger(); // 设置表头 for (String title : titles) { Cell cell = row.createCell(headersAi.getAndIncrement()); cell.setCellValue(title); } // 从第二行开始 AtomicInteger datasAi = new AtomicInteger(1); Cell cell = null; Row dataRow; for (int i = 0; i < 100000; i++) { for (User report : list) { dataRow= sheet.createRow(datasAi.getAndIncrement()); //编号 cell = dataRow.createCell(0); cell.setCellValue(report.getId()); //姓名 cell = dataRow.createCell(1); cell.setCellValue(report.getUsername()); //手机 cell = dataRow.createCell(2); cell.setCellValue(report.getMobile()); //性别 cell = dataRow.createCell(3); cell.setCellValue(report.getSex()); } } String fileName = URLEncoder.encode(month + "POI.xlsx", "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); sxssfWorkbook.write(response.getOutputStream()); } ```