在上一篇blog：java的poi技术读取Excel[2003-2007,2010] 中介绍了关于java中的poi技术读取excel的相关操作
读取excel和MySQL相关： java的poi技术读取Excel数据到MySQL
你也可以在 ： java的poi技术读取和导入Excel了解到写入Excel的方法信息
使用JXL技术 ：java的jxl技术导入Excel
本文主要讲的是java中poi读和写excel（版本是2003-2007,2010）
项目结构：
所用到的excel
运行效果：
Processing...lib/student_info.xls
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
======================================
Processing...lib/student_info.xlsx
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
======================================
write data to file : lib/student_info_2003-2007.xls
write data to file : lib/student_info_2010.xlsx
======================================
Processing...lib/student_info_2003-2007.xls
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
======================================
Processing...lib/student_info_2010.xlsx
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
=================================================
源码部分：
=================================================
/Excel2010/src/com/b510/excel/client/Client.java
/**
*
*/
package com.b510.excel.client; import java.util.List; import com.b510.excel.common.Common;
import com.b510.excel.util.ExcelUtil;
import com.b510.excel.vo.Student; /**
* @author Hongten
* @created 2014-5-21
*/
public class Client { public static void main(String[] args) throws Exception {
String read_excel2003_2007_path = Common.STUDENT_INFO_XLS_PATH;
String read_excel2010_path = Common.STUDENT_INFO_XLSX_PATH;
// read the 2003-2007 excel
List<Student> list = new ExcelUtil().readExcel(read_excel2003_2007_path);
if (list != null) {
for (Student student : list) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
System.out.println("======================================");
// read the 2010 excel
List<Student> list1 = new ExcelUtil().readExcel(read_excel2010_path);
if (list1 != null) {
for (Student student : list1) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
System.out.println("======================================");
String write_excel2003_2007_path = Common.STUDENT_INFO_XLS_OUT_PATH;
String write_excel2010_path = Common.STUDENT_INFO_XLSX_OUT_PATH;
new ExcelUtil().writeExcel(list, write_excel2003_2007_path);
new ExcelUtil().writeExcel(list, write_excel2010_path);
System.out.println("======================================"); // read the 2003-2007 excel
List<Student> list2 = new ExcelUtil().readExcel(write_excel2003_2007_path);
if (list != null) {
for (Student student : list2) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
System.out.println("======================================");
// read the 2010 excel
List<Student> list3 = new ExcelUtil().readExcel(write_excel2010_path);
if (list1 != null) {
for (Student student : list3) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
}
}
/Excel2010/src/com/b510/excel/common/Common.java
/**
*
*/
package com.b510.excel.common; /**
* @author Hongten
* @created 2014-5-21
*/
public class Common { public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"; public static final String EMPTY = "";
public static final String POINT = ".";
public static final String LIB_PATH = "lib";
public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
public static final String STUDENT_INFO_XLS_OUT_PATH = "lib/student_info_2003-2007.xls";
public static final String STUDENT_INFO_XLSX_OUT_PATH = "lib/student_info_2010.xlsx";
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
public static final String WRITE_DATA = "write data to file : "; }
/Excel2010/src/com/b510/excel/util/ExcelUtil.java
/**
*
*/
package com.b510.excel.util; import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.b510.excel.common.Common;
import com.b510.excel.vo.Student; /**
* @author Hongten
* @created 2014-5-20
*/
public class ExcelUtil { public void writeExcel(List<Student> list, String path) throws Exception {
if (list == null) {
return;
} else if (path == null || Common.EMPTY.equals(path)) {
return;
} else {
String postfix = Util.getPostfix(path);
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
writeXls(list, path);
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
writeXlsx(list, path);
}
}else{
System.out.println(path + Common.NOT_EXCEL_FILE);
}
}
} /**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public List<Student> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null;
} else {
String postfix = Util.getPostfix(path);
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path);
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path);
}
} else {
System.out.println(path + Common.NOT_EXCEL_FILE);
}
}
return null;
} /**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<Student> readXlsx(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
student = new Student();
XSSFCell no = xssfRow.getCell(0);
XSSFCell name = xssfRow.getCell(1);
XSSFCell age = xssfRow.getCell(2);
XSSFCell score = xssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
} /**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
public List<Student> readXls(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
student = new Student();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
} @SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
} @SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
} public void writeXls(List<Student> list, String path) throws Exception {
if (list == null) {
return;
}
int countColumnNum = list.size();
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet("studentSheet");
// option at first row.
HSSFRow firstRow = sheet.createRow(0);
HSSFCell[] firstCells = new HSSFCell[countColumnNum];
String[] options = { "no", "name", "age", "score" };
for (int j = 0; j < options.length; j++) {
firstCells[j] = firstRow.createCell(j);
firstCells[j].setCellValue(new HSSFRichTextString(options[j]));
}
//
for (int i = 0; i < countColumnNum; i++) {
HSSFRow row = sheet.createRow(i + 1);
Student student = list.get(i);
for (int column = 0; column < options.length; column++) {
HSSFCell no = row.createCell(0);
HSSFCell name = row.createCell(1);
HSSFCell age = row.createCell(2);
HSSFCell score = row.createCell(3);
no.setCellValue(student.getNo());
name.setCellValue(student.getName());
age.setCellValue(student.getAge());
score.setCellValue(student.getScore());
}
}
File file = new File(path);
OutputStream os = new FileOutputStream(file);
System.out.println(Common.WRITE_DATA + path);
book.write(os);
os.close();
} public void writeXlsx(List<Student> list, String path) throws Exception {
if (list == null) {
return;
}
//XSSFWorkbook
int countColumnNum = list.size();
XSSFWorkbook book = new XSSFWorkbook();
XSSFSheet sheet = book.createSheet("studentSheet");
// option at first row.
XSSFRow firstRow = sheet.createRow(0);
XSSFCell[] firstCells = new XSSFCell[countColumnNum];
String[] options = { "no", "name", "age", "score" };
for (int j = 0; j < options.length; j++) {
firstCells[j] = firstRow.createCell(j);
firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
}
//
for (int i = 0; i < countColumnNum; i++) {
XSSFRow row = sheet.createRow(i + 1);
Student student = list.get(i);
for (int column = 0; column < options.length; column++) {
XSSFCell no = row.createCell(0);
XSSFCell name = row.createCell(1);
XSSFCell age = row.createCell(2);
XSSFCell score = row.createCell(3);
no.setCellValue(student.getNo());
name.setCellValue(student.getName());
age.setCellValue(student.getAge());
score.setCellValue(student.getScore());
}
}
File file = new File(path);
OutputStream os = new FileOutputStream(file);
System.out.println(Common.WRITE_DATA + path);
book.write(os);
os.close();
}
}
/Excel2010/src/com/b510/excel/util/Util.java
/**
*
*/
package com.b510.excel.util; import com.b510.excel.common.Common; /**
* @author Hongten
* @created 2014-5-21
*/
public class Util { /**
* get postfix of the path
* @param path
* @return
*/
public static String getPostfix(String path) {
if (path == null || Common.EMPTY.equals(path.trim())) {
return Common.EMPTY;
}
if (path.contains(Common.POINT)) {
return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
}
return Common.EMPTY;
}
}
/Excel2010/src/com/b510/excel/vo/Student.java
/**
*
*/
package com.b510.excel.vo; /**
* Student
*
* @author Hongten
* @created 2014-5-18
*/
public class Student {
/**
* id
*/
private Integer id;
/**
* 学号
*/
private String no;
/**
* 姓名
*/
private String name;
/**
* 学院
*/
private String age;
/**
* 成绩
*/
private float score; public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getNo() {
return no;
} public void setNo(String no) {
this.no = no;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public String getAge() {
return age;
} public void setAge(String age) {
this.age = age;
} public float getScore() {
return score;
} public void setScore(float score) {
this.score = score;
} }
源码下载：http://pan.baidu.com/s/1eQpEf0u
========================================================
More reading,and english is important.
I'm Hongten
大哥哥大姐姐，觉得有用打赏点哦！多多少少没关系，一分也是对我的支持和鼓励。谢谢。
Hongten博客排名在100名以内。粉丝过千。
Hongten出品，必是精品。
E | hongtenzone@foxmail.com B | http://www.cnblogs.com/hongten
========================================================