java poi 6个数据类型cell_type_boolean,poi读取excel兼容(200三、2007)各类数据类型

package com.excel.poi;

import java.io.File;

import java.io.FileInputStream;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

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 ReadExcelPoi {

/**

*

* @param fileName 文件路径

* @param flag 是2003仍是2007 true：2003，false：2007

* @throws Exception

*/

public static void read(String fileName,boolean flag) throws Exception {

Workbook wb = null;

if(flag){//2003

File f = new File(fileName);

FileInputStream is = new FileInputStream(f);

POIFSFileSystem fs = new POIFSFileSystem(is);

wb = new HSSFWorkbook(fs);

is.close();

}else{//2007

wb = new XSSFWorkbook(fileName);

}

read(wb);

}

/**

*

* @param is 输入流

* @param flag 是2003仍是2007 true：2003，false：2007

* @throws Exception

*/

public static void read(InputStream is,boolean flag) throws Exception {

Workbook wb = null;

if(flag){//2003

wb = new HSSFWorkbook(is);

}else{//2007

wb = new XSSFWorkbook(is);

}

read(wb);

}

/**

* 具体读取Excel

* @param wb

* @throws Exception

*/

public static List>> read(Workbook wb) throws Exception {

List>> dataList= new ArrayList>>();

for (int k = 0; k < wb.getNumberOfSheets(); k++) {

List> sheetList=new ArrayList>();

//sheet

Sheet sheet = wb.getSheetAt(k);

int rows = sheet.getPhysicalNumberOfRows();

for (int r = 0; r < rows; r++) {

Map map=new HashMap();

// 定义 row

Row row = sheet.getRow(r);

if (row != null) {

int cells = row.getPhysicalNumberOfCells();

for (short c = 0; c < cells; c++) {

Cell cell = row.getCell(c);

if (cell != null) {

String value = null;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_FORMULA:

//公式类型

value =cell.getCellFormula();

break;

case Cell.CELL_TYPE_NUMERIC:

if(HSSFDateUtil.isCellDateFormatted(cell)){

//时间类型

value =""+cell.getDateCellValue();

}else{

//数字类型

value = String.valueOf(cell.getNumericCellValue());

Pattern pattern = Pattern.compile("E");

Matcher matcher = pattern.matcher(value);

if(matcher.find()){

int mulriple = Integer.parseInt(value.substring(matcher.end(),value.length()));

value = value.substring(0,matcher.start());

String [] values = value.split("\\.");

if(values != null && values.length > 1){

String benginStr = values[0];

String middStr = values[1].substring(0,mulriple);

String endStr = values[1].substring(mulriple);

String pointStr = "";

if(endStr != null && !"".equals(endStr.trim())){

pointStr = ".";

}

value = benginStr+middStr+pointStr+endStr;

}

}

else{

value = ""+cell.getNumericCellValue();

}

}

break;

case Cell.CELL_TYPE_STRING:

//字符类型

value = cell.getStringCellValue();

break;

case Cell.CELL_TYPE_BOOLEAN:

//BOOLEAN类型

value =""+cell.getBooleanCellValue();

cell.getDateCellValue();

break;

default:

}

System.out.println(value);

map.put("data"+(r+1)+(c+1), value);

sheetList.add(map);

}

}

}

}

dataList.add(sheetList);

}

return dataList;

}

/**

* @param args

* @throws Exception

*/

public static void main(String[] args) throws Exception {

// TODO Auto-generated method stub

String filePath="E:\\_channelInfo.xls";

File f = new File(filePath);

String suffix=filePath.substring(filePath.lastIndexOf("."));

FileInputStream is = new FileInputStream(f);

System.out.println(f.getName());

if(suffix.contains(".xlsx")){

//2003

read(is,false);

}else{

//2007以上

read(is,true);

}

}

}