EasyExcel多sheet页导出详细代码记录

修心光

已于 2024-03-02 10:40:44 修改

阅读量4.9k

点赞数 7

文章标签： java 数据库开发语言

于 2022-12-09 09:23:45 首次发布

本文链接：https://blog.csdn.net/qq18346342939/article/details/128247248

版权

一、引入pom依赖

        <!--excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

二、主程序

//controller层
@GetMapping(value = "dataBoardExcel")
@ApiOperation(value = "数据看板Excel导出")
public void dataBoardExcel(@RequestParam @ApiParam(value = "学校id") String schoolId, HttpServletResponse response) throws IOException {
    dataScreenNewService.dataBoardExcel(schoolId,response);
}


//具体实现方法
public void dataBoardExcelInfo(SchoolIdQo schoolIdQo, HttpServletResponse response){
        //获取基本情况用的
        SchoolSituationVo schoolSituationVo = schoolSituation(schoolIdQo);
        try {
            //第一个Sheet页数据
            List<SchoolPersonExcelVo> schoolPersonExcelVos = new ArrayList<>();
            SchoolPersonExcelVo schoolPersonExcelVo = new SchoolPersonExcelVo();
            schoolPersonExcelVo.setSchoolPersonNum(schoolSituationVo.getSchoolUserNum());
            schoolPersonExcelVo.setSchoolStuNum(schoolSituationVo.getStudentsNum());
            schoolPersonExcelVos.add(schoolPersonExcelVo);
            //第二个Sheet页数据
            List<BaseInfoExcelVo> baseInfoExcelVos = new ArrayList<>();
            BaseInfoExcelVo baseInfoExcelVo = new BaseInfoExcelVo();
            baseInfoExcelVo.setDepartmentNum(schoolSituationVo.getFacultyNum());
            baseInfoExcelVo.setMajorNum(schoolSituationVo.getProfessionalNum());
            baseInfoExcelVos.add(baseInfoExcelVo);
            //使用easyExcel需要将导出文件格式改为.xlsx，要不然导出文件打开回报“文件损坏或文件扩展名异常”
            String fileName = new String("文件名称.xlsx");
            //文件名格式需要使用这种格式，否则也会异常
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            response.addHeader("Content-Disposition", "filename=" + fileName);
            //设置类型，扩展名为.xls
            response.setContentType("application/vnd.ms-excel");
            //将数据写入sheet页中,其中.head方法是表头名称，对应类加了注解可以生成的
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "1.校本人员统计").head(SchoolPersonExcelVo.class).build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "2.基本情况").head(BaseInfoExcelVo.class).build();
            
            excelWriter.write(schoolPersonExcelVos, writeSheet1);
            excelWriter.write(baseInfoExcelVos, writeSheet2);
            excelWriter.finish();
            response.flushBuffer();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

三、相关类

SchoolPersonExcelVo.class

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.*;

@Data
@EqualsAndHashCode(callSuper=false)
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class SchoolPersonExcelVo {

	@ExcelProperty(value = "本校人员统计")
	private Integer schoolPersonNum;

	@ExcelProperty(value = "本校学生")
	private Integer schoolStuNum;

	@ExcelProperty(value = "社会学生")
	private Integer socialStudentNum;

	@ExcelProperty(value = "本校教师")
	private Integer schoolTeacherNum;

	@ExcelProperty(value = "合作企业主数")
	private Integer enterpriseNum;

}

BaseInfoExcelVo.class

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.*;

@Data
@EqualsAndHashCode(callSuper=false)
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class BaseInfoExcelVo {

	@ExcelProperty(value = "院系数量")
	private Integer departmentNum;

	@ExcelProperty(value = "专业数量")
	private Integer majorNum;


}

特殊情况，如果是多层表头们可以参考：


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;

import java.math.BigDecimal;


/**
 * 数据看板VO
 *
 * @author wcg
 * @since v1.0.0 2022-12-08
 */
@Data
@EqualsAndHashCode(callSuper=false)
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class TrainCourseBuildExcelVo {

	@ApiModelProperty(value = "虚拟仿真实训资源占比")
	@ExcelProperty(value = {"虚拟仿真实训资源占比","虚拟仿真"},index = 0)
	@ColumnWidth(value = 17)
	private BigDecimal schoolStudent;
	@ApiModelProperty(value = "虚拟仿真实训资源占比")
	@ExcelProperty(value = {"虚拟仿真实训资源占比","普通课程"},index = 1)
	@ColumnWidth(value = 17)
	@ContentStyle
	private BigDecimal socialStudent;

	@ApiModelProperty(value = "虚实结合实训资源占比")
	@ExcelProperty(value = {"虚实结合实训资源占比","虚实结合"},index = 2)
	@ColumnWidth(value = 12)
	private BigDecimal notParticipate;
	@ApiModelProperty(value = "虚实结合实训资源占比")
	@ExcelProperty(value = {"虚实结合实训资源占比","普通课程"},index = 3)
	@ColumnWidth(value = 17)
	private BigDecimal trainingSchoolStu;


	@ApiModelProperty(value = "虚拟仿真实训课程更新占比")
	@ExcelProperty(value = {"虚拟仿真实训课程更新占比","已更新"},index = 4)
	@ColumnWidth(value = 17)
	private BigDecimal trainingSocialStudent;
	@ApiModelProperty(value = "虚拟仿真实训课程更新占比")
	@ExcelProperty(value = {"虚拟仿真实训课程更新占比","未更新"},index = 5)
	@ColumnWidth(value = 17)
	private BigDecimal trainingActivityCertStu;

	@ApiModelProperty(value = "课节内容")
	@ExcelProperty(value = {"课节内容","今日新增"},index = 6)
	@ColumnWidth(value = 17)
	private BigDecimal lessonDayAdd;
	@ApiModelProperty(value = "课节内容")
	@ExcelProperty(value = {"课节内容","累计"},index = 7)
	@ColumnWidth(value = 17)
	private BigDecimal lessonStu;

	@ApiModelProperty(value = "学习资料")
	@ExcelProperty(value = {"学习资料","今日新增"},index = 8)
	@ColumnWidth(value = 17)
	private BigDecimal learnDataDayAdd;
	@ApiModelProperty(value = "学习资料")
	@ExcelProperty(value = {"学习资料","累计"},index = 9)
	@ColumnWidth(value = 17)
	private BigDecimal learnDataTotal;

	@ApiModelProperty(value = "课堂互动")
	@ExcelProperty(value = {"课堂互动","今日新增"},index = 10)
	@ColumnWidth(value = 17)
	private BigDecimal interactDayAdd;
	@ApiModelProperty(value = "课堂互动")
	@ExcelProperty(value = {"课堂互动","累计"},index = 11)
	@ColumnWidth(value = 17)
	private BigDecimal interactTotal;

	@ApiModelProperty(value = "讨论")
	@ExcelProperty(value = {"讨论","今日新增"},index = 12)
	@ColumnWidth(value = 17)
	private BigDecimal discussDayAdd;
	@ApiModelProperty(value = "讨论")
	@ExcelProperty(value = {"讨论","累计"},index = 13)
	@ColumnWidth(value = 17)
	private BigDecimal discussTotal;

	@ApiModelProperty(value = "作业")
	@ExcelProperty(value = {"作业","今日新增"},index = 14)
	@ColumnWidth(value = 17)
	private BigDecimal taskDayAdd;
	@ApiModelProperty(value = "作业")
	@ExcelProperty(value = {"作业","累计"},index = 15)
	@ColumnWidth(value = 17)
	private BigDecimal taskTotal;

	@ApiModelProperty(value = "考试")
	@ExcelProperty(value = {"考试","今日新增"},index = 16)
	@ColumnWidth(value = 17)
	private BigDecimal examDayAdd;
	@ApiModelProperty(value = "考试")
	@ExcelProperty(value = {"考试","累计"},index = 17)
	@ColumnWidth(value = 17)
	private BigDecimal examTotal;

}

四、测试结果

遇到的一个问题：

就是展示数据的时候只有表头，没有数据，是因为在使用easyexcel导出数据时，发现导出的数据只有表头没有数据，是因为映射实体类使用了lombok的@Data,字段命名是驼峰式命名，而easyExcel自动调用get方法，对于驼峰式命名是不生效的，网上有些说只要第一个和第二个单词不为大写就可以，试过之后不行，后面就索性都是小写，数据就可以读取得到了。