Excel报表的导入导出

最新推荐文章于 2024-03-14 14:16:10 发布

似夜晓星辰

最新推荐文章于 2024-03-14 14:16:10 发布

阅读量1k

点赞数 1

分类专栏： java 初级文章标签： java poi

本文链接：https://blog.csdn.net/qq_50990903/article/details/118281025

版权

java 初级专栏收录该内容

28 篇文章 3 订阅

订阅专栏

EasyPoi导出

一：引入依赖

		<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

二：注解解析

@Excel

这个是必须使用的注解,如果需求简单只使用这一个注解也是可以的,涵盖了常用的Excel需求,需要大家熟悉这个功能,主要分为基础,图片处理,时间处理,合并处理几块,name_id是上面讲的id用法,这里就不累言了

属性	类型	默认值	功能
name	String	null	列名,支持name_id
needMerge	boolean	fasle	是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
orderNum	String	“0”	列的排序,支持name_id
replace	String[]	{}	值得替换导出是{a_id,b_id} 导入反过来
savePath	String	“upload”	导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/
type	int	1	导出类型 1 是文本 2 是图片,3 是函数,10 是数字默认是文本
width	double	10	列宽
height	double	10	列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意
isStatistics	boolean	fasle	自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点
isHyperlink	boolean	false	超链接,如果是需要实现接口返回对象
isImportField	boolean	true	校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id
exportFormat	String	“”	导出的时间格式,以这个是否为空来判断是否需要格式化日期
importFormat	String	“”	导入的时间格式,以这个是否为空来判断是否需要格式化日期
format	String	“”	时间格式,相当于同时设置了exportFormat 和 importFormat
databaseFormat	String	“yyyyMMddHHmmss”	导出时间设置,如果字段是Date类型则不需要设置数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出
numFormat	String	“”	数字格式化,参数是Pattern,使用的对象是DecimalFormat
imageType	int	1	导出类型 1 从file读取 2 是从数据库中读取默认是文件同样导入也是一样的
suffix	String	“”	文字后缀,如% 90 变成90%
isWrap	boolean	true	是否换行即支持\n
mergeRely	int[]	{}	合并单元格依赖关系,比如第二列合并是基于第一列则{0}就可以了
mergeVertical	boolean	fasle	纵向合并内容相同的单元格
fixedIndex	int	-1	对应excel的列,忽略名字
isColumnHidden	boolean	false	导出隐藏列

@ExcelTarget

限定一个到处实体的注解,以及一些通用设置,作用于最外面的实体

属性	类型	默认值	功能
value	String	null	定义ID
height	double	10	设置行高
fontSize	short	11	设置文字大小

@ExcelEntity

标记是不是导出excel 标记为实体类,一遍是一个内部属性类,标记是否继续穿透,可以自定义内部id

属性	类型	默认值	功能
id	String	null	定义ID

@ExcelCollection

一对多的集合注解,用以标记集合是否被数据以及集合的整体排序

属性	类型	默认值	功能
id	String	null	定义ID
name	String	null	定义集合列名,支持nanm_id
orderNum	int	0	排序,支持name_id
type	Class<?>	ArrayList.class	导入时创建对象使用

三：测试

3.1-实体类

//课程实体类
@ExcelTarget("courseEntity")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CourseEntity implements Serializable {
    /**
     * 主键
     */
    private String id;
    /**
     * 课程名
     */
    @Excel(name="课程名称",orderNum = "1",width=25)
    private String courseNamel;
    /**
     * 老师
     */
    @ExcelEntity(id="absent")
    private TeacherEntity teacherEntity;
    /**
     * 学生
     */
    @ExcelCollection(name="学生",orderNum = "3")
    private List<StudentEntity> students;
}

//老师实体类
@ExcelTarget("absent")
@AllArgsConstructor
@NoArgsConstructor
@Data
public class TeacherEntity implements java.io.Serializable {
    /**
     * name
     */
    @Excel(name = "主讲老师_teacherEntity,代课老师_absent", orderNum = "1", mergeVertical = true, needMerge = true, isImportField = "true_major,true_absent")
    private String name;

    @Excel(name="打分",orderNum = "2")
    private float mark;
}

//学生实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentEntity implements java.io.Serializable {
    /**
     * id
     */
    private String        id;
    /**
     * 学生姓名
     */
    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st")
    private String        name;
    /**
     * 学生性别
     */
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
    private int           sex;

    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
    private Date birthday;

    @Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
    private Date registrationDate;

    @Excel(name="成绩",isImportField = "true_st",isStatistics = true)
    private Float grade;

 }

//公司实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ComponenyEntity {
    @Excel(name="公司名称",orderNum = "1")
    private String name;
    @Excel(name="公司Logo",orderNum = "2",type=2,width=40,height = 20,imageType = 1)
    private String logo;
    @Excel(name="公司地址",orderNum = "3")
    private String address;
}

3.2-本地测试

public class MyTest {
    public static void main(String[] args)throws Exception{
//        test1();
//        test2();
//        test3();
//        test4();

    }
    //导出学生测试
    public static void test1()throws Exception{
        List<StudentEntity> list=new ArrayList<>();
        list.add(new StudentEntity("001","sahngsan",2,new Date(),new Date(),43.4f));
        list.add(new StudentEntity("002","lisi",1,new Date(),new Date(),23.7f));
        list.add(new StudentEntity("003","wangwu",2,new Date(),new Date(),65.8f));
        list.add(new StudentEntity("004","zhaoliu",1,new Date(),new Date(),23.9f));
        System.out.println(list.size());
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机一班学生","学生"),
                StudentEntity.class, list);
       /* File file=new File("D:\\MyTest");
        System.out.println(file);
        if(!file.exists()){
            file.mkdir();
        }*/
        String uuid = UUID.randomUUID().toString();
        System.out.println(uuid);
        workbook.write(new FileOutputStream("D:\\MyTest\\"+uuid+".csv"));
        System.out.println("hello");
        System.out.println(list.toString());
    }
    //导出老师数据测试
    public static void test2()throws Exception{
        List<TeacherEntity> list=new ArrayList<>();
        list.add(new TeacherEntity("英语",23f));
        list.add(new TeacherEntity("数学",43f));
        list.add(new TeacherEntity("语文",98f));
        list.add(new TeacherEntity("物理",67f));
        System.out.println(list.size());
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
                TeacherEntity.class, list);
        /*File file=new File("D:\\MyTest\\excel.csv");
        System.out.println(file);
        if(!file.exists()){
            file.mkdir();
        }*/
        String uuid = UUID.randomUUID().toString();
        System.out.println(uuid);
        workbook.write(new FileOutputStream("D:\\MyTest\\"+uuid+".csv"));
        System.out.println("hello");
        System.out.println(list.toString());
    }
    //导出课程老师学生关系测试
    public static void test3()throws Exception{
        List<StudentEntity> listStu=new ArrayList<>();
        List<StudentEntity> listStu1=new ArrayList<>();
        List<StudentEntity> listStu2=new ArrayList<>();
        List<StudentEntity> listStu3=new ArrayList<>();
        listStu.add(new StudentEntity("001","sahngsan",2,new Date(),new Date(),43.4f));
        listStu.add(new StudentEntity("002","lisi",1,new Date(),new Date(),23.7f));
        listStu1.add(new StudentEntity("003","wangwu",2,new Date(),new Date(),65.8f));
        listStu1.add(new StudentEntity("004","zhaoliu",1,new Date(),new Date(),23.9f));
        listStu2.add(new StudentEntity("004","张三",1,new Date(),new Date(),23.9f));
        listStu2.add(new StudentEntity("004","李四",2,new Date(),new Date(),23.9f));
        listStu3.add(new StudentEntity("004","王五",1,new Date(),new Date(),23.9f));
        listStu3.add(new StudentEntity("004","赵六",2,new Date(),new Date(),23.9f));
        List<CourseEntity> list=new ArrayList<>();
        list.add(new CourseEntity("001","课程一",new TeacherEntity("老师一",22f),listStu));
        list.add(new CourseEntity("001","课程二",new TeacherEntity("老师二",25f),listStu1));
        list.add(new CourseEntity("001","课程三",new TeacherEntity("老师三",26f),listStu2));
        list.add(new CourseEntity("001","课程四",new TeacherEntity("老师四",24f),listStu3));

        System.out.println(list.size());
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
                CourseEntity.class, list);
        /*File file=new File("D:\\MyTest\\excel.csv");
        System.out.println(file);
        if(!file.exists()){
            file.mkdir();
        }*/
        String uuid = UUID.randomUUID().toString();
        System.out.println(uuid);
        workbook.write(new FileOutputStream("D:\\MyTest\\"+uuid+".csv"));
        System.out.println("hello");
        System.out.println(list.toString());
    }
    //导出带图片参数测试
    public static void test4()throws Exception{
        List<ComponenyEntity> list=new ArrayList<>();
        list.add(new ComponenyEntity("腾讯","C:\\Users\\Administrator\\Pictures\\background\\1.jpg","上海"));
        list.add(new ComponenyEntity("阿里","C:\\Users\\Administrator\\Pictures\\background\\2.jpg","杭州"));
        list.add(new ComponenyEntity("百度","C:\\Users\\Administrator\\Pictures\\background\\4.jpg","北京"));
        Workbook workbook=ExcelExportUtil.exportExcel(new ExportParams(),ComponenyEntity.class,list);
        String uuid = UUID.randomUUID().toString();
        System.out.println(uuid);
        workbook.write(new FileOutputStream("D:\\MyTest\\"+uuid+".csv"));
    }
}

3.3：课程导出效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dPuDd5NF-1624803112830)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210624143130020.png)]

四：生产环境使用

4.1：编写ExcelUtils工具类

public class ExcelUtils {
    public static void exportExcel(HttpServletRequest request, HttpServletResponse response, List list,String name)throws Exception{
        String path=request.getSession().getServletContext().getRealPath("/")+"/excel";
        String fileName=path+"name"+".csv";
        File file =new File(path);
        if(!file.exists()){
            file.mkdirs();
        }
        ExportParams exportParams=new ExportParams();
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, list.get(0).getClass(), list);
        FileOutputStream outputStream=new FileOutputStream(fileName);
        workbook.write(outputStream);
        outputStream.close();
        name = new String(name.getBytes(), "ISO-8859-1");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition", "attachment;filename=" + name + ".csv");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("UTF-8");
        ServletOutputStream out = response.getOutputStream();
        File fileout=new File(fileName);
        InputStream in=new FileInputStream(fileout);
        int read = in.read();
        while(read!=-1){
            out.write(read);
            read=in.read();
        }
        out.close();
        in.close();
        fileout.delete();
    }
}

4.2：导出接口

@RestController
@RequestMapping("/excel")
public class ExcelController {
    @GetMapping("/test1")
    public void test1(HttpServletRequest request, HttpServletResponse response)throws Exception{
        List<StudentEntity> list=new ArrayList<>();
        list.add(new StudentEntity("001","sahngsan",2,new Date(),new Date(),43.4f));
        list.add(new StudentEntity("002","lisi",1,new Date(),new Date(),23.7f));
        list.add(new StudentEntity("003","wangwu",2,new Date(),new Date(),65.8f));
        list.add(new StudentEntity("004","zhaoliu",1,new Date(),new Date(),23.9f));
        System.out.println(list.size());
        ExcelUtils.exportExcel(request,response,list,"测试");
    }
}

EasyPoi导入

一：实体类

@Data
public class StudentDto {
    /**
     * 学生姓名
     */
    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st",fixedIndex = 0)
    private String name;
    /**
     * 学生性别
     */
    @Excel(name = "学生性别", replace = { "男生_1", "女生_2" }, isImportField = "true_st")
    private Integer    sex;

    @Excel(name = "出生日期", importFormat = "yyyy-MM-dd HH:mm:ss", isImportField = "true_st", width = 20)
    private Date birthday;

    @Excel(name = "进校日期", format = "yyyy-MM-dd")
    private Date registrationDate;

    @Excel(name="成绩",isImportField = "true_st",isStatistics = true)
    private Float grade;
}

二：导入测试

public class ImportTest {
    public static void main(String[] args) {
        ImportParams params = new ImportParams();
        //表格标题行数
        params.setTitleRows(0);
        //表头行数
        params.setHeadRows(2);
        long start = new Date().getTime();
        List<StudentDto> list = ExcelImportUtil.importExcel(
                new File("D:\\MyTest\\bc72e4c9-28c2-4dd8-aef4-2cd9788bfaa5.xlsx"),
                StudentDto.class, params);
        System.out.println(new Date().getTime() - start);
        System.out.println(list.size());
        System.out.println(ReflectionToStringBuilder.toString(list.get(0)));
        for (StudentDto studentDto : list) {
            System.out.println(studentDto);
        }
    }
}

似夜晓星辰

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Excel报表的导入导出

EasyPoi导出一：引入依赖 <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency&
复制链接

扫一扫