java excel导出 poi 后缀名_java使用POI实现excel文件的读取，兼容后缀名xls和xlsx

importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;importorg.apache.log4j.Logger;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.springframework.web.multipart.MultipartFile;/*** excel读写工具类*/

public classPOIUtil {private static Logger logger = Logger.getLogger(POIUtil.class);private final static String xls = "xls";private final static String xlsx = "xlsx";/*** 读入excel文件，解析后返回

*@paramfile

*@throwsIOException*/

public static List readExcel(MultipartFile file) throwsIOException{//检查文件

checkFile(file);//获得Workbook工作薄对象

Workbook workbook =getWorkBook(file);//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回

List list = new ArrayList();if(workbook != null){for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){//获得当前sheet工作表

Sheet sheet =workbook.getSheetAt(sheetNum);if(sheet == null){continue;

}//获得当前sheet的开始行

int firstRowNum =sheet.getFirstRowNum();//获得当前sheet的结束行

int lastRowNum =sheet.getLastRowNum();//循环除了第一行的所有行

for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){//获得当前行

Row row =sheet.getRow(rowNum);if(row == null){continue;

}//获得当前行的开始列

int firstCellNum =row.getFirstCellNum();//获得当前行的列数

int lastCellNum =row.getPhysicalNumberOfCells();

String[] cells= newString[row.getPhysicalNumberOfCells()];//循环当前行

for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){

Cell cell=row.getCell(cellNum);

cells[cellNum]=getCellValue(cell);

}

list.add(cells);

}

}

workbook.close();

}returnlist;

}public static void checkFile(MultipartFile file) throwsIOException{//判断文件是否存在

if(null ==file){

logger.error("文件不存在！");throw new FileNotFoundException("文件不存在！");

}//获得文件名

String fileName =file.getOriginalFilename();//判断文件是否是excel文件

if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){

logger.error(fileName+ "不是excel文件");throw new IOException(fileName + "不是excel文件");

}

}public staticWorkbook getWorkBook(MultipartFile file) {//获得文件名

String fileName =file.getOriginalFilename();//创建Workbook工作薄对象，表示整个excel

Workbook workbook = null;try{//获取excel文件的io流

InputStream is =file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象

if(fileName.endsWith(xls)){//2003

workbook = newHSSFWorkbook(is);

}else if(fileName.endsWith(xlsx)){//2007

workbook = newXSSFWorkbook(is);

}

}catch(IOException e) {

logger.info(e.getMessage());

}returnworkbook;

}public staticString getCellValue(Cell cell){

String cellValue= "";if(cell == null){returncellValue;

}//把数字当成String来读，避免出现1读成1.0的情况

if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC){

cell.setCellType(Cell.CELL_TYPE_STRING);

}//判断数据的类型

switch(cell.getCellType()){case Cell.CELL_TYPE_NUMERIC: //数字

cellValue =String.valueOf(cell.getNumericCellValue());break;case Cell.CELL_TYPE_STRING: //字符串

cellValue =String.valueOf(cell.getStringCellValue());break;case Cell.CELL_TYPE_BOOLEAN: //Boolean

cellValue =String.valueOf(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_FORMULA: //公式

cellValue =String.valueOf(cell.getCellFormula());break;case Cell.CELL_TYPE_BLANK: //空值

cellValue = "";break;case Cell.CELL_TYPE_ERROR: //故障

cellValue = "非法字符";break;default:

cellValue= "未知类型";break;

}returncellValue;

}

}