Java实现集合和Excel文件相互转换

谁借莪１个温暖的怀抱￠ 2024-02-18 11:05 21阅读 0赞

#### 目录 ####

*   *  一、集合转化为Excel文件
     *  二、Excel文件转化为集合

### 一、集合转化为Excel文件 ###

效果如下，是将集合转化为Excel文件，Excel包含合并单元格。  
![在这里插入图片描述][c3579a36bfba4d0b9d42f24126b43bb7.png]  
实体类：

@Data
    public class ClassGrade {
        
        /** 年级 */
        private String grade;
        /** 班主任 */
        private String leader;
        /** 学生列表 */
        private List<Student> students;
    
        @Data
        public static class Student {
        
            /** 姓名 */
            private String name;
            /** 年龄 */
            private Integer age;
            /** 性别 */
            private String sex;
            /** 成绩 */
            private Integer gradeResult;
        }
    }

需求就是将ClassGrade的集合转化为Excel表格对外输出。没有针对当前类去逐个取值处理，用到了反射来处理，达到了简化代码通用的目的。这个只针对有一个合并单元格的情形，如果是合并单元格中包含合并单元格的话，还需要加代码去处理。  
实现代码如下：  
1.初始化表头类，参数为表头集合

public static SXSSFWorkbook makeExcelHead(String[] titles) {
        
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            CellStyle styleTitle = getTitleStyle(workbook, (short) 16);
            SXSSFSheet sheet = workbook.createSheet();
            SXSSFRow rowTitle = sheet.createRow(0);
            for (int i = 0; i < titles.length; i++) {
        
                sheet.setDefaultColumnWidth(25);
                SXSSFCell cellTitle = rowTitle.createCell(i);
                // 为标题设置背景颜色
                styleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                styleTitle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
                cellTitle.setCellValue(titles[i]);
                cellTitle.setCellStyle(styleTitle);
            }
            return workbook;
        }

2.反射获取实体的值

public static <T> Object getProperty(T t, String propertyName) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, IndexOutOfBoundsException {
        
            Class<?> aClass = t.getClass();
            propertyName = propertyName.substring(0, 1).toUpperCase() + propertyName.substring(1);
            Method method = aClass.getMethod("get" + propertyName);
            Object invoke = method.invoke(t);
            return invoke;
        }

2.将集合转化为Excel

@Test
        public void exportExcel() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        
            //初始化数据
            List<ClassGrade> fileList = new ArrayList<>();
            for (int m = 1; m <= 1; m++) {
        
                fileList.addAll(getGrades());
            }
    
            //表头名称
            String[] title = {
        "班主任", "学生姓名", "学生年龄", "学生性别", "学生成绩", "班级"};
            SXSSFWorkbook workbook = SXSSFWorkbookUtil.makeExcelHead(title);
    
            //每一列表头属性，如果子类里面的，则提取子类里面对应的属性名
            String[] properties = {
        "leader", "name", "age", "sex", "gradeResult", "grade"};
    
            //获取当前sheet
            SXSSFSheet sheet = workbook.getSheetAt(0);
            int initRowNum = 0;
            //遍历数据，需要根据业务逻辑去处理是否合并单元格
            for (int i = 0; i < fileList.size(); i++) {
        
                ClassGrade file = fileList.get(i);
                int size = file.getStudents().size();
    
    			//创建行，以子类的集合数为准
                int startRowNum = initRowNum + 1;
                int lastRowNum = startRowNum + size - 1;
                SXSSFRow row = sheet.getRow(startRowNum);
                if (row == null) {
        
                    row = sheet.createRow(startRowNum);
                }
    
                //班主任一列，处理合并单元格
                for (int m = 0; m < 1; m++) {
        
                    if (lastRowNum - startRowNum > 0) {
        
                        sheet.addMergedRegion(new CellRangeAddress(startRowNum, lastRowNum, m, m));
                    }
                    createCell(row, m, SXSSFWorkbookUtil.getProperty(file, properties[m]));
                }
    
                //处理学生姓名~学生成绩四列，非合并单元格信息
                int xRowNum = startRowNum;
                List<ClassGrade.Student> receiptItems = file.getStudents();
                for (ClassGrade.Student student : receiptItems) {
        
                    SXSSFRow row1 = sheet.getRow(xRowNum);
                    if (row1 == null) {
        
                        row1 = sheet.createRow(xRowNum);
                    }
                    for (int q = 1; q <= 4; q++) {
        
                    	//利用反射获取到值，并且设置到cell里面
                        createCell(row1, q, SXSSFWorkbookUtil.getProperty(student, properties[q]));
                    }
                    xRowNum++;
                }
    
                //处理班级信息合并单元格
                for (int n = 5; n <= 5; n++) {
        
                    if (lastRowNum - startRowNum >= 1) {
        
                        sheet.addMergedRegion(new CellRangeAddress(startRowNum, lastRowNum, n, n));
                    }
                    createCell(row, n, SXSSFWorkbookUtil.getProperty(file, properties[n]));
                }
                initRowNum = lastRowNum;
    
            }
    
            //导出
            try (
                    FileOutputStream excel = new FileOutputStream("excel.xls");
                    BufferedOutputStream bos = new BufferedOutputStream(excel)) {
        
                workbook.write(bos);
                System.out.println("导出完成");
            } catch (
                    IOException e) {
        
                System.out.println("导出失败:" + e.getMessage());
            }
    
        }
    
        private void createCell(SXSSFRow row, int column, Object value) {
        
            SXSSFCell cell = row.createCell(column);
            if (value != null) {
        
                cell.setCellValue(String.valueOf(value));
            }
        }

效果  
![在这里插入图片描述][dc79c8ddfeef4c80b84674f6ae028c62.png]

### 二、Excel文件转化为集合 ###

如题，将获取到的excel文件流转化为集合进行处理。挺简单的。思路就是将一个文件流转化为一个备用类，再将备用类转化为想要的集合。  
excel：  
![在这里插入图片描述][75e84a8f5a0b4aecaecf07ff4f7fd65c.png]  
备用类代码：

@Data
    public class ClassGrade2 {
        
        /** 年级 */
        private String grade;
        /** 班主任 */
        private String leader;
        /** 姓名 */
        private String name;
        /** 年龄 */
        private Integer age;
        /** 性别 */
        private String sex;
        /** 成绩 */
        private Integer gradeResult;
    }

转化代码：

@Test
        public void test1() {
        
            ExcelReader reader = ExcelUtil.getReader("excelToList.xls");
            List<List<Object>> rows = reader.read();
            //根据excel的结构，需要准备一个备用类接收数据
            List<ClassGrade2> listBaks = new ArrayList<>();
    
            //初始化属性，属性是备用类的属性名
            String[] properties = {
        "leader", "name", "age", "sex", "gradeResult", "grade"};
            for (int j = 1; j < rows.size(); j++) {
        
                List<Object> cells = rows.get(j);
                try {
        
                    //反射获取值，组装成备用类
                    ClassGrade2 file = new ClassGrade2();
                    Class<?> clz = file.getClass();
                    Method[] methods = clz.getDeclaredMethods();
                    for (int i = 0; i < cells.size(); i++) {
        
                        String propertyName = properties[i].substring(0, 1).toUpperCase() + properties[i].substring(1);
                        Method method = Arrays.stream(methods).filter(m -> Objects.equal(m.getName(), "set" + propertyName)).findFirst().orElse(null);
                        Object cell = cells.get(i);
                        if (cell == null) {
        
                            continue;
                        }
                        Field field = clz.getDeclaredField(properties[i]);
                        String fieldType = field.getType().getName();
                        if (fieldType.equals("java.lang.String")) {
        
                            method.invoke(file, String.valueOf(cell));
                        } else if (fieldType.equals("java.math.BigDecimal")) {
        
                            method.invoke(file, new BigDecimal(String.valueOf(cell)));
                        }
                    }
                    listBaks.add(file);
                } catch (IllegalAccessException e) {
        
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
        
                    e.printStackTrace();
                } catch (NoSuchFieldException e) {
        
                    e.printStackTrace();
                } finally {
        
    
                }
            }
            //备用类转化为想要的集合类
            int i = 0;
            List<ClassGrade> files = new ArrayList<>();
            for (int m = 0; m < listBaks.size(); m++) {
        
                ClassGrade2 fileBak = listBaks.get(m);
                if (Strings.isNotEmpty(fileBak.getGrade())) {
        
                    ClassGrade file = BeanUtil.copyProperties(fileBak, ClassGrade.class);
                    files.add(file);
    
                    ClassGrade.Student item = BeanUtil.copyProperties(fileBak, ClassGrade.Student.class);
                    List<ClassGrade.Student> items = new ArrayList<>();
                    items.add(item);
                    file.setStudents(items);
                    i++;
                } else {
        
                    ClassGrade.Student item = BeanUtil.copyProperties(fileBak, ClassGrade.Student.class);
                    ClassGrade file = files.get(i - 1);
                    List<ClassGrade.Student> items = CollectionUtils.isEmpty(file.getStudents()) ? new ArrayList<>() : file.getStudents();
                    items.add(item);
                    file.setStudents(items);
                }
            }
            log.info("{}", JSONArray.toJSONString(listBaks));
            log.info("{}", JSONArray.toJSONString(files));
        }

实现效果：

11:47:03.635 [main] INFO Excel3Test - [{
        "grade":"八年级1班","leader":"赵老师","name":"张三","sex":"女"},{
        "grade":"八年级1班","leader":"赵老师","name":"李四","sex":"女"},{
        "grade":"八年级1班","leader":"赵老师","name":"王五","sex":"女"}]
    11:47:03.647 [main] INFO Excel3Test - [{
        "grade":"八年级1班","leader":"赵老师","students":[{
        "name":"张三","sex":"女"}]},{
        "grade":"八年级1班","leader":"赵老师","students":[{
        "name":"李四","sex":"女"}]},{
        "grade":"八年级1班","leader":"赵老师","students":[{
        "name":"王五","sex":"女"}]}]

[c3579a36bfba4d0b9d42f24126b43bb7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/380e99a25e7046c6a2353139ace4b417.png
[dc79c8ddfeef4c80b84674f6ae028c62.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/0da61ed2bbaa4d1aa97f5a36b400f8c2.png
[75e84a8f5a0b4aecaecf07ff4f7fd65c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/c71210598e774b4d91db83e7f552cd92.png