最近项目中遇到的excle表格解析的事情,看之前的代码是用jxl解析了,解析较耗时,需要换成POI方式进行解析。
为此自己也写了个简单的测试例子,支持xls,和xlsx格式,未经细琢,仅供参考。
需要导入的jar包( commons-io-2.5.jar 非必需);
package poi;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import model.User;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOUtils;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
public class PoiForExcelTest {
@Test
public void parseExcel(){
File file = new File("D:\\workspace_own\\testExcel.xlsx");
importExcel(file);
}
/**
* 解析Excel
* @param excelFile
* @throws Exception
*/
public void importExcel(File excelFile){
if(excelFile.exists() && excelFile.isFile()){
if("xls".equals(FilenameUtils.getExtension(excelFile.getName()))){
parseXls(excelFile);
}else if("xlsx".equals(FilenameUtils.getExtension(excelFile.getName()))){
parseXlsx(excelFile);
}
}
}
/**
* 解析 xls格式表格
* @param is
*/
public void parseXls(File excelFile){
FileInputStream is = null;
try{
if(excelFile == null){
throw new Exception("参数[excelFile]为空.");
}
is = new FileInputStream(excelFile);
HSSFWorkbook wb=new HSSFWorkbook(is);
int sheetNum=wb.getNumberOfSheets();
System.out.println("Excel页数:" + sheetNum);;
HSSFSheet childSheet = wb.getSheetAt(1);
int rowNum = childSheet.getLastRowNum(); //行数-1
System.out.println("Excel记录行数:" + rowNum);
//j=1 以第二行开始遍历 第一行为标题栏
List<User> list = new ArrayList<User>();
for(int j=1;j<=rowNum;j++){
HSSFRow row = childSheet.getRow(j);
User user = new User(row.getCell(0).toString(),row.getCell(1).toString(),row.getCell(2).toString(),row.getCell(3).toString());
list.add(user);
}
System.out.println("解析完成+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
int i = 1;
for(User u:list){
System.out.println("用户"+i+" "+u.toString());
i++;
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(is != null){
IOUtils.closeQuietly(is);
}
}
}
/**
* 解析 xlsx格式表格
* @param is
*/
public void parseXlsx(File excelFile){
FileInputStream is = null;
try{
if(excelFile == null){
throw new Exception("参数[excelFile]为空.");
}
is = new FileInputStream(excelFile);
XSSFWorkbook wb=new XSSFWorkbook(is);
int sheetNum=wb.getNumberOfSheets();
System.out.println("Excel页数:" + sheetNum);;
XSSFSheet childSheet = wb.getSheetAt(1);
int rowNum = childSheet.getLastRowNum(); //行数-1
System.out.println("Excel记录行数:" + rowNum);
//j=1 以第二行开始遍历 第一行为标题栏
List<User> list = new ArrayList<User>();
for(int j=1;j<=rowNum;j++){
XSSFRow row = childSheet.getRow(j);
User user = new User(row.getCell(0).toString(),row.getCell(1).toString(),row.getCell(2).toString(),row.getCell(3).toString());
list.add(user);
}
System.out.println("解析完成+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
int i = 1;
for(User u:list){
System.out.println("用户"+i+" "+u.toString());
i++;
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(is != null){
IOUtils.closeQuietly(is);
}
}
}
}