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

@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;
    }
}

    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;
    }

    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;
    }

    @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));
        }
    }

@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));
    }