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));}}

?