快捷導(dǎo)航

Spring Boot Excel文件導(dǎo)出下載實(shí)現(xiàn)代碼

更新時間：2018年11月20日 11:56:32 作者：從今天起做個幸福的人

這篇文章帶領(lǐng)我們直接實(shí)現(xiàn)Excel文件的直接導(dǎo)出下載，后續(xù)開發(fā)不需要開發(fā)很多代碼，直接繼承已經(jīng)寫好的代碼，增加一個Xml配置就可以直接導(dǎo)出。具體實(shí)現(xiàn)代碼大家跟隨小編一起通過本文學(xué)習(xí)吧

Spring Boot Excel 文件導(dǎo)出

目標(biāo)：

實(shí)現(xiàn)Excel文件的直接導(dǎo)出下載，后續(xù)開發(fā)不需要開發(fā)很多代碼，直接繼承已經(jīng)寫好的代碼，增加一個Xml配置就可以直接導(dǎo)出。

實(shí)現(xiàn)：

1、抽象類 BaseExcelView 繼承 webmvc 的 AbstractXlsxStreamingView 抽象類， AbstractXlsxStreamingView 是webmvc繼承了最頂層View接口，是可以直接大量數(shù)據(jù)導(dǎo)出的不會造成內(nèi)存泄漏問題，即 SXSSFWorkbook 解決了內(nèi)存問題，導(dǎo)出只支持xlsx類型文件。

抽象類代碼 BaseExcelView ：

public abstract class BaseExcelView extends AbstractXlsxStreamingView {
  private static final Logger logger = LoggerFactory.getLogger(BaseExcelView.class);
  /**
   * 獲取導(dǎo)出文件名
   *
   * @return
   */
  abstract protected String getFileName();
  /**
   * 獲取表單名稱
   *
   * @return
   */
  abstract protected String getSheetName();
  /**
   * 獲取標(biāo)題欄名稱
   *
   * @return
   */
  abstract protected String[] getTitles();
  /**
   * 獲取列寬
   *
   * @return
   */
  abstract protected short[] getColumnWidths();
  /**
   * 構(gòu)造內(nèi)容單元格
   *
   * @param sheet
   */
  abstract protected void buildContentCells(Sheet sheet);
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response)
      throws Exception {
    // 構(gòu)造標(biāo)題單元格 SXSSFWorkbook
    Sheet sheet = buildTitleCells(workbook);
    // 構(gòu)造內(nèi)容單元格
    buildContentCells(sheet);
    // 設(shè)置響應(yīng)頭
    setResponseHead(request, response);
  }
  /**
   * 設(shè)置響應(yīng)頭
   *
   * @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("客戶端請求頭內(nèi)容:");
    logger.info("user-agent\t值: {}", userAgent);
    if (userAgent != null) {
      if (userAgent.contains("firefox")) {
        // firefox有默認(rèn)的備用字符集是西歐字符集
        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");
      }
    }
    //響應(yīng)頭信息
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/ms-excel; charset=UTF-8");
    response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
  }
  /**
   * 構(gòu)造標(biāo)題單元格
   *
   * @param
   * @return
   */
  protected Sheet buildTitleCells(Workbook workbook) {
    // 表單名稱
    String sheetName = getSheetName();
    // 標(biāo)題名稱
    String[] titles = getTitles();
    // 列寬
    short[] colWidths = getColumnWidths();
    // 創(chuàng)建表格
    Sheet sheet = workbook.createSheet(sheetName);
    // 標(biāo)題單元格樣式
    CellStyle titleStyle = getHeadStyle(workbook);
    // 默認(rèn)內(nèi)容單元格樣式
    CellStyle contentStyle = getBodyStyle(workbook);
    // 標(biāo)題行
    Row titleRow = sheet.createRow(0);
    // 創(chuàng)建標(biāo)題行單元格
    for (int i = 0; i < titles.length; i++) {
      // 標(biāo)題單元格
      Cell cell = titleRow.createCell((short) i);
      cell.setCellType(CellType.STRING);
      cell.setCellValue(new XSSFRichTextString(titles[i]));
      cell.setCellStyle(titleStyle);
      // 設(shè)置列寬
      sheet.setColumnWidth((short) i, (short) (colWidths[i] * 256));
      // 設(shè)置列默認(rèn)樣式
      sheet.setDefaultColumnStyle((short) i, contentStyle);
    }
    return sheet;
  }
  /**
   * 設(shè)置表頭的單元格樣式
   */
  public CellStyle getHeadStyle(Workbook workbook) {
    // 創(chuàng)建單元格樣式
    CellStyle cellStyle = workbook.createCellStyle();
    // 設(shè)置單元格的背景顏色為淡藍(lán)色
    cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
    // 設(shè)置填充字體的樣式
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    // 設(shè)置單元格居中對齊
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    // 設(shè)置單元格垂直居中對齊
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 創(chuàng)建單元格內(nèi)容顯示不下時自動換行
    cellStyle.setWrapText(true);
    // 設(shè)置單元格字體樣式
    Font font = workbook.createFont();
    // 字號
    font.setFontHeightInPoints((short) 12);
    // 加粗
    font.setBold(true);
    // 將字體填充到表格中去
    cellStyle.setFont(font);
    // 設(shè)置單元格邊框?yàn)榧?xì)線條（上下左右）
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    return cellStyle;
  }
  /**
   * 設(shè)置表體的單元格樣式
   */
  public CellStyle getBodyStyle(Workbook workbook) {
    // 創(chuàng)建單元格樣式
    CellStyle cellStyle = workbook.createCellStyle();
    // 設(shè)置單元格居中對齊
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    // 設(shè)置單元格居中對齊
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 創(chuàng)建單元格內(nèi)容不顯示自動換行
    cellStyle.setWrapText(true);
    //設(shè)置單元格字體樣式字體
    Font font = workbook.createFont();
    // 字號
    font.setFontHeightInPoints((short) 10);
    // 將字體添加到表格中去
    cellStyle.setFont(font);
    // 設(shè)置單元格邊框?yàn)榧?xì)線條
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    return cellStyle;
  }
}

Excel導(dǎo)出實(shí)現(xiàn) 1: 可以直接繼承 BaseExcelView 實(shí)現(xiàn)定義的方法 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 "導(dǎo)出文件" + time;
  }
  @Override
  protected String getSheetName() {
    return "報(bào)表";
  }
  @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()));
   }
  }
}

導(dǎo)出實(shí)現(xiàn) 2： XML配置導(dǎo)出

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解析配置

@Root
public 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方法配置

@Service
public 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、導(dǎo)出基礎(chǔ) ExcelExportView 代碼實(shí)現(xiàn)

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();
    }
  }
}

最后：導(dǎo)出Controller代碼實(shí)現(xiàn)

@RequestMapping(path = "/export", method = RequestMethod.GET, produces = "application/octet-stream;charset=UTF-8")
public @ResponseBody
ModelAndView 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>

總結(jié)

以上所述是小編給大家介紹的Spring Boot Excel文件導(dǎo)出下載實(shí)現(xiàn)代碼，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！

您可能感興趣的文章: