Create excel files in JAVA

March 05, 2016 | Updated last March, 2016 | 641 views | Comments

Generate excel files in JAVA, write records to an excel file and save it locally using Apache POI. Maven will be the build manager.

 

Built and tested with the following:

  • Eclipse Juno IDE
  • Apache Tomcat 7.0.47
  • JDK 1.7.0
  • Maven 3.0.4
  • Apache POI 3.14

 

Step 1. Add dependencies

Open pom.xml and add the following under dependencies.


        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.14</version>
        </dependency>

 

Step 2. Write and save the excel file

Create a new JAVA class and put the following codes.

/*
 * Set up the desired file name,
 * with the path where to save it.
 */
String fileName = "/PATH/write-excel-files-in-java.xlsx";

/*
 * Set up header records to put on the excel.
 */
Map<Long, Object[]> dataToExcel = new TreeMap<Long, Object[]>();
dataToExcel.put((long) 0, new Object[] {"Column A", "Column B", "Column C", "Column D", "Column E"});

/*
 * Create a new blank workbook.
 */
@SuppressWarnings("resource")
XSSFWorkbook workbookFile = new XSSFWorkbook();

/*
 * Set up desired sheet name.
 */
String sheetName = "Letters Sheet";

/*
 * Create a new sheet.
 */
XSSFSheet sheetFile = workbookFile.createSheet(sheetName);
sheetFile.setAutobreaks(true);

/*
 * Add some records.
 */
for (int i=1; i<10; i++) {
    dataToExcel.put((long) i, new Object[] {("A" + i), ("B" + i), ("C" + i), ("D" + i), ("E" + i)});
}

/*
 * Iterate over the data,
 * and write to the sheet.
 */
Set<Long> keyset = dataToExcel.keySet();
int rownum = 0;
for (Long key : keyset) {
    Row row = sheetFile.createRow(rownum++);
    Object [] objArr = dataToExcel.get(key);
    
    int cellnum = 0;
    for (Object obj : objArr) {
        
       Cell cell = row.createCell(cellnum++);
       
       if (obj instanceof String) {
            cell.setCellValue((String)obj);
       } else if(obj instanceof Integer) {
            cell.setCellValue((Integer)obj);
       } else if(obj instanceof Long) {
            cell.setCellValue((Long)obj);
       }
    }
}

try {
    /*
     * Write the workbook in file system.
     */
    FileOutputStream out = new FileOutputStream(new File(fileName));
    workbookFile.write(out);
    out.close();
    /*workbookFile.write(response.getOutputStream());*/
} catch (Exception e) {
    e.printStackTrace();
}