项目上这种功能很多，写了一个工具类，代码有点垃圾，大神勿喷
导入导出poi组件Excel
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
package com.tc.util; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Footer; import org.apache.poi.ss.usermodel.Header; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Component; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.lang.reflect.Field; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 导出工具类 <p>07导出不限制行数，03有66535局限性</p> * * @author Fyg_gm * @since 2021-05-12 21:01:20 */ @Component public class ExcelUtil { Logger logger = LoggerFactory.getLogger(ExcelUtil.class); /** * 07 无限制导出 * * @param response * @param list 导出的数据 * @param filename 导出文件名 * @param title 导出表头 */ public void exportMultiToDownFile(HttpServletResponse response, List<?> list, String filename, String[] title) { Logger logger = LoggerFactory.getLogger(ExcelUtil.class); filename += new SimpleDateFormat("yyyy-MM-dd").format(System.currentTimeMillis()); //创建工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); //创建表单 XSSFSheet sheet = genSheet(workbook, filename); //创建表单样式 Map<String, XSSFCellStyle> tableStyle = createStyle(workbook);//创建表头样式 Map<String, XSSFCellStyle> titleStyle = createStyle(workbook);//创建标题样式 Map<String, XSSFCellStyle> contextStyle = createStyle(workbook);//创建正文样式 //创建Excel genExcel(filename, list, title, sheet, tableStyle.get("cellStyle2"), titleStyle.get("cellStyle"), contextStyle.get("cellStyle3")); String suffix = ".xls"; filename += suffix; // 最终已流的形式返回 OutputStream out = null; try { out = response.getOutputStream(); response.setHeader("content-type", "application/octet-stream"); response.setContentType("application/octet-stream"); response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); logger.info(e.getMessage()); } finally { try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); logger.info(e.getMessage()); } } } /** * 03 限制导出行数66535 有局限性 * * @param response * @param list 导出的数据 * @param filename 导出的文件名 * @param title 导出的表头 */ public void exportToDownFile(HttpServletResponse response, List<?> list, String filename, String[] title) { filename += new SimpleDateFormat("yyyy-MM-dd").format(System.currentTimeMillis()); String suffix = ".xls"; filename += suffix; //创建excel表 HSSFWorkbook workbook = new HSSFWorkbook(); //建立sheet对象 HSSFSheet sheet = workbook.createSheet(filename); //设置默认行宽 sheet.setDefaultColumnWidth(20); //创建样式 Map<String, HSSFCellStyle> style = createStyle(workbook); //创建表头 HSSFRow row = sheet.createRow(0); row.setHeightInPoints(20);//行高 HSSFCell cell = row.createCell(0); cell.setCellValue(filename); cell.setCellStyle(style.get("cellStyle")); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.length - 1))); //创建标题 HSSFRow rowTitle = sheet.createRow(1); rowTitle.setHeightInPoints(20); HSSFCell hc; for (int i = 0; i < title.length; i++) { hc = rowTitle.createCell(i); hc.setCellValue(title[i]); //设置标题样式 hc.setCellStyle(style.get("cellStyle2")); } //创建表格数据 Field[] fields; int i = 2; int index = 0;//记录额外创建的sheet数量 //数据源 for (Object obj : list) { //反射获取到实体 fields = obj.getClass().getDeclaredFields(); HSSFRow rowBody = sheet.createRow(i); rowBody.setHeightInPoints(20); int j = 0; //遍历渲染表格 for (Field f : fields) { f.setAccessible(true); Object va = null; try { va = f.get(obj); } catch (IllegalAccessException e) { e.printStackTrace(); } if (null == va) { va = "---"; } //创建行数 hc = rowBody.createCell(j); //赋值数据 hc.setCellValue(va.toString()); //设置表格样式 hc.setCellStyle(style.get("cellStyle3")); j++; } i++; } //最终已流的形式返回 OutputStream out = null; try { out = response.getOutputStream(); response.setHeader("content-type", "application/octet-stream"); response.setContentType("application/octet-stream"); response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); logger.info(e.getMessage()); } finally { try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); logger.info(e.getMessage()); } } } //封装动态渲染excel public static void genExcel(String filename, List<?> list, String[] title, XSSFSheet sheet, XSSFCellStyle tableStyle, XSSFCellStyle titleStyle, XSSFCellStyle contextStyle) { //设置默认行宽 sheet.setDefaultColumnWidth(20); //创建第一行，为标题，index从0开始 XSSFRow row = sheet.createRow(0); row.setHeightInPoints(20); //创建一列 XSSFCell cell = row.createCell(0); //标题 cell.setCellValue(filename); //设置标题样式 cell.setCellStyle(titleStyle); //设置标题位置 sheet.addMergedRegion(new CellRangeAddress( 0, //first row 0, //last row 0, //first column (title.length - 1) //last column )); //创建第二行 表头 XSSFRow rowTitle = sheet.createRow(1); rowTitle.setHeightInPoints(20); XSSFCell xc; for (int i = 0; i < title.length; i++) { xc = rowTitle.createCell(i); xc.setCellValue(title[i]); //设置表头样式 xc.setCellStyle(tableStyle); } //从数据库取数据填充到Excel， Field[] fields; //i从2开始计数，因为上面已经创建了 0 1行 int i = 2; for (Object obj : list) { //反射获取到实体 fields = obj.getClass().getDeclaredFields(); //从第三行动态去创建 XSSFRow rowBody = sheet.createRow(i); rowBody.setHeightInPoints(20); int j = 0; //遍历渲染表格 for (Field f : fields) { f.setAccessible(true); Object va = null; try { va = f.get(obj); } catch (IllegalAccessException e) { e.printStackTrace(); } if (null == va) { va = "---"; } //创建行数 xc = rowBody.createCell(j); //赋值数据 xc.setCellValue(va.toString()); //设置表格样式 xc.setCellStyle(contextStyle); j++; } i++; } } //设置表单，并生成表单 public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName) { //生成表单 XSSFSheet sheet = workbook.createSheet(sheetName); //设置表单文本居中 sheet.setHorizontallyCenter(true); sheet.setFitToPage(false); //打印时在底部右边显示文本页信息 Footer footer = sheet.getFooter(); footer.setRight("Page " + HeaderFooter.numPages() + " Of " + HeaderFooter.page()); //打印时在头部右边显示Excel创建日期信息 Header header = sheet.getHeader(); header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time()); //设置打印方式 XSSFPrintSetup ps = sheet.getPrintSetup(); ps.setLandscape(true); // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印 ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印尺寸大小设置为A4纸大小 return sheet; } //设置exportToDownFile样式 public Map<String, HSSFCellStyle> createStyle(HSSFWorkbook workbook) { Map<String, HSSFCellStyle> map = new HashMap<>(); //表头样式（加粗，水平居中，垂直居中） HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中 // cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //设置边框样式 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 HSSFFont fontStyle = workbook.createFont(); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(fontStyle); //标题样式（加粗，垂直居中） HSSFCellStyle cellStyle2 = workbook.createCellStyle(); // cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 cellStyle2.setFont(fontStyle); //设置边框样式 cellStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 cellStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 cellStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 cellStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 //字段样式（垂直居中） HSSFCellStyle cellStyle3 = workbook.createCellStyle(); //设置边框样式 cellStyle3.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 cellStyle3.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 cellStyle3.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 cellStyle3.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 map.put("cellStyle", cellStyle); map.put("cellStyle2", cellStyle2); map.put("cellStyle3", cellStyle3); return map; } //设置exportMultiToDownFile样式 public Map<String, XSSFCellStyle> createStyle(XSSFWorkbook workbook) { Map<String, XSSFCellStyle> map = new HashMap<>(); //表头样式（加粗，水平居中，垂直居中） XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中 // cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //设置边框样式 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 XSSFFont fontStyle = workbook.createFont(); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(fontStyle); //标题样式（加粗，垂直居中） XSSFCellStyle cellStyle2 = workbook.createCellStyle(); // cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 cellStyle2.setFont(fontStyle); //设置边框样式 cellStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 cellStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 cellStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 cellStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 //字段样式（垂直居中） XSSFCellStyle cellStyle3 = workbook.createCellStyle(); // cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //设置边框样式 cellStyle3.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 cellStyle3.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 cellStyle3.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 cellStyle3.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 map.put("cellStyle", cellStyle); map.put("cellStyle2", cellStyle2); map.put("cellStyle3", cellStyle3); return map; } }
