TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

Automation

Program to Read Column from an Excel Sheet

Overview

In this post, we will discuss conditional statements and loops in the Java programming language. Conditional structures The sheet used in the below example:

 

 

Sample code to read an excel column

The sample method to retrieve a column from an excel sheet is shown below. For simplicity, I’m only validating string values.

 

public ArrayList<String> getMyColumn(int colindex,int sheetIndex) throws IOException
    {
        File file = null;
        FileInputStream fis = null ;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        
        ArrayList<String> column = null;
        try {
            file = new File("TDocs.xlsx");
            fis = new FileInputStream(file);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheetAt(sheetIndex);
            Iterator<Row> rowIterator = sheet.iterator();
            column = new ArrayList<String>();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    if(row.getRowNum() > 0){ 
                        if(cell.getColumnIndex() == colindex){
                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                column.add(cell.getStringCellValue());
                            break;
                            }
                        }
                    }
                }
            }
        
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            fis.close();
            }
        return column;
    }

 


Sample main method to pass parameters to the method. I would retrieve the 2nd column in the excel sheet as shown below:

public static void main(String... args) throws IOException
    {
        ArrayList<String> consume = new ArrayList<String>();
        ReadXL readexcel = new ReadXL();
        consume = readexcel.getMyColumn(1, 0);
        System.out.println("**********OutPut************");
        for(String s : consume)
        {
            System.out.println(s);
        }
        
    }

 


Output

The run output of the program below:

**********OutPut************
Intern
Potter
Man

 

Note that we can also use sheet names instead of index. If we want to read from a sheet with name we can use below method.

workbook.getSheet(sheetName);

Related Posts

Automation /

Code Coverage Tools

Automation /

Selenium 4 Project Setup on Ubuntu Linux

Automation /

Testing webpage mobile-friendliness using a tool

Automation /

Error Severity Metrics

Automation /

Automation Environment Setup Verification

‹ Different Types of Locators in Selenium Webdriver› HSSF / XSSF Interaction with Microsoft Excel ( .xls and .xlsx )

Recent Posts

  • Scaler Academy – An Online Learning Platform
  • Difference between PHP and JavaScript?
  • MS Access Data Types
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com