文档章节

poi 读取excel

小代码2016
 小代码2016
发布于 2016/03/16 14:18
字数 1072
阅读 483
收藏 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
粉丝 36
博文 328
码字总数 153495
作品 0
安阳
程序员
poi读取excel出现excel中显示有值,但poi读取出来的类型为BLANK

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

心_行
2014/07/04
990
2
Java POI 导出EXCEL经典实现 Java导出Excel弹出下载框

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

文文1
2015/12/02
914
0
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

没有更多内容

加载失败,请刷新页面

加载更多

java基础知识,小栗子

来操作一下数组.....注意带参数的变长数组的使用. package com.avatus;import java.util.Random;import java.util.Scanner;public class Main { public static void main(St...

Oh_really
15分钟前
0
0
SSO单点登录PHP简单版

  前面做了一个新项目,需要用户资源可以需要共享。由于之前没有做过这样的东西,回家之后,立马网站百度“单点登录”。帖子很多,甄别之后,这里列几篇认为比较有营养。   http://blog...

slagga
53分钟前
2
0
Java 泛型详解-绝对是对泛型方法讲解最详细的,没有之一

对java的泛型特性的了解仅限于表面的浅浅一层,直到在学习设计模式时发现有不了解的用法,才想起详细的记录一下。 本文参考java 泛型详解、Java中的泛型方法、 java泛型详解 1 概述 泛型在j...

hensemlee
57分钟前
2
0
Annotation注解详细介绍

目录介绍 1.Annotation库的简单介绍 2.@Nullable和@NonNull 3.资源类型注释 4.类型定义注释 5.线程注释 6.RGB颜色纸注释 7.值范围注释 8.权限注释 9.重写函数注释 10.返回值注释 11.@Keep注释...

潇湘剑雨
58分钟前
2
0
一步步编写自己的PHP爬取代理IP项目(二)

这一章节我们正式开展我们的爬虫项目,首先我们先要知道哪个网站能获取到免费代理IP,目前比较火的有西刺代理,快代理等,这里我们拿西刺代理作为例子。 这里就是一个个免费的IP地址以及各自...

NateHuang
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部