Spring Boot Excel文件導出下載實現代碼

2024-07-14 08:42:53

字體：大中小

來源：轉載

供稿：網友

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武林網網站的支持！

注：相關教程知識閱讀請移步到JAVA教程頻道。

上一篇：Spring MVC學習教程之視圖深入解析

下一篇：Java 中運行字符串表達式的方法