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

一、引入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方法,对于驼峰式命名是不生效的,网上有些说只要第一个和第二个单词不为大写就可以,试过之后不行,后面就索性都是小写,数据就可以读取得到了。

五、实体类相关注解,会用到的

1、@ExcelProperty

必要的一个注解,注解中有三个参数value,index分别代表列明,列序号

1.value 通过标题文本对应
2.index 通过文本行号对应

2、@ColumnWith

设置列宽度,只有一个参数value,value的单位是字符长度,最大可以设置255个字符,因为一个excel单元格最大可以写入的字符个数就是255个字符

3、@ContentRowHeight

用于设置行高

4、@ContentStyle

设置内容格式注解

5、@ContentFontStyle

用于设置单元格内容字体格式的注解

6、@HeadRowHeight

设置标题行行高

7、@HeadFontStyle

用于定制标题字体格式

8、@ExcelIgnore

不将该字段转换成Excel

9、附EasyExcel官方文档地址:关于Easyexcel | Easy Excel

OK!

  • 7
    点赞
  • 24
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
对于使用 EasyExcel 进行分 sheet 导出,你可以按照以下步骤进行操作: 1. 导入 EasyExcel 依赖: ```xml <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.11</version> </dependency> ``` 2. 创建一个 ExcelWriter 对象: ```java String fileName = "path_to_save_file.xlsx"; ExcelWriter excelWriter = EasyExcel.write(fileName).build(); ``` 3. 定义数据模型类,作为每个 sheet 的数据对象: ```java public class DataModel { // 定义需要导出的字段 private String field1; private String field2; // ... // 省略构造函数、getter 和 setter 方法 } ``` 4. 创建多个 sheet,并写入数据: ```java List<DataModel> data = getData(); // 获取数据列表 WriteSheet sheet1 = EasyExcel.writerSheet(0, "Sheet1").head(DataModel.class).build(); excelWriter.write(data, sheet1); // 创建并写入其他 sheet WriteSheet sheet2 = EasyExcel.writerSheet(1, "Sheet2").head(DataModel.class).build(); excelWriter.write(data, sheet2); // ... ``` 5. 写入完成后关闭 ExcelWriter 对象: ```java excelWriter.finish(); ``` 6. 完整的示例代码如下: ```java public class ExcelExportUtil { public static void main(String[] args) { String fileName = "path_to_save_file.xlsx"; ExcelWriter excelWriter = EasyExcel.write(fileName).build(); List<DataModel> data = getData(); WriteSheet sheet1 = EasyExcel.writerSheet(0, "Sheet1").head(DataModel.class).build(); excelWriter.write(data, sheet1); WriteSheet sheet2 = EasyExcel.writerSheet(1, "Sheet2").head(DataModel.class).build(); excelWriter.write(data, sheet2); excelWriter.finish(); } private static List<DataModel> getData() { // 获取数据的逻辑 // ... } } ``` 这样,使用 EasyExcel 就可以实现分 sheet 导出了。你只需要根据你的需求,设置不同的数据对象和 sheet 名称即可。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值