文档章节

poi 读取excel

小代码2016
 小代码2016
发布于 2016/03/16 14:18
字数 1072
阅读 493
收藏 5

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>






本文转载自:http://blog.csdn.net/zht666/article/details/11598711

共有 人打赏支持
小代码2016
粉丝 37
博文 328
码字总数 153495
作品 0
安阳
程序员
私信 提问
Java POI 导出EXCEL经典实现 Java导出Excel弹出下载框

在web开发中,有一个经典的功能,就是数据的导入导出。特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL或者...

文文1
2015/12/02
914
0
poi读取excel出现excel中显示有值,但poi读取出来的类型为BLANK

1,poi读取excel的时候有一列的类型读取出来为blank(空),但是在excel里面是有值的 2,有一列在excel里看是没有公式的,但是poi读取的时候却是有。。。 3,有一列在excel里看是有公式的(=J2...

心_行
2014/07/04
990
2
POI框架:Java程序读取Excel

What:POI是什么? Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 HOW:如何使用java程序读取Excel文件中的内容? 1.坐标(导...

aboyliupu
07/31
0
0
POI - 读取Excel2003、Excel2007或更高级的兼容性问题

我们使用POI中的HSSFWorkbook来读取Excel数据。 上边代码,读取Excel2003(xls)的文件没问题,但是一旦读取的是Excel2007(xlsx)的文件,就会报异常:“The supplied data appears to be i...

技术小美
2017/11/14
0
0
Java POI读取Office excel (2003,2007)及相关jar包

poi-3.7-20101029.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107089 geronimo-stax-api1.0spec-1.0.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107083 x......

姚君
2014/04/17
0
0

没有更多内容

加载失败,请刷新页面

加载更多

docker部署springboot项目

安装docker 菜鸟教程 springboot项目 maven依赖 <?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001......

yimingkeji
49分钟前
9
0
ios多个target

1.建立3个target,分别为heroone,heroone test,heroone dev;分别为正式环境,test环境,dev环境 2.注意取消掉autocreate以防止名字不对,分别以Duplicate的方式建立另外两个scheme 3.创建...

HeroHY
今天
5
0
php获取客户端IP

php获取客户端IP 首先先阅读关于IP真实性安全的文章:如何正確的取得使用者 IP? 「任何從客戶端取得的資料都是不可信任的!」 HTTP_CLIENT_IP头是有的,但未成标准,不一定服务器都实现。 ...

DrChenXX
昨天
0
0
. The valid characters are defined in RFC 7230 and RFC 问题

通过这里的回答,我们可以知道: Tomcat在 7.0.73, 8.0.39, 8.5.7 版本后,添加了对于http头的验证。 具体来说,就是添加了些规则去限制HTTP头的规范性 参考这里 具体来说: org.apache.tom...

west_coast
昨天
1
0
刷leetcode第704题-二分查找

今天双十一买的算法书到货了,路上刷到有人说的这个题,借(chao)鉴(xi)一下别人的思路,这个是C++标准库里面的经典方法,思路精巧,优雅好品味 int search(int* nums, int numsSize, in...

锟斤拷烫烫烫
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部