Create Excel file using Java: Apache POI - PART

This series of posts are intended towards creating excel files in xlsx format using Java's Apache POI Library. In this post we will create a basic Java project which will put some random data in the Excel file.

This post will cover basic features of Apache POI like:

Creating a workbook
Creating multiple sheets in a workbook
Creating rows and cells in those rows and feeding data in them.
Creating cell border styles and background colour styles, aligning text in the cell.

Please note that, we are also using a Jackson library to read the data from a JSON file. This JSON file data will be then saved in to a JAVA entity class. We will use this java entity class objects to write the data into excel file. Hence this tutorial also covers a basic understanding of using Jackson library as well.

Let’s start with the project setup first:

For this project we are using Eclipse Photon. Go ahead and download the libraries, or you can also use the maven dependencies if you are comfortable with them.

Apache POI Library URL: https://poi.apache.org/download.html

Please include all the below listed apache poi jars in your build path:

Poi-(version).jar
Poi-excelant-(version).jar
Poi-ooxml-(version).jar
Poi-scratchpad-(version).jar
Xmlbeans-(version).jar
Curvesapi-(version).jar
Ooxml-schemas-(version).jar

o Please note that this file is just a limited edition sort of file with all the functions which Apache POI people think that are enough for regular usage of POI library.

o This version will be enough for the current tutorial, as we are not dealing with CTChart classes to generate charts.

o But in upcoming posts of this series we will need the full version of this file which is around a size of 15MB and can be downloaded explicitly from below links –

§ https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas/1.3

§ https://github.com/niks1020/youthhworld/blob/master/Java-Excel%20ApachePOI/ooxml-schemas-1.3.jar

Commons-collection4-(version).jar

Jackson Library URL:

· https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core/2.2.3

· https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind/2.0.0

· https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-annotations

Create a new basic java project in Eclipse. Name it whatever you want.
Create a package inside your src (Say “com”) directory for the sake of some coding standards and conventions: -p.
Create a main executor class from where we will generate the excel file. I have named it Part1.java for now.
For this article I have used this JSON file to read the data: https://github.com/niks1020/youthhworld/blob/master/Java-Excel%20ApachePOI/part-1/testPart1.json
You can save this file in your project folder and customize the data yourself.

Reading the data:

As mentioned earlier, we are using a JSON file which (obviously) contains a JSON array of data in a fixed format. We are using Jackson Library of Java to read the JSON data.

In order to store the JSON data, from the file given above, in java object we have made a JAVA entity/POJO class: https://github.com/niks1020/youthhworld/blob/master/Java-Excel%20ApachePOI/part-1/TestJson.java

This class basically contains all the fields which will store the JSON data in them. Please note that if you are not using the Annotations based mapping between this class fields with the “keys” in JSON data, then name the getter methods of those fields carefully.

Refer this to know more about how Jackson maps the Key of JSON data with Java data: https://stackoverflow.com/questions/22162916/how-does-the-jackson-mapper-know-what-field-in-each-json-object-to-assign-to-a-c

For e.g

If you have a JSON data such as

{
 “name”: “xyz”,
 “city”: “pqr”
}

Then the corresponding Entity class fields which will store the values can be mapped with keys: name and city in two ways as below.

Class EntityClass{

/*-----Jackson will automatically check for the key “name” in JSON data to map with 
-------- this field when you have a getter method for that field */
String name; public String getName(){ return this.name;} 
String city; public String getCity(){return this.city;}

//OR
//Here the value should match exactly with the key of JSON data
@JsonProperty(value="name")
String name;
@JsonProperty(value="city")
String city;
}

Now that the JSON data is ready in JSON file, and we have made the corresponding POJO class to store each object data in that class’ objects. Let’s go ahead read the JSON data to store it in POJO class. We will do this in our main executor class file (Say Part1.java), this file contains main method which we will execute to read the data from JSON and write the data in our workbook.

Note: The link to full version of this file is given at the end of this post, because we have to cover the reading, writing of data part and also the styling code portion.

It only takes below 3 lines of code to read the data from JSON file and to store it inside the List of objects of POJO class.

byte[] json = Files.readAllBytes(Paths.get("testPart1.json"));

  ObjectMapper mapper = new ObjectMapper();

List<TestJson> myObjects = Arrays.asList(mapper.readValue(json, TestJson[].class));

Here in the above code:

At first reading the JSON file data into a byte array using java.nio.Files class’ readAllBytes method. This method also ensure that the file stream is close once all the data is read from the file.

Then we have initialized com.fasterxml.jackson.databind.ObjectMapper object which helps in reading the data from JSON byte array and storing it in to the POJO class objects array.
That’s what we have done in the third line here. We have used readValue method which takes two parameters: 1) Byte array of the json data and 2) Destination class’ value type.

· If the mapping/ field names have been defined perfectly in POJO class then this will do the trick and Array.asList will then convert the array returned by mapper.readValue to List of POJO class’ objects.

Creating the Workbook

Before starting to make the workbook and sheets, here is the link to official documentation of Apache POI, you can refer for below used classes and methods: https://poi.apache.org/apidocs/index.html

First step is now complete i.e we have read the data now second step is to write that in our workbook. Now comes the main part of this post, where we will create a workbook using class org.apache.poi.ss.usermodel.Workbook

Below is the simple code to make a new Workbook:

Workbook wb = new XSSFWorkbook();

We will now create a org.apache.poi.ss.usermodel.Sheet class object using this workbook object.

Sheet sheet = wb.createSheet();

To create a row in this sheet we can use createRow(int rowNumber) method of class org.apache.poi.ss.usermodel.Sheet which takes in an integer as an argument for the rowNumber of that Row Object.

Please note that this argument in this method is having zero based index. Hence to create the first row we will have to pass zero(0) as the argument, One(1) for second and so on. Below is the code for creating first row in our sheet.

Row row = sheet.createRow(0);

In Order to create the header row in our excel file. We have made an array of string which has all the heading row values stored in it.

String[] myHeaders = {"First Name","Last Name","Occupation","City"};

To feed these values we will have to first create the cells in which we can write the data. Now to create a cell in a row, we can use createCell(int n) method to create a cell on that particular row’s (n-1)th column. That means for this method also the indexing is zero based. For e.g: to create a cell in first column in our header row i.e the row object which we have declared above, we can write the below code:

Cell cell1 = row.createRow(0); //Creates cell in first column of this row object

Cell cell2 = row.createRow(1); //Creates cell in second column of this row object

To set value inside this cell, we just have to call setCellValue(String value) method of Cell class which sets the value of that cell in our excel file to the value passed inside as argument. Please note that this method has many overloaded versions with different data types, so we can use those overloaded methods according to the type of data to be set In the excel file and the the type of data to be set for that cell column.

To set value in cell1 object that we created above, we can write the code as below:

cell1.setCellValue(“Hello Shashi!”);

Using the above mentioned methods and objects below is the code snippet to create complete sheet using the JSON data we read in our object.

//=====HEADER ROW VALUES====
String[] myHeaders = {"First Name","Last Name","Occupation","City"};

//====ROW COUNT VARIBALE TO MAINTAIN ROW COUNT THROUGHOUT THE SHEET CODE
 int sheetRowCount = 0; 
  
 Workbook wb = new XSSFWorkbook();
 Sheet sheet = wb.createSheet("FirstSheet");
 
Row row = sheet.createRow(sheetRowCount++);
 
Cell cell= null;
 
 //===========CREATING THE HEADER ROW===============
int cellCounter = 0;
 for(String key: myHeaders) {
  cell = row.createCell(cellCounter);
  cell.setCellValue(myHeaders[cellCounter++]);
 }
 //===========HEADER ROW CREATED==================== 

//========GETTING THE NUMBER OF ROWS UP TO WHICH WE HAVE TO LOOP FOR //WRITING DATA
int numRows = myObjects.size();
  
 //=====WRITING THE DATA BELOW HEADER ROW FROM THE POJO objects
for(int i=0; i<numRows;i++) {
  row = sheet.createRow(sheetRowCount++);

  TestJson currentObject = myObjects.get(i);

  cellCounter = 0;

  for(String key: myHeaders) {
   cell = row.createCell(cellCounter);
  cell.setCellValue(currentObject.getData(myHeaders[cellCounter++]));
  }

 }

To generate the header row, we have just looped through the array of header row data and printed the same.
To write the data we incremented the global row counter variable sheetRowCount and created the next row. We have written a for loop of the number of iterations which equals the size of our POJO class object’s list. Hence that loop will take care of all the data we read from JSON File in POJO class.
While to outer loop takes care of the number of rows to be made, the inner for loop takes care of making cells in those rows and write the data in them. To write the data in the cells with respective columns data we made a getData method where we pass the current column’s header name. In the getData we have just written a simple switch case which sets the return value to the field value of POJO class object, required to be printed in that cell.

Applying border and background-colour style to cells:

Below are two of the methods to apply same border style on a cell on all four side and to apply background colour style to the cell.

//RETURNS CELL STYLE WITH SPECIFIED BORDER STYLE APPLIED TO ALL FOUR SIDE OF CELL
 public static CellStyle getBorderedCellStyle(Workbook wb, BorderStyle bs) {
  CellStyle cellStyle = wb.createCellStyle();
  cellStyle.setBorderTop(bs);
  cellStyle.setBorderBottom(bs);
  cellStyle.setBorderLeft(bs);
  cellStyle.setBorderRight(bs);
  return cellStyle;
 }
 
 //RETURN CELLSTYLE WITH SPECIFIED BORDER STYLE AND BACKGROUND FOREGROUND COLOR
 public static CellStyle getBorderedCellStyleWithBackground(Workbook wb, BorderStyle bs, short foreGround, FillPatternType fpt, short fontColor) {
  
  CellStyle cellStyle = getBorderedCellStyle(wb, bs);
  
  cellStyle.setFillForegroundColor(foreGround);
  cellStyle.setFillPattern(fpt);
  
  Font font = wb.createFont();
  font.setColor(fontColor);
  cellStyle.setFont(font);
  
  return cellStyle;
 }

We have created a separate class names GeneralCode.java which has these methods declared as static method, so that we can apply border and background styling to any cell in the project from anywhere we want.

To apply only thick border styling to a cell we just have to call the first method with thick border styling type passed as argument as below.

cell.setCellStyle(GeneralCode.getBorderedCellStyle(wb, BorderStyle.THICK));

Here we have passed workbook as first argument and the style type Thick of BorderStyle class as the second argument to our custom method which sets all the four side of this cell with thick border.

In order to set a background colour, we can call the second method mentioned above i.e getBorderedCellStyleWithBackground() as below:

cell.setCellStyle(GeneralCode

.getBorderedCellStyleWithBackground(

wb,

BorderStyle.HAIR,

IndexedColors.AQUA.getIndex(),

FillPatternType.SOLID_FOREGROUND,

IndexedColors.BLACK.getIndex()

)

);

This method getBorderedCellStyleWithBackground takes in five arguments:

Workbook of the cell in which we need to apply style
Borderstyle to be applied to cell on all four sides.
Short value of the IndexedColors for background colour of cell. Hence we have called getIndex() on the colour.
FillPatern style for the background colour. The SOLID_FOREGROUND works generally but you can play around different values available in this class.
Short value of IndexColors for font color in the cell. Since we are setting the background colour of cell, we also might need to tweak the font colour to make it properly visible.

This ends our code to write and style the data in our workbook and sheet object.

We can generate this workbook object as excel file using below 3 lines:

FileOutputStream fileOut = new FileOutputStream("BlogDemo.xlsx");
wb.write(fileOut);
fileOut.close();

Additional perks:

We can add a merged region in our sheet using addMergedRegion() method of Sheet class as below:

sheet.addMergedRegion(new CellRangeAddress(5,10,6,11));
  
row = (sheet.getRow(5)==null) ? (sheet.createRow(5)):(sheet.getRow(5));
 cell = (row.getCell(6)==null)?(row.createCell(6)):(row.getCell(6));
  
 cell.setCellValue("THIS IS MERGERD REGION");

We can auto size the columns according the value of cells in that columns using below code:

for(int i=0; i< myHeaders.length; i++)

sheet.autoSizeColumn(i);

Here we just have looper through the sheet on the column indexes needed to be autosized. Hence we have taken myHeaders.length as the loop size. Make sure you put this autosizing code after you are done with writing the values in sheet, before we output our workbook as file.

Link for the full code of main executor file:

https://github.com/niks1020/youthhworld/blob/master/Java-Excel%20ApachePOI/part-1/Part1.java

Below is the snapshot of my excel file generated using above code.

That’s it for this post guys. Hope you understood the methods and classes calls exactly as what I wanted to explain. But if there is any doubt or any improvement you need to suggest in the code then feel free to comment below.

Thanks for reading. Have a good day, Happy learning, Happy Coding, Take care.

Search This Blog

YOUTH WORLD BLOG