文章参考自:
http://www.cnblogs.com/hongten/p/java_poi_excel_xls_xlsx.html
直奔主题:
Student类
--------
public class Student {
private String no;
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
}
Util类
-----
public class Util {
public static String getPostfix(String path){
if(path == null || "".equals(path.trim()))
return "";
if(path.contains("."))
return path.substring(path.lastIndexOf(".") + 1);
return "";
}
}
ReadExcel类
----------
public class ReadExcel {
public List<Student> readExcel(String path) throws IOException {
if (path == null || "".equals(path))
return null;
else {
String postfix = Util.getPostfix(path);
if (!"".equals(postfix)) {
if ("xlsx".equals(postfix) || "xls".equals(postfix))
return readXlsx(path);
}
}
return null;
}
private List<Student> readXlsx(String path) throws IOException {
System.out.println(path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
XSSFDataFormat format = xssfWorkbook.createDataFormat();
XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
cellStyle.setDataFormat(format.getFormat("@"));
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);
no.setCellType(XSSFCell.CELL_TYPE_STRING);
no.setCellStyle(cellStyle);
student.setNo(getValue(no));
student.setName(getValue(name));
list.add(student);
}
}
}
return list;
}
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());
}
}
}
主函数测试
-----
public class Client {
private static final String path = "E:\\test\\";
public static void main(String[] args) throws IOException {
String xls = path+ "test.xls";
String xlsx = path + "test.xlsx";
// read the 2003-2007 excel
List<Student> list = new ReadExcel().readExcel(xls);
if (list != null) {
for (Student student : list) {
System.out.println("No: " + student.getNo() + ", name : " + student.getName());
}
}
System.out.println("======================================");
// read the 2010 excel
List<Student> list1 = new ReadExcel().readExcel(xlsx);
if (list1 != null) {
for (Student student : list1) {
System.out.println("No: " + student.getNo() + ", name : " + student.getName());
}
}
}
}
参考网址上的代码运行结果会使数字较长的字符串输出为指数类型,如11111111 会输出为1.1111111e7 ,即使将Excel中的单元格格式设置为文本也没有用处,后来自己通过搜索解决了这个问题。当然其他例如日期格式我还没试过。