Springboot使用Apache|Springboot使用Apache poi Excel 实现Excel导出 Springboot使用ApachepoiExcel实现E

使用Apache poi Excel实现Excel导出数据的工具类
1、添加maven依赖

org.apache.poi poi 3.16 org.apache.poi poi-ooxml 3.16

2、Excel导出工具类，支持07版Excel导出格式为“xlsx”

package com.linksfield.topup.utils; 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.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.List; import java.util.Map; /** * @author: songgt * @date: 2019-12-19 10:11 */ public class ExcelUtil {/** * 根据resultList生成Excel,但是只是生成xlsx为后缀格式的Excel，只能Excel 2007以上的版本可以打开 * 可生成多个工作表，每个工作表最多60000行数据，可以将缓存中的数据先存放到硬盘里再清理缓存 * @param response * @param fileName * @param nameArray * @param rtnList * @throws Exception */ public static void exportExcel2007(HttpServletResponse response, String fileName, String[] nameArray, String[] keyArray, List rtnList) { int maxRow = 60000; try{ OutputStream os = response.getOutputStream(); response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx"); response.setContentType("application/msexcel"); // 内存中缓存记录行数 int rowAccess = 100; SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess); int listSize = rtnList.size(); // 总工作表 int sheetNum = listSize / 60000 + 1; // 总列数 int cellTotal = nameArray.length; // 数据list的取值下标 int listNum = 0; for (int i = 0; i < sheetNum; i++) { Sheet sh = wb.createSheet(fileName + "sheet" + String.valueOf(i)); // 每个SHEET有60000ROW for (int rowNum = 0; rowNum <= maxRow; rowNum++) { Row row = sh.createRow(rowNum); // 每行有cellTotal个CELL if (rowNum == 0){ for (int cellNum = 0; cellNum < cellTotal; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(nameArray[cellNum]); } }else { // 判断是否已取完数据 if (listNum < listSize) { Map map = rtnList.get(listNum); listNum++; for (int cellNum = 0; cellNum < cellTotal; cellNum++) { Cell cell = row.createCell(cellNum); String key = keyArray[cellNum]; String value = https://www.it610.com/article/map.get(key) != null ? map.get(key).toString() :""; cell.setCellValue(value); } } else { break; } } // 每当行数达到设置的值就刷新数据到硬盘,以清理内存 if (rowNum % rowAccess == 0) { ((SXSSFSheet) sh).flushRows(); } } } wb.write(os); os.close(); }catch (Exception e){ e.printStackTrace(); } } }

【Springboot使用Apache|Springboot使用Apache poi Excel 实现Excel导出】3、编写测试Controller

/** * @author: songgt * @date: 2019-12-19 10:20 */ @RestController public class ExcelExportController {@ApiOperation(value = "https://www.it610.com/article/试用用户-导出excel") @GetMapping(path = "/exportTrialUserList") public void exportTrialUserList(HttpServletResponse response,@RequestHeader(name = "x-access-token", required = false) String token){ Map map = new HashMap(); map.put("a",1); map.put("b",2); map.put("c",3); map.put("d",4); List list = new ArrayList(); list.add(map); String[] names = {"字段1","字段2","字段3","字段4"}; String[] keys = map.keySet().toArray(new String[map.size()]); String fileName = "测试Excel"; ExcelUtil.exportExcel2007(response,fileName,names,keys,list); } }