Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet#getPhysicalNumberOfRows()

public static ArrayList readXlsx(String path) throws IOException {
    XSSFWorkbook xwb = new XSSFWorkbook(path);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row;
    String[] cell = new String[sheet.getPhysicalNumberOfRows() + 1];
    ArrayList<String> arrayList = new ArrayList<>();
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        cell[i] = "";
        row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            cell[i] += row.getCell(j).toString();
            cell[i] += " | ";
        }
        arrayList.add(cell[i]);
    }
    return arrayList;
}
 

private static List<List<String>> readSheet(XSSFSheet xssfSheet){
	int rows = xssfSheet.getPhysicalNumberOfRows();
	List<List<String>> rowList = new ArrayList<>();
	for(int i=0;i<rows;i++){//遍历每一行
		XSSFRow row = xssfSheet.getRow(i);
		if(row==null){
			continue;
		}
		int cells = row.getPhysicalNumberOfCells();
		List<String> cellList = new ArrayList<>();
		for(int j=0;j<cells;j++){//遍历每一列
			XSSFCell cell = row.getCell(j);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			//new Double("1.0").intValue()
			String cellValue = cell.getStringCellValue();
			cellList.add(cellValue);
		}
		rowList.add(cellList);
	}
	return rowList;
}
 

/**
 * 读取Excel 2007版，xlsx格式
 *
 * @param is
 * @return
 * @throws IOException
 */
private List<List<Object>> read2007Excel(InputStream is) throws IOException {
    List<List<Object>> list = new LinkedList<>();
    // 构造 XSSFWorkbook 对象，strPath 传入文件路径
    XSSFWorkbook xwb = new XSSFWorkbook(is);
    int sheetCount = xwb.getNumberOfSheets();
    for (int n = 0; n < sheetCount; n++) {
        XSSFSheet sheet = xwb.getSheetAt(n);
        Object value;
        XSSFRow row;
        XSSFCell cell;
        int counter = 0;
        for (int i = (startReadPos - 1); counter < sheet.getPhysicalNumberOfRows() - (startReadPos - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                value = getCellValue(cell);
                if (value == null || "".equals(value)) {
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
    }
    return list;
}
 

private int buildMaxColumn(XSSFSheet sheet){
	int rowCount=sheet.getPhysicalNumberOfRows();
	int maxColumnCount=0;
	for(int i=0;i<rowCount;i++){
		XSSFRow row=sheet.getRow(i);
		if(row==null){
			continue;
		}
		int columnCount=row.getPhysicalNumberOfCells();
		if(columnCount>maxColumnCount){
			maxColumnCount=columnCount;
		}
	}
	return maxColumnCount;
}
 

private static List<?> readSheet(XSSFSheet xssfSheet,Map<Integer,String> map,Class<?> objectClass) throws Exception{
	int rows = xssfSheet.getPhysicalNumberOfRows();
	List<Object> rowList = new ArrayList<>();
	for(int i=0;i<rows;i++){//遍历每一行
		Object target = objectClass.newInstance();
		XSSFRow row = xssfSheet.getRow(i);
		if(row==null){
			continue;
		}
		Set<Integer> set = map.keySet();
		for(Integer each:set){
			XSSFCell cell = row.getCell(each);
			if(cell==null){
				continue;
			}
			cell.setCellType(Cell.CELL_TYPE_STRING);
			String fieldName = map.get(each);
			Class<?> fieldType = objectClass.getDeclaredField(fieldName).getType();
			fieldName = fieldName.substring(0,1).toUpperCase()+fieldName.substring(1,fieldName.length());
			Object value = "";
			try{
				if("java.lang.Double".equals(fieldType.getName())){
					value = new Double(cell.toString());
					target.getClass().getDeclaredMethod("set"+fieldName,Double.class).invoke(target, value);
				}else if("java.lang.Integer".equals(fieldType.getName())){
					value = new Double(cell.toString()).intValue();
					target.getClass().getDeclaredMethod("set"+fieldName,Integer.class).invoke(target, value);
				}else if("java.lang.Float".equals(fieldType.getName())){
					value = new Float(cell.toString());
					target.getClass().getDeclaredMethod("set"+fieldName,Float.class).invoke(target, value);
				}else{
					value = cell.toString();
					target.getClass().getDeclaredMethod("set"+fieldName,String.class).invoke(target, value);
				}
			}catch(Exception e){
				//do nothing
			}
		}
		rowList.add(target);
	}
	return rowList;
}
 

/**
 * 读取Office 2007 excel
 */
private List<List<Object>> readExcel2007(File file) throws IOException {
	List<List<Object>> list = new LinkedList<List<Object>>();
	// 构造 XSSFWorkbook 对象，strPath 传入文件路径
	XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
	// 读取第一章表格内容
	XSSFSheet sheet = xwb.getSheetAt(0);
	Object value = null;
	XSSFRow row = null;
	XSSFCell cell = null;
	int counter = 0;
	for (int i = sheet.getFirstRowNum(); counter < sheet
			.getPhysicalNumberOfRows(); i++) {
		row = sheet.getRow(i);
		if (row == null) {
			continue;
		} else {
			counter++;
		}
		List<Object> linked = new LinkedList<Object>();
		for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
			cell = row.getCell(j);
			if (cell == null) {
				continue;
			}
			DecimalFormat df = new DecimalFormat("0");// 格式化 number String
			// 字符
			SimpleDateFormat sdf = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
			DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_STRING:
				System.out.println(i + "行" + j + " 列 is String type");
				value = cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				System.out.println(i + "行" + j
						+ " 列 is Number type ; DateFormt:"
						+ cell.getCellStyle().getDataFormatString());
				if ("@".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("General".equals(cell.getCellStyle()
						.getDataFormatString())) {
					value = nf.format(cell.getNumericCellValue());
				} else {
					value = sdf.format(HSSFDateUtil.getJavaDate(cell
							.getNumericCellValue()));
				}
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				System.out.println(i + "行" + j + " 列 is Boolean type");
				value = cell.getBooleanCellValue();
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				System.out.println(i + "行" + j + " 列 is Blank type");
				value = "";
				break;
			default:
				System.out.println(i + "行" + j + " 列 is default type");
				value = cell.toString();
			}
			if (value == null || "".equals(value)) {
				continue;
			}
			linked.add(value);
		}
		list.add(linked);
	}
	return list;
}