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文件導出下載實現代碼，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對VeVb武林網網站的支持！
新聞熱點
疑難解答
圖片精選
