快捷導(dǎo)航

Java實現(xiàn)集合和Excel文件相互轉(zhuǎn)換

更新時間：2025年08月01日 09:51:48 作者：CAT_cwds

本文主要介紹了使用Java和ApachePOI庫將集合轉(zhuǎn)化為包含合并單元格的Excel文件,及從Excel文件流中讀取并轉(zhuǎn)化為集合,具有一定的參考價值,感興趣的可以了解一下

一、集合轉(zhuǎn)化為Excel文件

效果如下，是將集合轉(zhuǎn)化為Excel文件，Excel包含合并單元格。

實體類：

@Data
public class ClassGrade {
    /** 年級 */
    private String grade;
    /** 班主任 */
    private String leader;
    /** 學(xué)生列表 */
    private List<Student> students;

    @Data
    public static class Student {
        /** 姓名 */
        private String name;
        /** 年齡 */
        private Integer age;
        /** 性別 */
        private String sex;
        /** 成績 */
        private Integer gradeResult;
    }
}

需求就是將ClassGrade的集合轉(zhuǎn)化為Excel表格對外輸出。沒有針對當(dāng)前類去逐個取值處理，用到了反射來處理，達到了簡化代碼通用的目的。這個只針對有一個合并單元格的情形，如果是合并單元格中包含合并單元格的話，還需要加代碼去處理。

實現(xiàn)代碼如下：

1.初始化表頭類，參數(shù)為表頭集合

    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);
            // 為標(biāo)題設(shè)置背景顏色
            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.將集合轉(zhuǎn)化為Excel

    @Test
    public void exportExcel() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        //初始化數(shù)據(jù)
        List<ClassGrade> fileList = new ArrayList<>();
        for (int m = 1; m <= 1; m++) {
            fileList.addAll(getGrades());
        }

        //表頭名稱
        String[] title = {"班主任", "學(xué)生姓名", "學(xué)生年齡", "學(xué)生性別", "學(xué)生成績", "班級"};
        SXSSFWorkbook workbook = SXSSFWorkbookUtil.makeExcelHead(title);

        //每一列表頭屬性，如果子類里面的，則提取子類里面對應(yīng)的屬性名
        String[] properties = {"leader", "name", "age", "sex", "gradeResult", "grade"};

        //獲取當(dāng)前sheet
        SXSSFSheet sheet = workbook.getSheetAt(0);
        int initRowNum = 0;
        //遍歷數(shù)據(jù)，需要根據(jù)業(yè)務(wù)邏輯去處理是否合并單元格
        for (int i = 0; i < fileList.size(); i++) {
            ClassGrade file = fileList.get(i);
            int size = file.getStudents().size();

			//創(chuàng)建行，以子類的集合數(shù)為準(zhǔn)
            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]));
            }

            //處理學(xué)生姓名~學(xué)生成績四列，非合并單元格信息
            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++) {
                	//利用反射獲取到值，并且設(shè)置到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;

        }

        //導(dǎo)出
        try (
                FileOutputStream excel = new FileOutputStream("excel.xls");
                BufferedOutputStream bos = new BufferedOutputStream(excel)) {
            workbook.write(bos);
            System.out.println("導(dǎo)出完成");
        } catch (
                IOException e) {
            System.out.println("導(dǎo)出失敗:" + e.getMessage());
        }

    }

    private void createCell(SXSSFRow row, int column, Object value) {
        SXSSFCell cell = row.createCell(column);
        if (value != null) {
            cell.setCellValue(String.valueOf(value));
        }
    }

效果

二、Excel文件轉(zhuǎn)化為集合

如題，將獲取到的excel文件流轉(zhuǎn)化為集合進行處理。挺簡單的。思路就是將一個文件流轉(zhuǎn)化為一個備用類，再將備用類轉(zhuǎn)化為想要的集合。

excel：

備用類代碼：

@Data
public class ClassGrade2 {
    /** 年級 */
    private String grade;
    /** 班主任 */
    private String leader;
    /** 姓名 */
    private String name;
    /** 年齡 */
    private Integer age;
    /** 性別 */
    private String sex;
    /** 成績 */
    private Integer gradeResult;
}

轉(zhuǎn)化代碼：

    @Test
    public void test1() {
        ExcelReader reader = ExcelUtil.getReader("excelToList.xls");
        List<List<Object>> rows = reader.read();
        //根據(jù)excel的結(jié)構(gòu)，需要準(zhǔn)備一個備用類接收數(shù)據(jù)
        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 {

            }
        }
        //備用類轉(zhuǎn)化為想要的集合類
        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));
    }

實現(xiàn)效果：

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":"女"}]}]

到此這篇關(guān)于Java實現(xiàn)集合和Excel文件相互轉(zhuǎn)換的文章就介紹到這了,更多相關(guān)Java集合和Excel文件相互轉(zhuǎn)換內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: