HTTP接口下载excel文件

HTTP接口下载excel文件

DownloadController.java

定义一个RestController，并定义一个Get下载接口
需要自定义excel中字段名称和顺序

package com.xxx.data.board;

import com.xxx.data.board.dao.entity.AcutalWorkTypeInfo;
import com.xxx.data.board.util.CommUtil;
import com.xxx.data.board.vo.request.NewReviewReqVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Validated
@RestController
@Slf4j
public class DownloadController {

    @Autowired
    private TaskMapper taskMapper;

    @GetMapping("/task/amount/download")
    public void taskAmountDownload(HttpServletResponse response,
                                   @RequestParam(required=false) String taskCode,
                                   @RequestParam(required=true) String startDate,
                                   @RequestParam(required=true) String endDate) {

        NewReviewReqVO req = new NewReviewReqVO()
                .setEndDate(endDate)
                .setStartDate(startDate)
                .setTaskCode(taskCode);

        // excel字段名称 及 顺序
        Map<String, String> colNameMap = new HashMap<>();
        colNameMap.put("id","主键");
        colNameMap.put("siteCode","仓Id");
        colNameMap.put("taskCode","任务类型编号");
        colNameMap.put("taskName","任务类型名称"); 
        colNameMap.put("dateStr","任务日期");
        colNameMap.put("hourStr","小时");
        colNameMap.put("minuteStr","分钟");
        colNameMap.put("positionCode","岗位编号");
        colNameMap.put("positionName","岗位名称");
        colNameMap.put("actualType","分类");

        // 查询数据库获得数据
        List<AcutalWorkTypeInfo> dataList = taskMapper.getTaskAmountDetailData(req);

        // excel文件名称
        String tmpFileName = req.getSiteCode() + "-"+ System.currentTimeMillis() +".xlsx";

        // 调用静态方法 返回文件流
        CommUtil.download(response, dataList, colNameMap, tmpFileName);
    }
}

CommUtil.java

通过反射，定义一个通用的excel拼装方法，并通过文件流的形式返回给调用方

package com.XXXX.data.board.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class CommUtil {

    public static <T> void download(HttpServletResponse response,
                                    List<T> dataList,
                                    Map<String, String> colNameMap,
                                    String tmpFileName) {
        try {

            if (CollectionUtils.isEmpty(dataList)) {
                log.info("下载失败，没有数据。");
                return;
            }

            // 生成xlsx的Excel
            Workbook workbook = new SXSSFWorkbook();
            // 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
            //Workbook workbook = new HSSFWorkbook();
            // 生成Sheet表，写入第一行的列头
            Sheet sheet = workbook.createSheet("s");

            // 获得字段名称
            List<String> fieldList = new ArrayList<>();
            Field[] declaredFields = dataList.get(0).getClass().getDeclaredFields();
            for (Field field : declaredFields) {
                fieldList.add(field.getName());
            }

            // 设置列头
            Row head = sheet.createRow(0);
            int cNum = 0;
            for (int i = 0; i < fieldList.size(); i++) {
                if (!colNameMap.containsKey(fieldList.get(i))) {
                    continue;
                }

                String chineseName = colNameMap.get(fieldList.get(i));

                Cell cell = head.createCell(cNum);
                cell.setCellValue(chineseName);
                cNum++;
            }

            //构建每行的数据内容
            int rowNum = 1;
            for (T data : dataList) {

                //输出行数据
                Row row = sheet.createRow(rowNum++);

                int cellNum = 0;
                Class clazz = data.getClass();
                Field[] fields = clazz.getDeclaredFields();

                for (Field field : fields) {

                    if (!colNameMap.containsKey(field.getName())) {
                        continue;
                    }

                    Field declaredField = clazz.getDeclaredField(field.getName());
                    declaredField.setAccessible(true);
                    PropertyDescriptor pd = new PropertyDescriptor(declaredField.getName(), clazz);
                    Method readMethod = pd.getReadMethod();
                    readMethod.setAccessible(true);
                    Object val = readMethod.invoke(data);
                    // 每个字段赋值
                    Cell cell = row.createCell(cellNum++);
                    cell.setCellValue(null == val ? null : val + "");
                }
            }

            response.reset();
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(tmpFileName.getBytes("utf-8"), "ISO8859-1"));
            response.setContentType("application/octet-stream");

            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            workbook.write(toClient);
            workbook.close();

            toClient.flush();
            toClient.close();

        } catch (Exception e) {
            log.info("下载异常", e);
        }
    }
}

调用方式

在浏览器上输入地址即可下载:

http://localhost:8080/task/amount/download?taskCode=aaa&startDate=2021-01-01&endDate=2021-02-23