POI与easyPOI的导入导出

不念不忘少年蓝@ 2021-10-13 04:13 481阅读 0赞

## 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.实体类 ###

### 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表格：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzcxMzIz_size_16_color_FFFFFF_t_70]

## 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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzcxMzIz_size_16_color_FFFFFF_t_70]: /images/20211013/f1e276481bdd4fc28be9e226a5c6cb51.png