IEMS_12_个性化导出

✧*｡依枫忆风 ✧*｡

已于 2022-06-12 10:47:28 修改

阅读量349

点赞数

分类专栏：智能化教务系统_个人

于 2022-06-12 09:22:06 首次发布

本文链接：https://blog.csdn.net/qq_53174491/article/details/125241991

版权

intellij-idea

智能化教务系统_个人专栏收录该内容

12 篇文章 0 订阅

订阅专栏

为了满足以特定场景需要的格式导出需要的系统数据的需求。

框架自带的导出分析：

自带的导出中导出的是数据库该表中的所有行的信息。

框架自带的导出方法：

    @Value("${jeecg.path.upload}")
    private String upLoadPath;
    /**
     * 导出excel
     *
     * @param request
     */
    protected ModelAndView exportXls(HttpServletRequest request, T object, Class<T> clazz, String title) {
        // Step.1 组装查询条件
        QueryWrapper<T> queryWrapper = QueryGenerator.initQueryWrapper(object, request.getParameterMap());
        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();

        // Step.2 获取导出数据
        List<T> pageList = service.list(queryWrapper);
        List<T> exportList = null;

        // 过滤选中数据
        String selections = request.getParameter("selections");
        if (oConvertUtils.isNotEmpty(selections)) {
            List<String> selectionList = Arrays.asList(selections.split(","));
            exportList = pageList.stream().filter(item -> selectionList.contains(getId(item))).collect(Collectors.toList());
        } else {
            exportList = pageList;
        }

        // Step.3 AutoPoi 导出Excel
        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title); //此处设置的filename无效 ,前端会重更新设置一下
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        //update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
        ExportParams  exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);
        exportParams.setImageBasePath(upLoadPath);
        //update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
        mv.addObject(NormalExcelConstants.PARAMS,exportParams);
        mv.addObject(NormalExcelConstants.DATA_LIST, exportList);
        return mv;
    }

它需要将哪些列导出在excel是由实体类clazz中的@Excel 决定的

个性化导出：

主要是在于他的查询条件的不同，我们需要将查询结果换成自己想要的查询结果即可。

    //导出excel
    @RequestMapping(value = "/exToExcel_test")
    public ModelAndView exToExcel_test(@RequestParam(name="les_ord") String les_ord,
                                       @RequestParam(name="cs_id") String cs_id,
                                       HttpServletRequest req){

        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        List<List<Scores>> rowDatas = new ArrayList<List<Scores>>();


        List<Scores> allList = scoresService.getStuScoreList(cs_id,les_ord);


        String title = "scores";
        Class clazz = Scores.class;
        List<Scores> exportList = allList;

        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title); //此处设置的filename无效 ,前端会重更新设置一下
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        //update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
        //ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);
        ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);

        exportParams.setImageBasePath(upLoadPath);
        //update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
        mv.addObject(NormalExcelConstants.PARAMS,exportParams);
        mv.addObject(NormalExcelConstants.DATA_LIST, exportList);
        return mv;

    }

问题：

对于只需要相同模板导出的实体类来讲，可以直接在实体类上修改@Excel来决定留下哪些属性进行导出当然是没有问题的。但是如果对于同一个实体类而言，要导出两个或多个表结构不同的excel，这就不能直接通过修改该实体类的@Excel来完成了。

解决方案：

再创建一个包含相同属性的实体类，然后再新创建的实体类中对@Excel进行另一种修改。

例如我们的系统中需要对scores表进行两种不同格式的导出：

1、导出未录入成绩的空表，包含的属性有学生姓名、学号、平时成绩、实验成绩、考试成绩这五个；

2、导出录入成绩结果的表，包括该班级成绩的所有属性的完整的表；

这两个显然是没有办法在一个实体类中通过@Excel完成的，所以新建了一个实体类ScoresOutput：

Scores:

package org.jeecg.modules.demo.ScoresInput.entity;

import java.io.Serializable;
import java.io.UnsupportedEncodingException;
import java.util.Date;
import java.math.BigDecimal;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecg.common.aspect.annotation.Dict;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * @Description: 课程成绩表
 * @Author: jeecg-boot
 * @Date:   2022-04-27
 * @Version: V1.0
 */
@Data
@TableName("scores")
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="scores对象", description="课程成绩表")
public class Scores implements Serializable {
    private static final long serialVersionUID = 1L;

	/**主键*/
	@TableId(type = IdType.ASSIGN_ID)
    @ApiModelProperty(value = "主键")
    private String id;

	/**学生姓名*/
	@Excel(name = "学生姓名", width = 15)
    @ApiModelProperty(value = "学生姓名")
    private String stuName;
    /**学号*/
    @Excel(name = "学号", width = 15)
    @ApiModelProperty(value = "学号")
    private String stuId;
	/**课序号*/
	//@Excel(name = "课序号", width = 15)
    @ApiModelProperty(value = "课序号")
    private String lesOrd;
	/**课程编号*/
	//@Excel(name = "课程编号", width = 15)
    @ApiModelProperty(value = "课程编号")
    private String csId;
	/**教工号*/
	//@Excel(name = "教工号", width = 15)
    @ApiModelProperty(value = "教工号")
    private String teaId;
	/**学年学期*/
	//@Excel(name = "学年学期", width = 15, dictTable = "semester", dicText = "sem_name", dicCode = "sem_name")
	@Dict(dictTable = "semester", dicText = "sem_name", dicCode = "sem_name")
    @ApiModelProperty(value = "学年学期")
    private String csSem;
	/**平时成绩*/
	@Excel(name = "平时成绩", width = 15)
    @ApiModelProperty(value = "平时成绩")
    private Integer usualScores;
	/**实验成绩*/
	@Excel(name = "实验成绩", width = 15)
    @ApiModelProperty(value = "实验成绩")
    private Integer testScores;
	/**考试成绩*/
	@Excel(name = "期末成绩", width = 15)
    @ApiModelProperty(value = "考试成绩")
    private Integer examScores;
	/**成绩*/
	//@Excel(name = "成绩", width = 15)
    @ApiModelProperty(value = "成绩")
    private String scores;

	/**时间戳*/
    @ApiModelProperty(value = "时间戳")
    private Date createTime;
    public String getStuName() {
        return this.stuName;
    }


}

ScoresOutput：

package org.jeecg.modules.demo.ScoresInput.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import org.jeecg.common.aspect.annotation.Dict;
import org.jeecgframework.poi.excel.annotation.Excel;

import java.io.Serializable;
import java.util.Date;

/**
 * @Description: 课程成绩表
 * @Author: jeecg-boot
 * @Date:   2022-04-27
 * @Version: V1.0
 */
@Data

public class ScoresOutput {
    private static final long serialVersionUID = 1L;

	/**主键*/
	@TableId(type = IdType.ASSIGN_ID)
	//@ApiModelProperty(value = "主键")
	private String id;

	/**学生姓名*/
	@Excel(name = "学生姓名", width = 15)
    private String stuName;
    /**学号*/
    @Excel(name = "学号", width = 15)
    private String stuId;
	/**课序号*/
	@Excel(name = "课序号", width = 15)
    private String lesOrd;
	/**课程编号*/
	@Excel(name = "课程编号", width = 15)
    private String csId;
	/**教工号*/
	@Excel(name = "教工号", width = 15)
    private String teaId;
	/**学年学期*/
	@Excel(name = "学年学期", width = 15, dictTable = "semester", dicText = "sem_name", dicCode = "sem_name")
	@Dict(dictTable = "semester", dicText = "sem_name", dicCode = "sem_name")
    private String csSem;
	/**平时成绩*/
	@Excel(name = "平时成绩", width = 15)
    private Integer usualScores;
	/**实验成绩*/
	@Excel(name = "实验成绩", width = 15)
    private Integer testScores;
	/**考试成绩*/
	@Excel(name = "期末成绩", width = 15)
    private Integer examScores;
	/**成绩*/
	@Excel(name = "成绩", width = 15)
    private String scores;

	/**时间戳*/
    @ApiModelProperty(value = "时间戳")
    private Date createTime;
    public String getStuName() {
        return this.stuName;
    }


}

这样在Controller中在去调用不同的查询语句和实体类就可以解决问题了。

Controller:

第一种：

    //导出excel
    @RequestMapping(value = "/exToExcel_test")
    public ModelAndView exToExcel_test(@RequestParam(name="les_ord") String les_ord,
                                       @RequestParam(name="cs_id") String cs_id,
                                       HttpServletRequest req){

        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        List<List<Scores>> rowDatas = new ArrayList<List<Scores>>();


        List<Scores> allList = scoresService.getStuScoreList(cs_id,les_ord);


        String title = "scores";
        Class clazz = Scores.class;
        List<Scores> exportList = allList;

        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title); //此处设置的filename无效 ,前端会重更新设置一下
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        //update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
        //ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);
        ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);

        exportParams.setImageBasePath(upLoadPath);
        //update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
        mv.addObject(NormalExcelConstants.PARAMS,exportParams);
        mv.addObject(NormalExcelConstants.DATA_LIST, exportList);
        return mv;

    }

导出结果：

第二种：

    //导出excel
    @RequestMapping(value = "/exToExcel_testoutput")
    public ModelAndView exToExcel_testoutput(@RequestParam(name="les_ord") String les_ord,
                                             @RequestParam(name="cs_id") String cs_id,
                                             HttpServletRequest req){

        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();

        List<ScoresOutput> allList = scoresService.getStuScoreList_g(cs_id,les_ord);

        String title = "scoresoutput";
        Class clazz = ScoresOutput.class;

        List<ScoresOutput> exportList = allList;

        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title); //此处设置的filename无效 ,前端会重更新设置一下
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        //ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);
        ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);

        exportParams.setImageBasePath(upLoadPath);
        mv.addObject(NormalExcelConstants.PARAMS,exportParams);
        mv.addObject(NormalExcelConstants.DATA_LIST, exportList);
        return mv;

    }

导出结果：