Java XSSFSheet.getPhysicalNumberOfRows方法代码示例

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
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;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
public List<String[]> readAllLines(int sheetIndex){
	XSSFSheet sheet = getWorkBook().getSheetAt(sheetIndex);
	
	List<String[]> rows = CollectionFactory.newArrayList();
	for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
		XSSFRow row = sheet.getRow(i);
		if (row != null) {
			String[] rowValues = new String[row.getPhysicalNumberOfCells()];
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				rowValues[j] = (row.getCell(j) != null)? getReader().read(row.getCell(j)) : "";
			}
			rows.add(rowValues);
		}
	}
	return rows;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
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;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
private static JSONArray extractDataFromXls(XSSFWorkbook wb) {
    XSSFSheet sheet = wb.getSheetAt(0);

    int rows = sheet.getPhysicalNumberOfRows();
    int cols = sheet.getRow(0).getPhysicalNumberOfCells();
    int startRow = 1; // Pular cabecalho.

    JsonObjectCreator jsonObjCreator = new FedDepJsonObjectCreator();
    JSONArray jArr = new JSONArray();
    for(int row = startRow; row < rows; row++) {
        XSSFRow xssfRow = sheet.getRow(row);
        if(xssfRow != null) {

            String[] rowData = new String[cols];
            for(int col = 0; col < cols; col++) {
                XSSFCell cell = xssfRow.getCell(col);
                if(cell != null) {
                    String datum;
                    if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
                        datum = cell.getStringCellValue();
                    }
                    else if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                        datum = String.valueOf(cell.getNumericCellValue());
                    }
                    else {
                        System.out.println("A célula: [" + row + "," + col + "] não contém um String e nem um Número.");
                        datum = "";
                    }

                    rowData[col] = datum;
                }
            }

            JSONObject jObj = jsonObjCreator.processLine(rowData);
            jArr.add(jObj);
        }
    }

    return jArr;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
/**
 * 解析导出的视频excel文件
 */
public static void parse(File file) {
	try {
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		XSSFSheet sheet = xwb.getSheetAt(0);

		System.out.println(String.format("准备解析：%s, 表格：%s", file.getAbsolutePath(), sheet.getSheetName()));

		XSSFRow row = null;
		String cell = null;

		// 遍历行(从0开始)
		for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);

			// 忽略空行:真实
			if (row.getFirstCellNum() < 0) {
				continue;
			}

			// 遍历行-列
			for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
				if (row.getCell(j) == null) {
					continue;
				}
				cell = row.getCell(j).toString();
				if (StringUtil.isEmpty(cell)) {
					continue;
				}
				System.out.println(String.format("解析 行 %d 列 %d\t>>\t%s", i + 1, j, cell));
				appendSql(new Vi(cell));
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
private XSSFWorkbook processXlsx(File input, HashMap<String, String> map) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(input));
    int rowindex = 0;
    int columnindex = 0;
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rows = sheet.getPhysicalNumberOfRows();
    for (rowindex = 1; rowindex < rows; rowindex++) {
    	// Row만큼
        XSSFRow row = sheet.getRow(rowindex);
        if (row != null) {
        	// Row가 null이 아니면
            int cells = row.getPhysicalNumberOfCells();
            // 셀 갯수
            for (columnindex = 0; columnindex <= cells; columnindex++) {
            	// 셀 갯수만큼
                XSSFCell cell = row.getCell(columnindex);
                // row에서 셀 얻어오기
                if (cell != null && cell.getCellType() == 0) {
                	// 0 = Cell.CELL_TYPE_NUMERIC
                	// 학번임을 확인
                    String sNum = (String.valueOf((int) cell.getNumericCellValue()));
                    if (sNum != null) {
                    	// 학번이 null이 아니면
                        cell = row.getCell(columnindex + 1);
                        // 오른쪽 셀 얻어오기
                        if (cell.getCellType() == 1) {
                        	// 1 = Cell.CELL_TYPE_STRING
                        	// 학생 이름임을 확인
                            String type = map.get(sNum);
                            // 학번을 통해 잔류 상태 얻어오기
                            if (type != null) {
                            	// 잔류 상태가 null이 아니면
                                cell = row.getCell(columnindex + 2);
                                // 오른쪽 셀 얻어오기
                                cell.setCellValue(type);
                                // 잔류 상태 채우기
                            }
                        }
                    }
                }
            }
        }
    }
    return workbook;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
public static List<Map<String, String>> getExcelReader() {
	
	String[] colName = { "monday", "tuesday", "wednesday", "thursday", "friday", "saturday", "sunday" };
	
	List<Map<String, String>> list = new ArrayList<Map<String, String>>();
	
	try {
		
		FileInputStream fis = new FileInputStream("c:\\wdmFx\\db\\webtoonList1.xlsx");
		
		XSSFWorkbook workbook = new XSSFWorkbook(fis);
		
		// 시트 수 (첫번째에만 존재하므로 0을 준다)
		// 만약 각 시트를 읽기위해서는 FOR문을 한번더 돌려준다
		XSSFSheet sheet = workbook.getSheetAt(0);
		int rows = sheet.getPhysicalNumberOfRows();
		
		Map<String, String> map = null;

		for (int i = 0; i < rows; i++) {

			if (i != 0) {

				XSSFRow row = sheet.getRow(i); // 행을 읽는다

				String value = "";

				// 셀의 갯수를 실제 필드갯수로 지정.
				int cells = colName.length;

				map = new LinkedHashMap<String, String>();

				for (int j = 0; j < cells; j++) {

					XSSFCell cell = row.getCell(j); // 셀값을 읽는다

					if (cell != null) {
						switch (cell.getCellType()) {
						case XSSFCell.CELL_TYPE_FORMULA:
							if (!"".equals(cell.toString())) {
								value = cell.getCellFormula();
							}
							break;
						case XSSFCell.CELL_TYPE_NUMERIC:
							value = cell.getRawValue();
							break;
						case XSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case XSSFCell.CELL_TYPE_BLANK:
							value = "";
							break;
						case XSSFCell.CELL_TYPE_ERROR:
							value = cell.getErrorCellValue() + "";
							break;
						default:
							break;
						}

						map.put(colName[j], value);
					}

				} // end for

				if (map.get(colName[0]) != null)
					list.add(map);
				
			} // end for
			
		}
		
	} catch (IOException e) {
		e.printStackTrace();
	}

	return list;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
public static ArrayList<ArrayList<ArrayList<Object>>>  readExcel2007(File file){
	try{
		ArrayList<ArrayList<ArrayList<Object>>> sheetArray = new ArrayList<ArrayList<ArrayList<Object>>> ();
		XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
		for(int sheetNum = 0;sheetNum < wb.getNumberOfSheets();sheetNum++){
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			XSSFSheet sheet = wb.getSheetAt(sheetNum);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			for(int i = 0 , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if(row == null){
					//����ȡ��Ϊ��ʱ
					if(i != sheet.getPhysicalNumberOfRows()){//�ж��Ƿ������һ��
						rowList.add(colList);
					}
					continue;
				}else{
					rowCount++;
				}
				for( int j = 0 ; j <= row.getLastCellNum() ;j++){
					cell = row.getCell(j);
					if(cell == null ){
						//���õ�Ԫ��Ϊ��
						if(j != row.getLastCellNum()){//�ж��Ƿ��Ǹ��������һ����Ԫ��
							colList.add("");
						}
						continue;
					}
					switch(cell.getCellType()){
					 case XSSFCell.CELL_TYPE_STRING:  
		                    value = cell.getStringCellValue();  
		                    break;  
		                case XSSFCell.CELL_TYPE_NUMERIC:  
		                    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:  
		                    value = Boolean.valueOf(cell.getBooleanCellValue());
		                    break;  
		                case XSSFCell.CELL_TYPE_BLANK:  
		                    value = "";  
		                    break;  
		                default:  
		                    value = cell.toString();  
					}// end switch
					colList.add(value);
				}//end for j
				rowList.add(colList);
			}//end for i
			sheetArray.add(rowList);
		}// end sheetNum
		return sheetArray;
	}catch(Exception e){
		return null;
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
/**
 * 解析Excel-2007 参考：http://kxjhlele.iteye.com/blog/321392
 * 
 */
public static void main(String[] args) {
    String excelPath = "D:/Download/全国汇编（物化）.xlsx";

    try {
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(excelPath));
        XSSFSheet sheet = xwb.getSheetAt(0);

        XSSFRow row = null;
        String cell = null;

        Map<String, String> peOrderMap = new TreeMap<String, String>();
        int porder = 0, eorder = 0;
        // 遍历所有行
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            // 遍历每一行的所有列
            for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                cell = row.getCell(j).toString();
                if (j == 0 && !cell.isEmpty()) { // 试卷序号
                    porder = new Double(Double.parseDouble(cell)).intValue();
                }
                if (j == 2) { // 试卷下试题序号
                    eorder = new Double(Double.parseDouble(cell)).intValue();
                }
                if (j == 3) {// 录制老师姓名
                    peOrderMap.put(porder + "#" + eorder, cell);
                }
                System.out.print(cell + "\t");
            }
            System.out.println("");
        }

        for (Entry<String, String> en : peOrderMap.entrySet()) {
            System.out.println(en.getKey() + " - " + en.getValue());
        }

    } catch (Exception e) {
        e.printStackTrace();
    }

}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
/**
 * 解析考勤Excel表
 * 
 * @param file
 *            考勤文件
 */
public static void parseKaoQinExcel(File file) {
	try {
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		XSSFSheet sheet = xwb.getSheetAt(0);

		System.out.println(String.format("准备解析：%s, 表格：%s",
				file.getAbsolutePath(), sheet.getSheetName()));

		XSSFRow dayRow = null; // 日期-周几
		XSSFRow timeRow = null;// 打卡时间

		String dayCell = null;
		String timeCell = null;

		// 遍历行(从0开始)，从第3行开始
		for (int i = 9; i < sheet.getPhysicalNumberOfRows(); i = i + 2) {
			dayRow = sheet.getRow(i);
			timeRow = sheet.getRow(i + 1);

			// 忽略空行:真实
			if (dayRow.getFirstCellNum() < 0) {
				continue;
			}

			// 遍历行-列
			for (int j = dayRow.getFirstCellNum(); j < dayRow
					.getPhysicalNumberOfCells(); j++) {
				if (dayRow.getCell(j) == null) {
					continue;
				}
				dayCell = dayRow.getCell(j).toString();
				timeCell = timeRow.getCell(j).toString();
				if (StringUtil.isEmpty(dayCell)) {
					continue;
				}
				System.out.println(String.format(
						"解析 行=%d 列=%d\t>>\t日期=%s\t打卡=%s", i + 1, j,
						dayCell, timeCell));
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	}

}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
protected static List<SEPatient> readPatients(XSSFSheet xlSheet)
{
  String property,value,unit;
  List<SEPatient> patients = new ArrayList<SEPatient>();
  try
  {
    int rows = xlSheet.getPhysicalNumberOfRows();		  
    for (int r = 0; r < rows; r++) 
    {
      XSSFRow row = xlSheet.getRow(r);
      if (row == null) 
        continue;
      int cells = row.getPhysicalNumberOfCells();
      if(r==0)
      {// Allocate the number of patients we have
        for(int i=1;i<cells;i++)
          patients.add(new SEPatient());
      }
      property = row.getCell(0).getStringCellValue();
      if(property==null||property.isEmpty())
        continue;
      Log.info("Processing Patient Field : "+property);
      for (int c = 1; c < cells; c++) 
      {
        String cellValue=null;
        XSSFCell cell = row.getCell(c);
        switch(cell.getCellType())
        {
          case XSSFCell.CELL_TYPE_NUMERIC:
            cellValue = Double.toString(cell.getNumericCellValue());
            break;
          case XSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        }
        if(cellValue==null||cellValue.isEmpty())
          continue;
        int split = cellValue.indexOf(" ");
        // Pull The Value
        if(split==-1)
        {
          value=cellValue;
          unit="";
        }
        else
        {
          value = cellValue.substring(0,split);
          unit  = cellValue.substring(split+1);
        }
        if(value.equals("INF"))
          value = "Infinity";
        if(!setProperty(patients.get(c-1),property,value,unit))
        {
          Log.error("Error pulling"+property+" from "+cellValue);
          break;
        }
      }
    }
  }
  catch(Exception ex)
  {
    Log.error("Error reading XLS",ex);
    return null;
  }
  return patients;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
protected static List<SESubstanceCompound> readCompounds(XSSFSheet xlSheet, Map<String,SESubstance> substances)
{
  String property,value,unit;
  SESubstance s;
  SESubstanceCompound compound=null;
  SESubstanceCompoundComponent component=null;		
  List<SESubstanceCompound> compounds = new ArrayList<SESubstanceCompound>();
  try
  {
    int rows = xlSheet.getPhysicalNumberOfRows();		  
    for (int r = 0; r < rows; r++) 
    {
      XSSFRow row = xlSheet.getRow(r);
      if (row == null) 
        continue;
      int cells = row.getPhysicalNumberOfCells();
      if(r==0)
      {// Allocate the number of patients we have
        for(int i=1;i<cells;i++)
          compounds.add(new SESubstanceCompound());
      }
      property = row.getCell(0).getStringCellValue();
      if(property==null||property.isEmpty())
        continue;
      Log.info("Processing Patient Field : "+property);
      if(property.equals("Data Type"))
        continue;// Only one type at this point
      for (int c = 1; c < cells; c++) 
      {
        String cellValue=null;
        XSSFCell cell = row.getCell(c);
        switch(cell.getCellType())
        {
          case XSSFCell.CELL_TYPE_NUMERIC:
            cellValue = Double.toString(cell.getNumericCellValue());
            break;
          case XSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        }
        if(cellValue==null||cellValue.isEmpty())
          continue;
        int split = cellValue.indexOf(" ");
        // Pull The Value
        if(split==-1)
        {
          value=cellValue;
          unit="";
        }
        else
        {
          value = cellValue.substring(0,split);
          unit  = cellValue.substring(split+1);
        }
        compound=compounds.get(c-1);
        if(property.equals("Compound Name"))
        {
          compound.setName(value);
          continue;
        }
        if(property.equals("Component Name"))
        {
          s = substances.get(value);
          component = compound.getComponent(s);
          continue;
        }
        if(!setProperty(component,property,value,unit))
        {
          Log.error("Error setting property");
          break;
        }
      }
    }
  }
  catch(Exception ex)
  {
    Log.error("Error reading XLS",ex);
    return null;
  }
  return compounds;			
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入方法依赖的package包/类
protected static Map<String,SENutrition> readNutrition(XSSFSheet xlSheet)
{
  String property,value,unit;
  SENutrition meal;
  Map<String,SENutrition> map = new HashMap<String,SENutrition>();		
  List<SENutrition> meals = new ArrayList<SENutrition>();
  try
  {
    int rows = xlSheet.getPhysicalNumberOfRows();		  
    for (int r = 0; r < rows; r++) 
    {
      XSSFRow row = xlSheet.getRow(r);
      if (row == null) 
        continue;
      int cells = row.getPhysicalNumberOfCells();
      if(r==0)
      {// Allocate the number of environments we have
        for(int i=1;i<cells;i++)
          meals.add(new SENutrition());
      }
      property = row.getCell(0).getStringCellValue();
      if(property==null||property.isEmpty())
        continue;
      Log.info("Processing Nutrition Field : "+property);
      for (int c = 1; c < cells; c++) 
      {
        String cellValue=null;
        XSSFCell cell = row.getCell(c);
        switch(cell.getCellType())
        {
          case XSSFCell.CELL_TYPE_NUMERIC:
            cellValue = Double.toString(cell.getNumericCellValue());
            break;
          case XSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        }
        if(cellValue==null||cellValue.isEmpty())
          continue;
        int split = cellValue.indexOf(" ");
        // Pull The Value
        if(split==-1)
        {
          value=cellValue;
          unit="";
        }
        else
        {
          value = cellValue.substring(0,split);
          unit  = cellValue.substring(split+1);
        }
        meal = meals.get(c-1);
        if(property.equals("Name"))
        {
          map.put(cellValue, meal);
          continue;
        }					
        if(!setProperty(meal,property,value,unit))
        {
          Log.error("Error pulling"+property+" from "+cellValue);
          break;
        }
      }
    }
  }
  catch(Exception ex)
  {
    Log.error("Error reading XLS",ex);
    return null;
  }		
  return map;
}