如何将数据写入每个循环实例的新行中的 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 循环的第二个实例，它覆盖数据或不写入数据，我的要求是将数据写入 excelfor 循环的每个新实例的新行，而不是覆盖.. 有可能..?任何帮助将不胜感激.. 提前致谢

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屋！