java读取Excel

应用场景

近期做毕设需要批量导入题库，所以要用到读取表格的技术。针对Excel2007及以上版本，需要采用XSSF方式进行读取。

添加依赖

poi和poi-ooxml缺一不可。

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

读取Excel的工具类

public class POIReadExcelTool {

    public static List<Student> readXlSX(String path) throws Exception {
        List<Student> stuList = new ArrayList<Student>();
        InputStream is = new FileInputStream(new File(path));
        Workbook excel = WorkbookFactory.create(is);
        is.close();

        // 遍历所有表格
        for (int numSheet = 0; numSheet < excel.getNumberOfSheets(); numSheet++) {
            Sheet sheet = excel.getSheetAt(numSheet);
            System.out.println(sheet.getSheetName()); //输出该表格的名称
            if (sheet == null) {
                continue;
            }
            //遍历所有行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                Student stu = new Student();
                Cell cell0 = row.getCell(0);
                stu.setStuNumber((int) cell0.getNumericCellValue());
                Cell cell1 = row.getCell(1);
                stu.setStuName(cell1.getStringCellValue());
                Cell cell2 = row.getCell(2);
                stu.setAge((int) cell2.getNumericCellValue());
                Cell cell3 = row.getCell(3);
                stu.setStuMajor(cell3.getStringCellValue());

                stuList.add(stu);
            }
        }
        return stuList;
    }
}

测试和输出

测试代码

    public void test() throws Exception {
        List<Student> studentList = POIReadExcelTool.readXlSX("d:/yun_test.xlsx");
        for(Student stu : studentList) {
            System.out.println(stu);
        }
    }

输出

学生表
Student{stuNumber=1, stuName='马云', age=25, stuMajor='赚钱的艺术'}
Student{stuNumber=2, stuName='赵云', age=25, stuMajor='武术的魅力'}
Student{stuNumber=3, stuName='张翼德', age=25, stuMajor='砍人的艺术'}
Student{stuNumber=4, stuName='关羽', age=25, stuMajor='剃须的艺术'}

参考文章

POI操作Excel详解，HSSF和XSSF两种方式