ThinkTibits!: Apache POI Excel Gradient Fill Example

In this tutorial, we discuss how to set Gradient Fill on Excel Cells using Apache POI / Java with example programs. Filling cell background with gradient color pattern is not available in POI at the time of writing, so we have to extend POI with the support of OOXML. This tutorial is applicable for XLSX formats only and includes the following sections:

Gradient Fill – Basic Example
Gradient Fill – Shading Styles

Horizontal
Vertical
Diagonal Up
Diagonal Down
From Corner
From Center

Gradient Fill – Theme Colours / Standard Colors

Excel Gradient Fill - POI – Basic Example

List of steps involved in creating a gradient fill is captured below:

Excel Gradient Fill - POI - List of Steps

Create Workbook / Cell Data:

In this step, we read an existing workbook where you want to apply a Gradient Fill. We also create a Cell inside a worksheet here, on which we will create a gradient background. Java code for this step is provided below:

                /* Read Workbook and Access Worksheet */
                FileInputStream input_document = new FileInputStream(new File("c2.xlsx"));    
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
                XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0); 
                Row row = sheet.createRow(0); //create Row
                XSSFCell cell1 = (XSSFCell) row.createCell(0);//Create Cell for Gradient Fill
                cell1.setCellValue("Gradient Fill"); //Cell Data

Create Gradient Fill:

We can now create a gradient fill with CTFill object. You create a new instance of CTFill and then use addNewGradientFill method to create CTGradientFill object. From here, you can define the type of Gradient and set gradient parameters (more on this later). Finally, you can add the colors for your gradient background using CTGradientStop and CTColor object. You can select two colors for your gradient combination, and pass RGBA values of your gradient combination into it.

/* Get the Style Sheet - We have to add Gradient Fill here */
CTStylesheet wbStyle=my_xlsx_workbook.getStylesSource().getCTStylesheet();
long aLong = wbStyle.getFills().getCount();
/* Current Fill Count Value */
int anInt = new BigDecimal(aLong).intValueExact();
/* Add Fills with Gradient Information*/
CTFill myGradientFill= CTFill.Factory.newInstance(); ///* Create a Gradient fill                                                
CTGradientFill gFill=myGradientFill.addNewGradientFill(); ///* Set Type, left, right, top and bottom values                             
Double d=new Double("0.5");
gFill.setType(STGradientType.PATH);
gFill.setLeft(d);
gFill.setRight(d);
gFill.setTop(d);
gFill.setBottom(d);
/* Add Stop Position */
CTGradientStop pos0=gFill.addNewStop();
pos0.setPosition(new Double("0"));
/* Set Color 1 */
CTColor stopPositionColor=pos0.addNewColor();
stopPositionColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFFF0000"));                          
/* Add Stop Position */
CTGradientStop pos1=gFill.addNewStop();
pos1.setPosition(new Double("1"));
/* Set Color 2 */
CTColor stopPositionColor1=pos1.addNewColor();
stopPositionColor1.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFCCECFF"));

Modify Style Sheet with Gradient Fill

You have to update the style sheet with the gradient fill you created in the earlier step. You first create XSSFCellFill object, with the gradient information created earlier. Once this done, you attach XSSFCellFill object to the workbook style sheet using putFill method as shown below:

XSSFCellFill myCellFill= new XSSFCellFill(myGradientFill);
my_xlsx_workbook.getStylesSource().putFill(myCellFill);

Attach Fill to CTXf

In this step, you create a CTXf object and attach the gradient fill you created in step#2 as an attribute to this object. You also set other attributes for this object, and include this into the stylesheet using putCellXf method as shown below:

CTXf addCTX=CTXf.Factory.newInstance(); 
addCTX.setNumFmtId(0L);
addCTX.setFontId(0L);
addCTX.setFillId((long)anInt);
addCTX.setBorderId(0L);
addCTX.setXfId(0L);
addCTX.setApplyFill(true);


my_xlsx_workbook.getStylesSource().putCellXf(addCTX);

Apply Gradient Fill to Cell

You are now ready to apply the Gradient Fill to Cell. You use the method setS in CTCell to pass the reference to CTXf you created in the earlier step. Note that the value you pass to setS should be one less than the value you set in setFilld code. Otherwise, your gradient will not work. (And I don’t know why). This step applies the gradient background to your Cell.

cell1.getCTCell().setS(1L);

Update Workbook with Gradient Style

Finally, you can now write the output to a file and see your gradient background in action. The Java code for this bit is provided below, and the gradient background created by the code is also shown after.

/* Write changes to the workbook */
FileOutputStream out = new FileOutputStream(new File("c22.xlsx"));
my_xlsx_workbook.write(out);
out.close();

Gradient Fill Excel - Java Output

Download Full Code for this Example.

Gradient Fill – Shading Styles

You can apply different type of gradient fills , like what Excel has to offer as shown below, in POI.

Shading Styles - Gradient Fill - POI Example

We discuss how to make small tweaks to your Java code to get these Fills working.

Gradient – Horizontal Shading

In horizontal shading option, you can get three variants of the gradient fill to your original setup. Java Code change required is provided below:

Horizontal Shading Gradient - Variant #1

You have set the degree to 90 in CTGradientFill object. Rest of the code is intact as shown below:

/* Gradient with horizontal shading - Just set degree to 90 */
CTFill myGradientFill= CTFill.Factory.newInstance(); ///* Create a Gradient fill                                                
CTGradientFill gFill=myGradientFill.addNewGradientFill(); ///* Set Type, left, right, top and bottom values                             
Double d=new Double("90");
gFill.setDegree(d);                             
/* Add Stop Position */
CTGradientStop pos0=gFill.addNewStop();
pos0.setPosition(new Double("0"));
/* Set Color 1 */
CTColor stopPositionColor=pos0.addNewColor();
stopPositionColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFA50021"));                          
/* Add Stop Position */
CTGradientStop pos1=gFill.addNewStop();
pos1.setPosition(new Double("1"));
/* Set Color 2 */
CTColor stopPositionColor1=pos1.addNewColor();
stopPositionColor1.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFFFFFFF"));

Horizontal Shading Gradient - Variant #2

Same code as Variant #1, just change the degree to 270 instead of 90.

Horizontal Shading Gradient - Variant #3

This one is tricky. You have to make multiple changes to your POI code to get this done.

a) Set the degree to 90 as usual
b) Add three stop positions, 0 0.5 and 1. The color on stop positions 0 and 1 should be same as shown below:

The Java Code is provided below

/* Gradient with horizontal shading - Multiple Stop Positions*/
CTFill myGradientFill= CTFill.Factory.newInstance(); ///* Create a Gradient fill                                                
CTGradientFill gFill=myGradientFill.addNewGradientFill(); ///* Set Type, left, right, top and bottom values                             
Double d=new Double("90");
gFill.setDegree(d);                             
/* Add Stop Position */
CTGradientStop pos0=gFill.addNewStop();
pos0.setPosition(new Double("0"));
/* Set Color 1 */
CTColor stopPositionColor=pos0.addNewColor();
stopPositionColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFA50021"));                          
/* Add Stop Position */
CTGradientStop pos1=gFill.addNewStop();
pos1.setPosition(new Double("0.5"));
/* Set Color 2 */
CTColor stopPositionColor1=pos1.addNewColor();
stopPositionColor1.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFFFFFFF"));
/* Add Stop Position */
CTGradientStop pos2=gFill.addNewStop();
pos2.setPosition(new Double("1"));
/* Set Color 2 */
CTColor stopPositionColor2=pos2.addNewColor();
stopPositionColor2.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFA50021"));

The output across all the variants of this gradient fill is shown below:

POI - Gradient Fill - Horizontal Shading - Example

Gradient – Vertical Shading

Similar to horizontal shading, you get three variants in vertical shading also. You have to make minor modifications to your gradient fill from the horizontal shading option to get these variants. These are shown below:

Vertical Shading Gradient - Variant #1

Just remove the setDegree method and you are done. You get a vertical shading, Java code is provided below:

/* Gradient with Vertical shading */
CTFill myGradientFill= CTFill.Factory.newInstance(); ///* Create a Gradient fill                                                
CTGradientFill gFill=myGradientFill.addNewGradientFill(); 
/* Add Stop Position */
CTGradientStop pos0=gFill.addNewStop();
pos0.setPosition(new Double("0"));
/* Set Color 1 */
CTColor stopPositionColor=pos0.addNewColor();
stopPositionColor.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFA50021"));                          
/* Add Stop Position */
CTGradientStop pos1=gFill.addNewStop();
pos1.setPosition(new Double("0.5"));
/* Set Color 2 */
CTColor stopPositionColor1=pos1.addNewColor();
stopPositionColor1.setRgb(javax.xml.bind.DatatypeConverter.parseHexBinary("FFFFFFFF"));

Vertical Shading Gradient - Variant #2

Same as the horizontal option, just set degrees to 180 in this case.

Vertical Shading Gradient - Variant #3

Add multiple stop points as in the horizontal option, and do not set the degree option.

The output in all these cases is shown below. You can see three different vertical gradient fills from the screenshot:

POI Gradient Fill Vertical Shading Example

Gradient – Diagonal Up Shading

This section talks about implementing gradient fills with a diagonal fill approach in POI – and there are three variants in this approach as well. You have to follow the example given in the horizontal shading section with the changes listed below:

Variant #1

Set degree to 45.

Variant #2

Set degree to 225

Variant #3

Set Degree to 45 and add multiple stop points.

The output in all these cases is shown below:

POI Gradient Fill - Diagonal Up- Example Output

Gradient – Diagonal Down Shading

In diagonal down shading, you have to make small changes to your horizontal shading option as provided below to get the desired gradient fill with POI. These are listed below:

Variant #1

Set degree to 135.

Variant #2

Set degree to 315

Variant #3

Set Degree to 135 and add multiple stop points.

The output in all these cases is shown below:

POI Gradient Fill - Diagonal Down - Example Output

Gradient Fill- From Corner Shading

This is different to other shading options that we saw earlier. It has four variants as well as shown in the screenshot below:

POI Gradient Fill - From Corner - Example

Variant#1

set type=path

Variant#2

Set type=path, left=1, right=1

Variant#3

Set type=path, top=1, bottom=1

Variant#4

Set type=path, left=1, right=1, top=1, bottom=1

Gradient Fill – From Center Shading

This is same as our original example. Follow the full code given earlier if you want to implement center shading Gradient fill in Apache POI.

Gradient Fill – Theme Colours / Standard Colors

In all the examples we have provided earlier, we have used standard colors as gradient colors. These are colors with a RGB values. If you have a requirement to use “theme” based colors, you have to modify the original code slightly to get the outcome.

Hope this tutorial has given you plenty of examples for creating gradient color backgrounds to cell with Apache POI. Give a try and post us a comment if you need any help.

Apache POI Excel Gradient Fill Example