So sánh jxl workbook vs apache workbook

There are some popular libraries for working with Excel files in Java, such as Apache POI, JExcel, and Fastexcel. In this post, we’ll focus on Apache POI to interact with Excel files. Apache POI is an open-source Java library that provides APIs for working with Microsoft Office documents, such as Word, Excel, and PowerPoint.

In the Apache POI library, there are essential interfaces that model the elements of an Excel file, such as

• Workbook: An Excel workbook file (.xlsx or .xls).
• Sheet: A worksheet in an Excel workbook file.
• Row: A row of cells in an Excel worksheet.
• Cell: A cell in an Excel worksheet.

And there are multiple implementations for each interface,

Apache POI interfaces and their implementations by artifact names

The HSSF implementations are used for older Excel file formats (.xls), while the XSSF implementations are used for newer Excel file formats (.xlsx). The SXSSF implementations are used for writing very large Excel files (XLSX) in a memory optimized way.

We will be working with XSSF implementation for the examples below, but please beware of the differences between implementations for your own use cases. Maven dependency for the XSSF implementation can be found here.

Creating Excel Files

Creating Excel files can be summarized into the following steps,

1. Create a workbook.
2. Create a sheet in the workbook.
3. Create a row in the sheet.
4. Add cells in the sheet.
5. Repeat steps 3 and 4 to write more data.

Let’s put these steps in action in the following examples.

1. Example

For the first example I will skip over the 5th step and create a workbook with only a single data.

//create a workbook Workbook workbook = new XSSFWorkbook();

//create a sheet in the workbook(you can give it a name) Sheet sheet = workbook.createSheet("excel-sheet");

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

//add cells in the sheet Cell cell = row.createCell(0);

//set a value to the cell cell.setCellValue("something");

Row and Cell numbers start from 0 for Workbooks. I don’t think this is any surprising if you are familiar with data structures in Java.

And to see what it looks like save the Excel file with the following code snippet.

//save the Excel file try {

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

e.printStackTrace();  

Hopefully without any errors you should now see an Excel file with the name excel.xlsx in your working directory and when opened up it should look something like this:

An excel file with a single data.

2. Example

I hope you got the gist of how to create Excel files in Java. Let’s bring the 5th step back into play and insert multiple data to multiple rows and cells in the Excel file.

//create a workbook Workbook workbook = new XSSFWorkbook();

//create a sheet in the workbook Sheet sheet = workbook.createSheet("excel-sheet");

//create the 0th row using the createRow() method Row row0 = sheet.createRow(0); //create cell by using the createCell() method //and set the values to the cell by using the setCellValue() method row0.createCell(0).setCellValue("S.No."); row0.createCell(1).setCellValue("Customer Name"); row0.createCell(2).setCellValue("Account Number"); row0.createCell(3).setCellValue("e-mail"); row0.createCell(4).setCellValue("Balance");

//create the 1st row Row row1 = sheet.createRow(1); //insert data in the first row row1.createCell(0).setCellValue(1); row1.createCell(1).setCellValue("John William"); row1.createCell(2).setCellValue(9999999); row1.createCell(3).setCellValue("[email protected]"); row1.createCell(4).setCellValue("700000.00");

//create the 2nd row Row row2 = sheet.createRow(2); //insert data in the second row row2.createCell(0).setCellValue(2); row2.createCell(1).setCellValue("Mathew Parker"); row2.createCell(2).setCellValue(22222222); row2.createCell(3).setCellValue("[email protected]"); row2.createCell(4).setCellValue("200000.00");

//save the Excel file try {

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

e.printStackTrace();  

And this time the result should look something like this:

An Excel file with multiple data.

The setCellValue method accepts various data types, including numeric types (double, int, etc.), String, boolean, and date-related types. Specifying the correct data type is important to ensure accuracy, prevent type mismatches, and facilitate code readability when working with Excel cells.

3. Example

Although these examples might have their own use cases, in real life these are not as effective and practical when you are working with LOTS of data. That’s where arrays, lists, and for loops come into play.

Let’s have a list of employees’ data stored in an Object array. Each element in the array corresponds to a different attribute: name, employee ID, hire date, and salary. And then populate the Excel file using these data with a for loop.

// Create a list of employees with their data List<Object[]> employeeData = new ArrayList<>(); employeeData.add(new Object[]{"John Doe", 101, "2023-01-15", 55000.0}); employeeData.add(new Object[]{"Jane Smith", 102, "2022-08-20", 65000.0}); employeeData.add(new Object[]{"Bob Johnson", 103, "2023-03-10", 60000.0});

// Create a new Excel workbook Workbook workbook = new XSSFWorkbook();

// Create a new sheet Sheet sheet = workbook.createSheet("Employee Data");

// Create the header row Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Name"); headerRow.createCell(1).setCellValue("Employee ID"); headerRow.createCell(2).setCellValue("Hire Date"); headerRow.createCell(3).setCellValue("Salary");

// Populate the rows with employee data for (int i = 0; i < employeeData.size(); i++) {

Row row = sheet.createRow(i + 1); // Start from the second row  
// Employee Name  
row.createCell(0).setCellValue((String) employeeData.get(i)[0]);  
// Employee ID  
row.createCell(1).setCellValue((int) employeeData.get(i)[1]);  
// Hire Date  
row.createCell(2).setCellValue((String) employeeData.get(i)[2]);  
// Salary  
row.createCell(3).setCellValue((double) employeeData.get(i)[3]);  

// Save the Excel file to a local directory try (FileOutputStream fileOut = new FileOutputStream("EmployeeData.xlsx")) {

workbook.write(fileOut);  

e.printStackTrace();  

When populating the Excel cells, we cast the Object data to their respective types (String, int, double) before setting them in the cells.

And the resulting Excel file should look like the following.

Excel file formed with arrays, lists and for loops.

In this example we had 3 employees, but it wouldn’t much change even if there where thousands of them (considering you are getting your list of employees from a DB, etc.) thanks to the for loop. Maybe you might have to switch to if your Excel file gets too big though.