Reading and writing data to Excel files

Reading and writing data to Excel files

To read and write data to Excel files in Java, you can use Apache POI, a popular library for working with Microsoft Office documents. With Apache POI, you can create, read, and modify Excel files programmatically, allowing you to interact with Excel data in your test automation scripts.

First, you need to add the Apache POI libraries to your Java project. You can do this by downloading the Apache POI JAR files and adding them as external libraries in your IDE.

Examples

// Example 1: Reading data from an Excel file
// Create a FileInputStream to read the Excel file
FileInputStream inputStream = new FileInputStream(new File("data.xlsx"));

// Create an XSSFWorkbook object representing the Excel file
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

// Get the first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

// Iterate through each row of the sheet
Iterator rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
    Row row = rowIterator.next();
    // Iterate through each cell of the row
    Iterator cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        // Print the cell value
        System.out.print(cell.toString() + "\t");
    }
    System.out.println();
}

// Close the workbook and release resources
workbook.close();
inputStream.close();

// Example 2: Writing data to an Excel file
// Create a new XSSFWorkbook object
XSSFWorkbook workbook = new XSSFWorkbook();

// Create a new sheet in the workbook
XSSFSheet sheet = workbook.createSheet("Sheet10");

// Create a new row in the sheet
Row row = sheet.createRow(0);

// Create a new cell in the row and set its value
Cell cell = row.createCell(0);
cell.setCellValue("Hello from Software Testing Space!");

// Write the workbook to an Excel file
FileOutputStream outputStream = new FileOutputStream("output.xlsx");
workbook.write(outputStream);

// Close the workbook and release resources
workbook.close();
outputStream.close();

// Example 3: Modifying existing data in an Excel file
// Open an existing Excel file
FileInputStream inputStream = new FileInputStream(new File("data.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

// Get the first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

// Get the cell at row 1, column 1 and set its value
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
cell.setCellValue("Updated value");

// Write the modified workbook back to the file
FileOutputStream outputStream = new FileOutputStream("data.xlsx");
workbook.write(outputStream);

// Close the workbook and release resources
workbook.close();
outputStream.close();

FAQ (interview questions and answers)

1. How do you read data from an Excel file in Java?
   By using Apache POI library
   By using JDBC
   By using BufferedReader
   
2. How do you write data to an Excel file in Java?
   By using Apache HTTP client
   By using Apache POI library
   By using JUnit
   
3. What is the purpose of the close() method in file handling?
   To open a file
   To close an open file
   To write to a file
   
4. What happens if you attempt to read from a non-existent file?
   An IOException is thrown
   A FileNotFoundException is thrown
   A NoSuchFileException is thrown
   
5. Which method is used to modify existing data in an Excel file?
   setCellValue()
   createSheet()
   createRow()
   

Your Total Score: 0 out of 5

Remember to just comment if you have any doubts or queries.