Create Excel 2007 file using Apache POI

In the previous example, we described how to create XLS format file using Apache POI java library. In this post, we will explain how to create Excel 2007 and above files using the same library. The only difference when compared with the last post is that, you will use different class files to create Excel 2007 documents in Java. The Java program that writes a Excel 2007 file is provided below:

Excel 2007 - Java Program example

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //Write in Office 2007, 2012 format
import org.apache.poi.xssf.usermodel.XSSFSheet; //Write in Office 2007, 2012 format
import java.util.*;
public class createxlsx {  
        public static void main(String[] args) throws Exception{
                /* New Office Format */
                XSSFWorkbook new_workbook = new XSSFWorkbook(); //create a blank workbook object
                XSSFSheet sheet = new_workbook.createSheet("Score_Details");  //create a worksheet with caption score_details
                /* Create a Map of input data */
                Map<String, Object[]> excel_data = new HashMap<String, Object[]>(); //create a map and define data
                excel_data.put("1", new Object[] {"ID", "Subject", "Score","Rank"}); //add data
                excel_data.put("2", new Object[] {"1", "English", "95",1d});
                excel_data.put("3", new Object[] {"2", "Science", "65",2d});
                excel_data.put("4", new Object[] {"3", "Maths", "50",3d}); 
                Set<String> keyset = excel_data.keySet();
                int rownum = 0;
                for (String key : keyset) { //loop through the data and add them to the cell
                        Row row = sheet.createRow(rownum++);
                        Object [] objArr = excel_data.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr) {
                                Cell cell = row.createCell(cellnum++);
                                if(obj instanceof Double)
    FileOutputStream output_file = new FileOutputStream(new File("C:\\Write_2007_file.xlsx")); //create XLSX file
    new_workbook.write(output_file);//write excel document to output stream
    output_file.close(); //close the file

The output produced by this code is on the same lines as of the code in XLS creation tutorial. You would need the following JAR files at runtime for this code to work;
  • poi-3.8.jar
  • poi-ooxml-schemas-3.8
  • poi-ooxml-3.8.jar
  • xmlbeans-2.3.0.jar
  • dom4j-1.6.1.jar
You will get exceptions on the screen if you are missing any of these JAR files. Also, the file properties of the resulting spreadsheet, has the file type marked as "Microsoft Office Excel Worksheet" as against the previous case. The magic in this case is all done by XSSFWorkbook and XSSFSheet classes.

In the next tutorial, we will discuss how to update existing excel workbooks in POI. (Both old and new office formats)

This Tutorial: How to create Excel files in 2007, 2012 Microsoft Office Format by Using Apache POI Java library?
Keywords: Excel 2007 Format, Apache POI, Create XLS File, Java Program, Example Code
Next Tutorial: How to create XLS format files in POI over JDBC Data?

No comments:

Post a Comment