HSSFWorkbook与XSSFWorkbook的区别:
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx
对于不同版本的EXCEL文档要使用不同的工具类,如果使用错了,会提示如下错误信息。
org.apache.poi.openxml4j.exceptions.InvalidOperationException
org.apache.poi.poifs.filesystem.OfficeXmlFileException
依赖jar包如下:
定义用户类来接收读取的数据
public class User {
private String name;
private String nickname;
private Long age;
private String sex;
处理实现类:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelReader {
public static void main(String[] args) throws IOException {
File file=new File("D:/tmp/1.xls");
ExcelReader(file);
}
public static void ExcelReader(File file) throws IOException{
Workbook book=null;
Sheet sheet=null;
Iterator<Row> rowIterator=null;
FileInputStream fis=null;
fis=new FileInputStream(file);
if(file.getName().endsWith("xls")){//根据后缀决定转成何种版本的excel文件
book=new HSSFWorkbook(fis);
}else{
book=new XSSFWorkbook(fis);
}
sheet=book.getSheetAt(0);
rowIterator=sheet.rowIterator();
Row row0=rowIterator.next();
List<User> list=new ArrayList<User>();
while(rowIterator.hasNext()){
Row row=rowIterator.next();
User user=new User();
String[] cols = new String[row.getLastCellNum()-row.getFirstCellNum()];
for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
cols[cellIndex] = getCellValue(row.getCell(cellIndex));
}
user.setName(cols[0]);
user.setNickname(cols[1]);
user.setAge(Long.valueOf(cols[2]));
user.setSex(cols[3]);
list.add(user);
}
System.out.println(list);
}
protected static String getCellValue(Cell cell) {
String value = "";
if(cell != null){
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
long dd = (long) cell.getNumericCellValue();
value = dd + "";
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
case Cell.CELL_TYPE_FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BOOLEAN:// boolean型值
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
value = String.valueOf(cell.getErrorCellValue());
break;
default:
break;
}
}
return value.trim();
}
}
测试excel:
测试结果:
[User [name=张三, nickname=三儿, age=29, sex=男], User [name=李四, nickname=四儿, age=28, sex=女]]