springboot下生成excel并在页面下载

*. 生成及下载的excel格式均为xls

*. 包含一些业务逻辑,凑和看

*. pom.xml导入所需的jar包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

 

*.生成excel逻辑

package com.hm.archm.service;

import com.hm.archm.common.enums.ArchiveType;
import com.hm.archm.common.enums.ExportField;
import com.hm.archm.dao.LoanInfoDao;
import com.hm.archm.model.query.ExportQuery;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class ExportService {
    @Autowired
    private LoanInfoDao loanInfoDao;

    public Workbook  exportExcel(ExportQuery query) throws Exception {
        System.out.println(query);
        List<Map<String, Object>> resMaps = loanInfoDao.listExport(query);
        
        // Create a Workbook
        Workbook  workbook = new HSSFWorkbook(); // new HSSFWorkbook() for generating `.xls` file
        Sheet sheet = workbook.createSheet("sheel1");
        // Create a Row
        Row headerRow = sheet.createRow(0);
        // Create cells
        for (int i = 0; i < ExportField.values().length; i++) {
            ExportField field = ExportField.getByIndex(i).get();
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(field.getName());
        }
        int rowNum = 1;
        for (Map<String, Object> columnMap : resMaps) {
            Row row = sheet.createRow(rowNum++);
            for (Map.Entry<String, Object> columnEntry : columnMap.entrySet()) {
                String column = columnEntry.getKey();
                ExportField fieldEnum = ExportField.getByColumn(column).get();
                String columnValue = "";
                switch (fieldEnum) {
                    case type:
                        columnValue = ArchiveType.getByType(columnEntry.getValue().toString()).getName();
                        break;
                    default:
                        columnValue = columnEntry.getValue().toString();
                }
                row.createCell(fieldEnum.getIndex()).setCellValue(columnValue);
            }
        }
        return workbook;
    }
}

*.controller逻辑

package com.hm.archm.controller;

import com.fasterxml.jackson.databind.DeserializationFeature;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.hm.archm.model.query.ExportQuery;
import com.hm.archm.service.ExportService;
import org.apache.commons.io.IOUtils;
import org.apache.ibatis.annotations.Mapper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;

/**
 * @ClassName ExportController
 * @Description
 * @Author chenZhangWei
 * @Date 2019/12/13 0013 9:26
 * @Version 1.0
 */
@RestController
@RequestMapping("/export")
public class ExportController {
    @Autowired
    private ExportService exportService;

    @RequestMapping("/excel")
    public void exportExcel(@RequestBody String json,HttpServletResponse response) throws Exception {
        ObjectMapper objMapper = new ObjectMapper();
        objMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
        ExportQuery param = objMapper.readValue(json, ExportQuery.class);
        Workbook  workbook = exportService.exportExcel(param);
        workbook.write(response.getOutputStream());
    }
} 

*.js逻辑

                axios({
                    method: 'post',
                    url: "../../export/excel",
                    responseType: 'blob',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    data:this.param
                }).then(function (response) {
                    var blob = new Blob([response.data], {type: 'application/vnd.ms-excel'});//根据实际情况设置type
                    var link = document.createElement('a');
                    var body = document.querySelector('body');

                    link.href = window.URL.createObjectURL(blob);
                    link.download = "导出台账.xls";

                    // fix Firefox
                    link.style.display = 'none';
                    body.appendChild(link);

                    link.click();
                    body.removeChild(link);

                    window.URL.revokeObjectURL(link.href);
                })