解析Excel的包很多,之前用的是jxl,但是jxl只能是解析Excel2003即office兼容版。
后来发现一个Apache的开源包,POI,这个可以处理xls和xlsx扩展名的Excel文件。
POI依赖jar包下载:http://download.csdn.net/detail/gopain/8200713
需要的jar包:
给一份代码供参考:
package cn.gopain.serverTeacher;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
/以下为POI需要的类库
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.ccbupt.kaoshi.dao.XueShengLinShi;
import cn.gopain.comm.define.KAOSHI_FILE;
import cn.gopain.main.Encrypt;
/**
* @author gopain
* 测试文件读取,解析考题文件。仅测试
*/
public class ReadFile {
/**
* @param args
*/
public static void main(String[] args) {
HSSFWorkbook hssfWorkbook = null;//Excel2003
try {
hssfWorkbook = new HSSFWorkbook(new FileInputStream("2014-2015-1体育_xls.xls"));
} catch (IOException e1) {
e1.printStackTrace();
}
if (hssfWorkbook == null) System.out.println("File not found");
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {//遍历每一个sheet
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) {
XueShengLinShi x = new XueShengLinShi();
HSSFCell yuanxi = hssfRow.getCell(0);//获取列数据,0为第一列数据,以此类推
HSSFCell banji = hssfRow.getCell(1);
HSSFCell xuehao = hssfRow.getCell(2);
HSSFCell xingming = hssfRow.getCell(3);
System.out.println(getValue(yuanxi));//获取该单元的数据
System.out.println(getValue(banji));
System.out.println(getValue(xuehao));
System.out.println(getValue(xingming));
try {//此处是获取一列可能存在的数据,可以不用try catch,该单元为空也会正常读取。
HSSFCell mima = hssfRow.getCell(4);
System.out.println(getValue(mima));
} catch (Exception e) {
e.printStackTrace();
// return readXLSX_3(stream);
}
}
}
}
/*
XSSFWorkbook xssfWorkbook = null;
try {
xssfWorkbook = new XSSFWorkbook("2014-2015-1体育.xlsx");
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (null == xssfWorkbook)
System.out.println("File not found!");
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) {
XueShengLinShi x = new XueShengLinShi();
XSSFCell yuanxi = xssfRow.getCell(0);
XSSFCell banji = xssfRow.getCell(1);
XSSFCell xuehao = xssfRow.getCell(2);
XSSFCell xingming = xssfRow.getCell(3);
System.out.println(getValue(yuanxi));
System.out.println(getValue(banji));
System.out.println(getValue(xuehao));
System.out.println(getValue(xingming));
try {
XSSFCell mima = xssfRow.getCell(4);
System.out.println(getValue(mima));
} catch (Exception e) {
e.printStackTrace();
// return readXLSX_3(stream);
}
// xueshengs.add(x);
}
}
}
*/
// KAOSHI_FILE in = new KAOSHI_FILE();
// ReadFile m = new ReadFile();
// m.readFile(in, "KAOTI_ZIP", "化工大学-C语言-教学组", ".5IC");
}
@SuppressWarnings("static-access")
private static String getValue(HSSFCell hssfCell) {
if(null == hssfCell) return "";
// return String.valueOf(hssfCell.getStringCellValue());//不可以直接全部按String读取
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {//boolean类型数据
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {//numeric数据
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());//普通String
}
}
@SuppressWarnings("static-access")
private static String getValue(XSSFCell xssfRow) {
if (xssfRow == null) return "";
// return String.valueOf(xssfRow.getStringCellValue());
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());
}
}}
以上为读取Excel文件代码,请勿直接使用,稍作修改即可,请先引入依赖的jar包。