2003版office excel读取 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; } } 2007版 excel 读取 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)); } }
java POI读取excel 2007/2003
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
上一篇:java对象转json应clone,避免生成json串有问题
下一篇:java.lang.IllegalStateException at org.apache.catalina.connector.ResponseFacade
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Java Excel导入导出
Java的两种方式操作Excel
数据 java excel 导出数据 -
Python使用openpyxl库读取Excel工作表数据实践
Python使用opendpyxl读取Excel工作表实践
Python openpyxl 读取Excel工作表 -
POI - 读取Excel2003、Excel2007或更高级的兼容性问题
我们使用POI中的HSSFWorkbook来读取Excel数据。public void test(File file) throws IOException { InputStream inp =&n
different Documents calling appears supplied