Course – LS – All

Get started with Spring and Spring Boot, through the Learn Spring course:

>> CHECK OUT THE COURSE

1. Overview

In this tutorial, we’ll learn how to add borders to an Excel sheet using the Apache POI Java library.

For more basics on excel handling, we can start with working with Microsoft Excel in Java.

2. Excel Borders

We can create borders to an excel cell or for a range of cells. These borderlines can be in a variety of styles. Some example styles include thick lines, thin lines, medium lines, dotted lines. To add more variety, we can have colored borders.

This image shows some of these variety borders:

ExcelCellBorders
  • Cell B2 is with thick line border
  • D2 cell is with a wide violet border
  • F2 cell is with a crazy border, each side of the border is with different style and color
  • Range B4:F6 is with medium-sized border
  • Region B8:F9 is with medium-sized orange border

3. Coding for the Excel Borders

The Apache POI library provides multiple ways to handle borders. One simple way is to refer to cell ranges and apply borders.

3.1. Cell Ranges or Regions

To refer to a range of cells we can use CellRangeAddress class:

CellRangeAddress region = new CellRangeAddress(7, 8, 1, 5);

CellRangeAddress constructor takes four parameters first row, last row, first column, and last column. Each row and column index starts with zero. In above code, it refers to cell range B8:F9.

We can also refer to one cell using CellRangeAddress class:

CellRangeAddress region = new CellRangeAddress(1, 1, 5, 5);

The above code is referring to the F2 cell.

3.2. Cell Borders

Each border has four sides: Top, Bottom, Left, and Right borders. We have to set each side of the border style separately. BorderStyle class provides a variety of styles.

We can set borders using RangeUtil class:

RegionUtil.setBorderTop(BorderStyle.DASH_DOT, region, sheet);
RegionUtil.setBorderBottom(BorderStyle.DOUBLE, region, sheet);
RegionUtil.setBorderLeft(BorderStyle.DOTTED, region, sheet);
RegionUtil.setBorderRight(BorderStyle.SLANTED_DASH_DOT, region, sheet);

3.3. Border Colors

Border colors also have to be set separately on each side. IndexedColors class provides a range of colors to use.

We can set border colors using RangeUtil class:

RegionUtil.setTopBorderColor(IndexedColors.RED.index, region, sheet);
RegionUtil.setBottomBorderColor(IndexedColors.GREEN.index, region, sheet);
RegionUtil.setLeftBorderColor(IndexedColors.BLUE.index, region, sheet);
RegionUtil.setRightBorderColor(IndexedColors.VIOLET.index, region, sheet);

4. Conclusion

In this short article, we have seen how to generate a variety of cell borders using CellRangeAddress, RegionUtil, BorderStyles, and IndexedColors classes. Each side of the border has to be set separately.

As usual, the code and tests are available over on GitHub.

Course – LS – All

Get started with Spring and Spring Boot, through the Learn Spring course:

>> CHECK OUT THE COURSE
res – REST with Spring (eBook) (everywhere)
Comments are closed on this article!