Java利用JXL&POI读取xls和xlsx
Java利用JXL&POI读取xls和xlsx
?
POM?dependencies:
import java.io.File;/** * <b>Description</b>: * <p> * <b>Features or change log:</b> * <ol> * <li>2013-7-13 下午3:59:42, jervalj, C001:</li> * <li></li> * </ol> */public class ReadExcelUtil { public static List<List<String>> parseXlsByJxl(File excelFile) { List<List<String>> items = new ArrayList<List<String>>(); Workbook wb = null; try { wb = Workbook.getWorkbook(excelFile); } catch (Exception e) { e.printStackTrace(); } if (wb != null && wb.getNumberOfSheets() > 0) { try { // get first sheet object Sheet sheet = wb.getSheet(0); // get current Workbook rows int rowNum = sheet.getRows(); List<String> item = null; for (int i = 0;i < rowNum;i++) { // get current row cells Cell[] cells = sheet.getRow(i); if (cells != null && cells.length > 0) { item = new ArrayList<String>(); for (Cell cell:cells) { item.add(cell.getContents()); } items.add(item); } else { throw new RuntimeException("No cells found in excel ."); } } } catch (Exception e) { throw new RuntimeException(e); } finally { if (null != wb) { // close Workbook wb.close(); } } } else { throw new RuntimeException("Please select a valided excel file !"); } return items; } public static List<List<String>> parseXlsByPoi(File file) { HSSFWorkbook wb = null; try { wb = new HSSFWorkbook(new FileInputStream(file)); } catch (Exception e) { e.printStackTrace(); } List<List<String>> items = new ArrayList<List<String>>(); if (wb != null && wb.getNumberOfSheets() > 0) { try { // get first sheet object HSSFSheet sheet = wb.getSheetAt(0); // get current Workbook rows int rowNum = sheet.getLastRowNum(); List<String> item = null; for (int i = 0;i < rowNum;i++) { // get current row cells HSSFRow row = sheet.getRow(i); int cellNum = row.getLastCellNum(); if (cellNum > 0) { item = new ArrayList<String>(); for (int j = 0;j < cellNum;j++) { HSSFCell cell = row.getCell(j); if (null != cell) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 item.add(Double.toString(cell.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_STRING: // 字符串 item.add(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean item.add(Boolean.toString(cell.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 item.add(cell.getCellFormula()); break; case HSSFCell.CELL_TYPE_BLANK: // 空值 item.add(""); break; case HSSFCell.CELL_TYPE_ERROR: // 故障 item.add(""); break; default: item.add(""); break; } } else { item.add(""); } } items.add(item); } else { throw new RuntimeException("No cells found in excel ."); } } } catch (Exception e) { throw new RuntimeException(e); } } else { throw new RuntimeException("Please select a valided excel file !"); } return items; } public static List<List<String>> parseXlsxByPoi(File file) { XSSFWorkbook wb = null; try { wb = new XSSFWorkbook(new FileInputStream(file)); } catch (Exception e) { e.printStackTrace(); } List<List<String>> items = new ArrayList<List<String>>(); if (wb != null && wb.getNumberOfSheets() > 0) { try { // get first sheet object XSSFSheet sheet = wb.getSheetAt(0); // get current Workbook rows int rowNum = sheet.getLastRowNum(); List<String> item = null; for (int i = 0;i < rowNum;i++) { // get current row cells XSSFRow row = sheet.getRow(i); int cellNum = row.getLastCellNum(); if (cellNum > 0) { item = new ArrayList<String>(); for (int j = 0;j < cellNum;j++) { XSSFCell cell = row.getCell(j); if (null != cell) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: // 数字 item.add(Double.toString(cell.getNumericCellValue())); break; case XSSFCell.CELL_TYPE_STRING: // 字符串 item.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean item.add(Boolean.toString(cell.getBooleanCellValue())); break; case XSSFCell.CELL_TYPE_FORMULA: // 公式 item.add(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_BLANK: // 空值 item.add(""); break; case XSSFCell.CELL_TYPE_ERROR: // 故障 item.add(""); break; default: item.add(""); break; } } else { item.add(""); } } items.add(item); } else { throw new RuntimeException("No cells found in excel ."); } } } catch (Exception e) { throw new RuntimeException(e); } } else { throw new RuntimeException("Please select a valided excel file !"); } return items; } public static List<List<String>> parseXlsXlsxByPoi(File file) { org.apache.poi.ss.usermodel.Workbook wb = null; try { wb = new XSSFWorkbook(new FileInputStream(file)); } catch (Exception e) { try { wb = new HSSFWorkbook(new FileInputStream(file)); } catch (Exception e2) { throw new RuntimeException("Unknow File Type!!!"); } } List<List<String>> items = new ArrayList<List<String>>(); if (wb != null && wb.getNumberOfSheets() > 0) { try { // get first sheet object org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); // get current Workbook rows int rowNum = sheet.getLastRowNum(); List<String> item = null; for (int i = 0;i < rowNum;i++) { // get current row cells Row row = sheet.getRow(i); int cellNum = row.getLastCellNum(); if (cellNum > 0) { item = new ArrayList<String>(); for (int j = 0;j < cellNum;j++) { org.apache.poi.ss.usermodel.Cell cell = row.getCell(j); if (null != cell) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: // 数字 item.add(Double.toString(cell.getNumericCellValue())); break; case XSSFCell.CELL_TYPE_STRING: // 字符串 item.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean item.add(Boolean.toString(cell.getBooleanCellValue())); break; case XSSFCell.CELL_TYPE_FORMULA: // 公式 item.add(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_BLANK: // 空值 item.add(""); break; case XSSFCell.CELL_TYPE_ERROR: // 故障 item.add(""); break; default: item.add(""); break; } } else { item.add(""); } } items.add(item); } else { throw new RuntimeException("No cells found in excel ."); } } } catch (Exception e) { throw new RuntimeException(e); } } else { throw new RuntimeException("Please select a valided excel file !"); } return items; }}?