如何利用Jakarta的POI来读写微软的Excel文件?
首先简单介绍一下POI,POI是Apache提供的一套开源的API,用来操作微软的基于OLE2组件格式的文档。目前POI已经提供的API可以操作Excel文件,但是读写WORD文件还没有完成,大家可以不断关注Apache的动态,至少现在我们可以操作Excel表。这套API我们可以从Apache的官方网站下载到:
HTTP://jakarta.apache.org/builds/jakarta-poi.
下面的是具体的一个简单例子实现从Excel表读取数据并存入Mysql中的简单代码,希望对你们有帮助:
package com.text;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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.poifs.filesystem.POIFSFileSystem;
public class ReadExcel {
private static Connection conn=null;
private String name=null;
private String sex=null;
private FileInputStream fis;
public Connection getConnection(){
String ClassForName="com.mysql.jdbc.Driver";
if(conn==null){
try {
Class.forName(ClassForName).newInstance();
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/text?user=text&password=text");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return conn;
}
public void readExcelRow(){
try {
fis=new FileInputStream("C://Documents and Settings//Administrator//桌面//text.xls");
POIFSFileSystem fs=new POIFSFileSystem(fis);
HSSFWorkbook book=new HSSFWorkbook(fs);
HSSFSheet sheet=book.getSheet("Sheet1");
int countRow=sheet.getPhysicalNumberOfRows();
System.out.println(countRow);
for(int rowIndex=1;rowIndex<countRow;rowIndex++){
HSSFRow row=sheet.getRow(rowIndex);
int cols=row.getPhysicalNumberOfCells();
if(row==null)
continue;
for (int colIndex=0;colIndex<cols;colIndex++){
HSSFCell cell=row.getCell(colIndex);
if(cell.getCellNum()==0){
if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
name=cell.getRichStringCellValue().getString();
System.out.println(name);
}
}
if(cell.getCellNum()==1){
sex=cell.getRichStringCellValue().getString();
System.out.println(sex);
}
}
conn=this.getConnection();
String sql="insert into text(name,sex) values (?,?)";
PreparedStatement sta=conn.prepareStatement(sql);
sta.setString(1,name);
sta.setString(2,sex);
sta.executeUpdate(sql);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
try {
fis.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args){
ReadExcel re=new ReadExcel();
re.readExcelRow();
}
}