数据库数据导出至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);
}