Java Sheet.getLastRowNum方法代碼示例

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
public static List<String[]> readExcel(InputStream is, int sheetIndex) throws Exception {
	Workbook workbook = WorkbookFactory.create(is);
	Sheet sheet = workbook.getSheetAt(sheetIndex);
	List<String[]> data = new ArrayList<>();
	for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
		Row row = sheet.getRow(i);
		if (row == null) continue;
		int last = row.getLastCellNum();
		String[] rowData = new String[last];
		for (int j = 0; j < last; j++) {
			Cell cell = row.getCell(j);
			rowData[j] = cell == null ? null : getCellString(cell);
		}
		data.add(rowData);
	}
	return data;
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
private void setSheetData(SheetData data, String group) {
	data.setCurrentGroup(group);
	// start from 1
	data.setCurrentIndex(1);
	// get sheet
	Sheet vSheet = getWorkBook().getSheet(group);
	Assert.notNull(vSheet, "Can't get sheet with name: " + group);
	data.setSheet(vSheet);
	// get row number
	int vRowCount = vSheet.getLastRowNum() + 1;
	data.setRowCount(vRowCount);
	// get first row
	Row vRow = vSheet.getRow(0);
	Assert.notNull(vRow, "Invalid format: first row must be title");
	// get column number
	int vColumnCount = vRow.getLastCellNum();
	String[] vTitles = new String[vColumnCount];
	// read titles
	for (int i = 0; i < vColumnCount; ++i) {
		Cell vCell = vRow.getCell(i);
		vTitles[i] = vCell.getStringCellValue();
	}
	data.setTitles(vTitles);
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
public static int getEmptyRow(Workbook wb, int sheetIndex, String cellRef) {
	final Sheet sheet = wb.getSheetAt(sheetIndex);
	final CellReference cellReference = new CellReference(cellRef); // һ����A1
	boolean flag = false;
	for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
		final Row r = sheet.getRow(i);
		if (r == null) {
			// ����ǿ��У���û���κ����ݡ���ʽ����ֱ�Ӱ������µ����������ƶ�
			sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
			continue;
		}
		flag = false;
		for (final Cell c : r) {
			if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
				flag = true;
				break;
			}
		}
		if (flag) {
			i++;
			continue;
		} else {// ����ǿհ��У�������û�����ݣ�������һ����ʽ��
			if (i == sheet.getLastRowNum())// ����������һ�У�ֱ�ӽ���һ��remove��
				sheet.removeRow(r);
			else// �����û�����һ�У�������������һ��
				sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
		}
	}
	return sheet.getLastRowNum() + 1;
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
private int getLastRow(Sheet sheet) throws SheetParsingException {
	int lastRowIndex = -1;
	if (sheet.getPhysicalNumberOfRows() > 0) { // getLastRowNum() actually returns an index, not a row number
		lastRowIndex = sheet.getLastRowNum();

		// now, start at end of spreadsheet and work our way backwards until we find a
		// row having data
		for (; lastRowIndex >= 0; lastRowIndex--) {
			Row row = sheet.getRow(lastRowIndex);
			if (row != null) {
				Cell cell = row.getCell(0);
				if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
					break;
			}
		}
		return lastRowIndex;
	} else
		throw new SheetParsingException(sheet.getSheetName() + " is empty");
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
private BaudData generateBaudEntries(File xls) throws Exception {
    BaudData baudData = new BaudData();
    Set<BaudEntryDay> baudEntries = new TreeSet<>();
    baudData.setBaudEntries(baudEntries);

    FileInputStream fis = new FileInputStream(xls);

    Workbook wb = new HSSFWorkbook(fis);
    Sheet sheet = wb.getSheetAt(0);

    String name = sheet.getRow(1).getCell(0).getStringCellValue();
    baudData.setFundId(name);

    for(int i = 4; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);

        String date = row.getCell(0).getStringCellValue();

        LocalDate localDate = LocalDate.parse(date, DateTimeFormatter.ofPattern("dd.MM.yyyy"));
        double totalNav = row.getCell(5).getNumericCellValue();
        double sharePrice = row.getCell(6).getNumericCellValue();

        BaudEntryDay baudEntryDay = new BaudEntryDay();
        baudEntryDay.setDate(localDate);
        baudEntryDay.setTotalNav(totalNav);
        baudEntryDay.setSharePrice(sharePrice);

        baudEntries.add(baudEntryDay);
    }

    wb.close();

    return baudData;
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
/**
 * Iterates through the sheet and determines the cells that are between START_TEST_CASE and END_TEST_CASE comments.
 * Also determines if the data table is to be returned as a Cartesian product of the rows.
 * @param sheet
 * @throws DataProviderException
 */
private void determineTestDataFrame(
                                     Sheet sheet ) throws DataProviderException {

    int rows = sheet.getLastRowNum() + 1;
    int columns = sheet.getRow(sheet.getLastRowNum()).getLastCellNum();
    // iterate throughout the spreadsheet's cells
    for (int x = 0; x < columns; x++) {
        for (int y = 0; y < rows; y++) {
            Row rowValue = sheet.getRow(y);
            if (rowValue != null) {

                if (rowValue.getLastCellNum() > columns) {
                    columns = rowValue.getLastCellNum();
                }

                Cell current = rowValue.getCell(x, Row.CREATE_NULL_AS_BLANK);
                if (hasComments(current)) {
                    if (isStartingCell(current)) {
                        this.startingCell = current;
                    }
                    if (isEndingCell(current)) {
                        this.endingCell = current;
                    }
                    if (isMultiplyCell(current)) {
                        this.isMultipliable = true;
                    }
                }
            }
        }
    }

    if (this.startingCell == null) {
        throw new DataProviderException(ERROR_LOCATING_STARTING_CELL);
    } else if (this.endingCell == null) {
        throw new DataProviderException(ERROR_LOCATING_ENDING_CELL);
    }

    if (this.startingCell.getRowIndex() <= this.endingCell.getRowIndex()) {
        if (this.startingCell.getColumnIndex() <= this.endingCell.getColumnIndex()) {
            return;
        }
    }

    throw new DataProviderException(WRONG_ORDER);
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
private void processSpreadsheetUpload(Workbook wb, List<Map<QName,String>> users)
    throws IOException
{
    if (wb.getNumberOfSheets() > 1)
    {
        logger.info("Uploaded Excel file has " + wb.getNumberOfSheets() + 
                " sheets, ignoring  all except the first one"); 
    }
    
    int firstRow = 0;
    Sheet s = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    
    String[][] data = new String[s.getLastRowNum()+1][];
                                 
    // If there is a heading freezepane row, skip it
    PaneInformation pane = s.getPaneInformation();
    if (pane != null && pane.isFreezePane() && pane.getHorizontalSplitTopRow() > 0)
    {
        firstRow = pane.getHorizontalSplitTopRow();
        logger.debug("Skipping excel freeze header of " + firstRow + " rows");
    }
    
    // Process each row in turn, getting columns up to our limit
    for (int row=firstRow; row <= s.getLastRowNum(); row++)
    {
        Row r = s.getRow(row);
        if (r != null)
        {
            String[] d = new String[COLUMNS.length];
            for (int cn=0; cn<COLUMNS.length; cn++)
            {
                Cell cell = r.getCell(cn);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                {
                    d[cn] = df.formatCellValue(cell);
                }
            }
            data[row] = d;
        }
    }
    
    // Handle the contents
    processSpreadsheetUpload(data, users);
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
public void KeywordDrivenLoginTest() throws Exception {
	
	String propertiesFilePath = home.getAbsolutePath()+"/src/test/resources/TestData/MercuryData.properties";
	String excelFilePath = home.getAbsolutePath()+"/src/test/resources/TestData/MercuryData.xlsx";
	
	//Get chrome driver
	WebDriver driver = DriverHolder.getDriver("WINDOWS","CHROME","64");
	
	System.out.println("DEBUG----LOADING PROPERTIES----DEBUG");
	Properties objectProperties = ReadObjectFile.getObjectData(propertiesFilePath);
	WebOperation webOperation = new WebOperation(driver);
	
	//Read keyword excel sheet
	Sheet sheet = ReadExcelFile.readExcel(excelFilePath, "MercuryKeyword.xlsx", "TestSheet");
	//Get row count in excel sheet
	System.out.println("DEBUG----GETTING ROW COUNT----DEBUG");
	System.out.println("DEBUG----" + sheet + "----DEBUG");
	int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();
	System.out.println("row count: " + rowCount);
	//Loop through rows to read data
	//Start at second row (i=1)
	System.out.println("DEBUG----STARTING LOOP----DEBUG");
	for(int i=1; i< rowCount+1; i++) {
		//
		Row row = sheet.getRow(i);
		
		//If first cell contains data -> Then it is a test case
		if(row.getCell(0) == null) {
			System.out.println("DEBUG--- row count: " + row.getRowNum() + "----DEBUG");
			//Print test case information
			System.out.println(row.getCell(1) + " " +
							   row.getCell(2) + " " +
							   row.getCell(3) + " " +
	                           row.getCell(4) + ": 4th cell ");
			
			String objectName;
			String objectType;
			String objectValue;
			
			if (row.getCell(2) == null) {
				objectName = "";
			} else {
				objectName = row.getCell(2).toString();
			}
			if (row.getCell(3) == null) {
				objectType = "";
			} else {
				objectType = row.getCell(3).toString();
			}
			if (row.getCell(4) == null) {
				objectValue = "";
			} else {
				objectValue = row.getCell(4).toString();
			}
			
			//Call action to perform an event
			webOperation.action(objectProperties,
								row.getCell(1).toString(), 
								objectName, 
								objectType, 
								objectValue);
			
		}
		else {
				//Print the new testcase name when it started
                System.out.println("New Testcase->"+row.getCell(0).toString() +" Started");
				
		}
	}
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入方法依賴的package包/類
public static void test(){
    File excelFile = new File("Excel2Xml/excel.xlsx");

    Workbook workbook = ExcelUtils.workbook(excelFile);

    Sheet sheet = workbook.getSheetAt(0);

    int totalRow = sheet.getLastRowNum();

    // lua文件
    File xml = new File(excelFile.getParentFile(), "string.xml");
    File xmlCn = new File(excelFile.getParentFile(), "string-cn.xml");
    deleteExists(xml);
    deleteExists(xmlCn);

    //<?xml version="1.0" encoding="utf-8"?>
    //<resources>
    //  <string name="inviting_you_to_a_video_call">Inviting you to a video call…</string>
    //</resources>

    try (BufferedWriter out = new BufferedWriter(new FileWriter(xml));
         BufferedWriter outCn = new BufferedWriter(new FileWriter(xmlCn))){

        appendHeader(out);
        appendHeader(outCn);

        // 迭代每一行
        Row row;
        for (int i = 1; i <= totalRow; i++) {
            // 獲取每一行
            row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            Cell cell1 = row.getCell(0);
            Cell cell2 = row.getCell(1);

            appendItem(out, cell1, cell2);
            appendItemCn(outCn, cell1, cell2);
        }

        appendFooter(out);
        appendFooter(outCn);
    } catch (IOException e) {
        throw new RuntimeException("文件導出失敗。");
    }
    System.out.println("導出完成");
}