POI与easyPOI的导入导出

1.技术简介

   Apache POI 是 Apache 软件基金会的开放源码函式库,POI 提供 API 给 Java 程序对
Microsoft Office 格式档案读和写的功能。

2.技术特点

1. 效率高
2. 支持公式,宏,一些企业应用上会非常实用
3. 能够修饰单元格属性
4. 支持字体、数字、日期操作
5. API 丰富,支持多种模式的读写
6. 支持大数量大文件的读写操作

3.POI抽象出来的对象

Excel文档			HSSFWorkbook
Excel工作表		HSSFSheet
Excel 的行		HSSFRow
Excel 中的单元格	  HSSFCell
Excel 字体		HSSFFont
Excel 单元格样式	  HSSFCellStyle
Excel 颜色		HSSFColor
合并单元格		  CellRangeAddress

4.需要的实体对象

需要的依赖

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.4</version>
        <scope>provided</scope>
    </dependency>
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    @ExcelTarget(value = "student")
    public class Student {

        @Excel(name = "ID")
        private String id;
        @Excel(name = "名字")
        private String name;
        @Excel(name = "年龄")
        private Integer age;
        @Excel(name = "生日",format = "yyyy-MM-dd",width = 20)
        private Date bir;
    }

5.POI的数据的导出

	 @Test
    public void testPoiOutput(){
		//样本数据
        Student student  = new Student("1","张三",18,new Date());
        Student student1 = new Student("2","李四",23,new Date());
        Student student2 = new Student("3","王五",16,new Date());
        Student student3 = new Student("4","赵六",27,new Date());
        Student student4 = new Student("5","什七",30,new Date());
        List<Student> students = Arrays.asList(student, student1, student2, student3, student4);

        //创建一个Excel文档
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作薄   参数是工作薄的名字(sheet1,sheet2....)
        Sheet sheet = workbook.createSheet("用户信息表1");
        //创建标题行  (下标从0开始)
        Row row = sheet.createRow(0);
        //设置内容
        Cell cellTitle = row.createCell(0);
        cellTitle.setCellValue("学生信息");
        //合并行  参数:起始行,结束行,起始列,结束列
        CellRangeAddress addresses = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(addresses);
        //设置列宽   (参数一:列下标,参数二:列宽    )
        sheet.setColumnWidth(3,20*256);
        
        CellStyle cellStyleTitle = workbook.createCellStyle();
         //设置标题居中
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        cellTitle.setCellStyle(cellStyleTitle);

        //设置字体样式
        Font font = workbook.createFont();
        //font.setBold(true);
        font.setColor(Font.COLOR_RED);
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 14);
        //font.setItalic(true);

        //创建字体样式对象
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);

        //创建表头  参数是行下标(下标是1)
        Row row1 = sheet.createRow(1);
        //设置行高   参数行高(注意单位:是20分之一   short类型)
        row1.setHeight((short) 450);

        //给目录行设置数据
        String[] title = {"ID","名字","年龄","生日"};
        for (int i =0; i<title.length;i++){
            //创建单元格
            Cell cell = row1.createCell(i);
            //创建的单元内容
            cell.setCellValue(title[i]);
            //给字体设置样式
            cell.setCellStyle(cellStyle);
        }
        //创建一个日期格式对象
        DataFormat dataFormat = workbook.createDataFormat();
        //创建一个样式对象
        CellStyle cellStyle1 = workbook.createCellStyle();
        //将日期格式放入到样式对象中
        cellStyle1.setDataFormat(dataFormat.getFormat("yyyy-MM-dd"));

        //处理行数据
        for(int i = 0;i<students.size();i++){
            Row row2 = sheet.createRow(i + 2);
            //设置单元格
            //设置单元格的内容
            row2.createCell(0).setCellValue(students.get(i).getId());
            row2.createCell(1).setCellValue(students.get(i).getName());
            row2.createCell(2).setCellValue(students.get(i).getAge());
            Cell cell = row2.createCell(3);
            cell.setCellStyle(cellStyle1);
            cell.setCellValue(students.get(i).getBir());
        }
        //导出单元格
        try {
            workbook.write(
                new FileOutputStream(new File("C:/Users/DELL/Desktop/TestPoi.xls")));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

5.POI的数据导入

1.基础代码

	@Test
    public void testPoiInput(){
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(
                new FileInputStream(new File("C:/Users/DELL/Desktop/TestPoi.xls")));
            HSSFSheet sheet = workbook.getSheet("用户信息表1");
            for (int i = 2;i<=sheet.getLastRowNum();i++){
                Student student = new Student();
                HSSFRow row = sheet.getRow(i);
                //依次给每个属性赋值
                student.setId(row.getCell(0).getStringCellValue());
                student.setName(row.getCell(1).getStringCellValue());
                double ages = row.getCell(2).getNumericCellValue();
                student.setAge((int) ages);
                student.setBir(row.getCell(3).getDateCellValue());
                //插入数据库的操作
                System.out.println("第  "+(i+1)+"  行数据是"+student);
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

2.代码优化(反射)

 	@Test
    public void testPoiInput1() {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("C:/Users/DELL/Desktop/TestPoi.xls")));
            HSSFSheet sheet = workbook.getSheet("用户信息表1");
            for (int i = 2;i<=sheet.getLastRowNum();i++){
                Student student = new Student();
                HSSFRow row = sheet.getRow(i);
                //拿到类对象
                Class<? extends Student> studentClass = student.getClass();
                //拿到本类中的所有属性
                Field[] fields = studentClass.getDeclaredFields();
                for (int j = 0; j < fields.length; j++) {
                    //依次拿到类中的属性名
                    String fieldName = fields[j].getName();
                    HSSFCell cell = row.getCell(j);
                    //拼接set方法
                    String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
                    //日期类型的处理
                    if(fieldName.equals("bir")){
                        Date date = cell.getDateCellValue();
                        Method method = studentClass.getDeclaredMethod(methodName, Date.class);
                        method.invoke(student,date);
                    }
                    //int类型的处理
                    else if(fieldName.equals("age")){
                        int ages = (int) cell.getNumericCellValue();
                        Method method = studentClass.getDeclaredMethod(methodName, Integer.class);
                        method.invoke(student,ages);
                    }
                    //String类型的处理
                    else {
                        String content = cell.getStringCellValue();
                        Method method = studentClass.getDeclaredMethod(methodName, String.class);
                        method.invoke(student,content);
                    }
                }
                //入库操作
                System.out.println("第  "+(i+1)+"  行数据是"+student);
            }
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

6.easyPoi基本导出

1.实体类

    //学生实体
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    @ExcelTarget(value = "student")
    public class Student {
        @Excel(name = "ID")
        private String id;
        @Excel(name = "名字")
        private String name;
        @Excel(name = "年龄")
        private Integer age;
        @Excel(name = "生日",format = "yyyy-MM-dd",width = 20)
        private Date bir;
    }

2.导出类

	@Test
    public void testEasyPoiOutput() {
        //模拟数据库查询出来的数据
        ArrayList<Student> stus = new ArrayList<>();
        stus.add(new Student("1","张三",18,new Date()));
        stus.add(new Student("2","李四",23,new Date()));
        stus.add(new Student("3","王五",16,new Date()));
        stus.add(new Student("4","赵六",27,new Date()));
        stus.add(new Student("5","什七",30,new Date()));
        //参数  : 标题  表名  实体类的类对象   导出的集合对象
        Workbook workbook = ExcelExportUtil.exportExcel(
            new ExportParams("计算机一班学生","学生"),Student.class, stus);
        try {
            workbook.write(
                new FileOutputStream(new File("C:/Users/DELL/Desktop/EasyPoi.xls")));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

7.easyPoi关系导出

1.实体类

//学生实体
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    @ExcelTarget(value = "student")
    public class Student {
        @Excel(name = "ID")
        private String id;
        @Excel(name = "名字")
        private String name;
        @Excel(name = "年龄")
        private Integer age;
        @Excel(name = "生日",format = "yyyy-MM-dd",width = 20)
        private Date bir;
    } 	
//教师实体
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    @ExcelTarget(value = "teacher")
    public class Teacher {
        @ExcelIgnore
        private String id;
        @Excel(name = "姓名",needMerge = true)
        private String name;
        @ExcelCollection(name = "学生信息")
        private List<Student> student;
    }

2.导出类

	@Test
    public void testEasyPoiOutput2() {
        //模拟从数据查询出来的数据
        List<Student> stus = new ArrayList<>();
        stus.add(new Student("1","张三",18,new Date()));
        stus.add(new Student("2","李四",23,new Date()));
        stus.add(new Student("3","王五",16,new Date()));
        stus.add(new Student("4","赵六",27,new Date()));
        stus.add(new Student("5","什七",30,new Date()));
      	List<Teacher> teachers = new ArrayList<Teacher>();
        teachers.add(new Teacher("1","A教师",stus));
        teachers.add(new Teacher("2","B教师",stus));
        //参数  : 标题  表名  实体类的类对象   导出的集合对象
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("班级学生信息", "java", "班级学生表"),
                Teacher.class, teachers);
        try {![在这里插入图片描述](https://img-blog.csdnimg.cn/20190821193612568.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzcxMzIz,size_16,color_FFFFFF,t_70)
            workbook.write(
                new FileOutputStream(new File("C:/Users/DELL/Desktop/EasyPoi.xls")));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

导出来的Excel表格:
在这里插入图片描述

8.easyPoi关系导入

@Test
public void testPoiInput(){
    ImportParams params = new ImportParams();
    params.setTitleRows(2);  //设置标题行数,默认时0
    params.setHeadRows(2);   //表头的行数  ,默认是1
    //获取导入数据
    try {
        List<Teacher> teachers  = ExcelImportUtil.importExcel(new FileInputStream(new File("C:/Users/DELL/Desktop/EasyPoi.xls")),Teacher.class, params);
        //入库操作
        for (Teacher teacher : teachers) {
            System.out.println(teacher);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

如过你要更加详细地学习easypoi,详情请见官网:http://easypoi.mydoc.io

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值