In our previous article, we went through the details of Apache POI, an open-source Java API for working with Microsoft file formats. In this article, we’ll go through the Apache POI component for working with Excel Workbooks. We will also show how to use Apache POI to create an Excel file and add sheets to it using this Java library.

Apache POI Java APIs for Accessing Excel File Formats

Apache POI Java API provides separate components for working with two different Excel file formats. These are:

  1. HSSF (Horrible Spreadsheet Format) – For working with Excel 97-2003 File Formats
  2. XSSF (XML Spreadsheet Format) – For working with Excel 2007 and later File Formats

Specifically, HSSF lets you read XLS files whereas XSSF lets you read XLS and XLSX files. Both these APIs let you create, read and manipulate Excel files from within your Java application without going into the details of underlying file formats.

How to Create Excel XLSX File in Java?

In this example, we’ll be going through the details of using Apache POI XSSF to create XLSX files in our Java application. At this stage, we assume that you have already installed and configured Apache POI API in your Java application. If you haven’t done so, you can visit the Install Apache POI for Java and follow the steps.

Steps to Create Excel Workbook using Apache POI

Creating an Excel Workbook using Apache POI API for Java is simple and easy, and can be achieved using the following steps.

  1. Create a workbook using the XSSFWorkbook class
  2. Close the output stream.

Java code to create Excel Workbook using Apache POI is as follows.

XSSFWorkbook wb = new XSSFWorkbook();
...
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

The above code will create an Excel workbook in XLSX file format. However, if you will open the workbook at this stage, you will receive the following error.

The reason for this is that an Excel workbook using Apache POI without any worksheet gives this error.  In order to resolve this problem, we need to add at least one worksheet to the generated Excel file.

Add Worksheet to Excel Workbook with Apache POI in Java

This brings us to the step where we need to add a worksheet to the workbook in order for it to work properly. This can be achieved by using the XSSFSheet object which is added to the workbook as shown in the following code sample.

XSSFWorkbook wb1 = new XSSFWorkbook();
try
{
	XSSFSheet sheet1 = wb1.createSheet("Ripon");
	FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
	wb1.write(fileOut);    
	fileOut.close();
}
catch(Exception ex)
{
	System.out.println("XLSX Generated Error...");
}

Complete XSSFWorkbook Example

Now that we have seen how to create a workbook and add a sheet to it with Apache POI API for Java, let’s have a look at how we will add rows and cells to the created worksheet.

The XSSFSheet class has the method createRow that lets you add a row to the sheet. This returns an object of the Row class that represents a Row in the Excel worksheet. In order to create Cells in the worksheet, we have to use the createCell method that returns an object of XSSFCell class. The setCellValue method of the XSSFCell is used to set the value of the cell.

A complete example of creating a workbook using XSSFWorkbook, adding a sheet using XSSFSheet, and adding XSSFRow to it is shown below.

// Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Creating a blank Excel sheet
        XSSFSheet sheet = workbook.createSheet("student Details");
        // Creating an empty TreeMap of string and Object][] type
        Map<String, Object[]> data = new TreeMap<String, Object[]>();
        // Writing data to Object[]
        // using put() method
        data.put("1", new Object[] { "ID", "CITY", "STATE" });
        data.put("2", new Object[] { 1, "Clanton", "Alabama" });
        data.put("3", new Object[] { 2, "Cordova", "Alaska" });
        data.put("4", new Object[] { 3, "Clifton", "Arizona" });
        data.put("5", new Object[] { 4, "Arcadia", "California" });
  
	// Iterating over data and writing it to sheet
	Set<String> keyset = data.keySet(); 
        int rownum = 0;
        for (String key : keyset) { 
            // Creating a new row in the sheet
            XSSFRow row = sheet.createRow(rownum++); 
            Object[] objArr = data.get(key); 
            int cellnum = 0; 
            for (Object obj : objArr) { 
                // This line creates a cell in the next column of that row
                XSSFCell cell = row.createCell(cellnum++); 
                if (obj instanceof String)
                    cell.setCellValue((String)obj); 
                else if (obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }     
        // Writing the workbook
        FileOutputStream out = new FileOutputStream("gfgcontribute.xlsx");
        workbook.write(out); 
        // Closing file output connections
        out.close();

Conclusion

In this blog article, we demonstrated how to create an Excel workbook using Apache POI API for Java. We used the XSSFWorkbook to generate an XLSX workbook, added worksheets to it, added rows to the worksheet, and populated data in the cells in the worksheet. For more examples of working with Excel workbooks using Apache POI for Java, stay tuned.