Poi 导入

packagecom.huishang.utils;

 

importjava.io.File;

importjava.io.FileInputStream;

importjava.io.FileNotFoundException;

importjava.io.IOException;

importjava.text.DecimalFormat;

importjava.text.SimpleDateFormat;

importjava.util.LinkedList;

importjava.util.List;

 

importorg.apache.poi.hssf.usermodel.HSSFCell;

importorg.apache.poi.hssf.usermodel.HSSFDateUtil;

importorg.apache.poi.hssf.usermodel.HSSFRow;

importorg.apache.poi.hssf.usermodel.HSSFSheet;

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;

importorg.apache.poi.xssf.usermodel.XSSFCell;

importorg.apache.poi.xssf.usermodel.XSSFRow;

importorg.apache.poi.xssf.usermodel.XSSFSheet;

importorg.apache.poi.xssf.usermodel.XSSFWorkbook;

 

publicclassImportUtil{

 

publicstaticList<List<Object>>readExcel(Filefile)throwsIOException{

StringfileName=file.getName();

Stringextension=fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);

if("xls".equals(extension)){

returnread2003Excel(file);

}elseif("xlsx".equals(extension)){

returnread2007Excel(file);

}else{

thrownewIOException("不支持的文件类型");

}

}

 

/**

*读取Office2007excel

*/

privatestaticList<List<Object>>read2007Excel(Filefile)throwsIOException{

List<List<Object>>list=newLinkedList<List<Object>>();

//构造XSSFWorkbook对象，strPath传入文件路径

XSSFWorkbookxwb=newXSSFWorkbook(newFileInputStream(file));

//读取第一章表格内容

XSSFSheetsheet=xwb.getSheetAt(0);

Objectvalue=null;

XSSFRowrow=null;

XSSFCellcell=null;

for(inti=sheet.getFirstRowNum();i<=sheet.getPhysicalNumberOfRows();i++){

row=sheet.getRow(i);

if(row==null){

continue;

}

List<Object>linked=newLinkedList<Object>();

for(intj=row.getFirstCellNum();j<=row.getLastCellNum();j++){

cell=row.getCell(j);

if(cell==null){

continue;

}

DecimalFormatdf=newDecimalFormat("0");//格式化numberString

//字符

SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");//格式化日期字符串

DecimalFormatnf=newDecimalFormat("0");//格式化数字

switch(cell.getCellType()){

caseXSSFCell.CELL_TYPE_STRING:

//System.out.println(i+"行"+j+"列isStringtype");

value=cell.getStringCellValue();

break;

caseXSSFCell.CELL_TYPE_NUMERIC:

//System.out.println(i+"行"+j+"列isNumbertype;

//DateFormt:"+cell.getCellStyle().getDataFormatString());

if("@".equals(cell.getCellStyle().getDataFormatString())){

value=df.format(cell.getNumericCellValue());

}elseif("General".equals(cell.getCellStyle().getDataFormatString())){

value=nf.format(cell.getNumericCellValue());

}else{

value=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));

}

break;

caseXSSFCell.CELL_TYPE_BOOLEAN:

//System.out.println(i+"行"+j+"列isBooleantype");

value=cell.getBooleanCellValue();

break;

caseXSSFCell.CELL_TYPE_BLANK:

//System.out.println(i+"行"+j+"列isBlanktype");

value="";

break;

default:

//System.out.println(i+"行"+j+"列isdefaulttype");

value=cell.toString();

}

if(value==null||"".equals(value)){

continue;

}

linked.add(value);

}

list.add(linked);

}

returnlist;

}

 

/**

*读取office2003excel

*

*@throwsIOException

*@throwsFileNotFoundException

*/

privatestaticList<List<Object>>read2003Excel(Filefile)throwsIOException{

List<List<Object>>list=newLinkedList<List<Object>>();

HSSFWorkbookhwb=newHSSFWorkbook(newFileInputStream(file));

HSSFSheetsheet=hwb.getSheetAt(0);

Objectvalue=null;

HSSFRowrow=null;

HSSFCellcell=null;

for(inti=sheet.getFirstRowNum();i<=sheet.getPhysicalNumberOfRows();i++){

row=sheet.getRow(i);

if(row==null){

continue;

}

List<Object>linked=newLinkedList<Object>();

for(intj=row.getFirstCellNum();j<=row.getLastCellNum();j++){

cell=row.getCell(j);

if(cell==null){

continue;

}

DecimalFormatdf=newDecimalFormat("0");//格式化numberString

//字符

SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");//格式化日期字符串

DecimalFormatnf=newDecimalFormat("0");//格式化数字

switch(cell.getCellType()){

caseXSSFCell.CELL_TYPE_STRING:

//System.out.println(i+"行"+j+"列isStringtype");

value=cell.getStringCellValue();

break;

caseXSSFCell.CELL_TYPE_NUMERIC:

//System.out.println(i+"行"+j+"列isNumbertype;

//DateFormt:"+cell.getCellStyle().getDataFormatString());

if("@".equals(cell.getCellStyle().getDataFormatString())){

value=df.format(cell.getNumericCellValue());

}elseif("General".equals(cell.getCellStyle().getDataFormatString())){

value=nf.format(cell.getNumericCellValue());

}else{

value=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));

}

break;

caseXSSFCell.CELL_TYPE_BOOLEAN:

//System.out.println(i+"行"+j+"列isBooleantype");

value=cell.getBooleanCellValue();

break;

caseXSSFCell.CELL_TYPE_BLANK:

//System.out.println(i+"行"+j+"列isBlanktype");

value="";

break;

default:

//System.out.println(i+"行"+j+"列isdefaulttype");

value=cell.toString();

}

if(value==null||"".equals(value)){

continue;

}

linked.add(value);

}

list.add(linked);

}

returnlist;

}

}