JAVA 通常有两种方法来操作Excel,分别是POI和JExcelAPI,而且都是开源的。POI是Apace公司开发的,对中文的支持比较弱一些;JExcelAPI是韩国公司开发的,对中文的支持好,而且由于是纯JAVA编写的,所以可以跨平台操作。本文介绍的也是JExcelAPI的使用方法。
1
、环境配置
到 http://www.andykhan.com/jexcelapi/download.html 下载对应的功能包
然后解压得到:
jxl.jar
源代码
文档
如果使用集成开发环境,则只要引入jxl.jar功能包就可以。
本人读取Excel文件的代码如下:
package com.shinewaysoft.supperlotto.util;
import com.shinewaysoft.supperlotto.database.SupperLottoTable;
import com.shinewaysoft.supperlotto.database.SupperLottoTableJpaController;
import com.shinewaysoft.supperlotto.database.exceptions.NonexistentEntityException;
import java.io.File;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
*
* @describe 使用了jxl功能包,用于读取要导入的Excel文件
* @author <guowenbin>
* @date (2012-1-13)
*
*/
public class ReadInXLS {
// public static void main(String[] args) throws IOException{
// ReadInXLS.getExcelFile("C:\\Documents and Settings\\Administrator\\桌面\\新建 Microsoft Excel 工作表.xls");
// }
public ReadInXLS(){}
/*
获得excel文件,并读取第一个sheet对应的内容
*/
public static boolean getExcelFile(String filePath) throws IOException, NonexistentEntityException, Exception{
try {
String oldChar = "\\";
String newChar = "\\\\";
filePath = filePath.replace(oldChar, newChar);
File file = new File(filePath);
if(file.exists()){
Workbook book = Workbook.getWorkbook(file);
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格
int row = sheet.getRows();
EntityManagerFactory emf = Persistence.createEntityManagerFactory("SupperLotto1.0PU");
SupperLottoTable supperLottoTable = new SupperLottoTable();
SupperLottoTableJpaController controller = new SupperLottoTableJpaController(emf);
Cell[] cell = null;
for(int i=1; i<row ; i++){
cell = sheet.getRow(i);
supperLottoTable.setIssue(Integer.parseInt(cell[0].getContents()));
supperLottoTable.setBefNumber1(Integer.parseInt(cell[1].getContents()));
supperLottoTable.setBefNumber2(Integer.parseInt(cell[2].getContents()));
supperLottoTable.setBefNumber3(Integer.parseInt(cell[3].getContents()));
supperLottoTable.setBefNumber4(Integer.parseInt(cell[4].getContents()));
supperLottoTable.setBefNumber5(Integer.parseInt(cell[5].getContents()));
supperLottoTable.setAftNumber1(Integer.parseInt(cell[6].getContents()));
supperLottoTable.setAftNumber2(Integer.parseInt(cell[7].getContents()));
controller.edit(supperLottoTable);
}
book.close();
} else {
System.out.println("没有找到文件");
return false;
}
} catch (BiffException ex) {
Logger.getLogger(ReadInXLS.class.getName()).log(Level.SEVERE, null, ex);
}
return true;
}
}