Working with Excel Files in Java

Java provides several libraries to work with Excel files, including Apache POI and JExcelApi. In this tutorial, we will focus on using Apache POI, which is a popular and widely-used library for working with Microsoft Office file formats.

Introduction to Apache POI

Apache POI is an open-source library that allows you to read and write Excel files in Java. It supports various Excel file formats, including .xls, .xlsx, and .xlsm. To use Apache POI, you need to add the following dependencies to your project:

• poi-3.2-FINAL-20081019.jar
• poi-scratchpad-3.7-20101029.jar
• poi-ooxml-3.7-20101029.jar
• poi-ooxml-schemas-3.7-20101029.jar
• xmlbeans-2.3.0.jar
• dom4j-1.6.1.jar

Reading Excel Files with Apache POI

To read an Excel file using Apache POI, you need to follow these steps:

1. Create a FileInputStream object to read the Excel file.
2. Create a Workbook object by passing the FileInputStream object to the XSSFWorkbook constructor.
3. Get the first sheet from the workbook using the getSheetAt() method.
4. Iterate through each row in the sheet using an iterator.
5. For each row, iterate through each cell and get its value.

Here is an example code snippet that demonstrates how to read an Excel file:

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

public class ReadExcelFile {
    public static void main(String[] args) throws Exception {
        FileInputStream file = new FileInputStream(new File("example.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

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

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    }
}

Writing Excel Files with Apache POI

To write an Excel file using Apache POI, you need to follow these steps:

1. Create a Workbook object using the XSSFWorkbook constructor.
2. Create a sheet in the workbook using the createSheet() method.
3. Iterate through each row and create cells in the sheet using the createRow() and createCell() methods.
4. Set the values of the cells using the setCellValue() method.
5. Write the workbook to a file using the write() method.

Here is an example code snippet that demonstrates how to write an Excel file:

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

public class WriteExcelFile {
    public static void main(String[] args) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Example");

        Map<String, Object[]> data = new TreeMap<>();
        data.put("1", new Object[]{"Name", "Age", "City"});
        data.put("2", new Object[]{"John Doe", 30, "New York"});
        data.put("3", new Object[]{"Jane Doe", 25, "Los Angeles"});

        int rownum = 0;
        for (String key : data.keySet()) {
            XSSFRow row = sheet.createRow(rownum++);
            Object[] objArr = data.get(key);
            int cellnum = 0;

            for (Object obj : objArr) {
                XSSFCell cell = row.createCell(cellnum++);
                if (obj instanceof String) {
                    cell.setCellValue((String) obj);
                } else if (obj instanceof Integer) {
                    cell.setCellValue((Integer) obj);
                }
            }
        }

        FileOutputStream out = new FileOutputStream(new File("example.xlsx"));
        workbook.write(out);
        out.close();
    }
}

Conclusion

In this tutorial, we learned how to read and write Excel files using Apache POI. We covered the basics of creating a Workbook object, reading and writing sheets, and getting and setting cell values. With this knowledge, you can now create your own Java applications that work with Excel files.

Note: This is not an exhaustive tutorial on Apache POI, but rather a basic introduction to get you started with working with Excel files in Java.