EasyExcel导出多个sheet页,引用多个bean(2023)

Harbor Lau

已于 2023-01-17 16:25:43 修改

阅读量343

点赞数 1

文章标签： java 前端 html

于 2023-01-17 11:40:03 首次发布

本文链接：https://blog.csdn.net/weixin_61503139/article/details/128714055

版权

控制层

 //导出数据字典接口
    @GetMapping("exportData")

    public void exportDict(HttpServletResponse response) throws IOException {
        dictService.exportDictData(response);
    }

业务层

//导出数据字典接口
    @Override
    public void exportDictData(HttpServletResponse response) throws IOException {
        //设置下载信息
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = "dict";
        response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
        //查询数据库
        List<Dict> dictList = baseMapper.selectList(null);
       // baseMapper.selectAllll("666","hdiuhaihda");
        //Dict -- DictEeVo
        List<DictEeVo> dictVoList = new ArrayList<>();
        for(Dict dict:dictList) {
            DictEeVo dictEeVo = new DictEeVo();
           // dictEeVo.setId(dict.getId());
            BeanUtils.copyProperties(dict,dictEeVo);
            dictVoList.add(dictEeVo);}
        //调用方法进行写操作

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        //获取sheet0对象  多个javabean
        WriteSheet mainSheet = EasyExcel.writerSheet(0, "模型信息").head(DictEeVo.class).build();
        List<DictEeVo> dictEeVos = dictVoList.subList(0, 5);
        //获取模型信息,向sheet0写入数据
        excelWriter.write(dictEeVos,  mainSheet);

        //获取sheet1对象   多个javabean
        WriteSheet detailSheet = EasyExcel.writerSheet(1, "词条666信息").head(ExpertVo.class).build();

        excelWriter.write(dictList, detailSheet);
        //关闭流
        excelWriter.finish();
       /* try {
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet(0,"dict")
                    .doWrite(dictVoList);
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet(1,"dict1")
                    .doWrite(dictVoList);
        } catch (IOException e) {
            e.printStackTrace();
        }*/
    }

javabean

package com.atguigu.yygh.model.cmn;

import com.atguigu.yygh.model.base.BaseEntity;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * <p>
 * Dict
 * </p>
 *
 * @author qy
 */
@Data
@ApiModel(description = "数据字典")
@TableName("t_test")
public class Dict {

    private static final long serialVersionUID = 1L;


    public Dict(Long id, String name, Date date) {
        this.id = id;
        this.name = name;
        this.date = date;
    }

    @ApiModelProperty(value = "id")
    private Long id;

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public Dict() {
    }

    public  Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }


    @ApiModelProperty(value = "名称")
    public String getName() {
        return name;

    }


    public void setName(String name) {
        this.name = name;
    }









    @ApiModelProperty(value = "名称")
    @TableField("name")
    private String name;

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @ApiModelProperty(value = "名称")
    @TableField("date")
    private Date date;


    @Override
    public String toString() {
        return "Dict{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

package com.atguigu.yygh.vo.cmn;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * <p>
 * Dict
 * </p>
 *
 * @author qy
 */
@Data
public class DictEeVo {

	@ExcelProperty(value = "id"  )
	@DateTimeFormat
	private Long id;


	@ExcelProperty(value = "名称")
	private String name;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

package com.atguigu.yygh.vo.cmn;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.util.Date;

/**
 * <p>
 * Dict
 * </p>
 *
 * @author qy
 */
@Data
public class ExpertVo {

	@ExcelProperty(value = "id")
	private Long id;

	@ExcelProperty(value = "名称")
	private String name;

	@ExcelProperty(value = "日期")
	private Date date;

}

postman

导出的结果

数据库表结构

报错错误的类型转换的时候,添加这个配置类: (优先检查数据表和javabean类型是否对应,不然转转异常)

package com.atguigu.yygh.cmn.config;

import com.alibaba.fastjson.serializer.SerializerFeature;
import com.alibaba.fastjson.support.config.FastJsonConfig;
import com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter;

import org.springframework.context.annotation.Configuration;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.converter.HttpMessageConverter;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;


import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;

/**
 * @author liutao
 * @date 2020/1/16 14:24
 */
@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**")
                .allowedOrigins("*"). //允许跨域的域名，可以用*表示允许任何域名使用
                allowedMethods("*"). //允许任何方法（post、get等）
                allowedHeaders("*"). //允许任何请求头
                allowCredentials(true). //带上cookie信息
                exposedHeaders(HttpHeaders.SET_COOKIE).maxAge(3600L); //maxAge(3600)表明在3600秒内，不需要再发送预检验请求，可以缓存该结果
    }




    @Override
    public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
        //调用父类的配置
        WebMvcConfigurer.super.configureMessageConverters(converters);
        //创建FastJson的消息转换器
        FastJsonHttpMessageConverter convert = new FastJsonHttpMessageConverter();
        //创建FastJson的配置对象
        FastJsonConfig config = new FastJsonConfig();
        //对Json数据进行格式化
        config.setSerializerFeatures(SerializerFeature.PrettyFormat,
                SerializerFeature.WriteNullStringAsEmpty,
                SerializerFeature.WriteNullNumberAsZero,
                SerializerFeature.WriteNullListAsEmpty,
                SerializerFeature.WriteNullBooleanAsFalse,
                SerializerFeature.WriteMapNullValue,
                //禁止循环引用
                SerializerFeature.DisableCircularReferenceDetect);
        config.setDateFormat("yyyy-MM-dd HH:mm:ss");
        config.setCharset(Charset.forName("UTF-8"));
        convert.setFastJsonConfig(config);
        convert.setSupportedMediaTypes(getSupportedMediaTypes());
        converters.add(convert);
    }

    public List<MediaType> getSupportedMediaTypes() {
        //创建fastJson消息转换器
        List<MediaType> supportedMediaTypes = new ArrayList<>();
        supportedMediaTypes.add(MediaType.APPLICATION_JSON);
        supportedMediaTypes.add(MediaType.APPLICATION_JSON_UTF8);
        supportedMediaTypes.add(MediaType.APPLICATION_ATOM_XML);
        supportedMediaTypes.add(MediaType.APPLICATION_FORM_URLENCODED);
        supportedMediaTypes.add(MediaType.APPLICATION_OCTET_STREAM);
        supportedMediaTypes.add(MediaType.APPLICATION_PDF);
        supportedMediaTypes.add(MediaType.APPLICATION_RSS_XML);
        supportedMediaTypes.add(MediaType.APPLICATION_XHTML_XML);
        supportedMediaTypes.add(MediaType.APPLICATION_XML);
        supportedMediaTypes.add(MediaType.IMAGE_GIF);
        supportedMediaTypes.add(MediaType.IMAGE_JPEG);
        supportedMediaTypes.add(MediaType.IMAGE_PNG);
        supportedMediaTypes.add(MediaType.TEXT_EVENT_STREAM);
        supportedMediaTypes.add(MediaType.TEXT_HTML);
        supportedMediaTypes.add(MediaType.TEXT_MARKDOWN);
        supportedMediaTypes.add(MediaType.TEXT_PLAIN);
        supportedMediaTypes.add(MediaType.TEXT_XML);
        supportedMediaTypes.add(MediaType.ALL);
        return supportedMediaTypes;
    }

}

万能监听器导入用,先放在这里吧

package com.atguigu.yygh.cmn.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

/**
 * excel表格读取监视器
 */
public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();

    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);
        doSomething(o);
    }

    private void doSomething(Object object) {
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

导出排除指定字段