In this article we will show you the solution of read and write excel file in java using poi example, the Apache POI package, which offers a set of Java APIs for handling many Microsoft Office file formats, including Excel, makes it possible to read and write Excel files in Java.

Developers can extract data from Excel files, edit already-existing material, or generate new spreadsheets using POI.

The POI library can be used to open an Excel file and gain access to its worksheets and cells.

To extract data, you can read from particular cells or iterate over rows and columns.

For instance, you can get information on formatting, data formats, and cell values. Using POI, you can make a new workbook or open an existing one to write an Excel file.

The workbook can then be created, worksheets added, data entered, formatting applied, and saved.

You can use this to automate Excel-related operations, export data, or generate reports. Now use the concept of reading and writing Excel files in Java using an example utilising POI.

Step By Step Guide On Read And Write Excel File In Java Using Poi Example :-

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellReference;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelReadWriteExample {
    public static void main(String[] args) {
        String filePath = "path/to/excel/file.xlsx";
        // Reading from Excel file
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0); // Accessing the first sheet
            // Reading data from cells
            for (Row row : sheet) {
                for (Cell cell : row) {
                    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                    System.out.print(cellRef.formatAsString() + " - ");
                    CellType cellType = cell.getCellType();
                    if (cellType == CellType.STRING) {
                        System.out.println(cell.getStringCellValue());
                    } else if (cellType == CellType.NUMERIC) {
                        System.out.println(cell.getNumericCellValue());
                    }
                }
            }
            System.out.println("Reading from Excel file completed.");
            // Writing to Excel file
            Row newRow = sheet.createRow(sheet.getLastRowNum() + 1);
            Cell newCell = newRow.createCell(0);
            newCell.setCellValue("New Data");
            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                workbook.write(fos);
            }
            System.out.println("Writing to Excel file completed.");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

1. We begin by including the required imports, which include classes from the java.io package for file input/output operations and the org.apache.poi.ss.usermodel package for working with Excel files.
2. The main method of the ExcelReadWriteExample class, which serves as the program's entry point, is defined.
3. The path to the Excel file that will be read from and written to is represented by the String variable filePath, which is declared.
4. In the try-with-resources step, we open a FileInputStream so that it may read the Excel file that filePath has specified.
5. Additionally, a new XSSFWorkbook object is created to represent the Excel workbook.
6. Using getSheetAt(0), we can retrieve the first sheet of the workbook and assign it to the sheet variable in a Sheet.
7. Using extended for loops, we go through each row and cell in the worksheet.
8. We use CellReference to get the cell reference for each cell and print it to the console.
9. Using getCellType(), we identify the cell type, and then depending on the kind (CellType.STRING or CellType.NUMERIC), we obtain and output the value of the cell.
10. We print a message confirming that the reading process has ended once the Excel file has been completely read.
11. The Excel file is then written to after that.
12. Using the functions createRow() and createCell(), we add a new row at the end of the sheet and a new cell with the index 0 within it.
13. Using setCellValue(), we change the cell's value to "New Data".
14. To write the updated workbook back to the original Excel file identified by filePath, we start a FileOutputStream when we enter another try-with-resources phase.
15. To save the changes to the file, we employ the workbook's write() method.
16. We print a message at the end to show that the writing procedure is finished.
17. We catch the exception and output the stack trace in the event of any IOException while performing file operations.

Conclusion :-

As a result, using an example from POI, we were able to understand how to read and write Excel files in Java.

We also discovered how to access and alter the data in Excel workbooks by using the available classes and methods.

This includes reading cell values, iterating over rows and columns, and changing or adding data.

I hope this article on read and write excel file in java using poi example helps you and the steps and method mentioned above are easy to follow and implement.