poi 读取excel

最新推荐文章于 2025-08-11 09:20:14 发布

weixin_34320724

最新推荐文章于 2025-08-11 09:20:14 发布

阅读量73

点赞数

CC 4.0 BY-SA版权

文章标签： java 开发工具 python

原文链接：https://my.oschina.net/iamhere/blog/638481

2019独角兽企业重金招聘Python工程师标准>>>

excel文件内容：

项目目录：

D:\code\smvc\phone-poi>tree /f
卷 软件 的文件夹 PATH 列表
卷序列号为 0000-CD08
D:.
│  .classpath
│  .project
│  pom.xml
│
├─.settings
│      org.eclipse.core.resources.prefs
│      org.eclipse.jdt.core.prefs
│      org.eclipse.m2e.core.prefs
│
├─m
│      student.xls
│      student.xlsx
│
├─src
│  ├─main
│  │  └─java
│  │      └─com
│  │          └─laolang
│  │              ├─officeutil
│  │              │      StudentExcelUtil.java
│  │              │
│  │              └─pojo
│  │                      Student.java
│  │
│  └─test
│      └─java
│          └─com
│              └─laolang
│                  └─officeutil
│                          StudentExcelUtilTest.java
│
└─wps
        student.xls
        student.xlsx


D:\code\smvc\phone-poi>

这里共有四个excel文件，m 文件夹下的是我用microsoft excel 2010 编辑的,wps下的是我用 wps编辑的，每个文件夹下都有 97-03、07两种版本，内容基本上是一样的

代码：

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.laolang.phone-study</groupId>
	<artifactId>phone-poi</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>phone-poi</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>
	</dependencies>
</project>

com.laolang.pojo.Student

package com.laolang.pojo;

public class Student {

	public Student() {
		super();
	}

	public Student(String name, int age, String sex) {
		super();
		this.name = name;
		this.age = age;
		this.sex = sex;
	}

	public Student(int id, String name, int age, String sex) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
		this.sex = sex;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age
				+ ", sex=" + sex + "]";
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

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

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	private int id;
	private String name;
	private int age;
	private String sex;
}

com.laolang.officeutil.StudentExcelUtil

package com.laolang.officeutil;

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

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.laolang.pojo.Student;

public class StudentExcelUtil {

	public static List<Student> execelToStudent(String filename) {
		List<Student> stuList = new ArrayList<Student>();
		File excelFile = new File(filename);
		try {
			FileInputStream is = new FileInputStream(excelFile);
			Workbook workbook = WorkbookFactory.create(is);//这种方式 Excel 2003/2007/2010 都是可以处理的
			int sheetCount = workbook.getNumberOfSheets();//Sheet的数量 
			//遍历每个Sheet  
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = workbook.getSheetAt(s);//取得当前sheet
				int rowCount = sheet.getPhysicalNumberOfRows();//获取总行数  
				//遍历第一行，因为第一行，也就是索引为0的那一行是标题，所以这里从第二行也就是索引为1的行开始遍历
				for (int r = 1; r < rowCount; r++) {
					Student stu = new Student();
					Row row = sheet.getRow(r);
					int cellCount = row.getPhysicalNumberOfCells();//获取总列数 
					for (int c = 0; c < cellCount; c++) {
						Cell cell = row.getCell(c);
						switch (c) {
						case 0: {
							//我没有发现直接攻取int的方法，又不想先取其内容类型再取值，
							//所以我这里的方法是取出double型数据，然后再强转为int
							stu.setId((int)cell.getNumericCellValue());
							break;
						}
						case 1: {
							stu.setName(cell.getStringCellValue());
							break;
						}
						case 2: {
							stu.setAge((int)cell.getNumericCellValue());
							break;
						}
						case 3: {
							stu.setSex(cell.getStringCellValue());
							break;
						}
						}
					}
					
					stuList.add(stu);
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return stuList;

	}
}

com.laolang.officeutil.StudentExcelUtilTest

package com.laolang.officeutil;

import java.util.List;

import org.junit.Test;

import com.laolang.pojo.Student;

public class StudentExcelUtilTest {

	
	@Test
	public void testReadM(){
		System.out.println("microsoft 97-03:");
		List<Student> stuList = StudentExcelUtil.execelToStudent("m/student.xlsx");
		for( Student stu : stuList ){
			System.out.println(stu);
		}
		System.out.println("microsoft 97-03:");
		stuList = StudentExcelUtil.execelToStudent("m/student.xlsx");
		for( Student stu : stuList ){
			System.out.println(stu);
		}
	}
	@Test
	public void testReadWps(){
		System.out.println("wpd 97-03:");
		List<Student> stuList = StudentExcelUtil.execelToStudent("wps/student.xlsx");
		for( Student stu : stuList ){
			System.out.println(stu);
		}
		System.out.println("wps:");
		stuList = StudentExcelUtil.execelToStudent("m/student.xlsx");
		for( Student stu : stuList ){
			System.out.println(stu);
		}
	}
}

运行结果：

D:\code\smvc\phone-poi>mvn test
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building phone-poi 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-resources-plugin:2.4.3:resources (default-resources) @ phone-poi ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory D:\code\smvc\phone-poi\src\main\resources
[INFO]
[INFO] --- maven-compiler-plugin:2.3.2:compile (default-compile) @ phone-poi ---
[INFO] Compiling 2 source files to D:\code\smvc\phone-poi\target\classes
[INFO]
[INFO] --- maven-resources-plugin:2.4.3:testResources (default-testResources) @ phone-poi ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory D:\code\smvc\phone-poi\src\test\resources
[INFO]
[INFO] --- maven-compiler-plugin:2.3.2:testCompile (default-testCompile) @ phone-poi ---
[INFO] Compiling 1 source file to D:\code\smvc\phone-poi\target\test-classes
[INFO]
[INFO] --- maven-surefire-plugin:2.7.2:test (default-test) @ phone-poi ---
[INFO] Surefire report directory: D:\code\smvc\phone-poi\target\surefire-reports

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running com.laolang.officeutil.StudentExcelUtilTest
microsoft 97-03:
Student [id=1001, name=小代码, age=23, sex=男]
Student [id=1002, name=老狼, age=34, sex=男]
Student [id=1003, name=天涯, age=35, sex=男]
Student [id=1004, name=行者, age=23, sex=男]
microsoft 97-03:
Student [id=1001, name=小代码, age=23, sex=男]
Student [id=1002, name=老狼, age=34, sex=男]
Student [id=1003, name=天涯, age=35, sex=男]
Student [id=1004, name=行者, age=23, sex=男]
wpd 97-03:
Student [id=1001, name=小代码, age=23, sex=男]
Student [id=1002, name=老狼, age=34, sex=男]
Student [id=1003, name=行者, age=35, sex=男]
Student [id=1004, name=天涯, age=23, sex=男]
wps:
Student [id=1001, name=小代码, age=23, sex=男]
Student [id=1002, name=老狼, age=34, sex=男]
Student [id=1003, name=天涯, age=35, sex=男]
Student [id=1004, name=行者, age=23, sex=男]
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.82 sec

Results :

Tests run: 2, Failures: 0, Errors: 0, Skipped: 0

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.330s
[INFO] Finished at: Wed Mar 16 14:17:58 CST 2016
[INFO] Final Memory: 11M/19M
[INFO] ------------------------------------------------------------------------
D:\code\smvc\phone-poi>

转载于:https://my.oschina.net/iamhere/blog/638481