Java Program to perform excel operations

-

In this article we will learn about Java program to perform excel operations. For QA, it is important to implement data provider using excel sheet in their automation framework and hence this article will come as a handy guide for the QA who are learning framework creation from scratch. we will try to cover all the excel methods and operations here.

Java program to perform excel operations

Firstly, you need to import POI jars in your workspace. then copy paste below code in your framework in a package called util.

package util;

import java.io.FileInputStream;

public class ExcelRead {

public static String filename = "" ;
private XSSFSheet worksheet = null;
private XSSFRow sheetRow = null;
public String path;
public FileInputStream fileInput = null;
public FileOutputStream fileOutput = null;
private XSSFWorkbook workbook = null;
private XSSFCell cell = null;

public ExcelRead(String path) {          // Constructor declared

    this.path =path;
    try {
        fileInput = new FileInpuutStream(path);
        workbook = new XSSFWorkbook(fileInput);
        worksheet = workbook.getSheetAt(0);
        fileInput.close();
        }
            catch(Exception e) {
                e.printStackTrace();
        }
}

// return number of rows
public int fetchRowCount(String sheetName) {
    int index = workbook.getSheetIndex(sheetName);
    if(index == -1)
        return 0;
    else {
        worksheet = workbook.getSheetAt(index);
        int number = worksheet.getLastRowNum() + 1;
        return number;
        }
    }

//return true if cell is updated
public boolean setCellValue(String sheetName, String colName, int rowNum, String data) {
    try {
        fileInput = new FileInputStream(path);
        workbook = new XSSFWorkbook(fileInput);
        
        if(rowNum <=0) 
            return false;

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

        workSheet = workBook.getSheetAt(index);

        sheetRow = workSheet.getRow(0);
        for(int i = 0;i< sheetRow.getLastCellNum(); i++ ) { 
            if(sheetRow.getCell(i).getStringCellValue().trim().equals(colName))
                colNum = i;
}

if(colNum ==-1)
    return false;

workSheet.autoSizeColumn(colNum);
sheetRow = workSheet.getRow(rowNum-1);
if(sheetRow == null)
    sheetRow = workSheet.createRow(rowNum - 1);

cell = sheetRow.getCell(colNum);
if(cell == null)
    cell = sheetRow.createCell(colNum);
}

//check whether work sheet exists or not
public boolean isSheetPresent(String sheetName) {
int index = workbook.getSheetIndex(sheetName);
if(index = = -1)
{
    index = workbook.getSheetIndex(sheetName.toUpperCase());
    if(index = = -1)
        return false;
    else
        return true;
    }
        else
            return true;
    }





Comments

Post a Comment

Popular posts from this blog

Azure Tutorials Series - Azure Networking

-
Image
In this article, we will learn about Azure networking concepts such as architecture, microservices, virtual networks, VPNs, subnet, VPN gateways and most importantly Network Security Group(NSG). All these terms are to be understood if you appearing for AZ900 Microsoft Azure Fundamentals exam. Azure Networking In previous articles we have learnt about Micro- services and how they work, these are alos loosely coupled architecture. Some of the features includes Independent components interact with each other with the help of queues Can be updated and deployed independently Highly scalable Supports Asynchronous messaging N-tier Architecture Supports loosely coupled architecture Application divided into multiple tiers such that higher tier can access lower tier but not vice versa Tiers are re-usable and can be updated or replaced easily without any hassle. Three-tier in general Web Tier(front end) Application tier(back end) Data tier(Storage) Virtual Network Isolated network shared by few g
Read more

Coforge Interview Questions | Automation Testing profile

-
Image
In this article we will cover recent Coforge Interview questions asked for testing profile.  Date of Interview - 19th December 2020 Mode of Interview - Web conference through TEAMS Interviewer - Rahul Sharma Round - L1 Coforge Interview Questions | Automation testing profile Round L1 Tell me about your project I have been working as a QA for XYZ company from 5.4 years, during this tenure,  I have learnt about Manual testing, Selenium, Database testing, TestNg, framework designing and API testing. What is a webdriver? A webdriver is a an automation framework that allows executing our tests in different browsers. It also supports headless execution using HtmlUnit driver. What are hooks? @Before and @After are called Hooks, they are used before and after any test scenario. How would you like your automation framework to be designed? How can you use data properties in Test NG framework? Explain Access specifiers? Access modifiers define the visibility of the class. There are four different
Read more

Testing in CI/CD

-
If you are part of testing fraternity you would have heard CI/CD numerous times by now. It's the talk of the software testing town. In this article, we will cover the basics of CI/CD, what actually it is and how it is implemented in software testing. What is continuous integration? In simple terms, development teams can integrate their code into a shared repository. This helps in maintaining code quality and identifying potential issues with the local version of the code at an early stage. What is continuous delivery? Everything that is being continuously merged by the development team, is continuously deployed to the live environment. Continuous delivery is often coined as ‘Continuous Deployment’ as well. Since most developers work in parallel, continuously integrating their code into one repository would mean that the master branch is continuously updated with new features. In order to ensure that there is no compromise in the code quality with so many changes
Read more