POI读取excel适配07和03版本

03版读取import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class ReadExcel03 {/*** 读取excel* @param is* @return* @throws FileNotFoundException* @throws IOException*/public static  List<List<String>> readExcel(InputStream is)throws FileNotFoundException, IOException {// 构造 HSSFWorkbook 对象，传入参数为excel文件的io流HSSFWorkbook wb = new HSSFWorkbook(is);// 读取第一个sheet的内容HSSFSheet sheet = wb.getSheetAt(0);// 获取所有行的迭代对象Iterator<Row> rowIter = sheet.rowIterator();// 获取合并单元格对象的相关信息List<ExcelMergedRegionBean> mergedRegionMapList = new CopyOnWriteArrayList<ExcelMergedRegionBean>(getMergedRegionMapList(sheet));List<List<String>> contentList = new ArrayList<List<String>>();// 迭代所有行while (rowIter.hasNext()) {List<String> cellList = new ArrayList<String>();// 获取每一行Row row = rowIter.next();// 获取该行的列迭代对象Iterator<Cell> cellIter = row.cellIterator();// 迭代该行的每一列while (cellIter.hasNext()) {// 获取该行的每一列Cell cell = cellIter.next();// 获取该单元格的值String content = getActualCellValue(sheet, mergedRegionMapList,cell);cellList.add(content);}contentList.add(cellList);}return contentList;}/*** 获取单元格真实的值* @param sheet* @param mergedRegionMapList* @param myCell* @return*/public static String getActualCellValue(HSSFSheet sheet,List<ExcelMergedRegionBean> mergedRegionMapList, Cell myCell) {Cell actualCell = myCell;// 迭代合并单元格对象，判断myCell该对象是否属于合并单元格for (ExcelMergedRegionBean mb : mergedRegionMapList) {if (myCell.getRowIndex() > mb.getLastRow()) {mergedRegionMapList.remove(mb);}// 判断myCell该对象是否属于合并单元格，如果是的话，则直接退出循环if (myCell.getColumnIndex() <= mb.getLastCell()&& myCell.getColumnIndex() >= mb.getFirstCell()&& myCell.getRowIndex() <= mb.getLastRow()&& myCell.getRowIndex() >= mb.getFirstRow()) {Row row = sheet.getRow(mb.getFirstRow());Cell cell = row.getCell(mb.getFirstCell());actualCell = cell;break;}}// 返回该单元对应的真实值return getCellValue(actualCell);}/*** 处理合并的列* @param sheet* @return*/public static List<ExcelMergedRegionBean> getMergedRegionMapList(HSSFSheet sheet) {List<ExcelMergedRegionBean> mergedRegionMapList = new ArrayList<ExcelMergedRegionBean>();// 获得一个 sheet 中合并单元格的数量int sheetmergerCount = sheet.getNumMergedRegions();// 便利合并单元格for (int i = 0; i < sheetmergerCount; i++) {// 获得合并单元格CellRangeAddress ca = sheet.getMergedRegion(i);// 获得合并单元格的起始行, 结束行, 起始列, 结束列int firstC = ca.getFirstColumn();int lastC = ca.getLastColumn();int firstR = ca.getFirstRow();int lastR = ca.getLastRow();ExcelMergedRegionBean mb = new ExcelMergedRegionBean();mb.setFirstRow(firstR);mb.setLastRow(lastR);mb.setFirstCell(firstC);mb.setLastCell(lastC);mergedRegionMapList.add(mb);}// 排序，便于后面循环删除Collections.sort(mergedRegionMapList);return mergedRegionMapList;}/*** 获得单元格的值* @param cell* @return*/public static String getCellValue(Cell cell) {String cellValue = "";if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {cellValue = rPadZeroUtil(String.valueOf(cell.getNumericCellValue()));} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {cellValue = String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {cellValue = cell.getStringCellValue();} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {cellValue = String.valueOf(cell.getCellFormula());}return cellValue;}public static String rPadZeroUtil(String value) {if (value != null && !"".equals(value)) {if (value.endsWith(".0")) {return value.substring(0, value.indexOf(".0"));}}return value;}
}07版读取import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ReadExcel07 {/*** 读取excel* @param is* @return* @throws FileNotFoundException* @throws IOException*/public static  List<List<String>> readExcel(InputStream is)throws FileNotFoundException, IOException {// 构造 XSSFWorkbook 对象，传入参数为excel文件的io流XSSFWorkbook wb = new XSSFWorkbook(is);// 读取第一个sheet的内容XSSFSheet sheet = wb.getSheetAt(0);// 获取所有行的迭代对象Iterator<Row> rowIter = sheet.rowIterator();// 获取合并单元格对象的相关信息List<ExcelMergedRegionBean> mergedRegionMapList = new CopyOnWriteArrayList<ExcelMergedRegionBean>(getMergedRegionMapList(sheet));List<List<String>> contentList = new ArrayList<List<String>>();// 迭代所有行while (rowIter.hasNext()) {List<String> cellList = new ArrayList<String>();// 获取每一行Row row = rowIter.next();// 获取该行的列迭代对象Iterator<Cell> cellIter = row.cellIterator();// 迭代该行的每一列while (cellIter.hasNext()) {// 获取该行的每一列Cell cell = cellIter.next();// 获取该单元格的值String content = getActualCellValue(sheet, mergedRegionMapList,cell);cellList.add(content);}contentList.add(cellList);}return contentList;}/*** 获取单元格真实的值* @param sheet* @param mergedRegionMapList* @param myCell* @return*/public static String getActualCellValue(XSSFSheet sheet,List<ExcelMergedRegionBean> mergedRegionMapList, Cell myCell) {Cell actualCell = myCell;// 迭代合并单元格对象，判断myCell该对象是否属于合并单元格for (ExcelMergedRegionBean mb : mergedRegionMapList) {if (myCell.getRowIndex() > mb.getLastRow()) {mergedRegionMapList.remove(mb);}// 判断myCell该对象是否属于合并单元格，如果是的话，则直接退出循环if (myCell.getColumnIndex() <= mb.getLastCell()&& myCell.getColumnIndex() >= mb.getFirstCell()&& myCell.getRowIndex() <= mb.getLastRow()&& myCell.getRowIndex() >= mb.getFirstRow()) {Row row = sheet.getRow(mb.getFirstRow());Cell cell = row.getCell(mb.getFirstCell());actualCell = cell;break;}}// 返回该单元对应的真实值return getCellValue(actualCell);}/*** 处理有合并的列* @param sheet* @return*/public static List<ExcelMergedRegionBean> getMergedRegionMapList(XSSFSheet sheet) {List<ExcelMergedRegionBean> mergedRegionMapList = new ArrayList<ExcelMergedRegionBean>();// 获得一个 sheet 中合并单元格的数量int sheetmergerCount = sheet.getNumMergedRegions();// 便利合并单元格for (int i = 0; i < sheetmergerCount; i++) {// 获得合并单元格CellRangeAddress ca = sheet.getMergedRegion(i);// 获得合并单元格的起始行, 结束行, 起始列, 结束列int firstC = ca.getFirstColumn();//第一列int lastC = ca.getLastColumn();//最后一列int firstR = ca.getFirstRow();//第一行int lastR = ca.getLastRow();//第二行ExcelMergedRegionBean mb = new ExcelMergedRegionBean();mb.setFirstRow(firstR);mb.setLastRow(lastR);mb.setFirstCell(firstC);mb.setLastCell(lastC);mergedRegionMapList.add(mb);}// 排序，便于后面循环删除Collections.sort(mergedRegionMapList);return mergedRegionMapList;}/*** 获得单元格的值* @param cell* @return*/public static String getCellValue(Cell cell) {String cellValue = "";if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue());else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue());else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) cellValue = cell.getStringCellValue();else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) cellValue = String.valueOf(cell.getCellFormula());return cellValue;}
}适配器import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;/*** 读取excel适配器* @author Administrator**/
public class ReadExcelAdapter {private ReadExcelAdapter(){}/*** 适配方法:可读取03版本和07版本的excel* @return*/public static List<List<String>> readExcel(String filePath){InputStream is = null;try {is = new FileInputStream(filePath);return ReadExcel03.readExcel(is);} catch (Exception e) {try {is = new FileInputStream(filePath);return ReadExcel07.readExcel(is);} catch (FileNotFoundException e1) {e1.printStackTrace();} catch (IOException e3) {e3.printStackTrace();}}return null;}
}
测试类import java.util.List;public class ReadExcelTest {public static void main(String[] args) throws Exception {long startTime = System.currentTimeMillis();List<List<String>> result =  ReadExcelAdapter.readExcel("C:/test.xlsx");System.out.println(result);System.out.println("use time:"+ (System.currentTimeMillis() - startTime));}
}