java解析excel信息（excel打开时报破损警告）

工具包：jsoup-1.13.1.jar

 

 链接：https://pan.baidu.com/s/1npwaTaBk7YiWx4MbxIb5Zw

提取码：bgmz

代码：

EXCELUTIL：

package com.util;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {
    /*
     * Student{number,name,gender,clazz,grades}
     *            ——》{学号、姓名、性别、班级、成绩列表}
     *Grades{code,name,type,credit,score}
     *            ——》{课程代码、课程名、类型（必/选），学分，分数}
     * */
    public static List<Student> readGrades(InputStream inputStream) throws IOException {
        final Document document = Jsoup.parse(inputStream, StandardCharsets.UTF_8.name(), "");
        inputStream.close();
        final Pattern coursePattern = Pattern.compile("(?<code>\\d{6}[A-Z])(?<name>.+)\\[(?<type>.+)]\\[(?<credit>.+)学分]");
        final Map<String, Integer> nameToIndex = new HashMap<>();
        final Map<Integer, Course> indexToCourse = new HashMap<>();
        try {
            final Element table = document.getElementsByTag("table").get(0);
            final Elements trs = table.getElementsByTag("tr");
            final Element titleRow = trs.get(5);
            final Elements titleTds = titleRow.getElementsByTag("td");
            for (int i = 0; i < titleTds.size(); i++) {
                final String text = titleTds.get(i).text();
                final Matcher matcher = coursePattern.matcher(text);
                if (matcher.matches()) {
                    indexToCourse.put(i, new Course(
                            matcher.group("code"),
                            matcher.group("name"),
                            matcher.group("type"),
                            Float.parseFloat(matcher.group("credit"))
                    ));
                } else {
                    nameToIndex.put(text, i);
                }
            }
            final List<Student> students = new ArrayList<>();
            for (int i = 6; i < trs.size(); i++) {
                final Elements tds = trs.get(i).getElementsByTag("td");
                final Student student = new Student();
                student.setNumber(tds.get(nameToIndex.get("学号")).text());
                if (student.getNumber().isEmpty()) {
                    continue;
                }
                student.setName(tds.get(nameToIndex.get("姓名")).text());
                student.setGender(tds.get(nameToIndex.get("性别")).text());
                student.setClazz(tds.get(nameToIndex.get("班级")).text());
                final List<Student.Grade> grades = new ArrayList<>();
                for (int j = 0; j < tds.size(); j++) {
                    if (indexToCourse.containsKey(j)) {
                        grades.add(new Student.Grade(indexToCourse.get(j), Float.parseFloat(tds.get(j).text())));
                    }
                }
                student.setGrades(grades);
                students.add(student);
            }
            return students;
        } catch (IndexOutOfBoundsException e) {
            return null;
        }
    }

    public static class Course {
        public Course(String code, String name, String type, float credit) {
            this.code = code;
            this.name = name;
            this.type = type;
            this.credit = credit;
        }

        public Course(Course course) {
            this.code = course.code;
            this.name = course.name;
            this.type = course.type;
            this.credit = course.credit;
        }
        protected String code;
        protected String name;
        protected String type;
        protected float credit;

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public float getCredit() {
            return credit;
        }

        public void setCredit(float credit) {
            this.credit = credit;
        }
    }
    public static class Student implements Serializable {
        private String number;
        private String name;
        private String gender;
        private String clazz;
        private List<Grade> grades;

        @Override
        public String toString() {
            return "Student{" +
                    "number='" + number + '\'' +
                    ", name='" + name + '\'' +
                    ", gender='" + gender + '\'' +
                    ", clazz='" + clazz + '\'' +
                    ", grades=" + grades +
                    '}';
        }

        public static class Grade extends Course implements Serializable {
            public Grade(Course course, float score) {
                super(course);
                this.score = score;
            }

            private float score;

            public float getScore() {
                return score;
            }

            public void setScore(float score) {
                this.score = score;
            }

            @Override
            public String toString() {
                return "Grade{" +
                        "score=" + score +
                        '}';
            }
        }

        public String getNumber() {
            return number;
        }

        public void setNumber(String number) {
            this.number = number;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getGender() {
            return gender;
        }

        public void setGender(String gender) {
            this.gender = gender;
        }

        public String getClazz() {
            return clazz;
        }

        public void setClazz(String clazz) {
            this.clazz = clazz;
        }

        public List<Grade> getGrades() {
            return grades;
        }

        public void setGrades(List<Grade> grades) {
            this.grades = grades;
        }
    }
}

Test:

package com.util;

import com.util.ExcelUtil;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;

public class Test {
    /*
     * Student{number,name,gender,clazz,grades}
     *            ——》{学号、姓名、性别、班级、成绩列表}
     *Grades{code,name,type,credit,score}
     *            ——》{课程代码、课程名、类型（必/选），学分，分数}
     * */
    public static void main(String[] args) {
        try {
            final String filename = "E:\\578095023\\FileRecv\\综测系统\\Excel表格获取\\信2007-1班+2020学年第1学期成绩.xls";
            List<ExcelUtil.Student> students = ExcelUtil.readGrades(new FileInputStream(filename));
            if (students != null) {
                for(ExcelUtil.Student student:students)
                {
                    for(ExcelUtil.Student.Grade grade:student.getGrades())
                    {
                        System.out.println("AAAAAAAAAAAAAAAAAA");
                        System.out.println(grade.getCode());
                        System.out.println(grade.getName());
                        System.out.println(grade.getCredit());
                        System.out.println(grade.getScore());
                        System.out.println(grade.getType());
                        
                    }
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}