1.创建一个WritableWorkbook对象(用Workbook的createWorkbook方法创建)，要指定创建一个文件；
2.创建一个工作表WritableSheet(用workbook对象的createSheet方法创建)，注意要是WritableSheet，说明可以对其写；
3.创建单元格，再将单元格加入到sheet里；
4.执行workbook的write()方法进行写操作最后关闭workbook。
这篇blog主要是讲述java中poi读取excel，而excel的版本包括：2003-2007和2010两个版本， 即excel的后缀名为：xls和xlsx。
读取excel和MySQL相关： java的poi技术读取Excel数据到MySQL
代码如下
/***
*/
package com.b510.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 NOT_EXCEL_FILE = " : Not the Excel file!"
public static final String PROCESSING = "Processing..."
}
/**
*
*/
package com.b510.excel
import java.io.FileInputStream
import java.io.IOException
import java.io.InputStream
import java.util.ArrayList
import java.util.List
import org.apache.poi.hssf.usermodel.HSSFCell
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.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import com.b510.common.Common
import com.b510.excel.util.Util
import com.b510.excel.vo.Student
/**
* @author Hongten
* @created 2014-5-20
*/
public class ReadExcel {
/**
* 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())
}
}
}
/**
*
*/
package com.b510.excel.client
import java.io.IOException
import java.util.List
import com.b510.common.Common
import com.b510.excel.ReadExcel
import com.b510.excel.vo.Student
/**
* @author Hongten
* @created 2014-5-21
*/
public class Client {
public static void main(String[] args) throws IOException {
String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH
String excel2010 = Common.STUDENT_INFO_XLSX_PATH
// read the 2003-2007 excel
List<Student> list = new ReadExcel().readExcel(excel2003_2007)
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 ReadExcel().readExcel(excel2010)
if (list1 != null) {
for (Student student : list1) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore())
}
}
}
}
/**
*
*/
package com.b510.excel.util
import com.b510.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
}
}
/**
*
*/
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
}
}
需要对Excel中的数据进行读取操作。一、在开始进行Java读写Excel前，需要先下一个jxl的jar包，这个jar包中提供了相关读写Excel的方法，将jxl.jar放到classpath下或者在工程的buildpath中添加jxl.jar后，便可以开始Java读写Excel了。二、Java读取Excel数据,首先，创建一个xls文件（如：jxltest.xls），然后在文件中添加一些数据，Excel文件创建完成后，便可以开始写代码读取了。三、进行一个小小的扩展，读取一个目录下的所有Excel文件，读取的每个Excel文件的数据写入到不同的txt中。四、生成EXCEL需要手动写查询语句把ORACLE数据库中的数据查询出来，再通过操作写到EXCEL文件里面。通过EXCEL把数据读取到ORACLE，同样需要去读取EXCEL工作薄里面的内容，再通过INSERT语句去插入数据库操作。
