SpringBoot集成autopoi实现数据导出到excel表格

首先导入依赖

gradle导入
implementation group: 'org.jeecgframework', name: 'autopoi-web', version: '1.3.6'

maven导入
 		<dependency>
            <groupId>org.jeecgframework</groupId>
            <artifactId>autopoi-web</artifactId>
            <version>1.3.6</version>
        </dependency>

然后在实体类加上注解

	/**
	 **replace是把对应的状态码转为文字，比如在数据库里存的是1，实际上代表的是男，2代表的是女。如果不加就是普通数据
	 name代表在表格里的列名
	**/
	@Excel(name = "性别",replace = {"男_1","女_2"})
	@Column(name="sex" )
	private String sex;//性别 1.男 2女

然后是转换工具类

import org.jeecgframework.poi.excel.def.NormalExcelConstants;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;
import org.springframework.web.servlet.ModelAndView;

import java.util.List;

/**
 * @author qingshi
 * @date 2022/12/29 15:02
 * info:导出excel工具类
 */
public class ExcelUtils {
    /**
     * 导出excel
     *
     * @param title      文件标题
     * @param clazz      实体类型
     * @param exportList 导出数据
     * @param <T>
     * @return
     */
    public static <T> ModelAndView export(String title, Class<T> clazz, List<T> exportList) {
        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title);
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        mv.addObject(NormalExcelConstants.PARAMS, new ExportParams(title, title));
        mv.addObject(NormalExcelConstants.DATA_LIST, exportList);
        return mv;
    }
}

在controller或者servcie使用

	/**
	**这里的就诊卡信息汇总为表格中显示的头部信息
	**/
    @GetMapping("/exportExcel")
    public ModelAndView exportExcel() {
        List<VisitCard> visitCardList = visitCardRepository.findAll();
        return ExcelUtils.export("就诊卡信息汇总", VisitCard.class, visitCardList);
    }

不通过自定义的utils实现
public ModelAndView exportStatus(Integer id, String infoName, String startTime, String userName) {
        List<PromotionReserve> promotionReserveList = promotionMapper.checkStatus(id, null);
        PromotionReserve mergedData = new PromotionReserve();
        mergedData.setReserveTime("操作人： " + userName);
        mergedData.setThirdColumn("导出时间：" + TimeUtils.getTime());
        promotionReserveList.add(mergedData);
        String title = "预约情况导出";
        String secondTitle = "活动名称：" + infoName ;
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);//一级标题
        exportParams.setSecondTitle(secondTitle);//二级标题
        exportParams.setSheetName(title);//表名

        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title);//这里的title是导出时的那个文件名
        mv.addObject(NormalExcelConstants.CLASS, PromotionReserve.class);
        mv.addObject(NormalExcelConstants.PARAMS, exportParams);
        mv.addObject(NormalExcelConstants.DATA_LIST, promotionReserveList);
        return mv;
    }

这个是用模板导出的方式，比较灵活一点，适用于对表格有复杂的需求

首先最上面的是模板，下面的是导出后的表格
这是service层代码

public ModelAndView exportScoreRecord() {
        TemplateExportParams params = new TemplateExportParams("E:\\项目\\文档\\预约情况导出.xls");//这里是模板的本地路径
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("name", "名称111");//这里对应的是最外层的{{name}}
        map.put("date", 2000000.00);
        map.put("username", "贰佰万");
        map.put("expretime", "执笔潜行科技有限公司");
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        for (int i = 0; i < 4; i++) {
            Map<String, String> lm = new HashMap<String, String>();
            lm.put("mobile", i + 1 + "");//这里对应的是里层的{{t.mobile}}
            lm.put("reserveTime", "A001");
            listMap.add(lm);
        }
        map.put("maplist", listMap);//这里对应{{$fe:maplist t t.reserveTime}}中的fe:maplist，注意最后的}}是在最后一格里面才加
        ModelAndView mv = new ModelAndView(new JeecgTemplateExcelView());
        mv.addObject(TemplateExcelConstants.FILE_NAME, "预约情况导出"); //此处设置的filename无效 ,前端会重更新设置一下
        mv.addObject(TemplateExcelConstants.PARAMS, params);//参数
        mv.addObject(TemplateExcelConstants.MAP_DATA, map);
        return mv;
    }

接下来写一个简单的按钮调用

<button class="layui-icon layui-icon-export" id="downExcel">导出数据</button>

<script src="script/commn/jquery/jquery-2.2.3.min.js"></script>
<script type="text/javascript">
    $(document).on('click',"#downExcel",function(){
        window.location.href="http://localhost:56007/msgservice/index/exportExcel";  
    });
</script>

点击按钮弹出下载

表格效果如下

最后附上autopoi官方文档
http://doc.autopoi.jeecg.com/1623954