欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库数据导出至Excel

程序员文章站 2024-03-21 08:15:34
...

service业务层


public void downloadInfoByTime(String year, HttpServletResponse httpServletResponse) throws Exception {
        //获取数据库中想要得到的信息
        List<BudgetRecordsVo> dataList = budgetRecordsData.getBudgetInfoByTime(BudgetRecordTemplateTypeEnum.YEAR.getCode(), null, year, null, null);
        String[] headerFields = new String[]{"orderLabel","name","unitName","budget","budgetAdjust"};
        String[] headerLabel = new String[]{"序号","预算指标","单位","年度预算","年度调整预算"};
        HutoolUtils.downloadExcel(httpServletResponse,headerFields,headerLabel,dataList,year+"年度预算");
    }

HutoolUtils工具类

package com.wjh.util;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class HutoolUtils {

    /**
     * 下载Excle
     *
     * @param response
     * @param headerFields 表头字段
     * @param headerLabel  表头名称
     * @param data         数据
     * @param fileName     文件名
     * @throws Exception
     * @author yangj
     */
    public static void downloadExcel(HttpServletResponse response, String[] headerFields, String[] headerLabel, List data, String fileName) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");

        ExcelWriter writer = ExcelUtil.getWriter();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

        //设置表头
        for (int i = 0; i < headerFields.length; i++) {
            writer.addHeaderAlias(headerFields[i], headerLabel[i]);
        }
        writer.setOnlyAlias(true);
        writer.write(data, true);

        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
        }
        IoUtil.close(out);
    }
}

Controller控制层

	
   @ApiImplicitParam(name = "year", value = "年,如 2021", dataTypeClass = String.class, required = true) //可有可无
    @GetMapping("downloadInfoByTime")
    public void downloadInfoByTime(@RequestParam(value = "year") String year, HttpServletResponse response) throws Exception {
        getAnnualIndicatorsService.downloadInfoByTime(year, response);
    }