how to write values obtained using List and Iterator in a excel file using apache poi and selenium webdriver

Question

Hi I need to store a list of values which I obtained from a webpage using List and Iterator using selenium webdriver, I want to store/write those values in a excel sheet Link of the website:https://www.zigwheels.com/used-car I have obtained a list of values under popular car models and I need to store it in a excel sheet using apache poi

Coding starts here: WebElement li= driver.findElement(By.xpath("//span[text()='Brand and Model']//parent::div//followingsibling::div//child::div[4]/ul"));

    List<WebElement> alloptions=li.findElements(By.tagName("li"));   
    Iterator<WebElement> itr=alloptions.iterator();
    while(itr.hasNext()) {
        WebElement lii=itr.next();
        String list=lii.getText();
        System.out.println(list);

Gaj Julije Gaj Julije · Accepted Answer · 2021-03-01T13:28:24

Create file, than sheet, then rows, and fill cells in row:

public void CreateNew_Make_BeckupFile() throws IOException
    {
String filePath = "filepath.xls";
       
        File f = new File(filePath); 
        if (!(f.exists() && f.isFile())) 
        {
                
            Workbook workbook = new XSSFWorkbook(); // new HSSFWorkbook() for generating `.xls` file

            /*
             * CreationHelper helps us create instances of various things like DataFormat,
             * Hyperlink, RichTextString etc, in a format (HSSF, XSSF) independent way
             */
            CreationHelper createHelper = workbook.getCreationHelper();

            for (int p = 0; p < listaTimova.size(); p++) {
                // Create a Sheet
                // Sheet sheet = workbook.createSheet("Employee");
                SheetCreation (workbook, p);
            }
            // Write the output to a file
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            // Closing the workbook
            workbook.close();
            outputStream.close();       
        } 

 public void SheetCreation (Workbook workbook, int teamNumber)
        {
            CreationHelper createHelper = workbook.getCreationHelper();
            
            Sheet sheet = workbook.createSheet("Sheet Name");
            
            // Create a Font for styling header cells
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 14);
            headerFont.setColor(IndexedColors.BLUE.getIndex());
    
            // Create a CellStyle with the font
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);
    
            // Create a Row
            Row headerRow = sheet.createRow(0);
    
            // Create cells
            for (int i = 0; i < columns.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns[i]);
                cell.setCellStyle(headerCellStyle);
            }
            
            headerFont.setColor(IndexedColors.BLACK.getIndex());
            headerCellStyle.setFont(headerFont);
    
            for (int u = 0; u < listaTimova.get(teamNumber).listaIgracaTima.size(); u++)
            {   
                Row headerRow2 = sheet.createRow(u + 1);
                **RowCreation(headerRow2, teamNumber, u, 0);**      
            }
            
            for(int i = 0; i < columns.length; i++) 
            {
                sheet.autoSizeColumn(i);
            }
        }
    
    
    **public void RowCreation(Row row, int teamNumber, int playerNumber, int cellNumber)** 
        {
            
            row.createCell(cellNumber, CellType.STRING).setCellValue("Any value for cell");
            row.createCell(cellNumber+1, CellType.STRING).setCellValue("next cell in row value...");
            }

how to write values obtained using List and Iterator in a excel file using apache poi and selenium webdriver

1 Answers