Spring Boot Excel 文件导出

目标：

实现Excel文件的直接导出下载，后续开发不需要开发很多代码，直接继承已经写好的代码，增加一个Xml配置就可以直接导出。

实现：

1、抽象类 BaseExcelView 继承 webmvc 的 AbstractXlsxStreamingView 抽象类， AbstractXlsxStreamingView 是webmvc继承了最顶层View接口，是可以直接大量数据导出的不会造成内存泄漏问题，即 SXSSFWorkbook 解决了内存问题， 导出只支持xlsx类型文件。

抽象类代码 BaseExcelView ：

public abstract class BaseExcelView extends AbstractXlsxStreamingView { private static final Logger logger = LoggerFactory.getLogger(BaseExcelView.class); /** * 获取导出文件名 * * @return */ abstract protected String getFileName(); /** * 获取表单名称 * * @return */ abstract protected String getSheetName(); /** * 获取标题栏名称 * * @return */ abstract protected String[] getTitles(); /** * 获取列宽 * * @return */ abstract protected short[] getColumnWidths(); /** * 构造内容单元格 * * @param sheet */ abstract protected void buildContentCells(Sheet sheet); @Override protected void buildExcelDocument( Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // 构造标题单元格 SXSSFWorkbook Sheet sheet = buildTitleCells(workbook); // 构造内容单元格 buildContentCells(sheet); // 设置响应头 setResponseHead(request, response); } /** * 设置响应头 * * @param response * @throws IOException */ protected void setResponseHead(HttpServletRequest request, HttpServletResponse response) throws IOException { // 文件名 String fileName = getFileName(); String userAgent = request.getHeader("user-agent").toLowerCase(); logger.info("客户端请求头内容:"); logger.info("user-agent\t值: {}", userAgent); if (userAgent != null) { if (userAgent.contains("firefox")) { // firefox有默认的备用字符集是西欧字符集 fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); } else if (userAgent.contains("webkit") && (userAgent.contains("chrome") || userAgent.contains("safari"))) { // webkit核心的浏览器,主流的有chrome,safari,360 fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); } else { // 新老版本的IE都可直接用URL编码工具编码后输出正确的名称,无乱码 fileName = URLEncoder.encode(fileName, "UTF-8"); } } //响应头信息 response.setCharacterEncoding("UTF-8"); response.setContentType("application/ms-excel; charset=UTF-8"); response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx"); } /** * 构造标题单元格 * * @param * @return */ protected Sheet buildTitleCells(Workbook workbook) { // 表单名称 String sheetName = getSheetName(); // 标题名称 String[] titles = getTitles(); // 列宽 short[] colWidths = getColumnWidths(); // 创建表格 Sheet sheet = workbook.createSheet(sheetName); // 标题单元格样式 CellStyle titleStyle = getHeadStyle(workbook); // 默认内容单元格样式 CellStyle contentStyle = getBodyStyle(workbook); // 标题行 Row titleRow = sheet.createRow(0); // 创建标题行单元格 for (int i = 0; i < titles.length; i++) { // 标题单元格 Cell cell = titleRow.createCell((short) i); cell.setCellType(CellType.STRING); cell.setCellValue(new XSSFRichTextString(titles[i])); cell.setCellStyle(titleStyle); // 设置列宽 sheet.setColumnWidth((short) i, (short) (colWidths[i] * 256)); // 设置列默认样式 sheet.setDefaultColumnStyle((short) i, contentStyle); } return sheet; } /** * 设置表头的单元格样式 */ public CellStyle getHeadStyle(Workbook workbook) { // 创建单元格样式 CellStyle cellStyle = workbook.createCellStyle(); // 设置单元格的背景颜色为淡蓝色 cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index); // 设置填充字体的样式 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置单元格居中对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格垂直居中对齐 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 创建单元格内容显示不下时自动换行 cellStyle.setWrapText(true); // 设置单元格字体样式 Font font = workbook.createFont(); // 字号 font.setFontHeightInPoints((short) 12); // 加粗 font.setBold(true); // 将字体填充到表格中去 cellStyle.setFont(font); // 设置单元格边框为细线条（上下左右） cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); return cellStyle; } /** * 设置表体的单元格样式 */ public CellStyle getBodyStyle(Workbook workbook) { // 创建单元格样式 CellStyle cellStyle = workbook.createCellStyle(); // 设置单元格居中对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格居中对齐 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 创建单元格内容不显示自动换行 cellStyle.setWrapText(true); //设置单元格字体样式字体 Font font = workbook.createFont(); // 字号 font.setFontHeightInPoints((short) 10); // 将字体添加到表格中去 cellStyle.setFont(font); // 设置单元格边框为细线条 cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); return cellStyle; }}

Excel导出实现 1: 可以直接继承 BaseExcelView 实现定义的方法 eg:

public class CheckExcelView extends BaseExcelView { private List<T> vo; public CheckExcelView(List<T> vo) { this.vo= vo; } @Override protected String getFileName() { String time = DateUtils.getLocalFullDateTime14(); return "导出文件" + time; } @Override protected String getSheetName() { return "报表"; } @Override protected String[] getTitles() { return new String[] { "申请时间"}; } @Override protected short[] getColumnWidths() { return new short[] { 20}; } @Override protected void buildContentCells(Sheet sheet) { DecimalFormat df = new DecimalFormat("0.00"); int rowNum = 1; for (T o : vO) { Row crow = sheet.createRow(rowNum++); crow.createCell(0).setCellValue(o.getApplicationDate())); } }}

导出实现 2： XML配置导出

1、需要定义XML的配置 export-config.xml

<?xml version="1.0" encoding="UTF-8"?><configuration> <table id="demo" name="测试"> <columns> <column id="name" name="名称" width="40"></column> </columns> </table></configuration>

2、XMl解析配置

@Rootpublic class Export { @ElementList(entry = "table", inline = true) private List<Table> table; public List<Table> getTable() { return table; } public void setTable(List<Table> table) { this.table = table; } public static class Table { @Attribute private String id; @Attribute private String name; @ElementList(entry = "column") private List<Column> columns; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List<Column> getColumns() { return columns; } public void setColumns(List<Column> columns) { this.columns = columns; } } public static class Column { @Attribute private String id; @Attribute private String name; @Attribute private short width; @Attribute(required = false) private String mapping; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getMapping() { return mapping; } public void setMapping(String mapping) { this.mapping = mapping; } public short getWidth() { return width; } public void setWidth(short width) { this.width = width; } }}

3、解析XMl方法配置

@Servicepublic class IExportService { private Export tables; private Map<String, Export.Table> tableMap; @SuppressWarnings("rawtypes") @PostConstruct public void init() throws Exception { InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("export-config.xml"); Serializer serializer = new Persister(); tables = serializer.read(Export.class, inputStream); tableMap = new HashMap<>(); for (Export.Table table : tables.getTable()) { tableMap.put(table.getId(), table); } } public Export.Table getTable(String key) { return tableMap.get(key); }}

4、导出基础 ExcelExportView 代码实现

public class ExcelExportView extends BaseExcelView { private String[] titles; private short[] columnWidths; List<Map<String, Object>> results; private Export.Table table; private IExportService iExportService; @Override protected String getFileName() { return table.getName(); } @Override protected String getSheetName() { return table.getName(); } @Override protected String[] getTitles() { return this.titles; } @Override protected short[] getColumnWidths() { return this.columnWidths; } public ExcelExportView() { this.iExportService = ApplicationContextProvider.getBean(IExportService.class); } @Override protected void buildContentCells(Sheet sheet) { int dataIndex = 1; if(CollectionUtils.isEmpty(results)){ return; } for (Map<String, Object> data : results) { Row row = sheet.createRow(dataIndex++); for (int i = 0; i < table.getColumns().size(); i++) { Export.Column column = table.getColumns().get(i); Cell cell = row.createCell(i); Object value = data.get(column.getId()); if (value == null) { value = ""; } cell.setCellValue(new XSSFRichTextString(value.toString())); } } } public void exportExcel(String key, List<Map<String, Object>> results) { this.table = iExportService.getTable(key); if (null == table) { return; } this.results = results; this.titles = new String[table.getColumns().size()]; this.columnWidths = new short[table.getColumns().size()]; for (int i = 0; i < table.getColumns().size(); i++) { Export.Column column = table.getColumns().get(i); titles[i] = column.getName(); columnWidths[i] = column.getWidth(); } }}

最后：导出Controller代码实现

@RequestMapping(path = "/export", method = RequestMethod.GET, produces = "application/octet-stream;charset=UTF-8")public @ResponseBodyModelAndView export(){ Long loginComId = loginContext.getCompany().getId(); List<T> list = new ArrayList<>(); ExcelExportView exportView = new ExcelExportView(); exportView.exportExcel("XMl中表的ID", BeanUtils.objectToMapList(list)); return new ModelAndView(exportView);<em id="__mceDel"><em id="__mceDel">}</em></em>

总结

以上所述是小编给大家介绍的Spring Boot Excel文件导出下载实现代码，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！