如何在不替换以前的数据的情况下为每个循环实例在新行中将数据写入Excel工作表，即如何为每个循环实例追加行？ [英] how to write data into excel sheet in new row for every loop instance without replacing previous data i.e how to append rows for every loop instance?

问题描述

我正在尝试将数据从Web表写入excel工作表并能够将数据写入excel，但是对于for循环的第二个实例，它会覆盖数据或不写入数据，我的要求是将数据写入excel到for循环的每个新实例都有一个新行，并且不覆盖..是否有可能..？任何帮助将不胜感激..在此先感谢

i am trying to write data from web table to excel sheet and able to write the data to excel , but for second instance of for loop it override the data or do not write data, My requirement is to write data to excel into new row for every new instance of for loop and not to override.. is it possible..? any help would be appreciate.. thanks in advance

数据正在写入excel，但是需要在reader.setCellData

data is writing into excel but need help near reader.setCellData

public class DataScraper {

 public static void main(String[] args) throws InterruptedException {


    WebDriver driver = new ChromeDriver();
    driver.manage().window().maximize();
    driver.manage().timeouts().implicitlyWait(40, TimeUnit.SECONDS);
    driver.get("https://etrain.info/in?PAGE=LIST--RAJ-TRAINS--1");

    //*[@id="lowerdata"]/table/tbody/tr[1]/td/table/tbody/tr[5]/td[1]/a
    //*[@id="lowerdata"]/table/tbody/tr[1]/td/table/tbody/tr[6]/td[1]/a
    //*[@id="lowerdata"]/table/tbody/tr[1]/td/table/tbody/tr[29]/td[1]/a


    String beforeXpath_TrainNoRow = "//td[@id='content']//tr[";
    String afterXpath_TrainNoRow = "]//td[1]";

    //div[@id='sublowerdata']//tr[3]//td[1]
    //div[@id='sublowerdata']//tr[11]//td[1]

    String beforeXpath_No = "//div[@id='sublowerdata']//tr[";
    String afterXpath_No = "]//td[1]";



    //int rowCount = TrainList.size();

    Xls_Reader reader = new Xls_Reader("C:\\Selenium_Automation\\Projects\\DataDriven_FW\\src\\com\\testdata\\TrainSchedule.xlsx");

    if(!reader.isSheetExist("Rajdhani")) {
        reader.addSheet("Rajdhani");
        reader.addColumn("Rajdhani", "IslNo");
        //reader.addColumn("TrainSearch", "TrainName");


    }

    for(int i = 5; i<= 30; i++) {
        String actualXpath_TrainNoRow = beforeXpath_TrainNoRow + i + afterXpath_TrainNoRow ;
        WebElement TrainNo = driver.findElement(By.xpath(actualXpath_TrainNoRow));
        TrainNo.click();
        Thread.sleep(5000);

        List<WebElement> rows = driver.findElements(By.xpath("//table[@id='schtbl']//tr"));
        System.out.println("total rows is "+ rows.size());

        int rowCount = rows.size();

        for(int j = 3; j<= rowCount - 1; j++) {

            String actualXpath_No = beforeXpath_No + j + afterXpath_No ;
            String SrNo = driver.findElement(By.xpath(actualXpath_No)).getText();
            int islNo = Integer.parseInt(SrNo);
            System.out.println(islNo);

            reader.setCellData("Rajdhani", "IslNo", j, SrNo);

        }

        driver.navigate().back();
        driver.navigate().refresh();

    }

    //table[@id='schtbl']//tr -- table rows inner

}

}

来自XlsReader的setCellData方法

public boolean setCellData(String sheetName,String colName,int rowNum, 
String data){
        try{
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);

        if(rowNum<=0)
            return false;

        int index = workbook.getSheetIndex(sheetName);
        int colNum=-1;
        if(index==-1)
            return false;


        sheet = workbook.getSheetAt(index);


        row=sheet.getRow(0);
        for(int i=0;i<row.getLastCellNum();i++){
            //System.out.println(row.getCell(i).getStringCellValue().trim());
            if(row.getCell(i).getStringCellValue().trim().equals(colName))
                colNum=i;
        }
        if(colNum==-1)
            return false;

        sheet.autoSizeColumn(colNum); 
        row = sheet.getRow(rowNum-1);
        if (row == null)
            row = sheet.createRow(rowNum-1);

        cell = row.getCell(colNum); 
        if (cell == null)
            cell = row.createCell(colNum);

        // cell style
        //CellStyle cs = workbook.createCellStyle();
        //cs.setWrapText(true);
        //cell.setCellStyle(cs);
        cell.setCellValue(data);

        fileOut = new FileOutputStream(path);

        workbook.write(fileOut);

        fileOut.close();    

        }
        catch(Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

推荐答案

嘿，不确定你是什么试图在此脚本中实现，但这是更新的脚本。
请在以下代码中更新chrome驱动程序路径和excel文件路径。
仅供参考：xpath可以高效地写入模式，但在本文中并未涉及它们。

Hey Not sure what you are trying to achieve in this script, but here is the updated script. Please update the chrome driver path and excel file path in the below code. FYI: xpath can be written mode efficiently but did not touched them in this post.

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;

public class TrainDeatils {

    public static void main(String[] args) throws InterruptedException, IOException {
        System.setProperty("webdriver.chrome.driver", "XXXchromedriver path goes here XXX");
        WebDriver driver = new ChromeDriver();
        driver.manage().window().maximize();
        driver.manage().timeouts().implicitlyWait(40, TimeUnit.SECONDS);
        driver.get("https://etrain.info/in?PAGE=LIST--RAJ-TRAINS--1");

        String beforeXpath_TrainNoRow = "//td[@id='content']//tr[";
        String afterXpath_TrainNoRow = "]//td[1]";
        String beforeXpath_No = "//div[@id='sublowerdata']//tr[";
        String afterXpath_No = "]//td[1]";

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet spreadsheet = workbook.createSheet( "Rajdhani");
        XSSFRow header;
        header = spreadsheet.createRow(0);
        header.createCell(0).setCellValue("Rajdhani");
        header.createCell(1).setCellValue("IslNo");
        int rowNumber = 1;
        for(int i = 5; i<= 7; i++) {
            Thread.sleep(1000);
            String actualXpath_TrainNoRow = beforeXpath_TrainNoRow + i + afterXpath_TrainNoRow ;
            WebElement TrainNo = driver.findElement(By.xpath(actualXpath_TrainNoRow));
            TrainNo.click();
            Thread.sleep(5000);

            List<WebElement> rows = driver.findElements(By.xpath("//table[@id='schtbl']//tr"));
            System.out.println("total rows is "+ rows.size());

            int rowCount = rows.size();

            for(int j = 3; j<= rowCount - 1; j++) {
                rowNumber = rowNumber+1;
                XSSFRow currentRow = spreadsheet.createRow(rowNumber);
                String actualXpath_No = beforeXpath_No + j + afterXpath_No ;
                String SrNo = driver.findElement(By.xpath(actualXpath_No)).getText();
                int islNo = Integer.parseInt(SrNo);
                System.out.println(islNo);
                currentRow.createCell(0).setCellValue(j);
                currentRow.createCell(1).setCellValue(SrNo);
            }

            driver.navigate().back();
            driver.navigate().refresh();

        }
        FileOutputStream out = new FileOutputStream(new File("XXXexcel file path goes hereXXX\\TrainDetails.xlsx"));

        workbook.write(out);
        out.close();
        driver.close();



    }

}

这篇关于如何在不替换以前的数据的情况下为每个循环实例在新行中将数据写入Excel工作表，即如何为每个循环实例追加行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！