Author: vibssingh

vibssingh

25 Useful TestNG Multiple-Choice Questions | Self-Test Your Knowledge

Last Updated On

HOME

Are you familiar with TestNG and want to test your understanding? This post contains 25 useful TestNG multiple-choice questions (quiz) to self-test your knowledge.

1. What is TestNG?

Select the best answer

  1. A testing framework for java inspired by JUnit
  2. Programming Language
  3. Database
  4. All of the above

Answer

2. Who created TestNG?

Choose one option

  1. Sun microsystems
  2. Cédric Beust
  3. Bell labs
  4. None

Answer

3. A more powerful and easier-to-use framework, TestNG framework came ____ JUnit.

Choose one option

  1. Before
  2. After
  3. Alongwith
  4. None

Answer

4. What is meant by NG in TestNG?

Choose one option

  1. New Generation
  2. Next Generation
  3. Name Generation
  4. No Generation

Answer

5.  What is TRUE about TestNG?

Choose one option

  1. With TestNG, you are in complete control of what is happening in the test cases and how they are executed.
  2. Prior to running test case X, run multiple test cases as a pre-request.
  3. By utilizing easy annotations, grouping, sequencing, and parametrizing, the TestNG framework eliminates the limitations of the older framework.
  4. All of the above

Answer

6.  Unlike Junit, TestNG does not have constraints like ____.

  1. @beforeclass
  2. @afterclass
  3. Both A and B
  4. None of the above

Answer

7. ____ are all supported by TestNG.

  1. A suite before/after
  2. A test before/after
  3. A group of tests before/after
  4. All of the above

Answer

8. In the TestNG framework, test cases can be defined ____ one another.

  1. Dependently on
  2. Independently of
  3. Interdependently on
  4. Intradependently on

Answer

9. TestNG is written in

Choose one option

  1. Java
  2. Kotlin
  3. Python
  4. None

Answer

10. What is/are the feature(s) of TestNG?

Choose one option

  1. Before and After annotations
  2. Dependent methods
  3. Better reporting
  4. All of the above

Answer

11. ____ based files are used to configure TestNG test suites

Choose one option

  1. HTML
  2. CSS
  3. XML
  4. PHP

Answer

12.  The test suites are organized and run using the ____ file.

Choose one option

  1. Testng.html
  2. Testng.xml
  3. Testng.php
  4. Testng.pl

Answer

13. A testng.xml file defines ____, and test groups that are used to create test suites.

Choose one option

  1. Classes
  2. Methods
  3. Packages
  4. All of the above

Answer

14.  ____ files can be used to pass parameters to test methods.

Choose one option

  1. DataProviders
  2. Testng.xml
  3. Both A and B
  4. None of the above

Answer

15. Which of the following is/are the TestNG Annotation(s)?

Choose one option

  1. @BeforeSuite
  2. @AfterSuite
  3. @BeforeTest
  4. All of the above

Answer

16. All test methods in the suite will be executed before the @____ method runs.

Choose one option

  1. BeforeSuite
  2. BeforeClass
  3. BeforeTest
  4. BeforeMethod

Answer

17. A method with ‘@BeforeMethod’ annotation in a class will execute _____

Choose one option

  1. Before the first test method in the class executes
  2. Only once before the execution of the @Test, annotated methods begins
  3. Before each and every test method in the class i.e., the method with @BeforeMethod annotation will execute for every test method in the class

Answer

18. A method with ‘@BeforeTest’ annotation in a class will execute _______

Choose one option

  1. Before every test method in the class
  2. Once before the execution of test methods in the class begins
  3. After every test method in the class

Answer

 19. After all test methods in a suite have been executed, the @____ annotation will run.

  1. AfterSuite
  2. AfterTest
  3. AfterClass
  4. AfterMethod

Answer

20. A TestNG ____ is a piece of code that comes into play during test execution and controls the flow of the test.

Choose one option

  1. Groups
  2. Annotation
  3. Code
  4. Tags

Answer

21. All test methods in that folder will be executed before the @____ annotated method is executed.

Choose one option

  1. BeforeClass
  2. BeforeSuite
  3. BeforeTest
  4. BeforeMethod

Answer

22. Upon completion of all the test methods of the classes in the folder, the @____ method will be executed.

Choose one option

  1. AfterSuite
  2. AfterTest
  3. AfterClass
  4. AfterMethod

Answer

23.  This method is called before the class’s first method is invoked using the @____ annotation.

Choose one option

  1. BeforeSuite
  2. BeforeClass
  3. BeforeTest
  4. BeforeMethod

Answer

24. After all test methods of the current class have been executed, the @____ method will be invoked.

Choose one option

  1. AfterMethod
  2. AfterSuite
  3. AfterClass
  4. AfterTest

Answer

25. Every test method will be run prior to the @____ annotation.

Choose all options

  1. BeforeMethod
  2. BeforeTest
  3. BeforeClass
  4. BeforeSuite

Answer

vibssingh

How to test ToolTip in Selenium

HOME

In this tutorial, we will discuss the ToolTip and how to automate the ToolTip in Selenium.

What is ToolTip?

A ToolTip is a small, informational popup that appears when a user hovers over an element such as a button, image, or link. It provides additional context or information about the element. Tooltips are often implemented using the title attribute in HTML or through JavaScript and CSS for custom tooltips.

Tool Tips are implemented in different ways–

  • The basic implementation is based on HTML’s “title” attribute. getAttribute(title) gets the value of the tooltip.
  • Another tool tip implementation is using Action Class.

This tutorial explains the steps to automate the Custome ToolTip where no title attribute is present.

Let’s understand how one can automate the process of verifying tooltip in Selenium.

To test tooltips in Selenium, you generally follow these steps:

1. Locate the Element with Tooltip: Identify the element that contains the tooltip.
2. Hover Over the Element: Use Actions class to simulate mouse hover event.
3. Capture the Tooltip Text: Retrieve the text from the tooltip element.
4. Validate the Text: Assert that the tooltip text matches the expected value.

Implementation Steps

Locate the Tooltip Element

Identify the element that contains the tooltip. Here, the element has been found using By.id().

 WebElement elementWithTooltip = driver.findElement(By.id("toolTipButton"));

Hover Over the Element

Identify the element that contains the tooltip using Actions class to hover over the element.

Actions actions = new Actions(driver);
actions.moveToElement(elementWithTooltip).perform();

Retrieve Tooltip Text

Capturing the tooltip text typically found in the title attribute. But in this case, there is no title in page source, so I have created an xpath using contains(text()) to find the tooltip text.

WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(5));
WebElement tooltip = wait.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("//*[contains(text(),'You hovered over the Button')]")));

Validate the Text

Comparing the captured text with the expected tooltip text.

  if (expectedTooltipText.equals(actualTooltipText)) {
            System.out.println("Tooltip text is correct!");
        } else {
            System.out.println("Tooltip text is incorrect!");
 }

The complete program is shown below:

package com.example;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.openqa.selenium.interactions.Actions;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;

import java.time.Duration;

public class TooltipTest_Demo {

    static WebDriver driver;
    static String expectedTooltipText = "You hovered over the Button";
    static String actualTooltipText;

    public static void main(String[] args) {

        ChromeOptions options = new ChromeOptions();
        driver = new ChromeDriver(options);
        driver.manage().window().maximize();

        driver.get("https://demoqa.com/tool-tips");

        // Locate the element with the tooltip
        WebElement elementWithTooltip = driver.findElement(By.id("toolTipButton"));

        // Perform hover action using Actions class
        Actions actions = new Actions(driver);
        actions.moveToElement(elementWithTooltip).perform();

        // Wait for the tooltip to be visible
        WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(5));
        WebElement tooltip = wait.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("//*[contains(text(),'You hovered over the Button')]")));

        // Check the tooltip text
        actualTooltipText = tooltip.getText();
        System.out.println("Actual Tooltip Text: " + actualTooltipText);

        if (expectedTooltipText.equals(actualTooltipText)) {
            System.out.println("Tooltip text is correct!");
        } else {
            System.out.println("Tooltip text is incorrect!");
        }

        driver.quit();
    }

}

The output of the above program is

We are using WebDriverWait to wait for the hover text to appear.

Summary

  1. Custom ToolTip can be automated using Action class.
  2. moveToElement(element) of Actions class is used to mouse hover an element.
  3. Build() method of Actions class builds the sequence of user actions into an Action object.
  4. Perform() of Action class executes all the sequence of user actions at once.
vibssingh

How to compare JSON File with JSON Response

HOME

In the below tutorial, we will compare the JSON File structure and data with the JSON Response.

To compare a JSON file with a JSON response using Rest Assured in Java, follow these steps:

1. Read JSON from a file
2. Make a REST API call

3. Read JSON Content
4. Compare the JSON content

Add the below dependencies:-

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.13.2</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>io.rest-assured</groupId>
      <artifactId>rest-assured</artifactId>
      <version>5.4.0</version>
      <scope>test</scope>
    </dependency>

    <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.11.3</version>
    </dependency>
</dependencies>

JSON File

The file is placed in “src/test/resources/expectedUser.json”.

Sample JSON File is shown below:

Let us create a program to compare the JSON File with JSON response.

package com.example;

import io.restassured.RestAssured;
import io.restassured.response.Response;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.junit.Assert;
import org.junit.Test;

import java.io.File;
import java.io.IOException;

public class JsonFileComparison {

    @Test
    public void compareResponse() throws IOException {

        // Path to the JSON file
        File jsonFile = new File("src/test/resources/expectedUser.json");

        // Read JSON content from file
        ObjectMapper objectMapper = new ObjectMapper();
        JsonNode jsonFromFile = objectMapper.readTree(jsonFile);

        // Make an API call
        Response response = RestAssured.given().when().get("https://reqres.in/api/users/3").then().extract().response();
        String expectedResponse = response.getBody().asString();
        System.out.println("Response is : " + expectedResponse);

        // Verify the status code
        response.then().statusCode(200);

        // Read JSON content from response
        JsonNode jsonFromResponse = objectMapper.readTree(expectedResponse);

        Assert.assertEquals(jsonFromFile, jsonFromResponse);

        // Compare JSON content
        if (jsonFromFile.equals(jsonFromResponse)) {
            System.out.println("JSON from file matches JSON from response");
        } else {
            System.out.println("JSON content does not match");
        }
    }
}

The output of the above program is

The ObjectMapper class from the Jackson library is used to parse the JSON content, and the `equals` method is used to compare the two JsonNode objects.


Read JSON from a file:

Using the Jackson library’s ObjectMapper, read the JSON file’s content into a JsonNode object. This object represents the JSON structure.

ObjectMapper objectMapper = new ObjectMapper();
JsonNode jsonFromFile = objectMapper.readTree(jsonFile);

 Making a REST API Call

Use Rest Assured to make a GET request to the specified API endpoint. Capture the response in a Response object.

Response response = RestAssured.given().when().get("https://reqres.in/api/users/3").then().extract().response();
String expectedResponse = response.getBody().asString();
System.out.println("Response is : " + expectedResponse);

// Verify the status code
response.then().statusCode(200);

Reading JSON Content 

Convert the JSON content from the response into another JsonNode object using the ObjectMapper.

JsonNode jsonFromResponse = objectMapper.readTree(expectedResponse);

Compare the JSON content

Convert the JSON content from the response into another JsonNode object using the ObjectMapper.

 Assert.assertEquals(jsonFromFile, jsonFromResponse);

JSON Response data mismatches

In the below example, the data of the response does not matches.

The output of the above program is

JSON Response structure mismatches

The response returns email whereas JSON File has email_address as shown in the below image:

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

Fluent Wait in Serenity

HOME

In the previous tutorials, I explained the Implicit Wait in Serenity and Explicit Wait in Serenity. This tutorial will explain the Fluent Wait in Serenity.

What is Fluent Wait?

Fluent waits provide more flexibility, allowing us to specify polling intervals and ignore specific exceptions during the wait time.  Fluent Wait not only lets you specify the maximum amount of time to wait for a condition but also allows you to define the frequency with which the condition is checked and to ignore specific exceptions during the wait time.

Key Features of Fluent Wait:

  1. Timeout: The maximum amount of time to wait for the condition.
  2. Polling Frequency: How often the condition should be checked.
  3. Ignoring Exceptions: The ability to ignore specific types of exceptions while waiting.

Below is an example that shows that loading of element take sometime. So to wait for the element, we can use the Fluent Wait.

Step 1

Step 2

Step 3

Below is the example of Fluent wait.

import net.serenitybdd.annotations.DefaultUrl;
import net.serenitybdd.annotations.Managed;
import net.serenitybdd.core.annotations.findby.FindBy;
import net.serenitybdd.core.pages.PageObject;
import net.serenitybdd.core.pages.WebElementFacade;
import net.serenitybdd.junit.runners.SerenityRunner;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.openqa.selenium.ElementNotInteractableException;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.support.ui.FluentWait;

import java.time.Duration;
import java.util.function.Function;

@RunWith(SerenityRunner.class)
@DefaultUrl("http://the-internet.herokuapp.com/dynamic_loading/1")
public class FluentWait_Demo extends PageObject {

    @Managed
    WebDriver driver;

    @FindBy(xpath = "//*[@id='start']/button")
    WebElementFacade startButton;


    @FindBy(xpath = "//*[@id='finish']/h4")
    WebElementFacade pageText;

    @Test
    public void fluentWaitDemo() throws InterruptedException {

        open();
        startButton.click();
        waitForElementWithFluentWait(pageText);

    }

    public void waitForElementWithFluentWait(WebElement pageText) {
        FluentWait wait = new FluentWait<>(getDriver())
                .withTimeout(Duration.ofSeconds(10))
                .pollingEvery(Duration.ofSeconds(2))
                .ignoring(ElementNotInteractableException.class);

        wait.until((Function<WebDriver, Boolean>)
                driver -> pageText.isDisplayed());

        System.out.println("Text :" + pageText.getText());
        System.out.println("Fluent Time defined for the test (in seconds) :" + getWaitForTimeout().toSeconds());
    }
}

Explanation:

  1. withTimeout(Duration.ofSeconds(10)): Specifies the maximum time to wait for the condition to be met, which is 30 seconds in this case.
  2. pollingEvery(Duration.ofSeconds(2)): Specifies the interval at which the check should be performed, here it is every 5 seconds.
  3. ignoring(ElementNotInteractableException.class): Instructs the wait to ignore ElementNotInteractableException that might occur during the polling period.

The output of the above program is

Fluent Wait supports various conditions similar to explicit waits, such as:

  1. visibilityOfElementLocated
  2. presenceOfElementLocated
  3. elementToBeClickable
  4. textToBePresentInElementLocated
  5. alertIsPresent
  6. elementToBeSelected
  7. frameToBeAvailableAndSwitchToIt
  8. Custom conditions

We can use Lambda expression to simplify the condition:

public void waitForElementWithFluentWait(WebElement pageText) {
        FluentWait wait = new FluentWait<>(getDriver())
                .withTimeout(Duration.ofSeconds(10))
                .pollingEvery(Duration.ofSeconds(2))
                .ignoring(ElementNotInteractableException.class);

       wait.until(driver -> pageText.isDisplayed());

        System.out.println("Text :" + pageText.getText());
        System.out.println("Fluent Time defined for the test (in seconds) :" + getWaitForTimeout().toSeconds());
    }

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

Appending Data to Excel with Python using openpyxl

HOME

We are given an Excel file and our task is to append the data into this excel file using Python. In this article, we’ll explore openpyxl library to append data to an Excel file using Python.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Sample Excel

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Load the workbook

workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

Step 3 – Select the active worksheet

sheet = workbook.active

Step 4 – Create new data to be appended to the Excel in the list of lists.

new_data =[
    ["Scrum Master", "5-10 Years", "35K"],
    ["Consultatnt", "8-12 Years", "37K"]
]

Step 5 – Find the next available row in the sheet.

last_row = sheet.max_row + 1

Step 6 – Append the data to the excel by using the nested loops.

for i, row in enumerate(new_data, start=last_row):
    for j, value in enumerate(row, start=1):
        sheet.cell(row=i, column=j, value=value)

Step 7 – Save the changes made to the workbook

workbook.save("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

The complete program is

from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Font


#Load the workbook
workbook = load_workbook("C:\\Users\\ykv12\\Documents\\Vibha\\Automation\\SearchInBing.xlsx")

sheet = workbook.active

#Sample data
new_data =[
    ["Scrum Master", "5-10 Years", "35K"],
    ["Consultatnt", "8-12 Years", "37K"]
]

#Append the new data to the sheet
last_row = sheet.max_row + 1
for i, row in enumerate(new_data, start=last_row):
    for j, value in enumerate(row, start=1):
        sheet.cell(row=i, column=j, value=value)

#Save the workbook
workbook.save("C:\\Users\\ykv12\\Documents\\Vibha\\Automation\\SearchInBing.xlsx")

print("Data is appended in the Excel file successfully")

The output of the above program is

Updated Excel

That’s it! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

What is CucumberOptions in Cucumber?

HOME

In Cucumber, “@CucumberOptions“ is an annotation used to specify various options for running Cucumber tests. It allows you to configure different aspects of how your Cucumber tests are executed. This annotation is typically placed above the test runner class, which is responsible for triggering the execution of Cucumber tests.

1.Command-line arguments have the highest precedence. If you specify options through the CLI, they will override any settings provided by “@CucumberOptions“ or properties files. For example, running the tests with specific options from the terminal.

mvn clean test -Dcucumber.options="--tags @smoke --plugin pretty"

2. The “@CucumberOptions” annotation comes next in the precedence order. Settings configured here will override those provided in properties files but will be overridden by CLI arguments. For example, specifying configurations directly in the test runner class.

import org.junit.runner.RunWith;
import io.cucumber.junit.Cucumber;
import io.cucumber.junit.CucumberOptions;

@RunWith(Cucumber.class)
@CucumberOptions(
         features = "src/test/resources/features",
         glue = "com.mycompany.stepdefinitions",
         plugin = {"pretty", "html:target/cucumber-reports.html", "json:target/cucumber.json"},
         tags = "@smoke",
         monochrome = true,
         dryRun = false
     )

public class RunCucumberTests {
}

3. Configuration settings in properties files have the lowest precedence. These settings will be used if neither CLI arguments nor “@CucumberOptions“ provide a value for a particular option. For example, Creating a “cucumber.properties“ file to specify default configurations.

cucumber.features=src/test/resources/features
cucumber.glue=com.mycompany.stepdefinitions
cucumber.plugin=pretty, html:target/cucumber-reports.html, json:target/cucumber.json
cucumber.tags=@smoke

Configuration Options in CucumberOptions

Below is an example of CucumberOptions

import org.junit.runner.RunWith;
import io.cucumber.junit.Cucumber;
import io.cucumber.junit.CucumberOptions;

@RunWith(Cucumber.class)
@CucumberOptions( features = "src/test/resources/features",
    glue = "com.mycompany.stepdefinitions",
    plugin = {"pretty", "html:target/cucumber-reports.html", "json:target/cucumber.json"},
    tags = "@smoke",
    monochrome = true,
    dryRun = false,
    strict = true,
    name = "Login functionality"  )

public class RunCucumberTest {
}

In this example:

  1. The “features” attribute points to the location of the feature files.
  2. The “glue” attribute points to the package where the step definitions are located.
  3. The “plugin” attribute specifies three plugins: one for pretty output in the console, one for an HTML report, and one for a JSON report.
  4. The “tags” attribute specifies that only scenarios tagged with @smoke will be run.
  5. The “monochrome” attribute is set to true to make the console output more readable.
  6. The “dryRun” attribute is set to false to indicate that the tests should actually be executed.
  7. The “strict” attribute treats undefined and pending steps as errors, ensuring all steps are fully implemented.
  8. The “name” attribute allows running scenarios that match a specific name or pattern using regular expressions.
vibssingh

What is Glue in Cucumber?

Last Updated On

HOME

This tutorial explains the Glue code in Cucumber.

Table of Contents

  1. What is Glue code?
  2. Cucumber Glue Example
  3. How Glue Locate Step Definitions
  4. Multiple StepDefinitions in Glue Code

What is Glue code?

The glue option helps Cucumber locate the step definition file. In the glue cucumber option, we specify the package to load the glue code. Where glue code is our step definitions or hooks.

The glue code in Cucumber performs the following tasks:

– Scans the project for step definitions and associates them with the corresponding steps in the feature files.
– Executes the automation code defined in the step definitions when the corresponding step is encountered during test execution.
– Provides a way to configure Cucumber runtime options, such as defining test hooks, plugins, and other settings.

Cucumber Glue Example

import org.junit.runner.RunWith;
import io.cucumber.junit.Cucumber;
import io.cucumber.junit.CucumberOptions;
 
@RunWith(Cucumber.class)
@CucumberOptions(plugin = "pretty", features = "src/test/resources/features",
glue = "com.example.customer")
 
public class CucumberRunnerTest {
 
}

This option specifies the package where the step definition classes are located. In this example, the step definitions are expected to be present in the “com.example.customer” package.

How Glue Locate Step Definitions

1. By default, Cucumber expects the step definitions to be in the same package as the Test Runner class.
2. If the “glue“ option is not specified in the Cucumber options, Cucumber will automatically assume that the step definitions are located in the same package as the test runner. Therefore, if your test runner class is “com.example.runner“, Cucumber will consider the “com.example.runner“ package as the default package for locating the step definitions.

You can comment or remove the “glue“ line in the Cucumber options if your test runner and step definitions are in the same package.

Multiple StepDefinitions in Glue Code

If the step definitions are present in 2 different packages, you can separate packages using a comma and double quotes.

glue = {"com.example.customer", "com.example.agents"}

Without proper glue settings, Cucumber will not be able to connect the feature files’ scenarios with the corresponding implementation, resulting in unimplemented step errors.

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

Create and Format Excel Files with Python Openpyxl – Tutorial

HOME

Reading data from Excel files is a common practice in software development and testing. Excel provides a tabular format which is easy to understand and navigate. Data can be written to Excel for manipulation, analysis and visualization using Java, Python, R and soon. There are several ways to read the data from Excel in Python, but we will discuss openpyxl.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Implementation Steps

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Create a new workbook

workbook = Workbook()
sheet = workbook.active

Step 3 – Select or rename the Sheet

sheet.title ="Sample Sheet"

Step 4 – Prepare the data

data =[
    ["Name", "Designation", "EmployeeId"],
    ["Tom", "BA", 11001],
    ["Trina", "PO", 11002],
    ["Will", "Dev", 11003]
]

Step 5 – Write the data

Loop through each sub-list and use sheet.append(row) to add the row to the sheet

for row in data:
    sheet.append(row)

Step 6 – Save the workbook

workbook.save("C:\\Users\\Vibha\\Automation\\WriteExcel.xlsx")

The complete program is

from openpyxl.workbook import Workbook

#Create a new workbook
workbook = Workbook()
sheet = workbook.active

#Rename the sheet
sheet.title ="Sample Sheet"

#Sample data
data =[
    ["Name", "Designation", "EmployeeId"],
    ["Tom", "BA", 11001],
    ["Trina", "PO", 11002],
    ["Will", "Dev", 11003]
]

#Write data to the sheet
for row in data:
    sheet.append(row)

#Save the workbook
workbook.save("C:\\Users\\Documents\\Vibha\\Automation\\WriteExcel.xlsx")
print("Data is written the Excel file successfully")

The output of the above program is

The Excel is created and saved at the specified location.

Header with bold font

Below is the complete program:

from openpyxl.styles import Font
from openpyxl.workbook import Workbook

#Create a new workbook
workbook = Workbook()
sheet = workbook.active

#Rename the sheet
sheet.title ="Sample Sheet"

#Sample data
headers =  ["Name", "Designation", "EmployeeId"]
rows =[
    ["Tom", "BA", 11001],
    ["Trina", "PO", 11002],
    ["Will", "Dev", 11003]
]

#Write headers with bold font
for col_num, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_num, value=header)
    cell.font = Font(bold=True)


#Write rows
for row_num, row_data in enumerate(rows, start=2):
    for col_num, cell_value in enumerate(row_data, start=1):
       sheet.cell(row=row_num, column=col_num, value=cell_value)

#Save the workbook
workbook.save("C:\\Users\\Documents\\Vibha\\Automation\\FormattedExcel.xlsx")
print("Data is written the Excel file successfully")

Define the list “headers” containing the name of the headers. Iterate over the headers using enumerate(headers, start=1), which provides an index starting from 1. For each header, create a cell in the first row and corresponding column and set its value.

Use Font(bold=True) to make the font of the header bold and assign it to “cell.font”.

The output of the above program is

Formatted Excel

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

How to Write Data to Excel File in Java using Apache POI

HOME

This tutorial describes the steps to write data in Excel file in Java.

I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.

In the previous tutorial, I have explained How to read data from Excel in Java.

Dependency:

If you are using maven, then you need to add below dependency in pom.xml.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.3.0</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

Here are few classes which you need to aware of.

  • Workbook : This is high level class for representing excel workbook.
  • Sheet : This is high level class for representing excel sheet.
  • Row : This is high level class for representing excel row. It has methods which are related to row.
  • Cell: This is high level class for representing individual excel cell. It has methods which are related to cell for example : getDataType().

The basic steps for writing data into an excel file using Apache POI API are given below:

Implementation Steps

Step 1 – Create a blank workbook.

XSSFWorkbook wb = new XSSFWorkbook();

Step 2 – Import XSSFWorkbook from package.

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Step 3 – Create a sheet and pass name of the sheet.

XSSFSheet sheet = workbook.createSheet("Write_TestData");

Step 4 – Import XSSFSheet from package.

import org.apache.poi.xssf.usermodel.XSSFSheet;

Step 5 – Prepare Data to Write

An ArrayList of Object[] arrays is created to hold the data. Each array represents a row in the sheet.

ArrayList<Object[]> data = new ArrayList<Object[]>();
		data.add(new String[] { "Name", "Id", "Salary" });
		data.add(new Object[] { "Jim", "001A", 10000 });
		data.add(new Object[] { "Jack", "1001B", 40000 });
		data.add(new Object[] { "Tim", "2001C", 20000 });
		data.add(new Object[] { "Gina", "1004S", 30000 });

Step 6 – Create a Row. A spreadsheet consists of rows and cells. It has a grid layout.

 XSSFRow row = sheet.createRow(rownum++);

Step 7 – Import XSSFRow from package

import org.apache.poi.xssf.usermodel.XSSFRow;

Step 8 – Create cells in a row. A row is a collection of cells. When you enter data in the sheet, it is always stored in the cell.

 XSSFCell cell = row.createCell(cellnum++);

Step 9 – Import XSSFCell from package.

 XSSFCell cell = row.createCell(cellnum++);

Step 10 – Iterate over Data and Write to the sheet

	int rownum = 0;
		for (Object[] employeeDetails : data) {

			// Create Row
			XSSFRow row = sheet.createRow(rownum++);

			int cellnum = 0;
			for (Object obj : employeeDetails) {

				// Create cell
				XSSFCell cell = row.createCell(cellnum++);

				// Set value to cell
				if (obj instanceof String)
					cell.setCellValue((String) obj);
				else if (obj instanceof Double)
					cell.setCellValue((Double) obj);
				else if (obj instanceof Integer)
					cell.setCellValue((Integer) obj);
			}
		}

Step 11 – Open a FileOutputStream to a new file named “EmployeeDetails.xlsx”. Write data to an OutputStream. Use the below code to write output stream.

FileOutputStream out = new FileOutputStream(new File("EmployeeDetails.xlsx"));
workbook.write(out);

Step 12 – Close the output stream.

out.close();

Below is the sample code for writing the data in an excel in Java.

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadWriteExcelFile {

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

		// create blank workbook
		XSSFWorkbook workbook = new XSSFWorkbook();

		// Create a blank sheet
		XSSFSheet sheet = workbook.createSheet("Write_TestData");

		ArrayList<Object[]> data = new ArrayList<Object[]>();
		data.add(new String[] { "Name", "Id", "Salary" });
		data.add(new Object[] { "Jim", "001A", 10000 });
		data.add(new Object[] { "Jack", "1001B", 40000 });
		data.add(new Object[] { "Tim", "2001C", 20000 });
		data.add(new Object[] { "Gina", "1004S", 30000 });

		// Iterate over data and write to sheet
		int rownum = 0;
		for (Object[] employeeDetails : data) {

			// Create Row
			XSSFRow row = sheet.createRow(rownum++);

			int cellnum = 0;
			for (Object obj : employeeDetails) {

				// Create cell
				XSSFCell cell = row.createCell(cellnum++);

				// Set value to cell
				if (obj instanceof String)
					cell.setCellValue((String) obj);
				else if (obj instanceof Double)
					cell.setCellValue((Double) obj);
				else if (obj instanceof Integer)
					cell.setCellValue((Integer) obj);
			}
		}
		try {

			// Write the workbook in file system
			FileOutputStream out = new FileOutputStream(new File("EmployeeDetails.xlsx"));
			workbook.write(out);
			out.close();
			System.out.println("EmployeeDetails.xlsx has been created successfully");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			workbook.close();
		}
	}

}

The output of the above program is

Summary:

  1. Create a WorkBook and Sheet
  2. Prepare the data. Use ArrayList to store the rows of data.
  3. Write Data to the Excel by iterating over each row and cell
  4. Save the Workbook
  5. Handle the exceptions appropriately

That’s it! We have written data in Excel.

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!

vibssingh

Java Excel Tutorial: Creating Excel with Formulas Using Apache POI

Last Updated On

HOME

In the previous tutorial, I have explained How to update data in existing excel in Java. In this Java Excel tutorial, I will explain how to create an Excel with formula in a Java program. Excel is very excellent in calculating formulas. The Apache POI library provides excellent support for working with formulas in Excel.

I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.

If you are using maven, then you need to add below dependency in pom.xml.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.3.0</version>
</dependency>
  
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

To know about various Interfaces and Classes in Excel, please refer this link.

Implementation Steps

Step 1 – Create a blank workbook.

XSSFWorkbook workbook = new XSSFWorkbook();

Step 2 – Import XSSFWorkbook from package.

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Step 3 – Create a sheet and pass name of the sheet.

XSSFSheet sheet = workbook.createSheet("Calculate Salary");

Step 4 – Import XSSFSheet from package.

import org.apache.poi.xssf.usermodel.XSSFSheet;

Step 5 – Create a Row. A spreadsheet consists of rows and cells. It has a grid layout. I have created object called Header of XSSFRow.

XSSFRow header = sheet.createRow(0);

Step 6 – Below syntax create new cells within the row at index 0 and set a string value for the cell.

header.createCell(0).setCellValue("Employee_Name");

Step 7 – Below syntax creates a cell at index 4 and sets formula for cell.

dataRow.createCell(4).setCellFormula("B2+C2+D2");

Step 8 – The following line of code sets formula for the cell at the row #1 and column #5 (remember index is 0-based):

In case the column (Cell) does not exist (but the row does), use the following code.

XSSFRow dataRow = sheet.createRow(1);
dataRow.createCell(5).setCellFormula("SUM(B2:C2)");

Step 9 – Write the workbook to a File and close the workbook

try {

			// Write the workbook in file system
			FileOutputStream out = new FileOutputStream(new File("Salary_Slip.xlsx"));
			workbook.write(out);
			out.close();
			System.out.println("Excel written successfully.");

		} catch (IOException e) {
			e.printStackTrace();

		}

In the above line, note that you should make sure that the cell at position (1, 5) does exist, otherwise you get a NullPointerException.

Let us see a program where I have created a cell which contains the formula.

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class FormulaExcelDemo {
    
    public static void main(String[] args) {

        // Create object of XSSFWorkbook class
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Create object of XSSFSheet class
        XSSFSheet sheet = workbook.createSheet("Calculate Salary");

        // Create Header row using XSSFRow class
        XSSFRow header = sheet.createRow(0);
        header.createCell(0).setCellValue("Employee_Name");
        header.createCell(1).setCellValue("Base_Salary");
        header.createCell(2).setCellValue("Variable_Pay");
        header.createCell(3).setCellValue("Other_Benefits");
        header.createCell(4).setCellValue("Total Salary");
        header.createCell(5).setCellValue("Base_Variable Salary");

        XSSFRow dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue("George");
        dataRow.createCell(1).setCellValue(5000);
        dataRow.createCell(2).setCellValue(650);
        dataRow.createCell(3).setCellValue(1200);

        // Set formula
        dataRow.createCell(4).setCellFormula("B2+C2+D2");
        dataRow.createCell(5).setCellFormula("SUM(B2:C2)");

        try {

            // Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("Salary_Slip.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully.");

        } catch (IOException e) {
            e.printStackTrace();

        }
    }
}

The output of the above program is

Updated Excel

That’s it! Well Done! Cheers!!

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!