Set Excel Cell Border Color - Java POI Example Program

Color Cell Borders - Introduction


In this Apache POI tutorial, we will discuss how to apply colors to your Excel Cell borders, with suitable examples. POI provides four separate methods to set cell borders:

  • setBottomBorderColor - to set the color of the bottom border
  • setTopBorderColor - to set the color of the top border
  • setRightBorderColor - to set the color of the right border
  • setLeftBorderColor - to set the color of the left border.
We will draw a thick cell border to our cell and discuss how to set different colors to the borders. This tutorial provides separate examples to handle XLS and XLSX format files. So, you can use the one that is relevant to you.

XLS - Change Cell Border Color - Java Example Program


In case of .XLS files, we use org.apache.poi.ss.usermodel.IndexedColors to specify the border color we want to set for a cell. Here is a Java program example that sets different border colors to a cell and creates a .XLS file in the output.
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.ss.usermodel.IndexedColors;
public class ChangeCellBorderColor {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Border Color");
                /* Get access to HSSFCellStyle */
                HSSFCellStyle my_style = my_workbook.createCellStyle();
                
                /* First, let us draw a thick border so that the color is visible */            
                my_style.setBorderLeft(HSSFCellStyle.BORDER_THICK);             
                my_style.setBorderRight(HSSFCellStyle.BORDER_THICK);            
                my_style.setBorderTop(HSSFCellStyle.BORDER_THICK);              
                my_style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
                
                /* We will use IndexedColors to specify colors to the border */
                /* bottom border color */
                my_style.setBottomBorderColor(IndexedColors.BLUE.getIndex());
                /* Top border color */
                my_style.setTopBorderColor(IndexedColors.RED.getIndex());
                /* Left border color */
                my_style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
                /* Right border color */
                my_style.setRightBorderColor(IndexedColors.PINK.getIndex());
                
                /* Attach border colors to a cell */
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue("Different border colors for a Cell");                
                cell.setCellStyle(my_style);
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_border_color.xls"));
                my_workbook.write(out);
                out.close();
        }
}

The output of this program is shown below:

XLS - Change Cell Border Color - Java POI Example - Output
XLS - Change Cell Border Color - Java POI Example - Output

 XLSX - Set Border Colors to Cells - Java Program Example


When it comes to XLSX files, changing border colors works differently. XSSFCellStyle has one method setBorderColor which takes two parameters: the side of the border and the color to be applied. It uses these two parameters to generate the border color. Let us now write a simple Java program that will describe how to set border color for XLSX files using setBorderColor method.
import java.io.*;
import org.apache.poi.xssf.usermodel.*; 
import org.apache.poi.ss.usermodel.*;
/* To define the border side */
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
/* to define the color */
import org.apache.poi.xssf.usermodel.XSSFColor;
import java.awt.Color;
public class ChangeCellBorderColorXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet XLSX Format */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Cell Border Color");
                /* Get access to XSSFCellStyle */
                XSSFCellStyle my_style = my_workbook.createCellStyle();
                
                /* First, let us draw a thick border so that the color is visible */            
                my_style.setBorderLeft(XSSFCellStyle.BORDER_THICK);             
                my_style.setBorderRight(XSSFCellStyle.BORDER_THICK);            
                my_style.setBorderTop(XSSFCellStyle.BORDER_THICK);              
                my_style.setBorderBottom(XSSFCellStyle.BORDER_THICK);
                
                my_style.setBorderColor(BorderSide.LEFT,new XSSFColor(Color.RED));
                my_style.setBorderColor(BorderSide.RIGHT,new XSSFColor(Color.BLUE));
                my_style.setBorderColor(BorderSide.TOP,new XSSFColor(Color.GREEN));
                my_style.setBorderColor(BorderSide.BOTTOM,new XSSFColor(Color.PINK));
                
                /* Create a row in the sheet */
                Row row = my_sheet.createRow(0);
                /* Create a cell */
                Cell cell = row.createCell(0);
                cell.setCellValue("Cell Border Color Test");
                /* Attach the style to the cell */
                cell.setCellStyle(my_style);
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_border_color.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

If you look at the setBorderColor method above, you can realize that we have incorporated a different approach when it comes to setting border color for XLSX files. The output of the program is provided below:
XLSX - Cell Border Color - Java POI Example Program - Output
XLSX - Cell Border Color - Java POI Example Program - Output
Again, XLSX offers every single color known to us to be set as a cell border, unlike XLS where the colors are fixed. Try these codes at your own pace, and if you have any questions, post it in the comments section.

No comments:

Post a Comment