Add borders to cells in POI generated Excel File

Question

I am using POI to generate an Excel File. I need to add borders to specific cells in the worksheet.

How can I accomplish this?

score 66 · Accepted Answer · answered Apr 19 '11 at 17:09

66

Setting up borders in the style used in the cells will accomplish this. Example:

style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

answered Apr 19 '11 at 17:09

jzd

23,473
9
54
76

1

This worked for me, but I am still open to better solutions. Especially ones that don't involve new styles just to change the border. – jzd Apr 19 '11 at 17:11
Styles are the most common way to do it, Excel generally does these sorts of things with either styles or drawings. – Gagravarr Apr 19 '11 at 22:44
@Gagravarr, thanks. I guess it just seemed like a pain to have to have to have two styles for the same text, but one that has border and one that doesn't. Especially if you wanted a stronger border around the outer edges. I was hoping there was a better way. – jzd Apr 20 '11 at 12:34
The only thing I can suggest is try doing it in Excel, and see what Excel itself does in the file. I've a strong suspicion that you'll see that Excel'll create styles to do it... – Gagravarr Apr 20 '11 at 14:03

score 37 · Answer 2 · answered Mar 08 '17 at 13:07

37

In the newer apache poi versions:

XSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.MEDIUM);
style.setBorderBottom(BorderStyle.MEDIUM);
style.setBorderLeft(BorderStyle.MEDIUM);
style.setBorderRight(BorderStyle.MEDIUM);

answered Mar 08 '17 at 13:07

Mathias G.

4,875
3
39
60

score 25 · Answer 3 · edited Feb 15 '12 at 16:35

25

HSSFCellStyle style=workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

edited Feb 15 '12 at 16:35

Sapan Diwakar

10,480
6
33
43

answered Feb 15 '12 at 10:18

swamy

1,200
10
23

winklerrr · Answer 4 · 2021-09-09T10:42:00.283

XSSF

BorderStyle

Use XSSFCellStyle.BORDER_MEDIUM or XSSFBorderFormatting.BORDER_MEDIUM (both enums refer to the same value):

final XSSFCellStyle cellStyle = workbook.createCellStyle();

cellStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);

cell.setCellStyle(cellStyle);

BorderColor

Use setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, XSSFColor) or setBottomBorderColor(XSSFColor) (equivalent for top, left, right):

final XSSFCellStyle cellStyle = workbook.createCellStyle();
final XSSFColor color = new XSSFColor(new java.awt.Color(128, 0, 128));

cellStyle.setTopBorderColor(color);
cellStyle.setRightBorderColor(color);
cellStyle.setBottomBorderColor(color);
cellStyle.setLeftBorderColor(color);

cell.setCellStyle(cellStyle);

Great job @winklerrr finally someone who reads questions! It is XSSF not HSSF — Stosh15, Jun 03 '16 at 17:36

score 15 · Answer 5 · answered Feb 20 '17 at 10:53

a Helper function:

private void setRegionBorderWithMedium(CellRangeAddress region, Sheet sheet) {
        Workbook wb = sheet.getWorkbook();
        RegionUtil.setBorderBottom(CellStyle.BORDER_MEDIUM, region, sheet, wb);
        RegionUtil.setBorderLeft(CellStyle.BORDER_MEDIUM, region, sheet, wb);
        RegionUtil.setBorderRight(CellStyle.BORDER_MEDIUM, region, sheet, wb);
        RegionUtil.setBorderTop(CellStyle.BORDER_MEDIUM, region, sheet, wb);
    }

When you want to add Border in Excel, then

String cellAddr="$A$11:$A$17";

setRegionBorderWithMedium(CellRangeAddress.valueOf(cellAddr1), sheet);

score 4 · Answer 6 · answered Apr 15 '20 at 21:00

If you're using the org.apache.poi.ss.usermodel (not HSSF or XSSF) you can use:

style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);

all the border styles are here at the apache documentation

score 3 · Answer 7 · answered Dec 07 '18 at 09:54

3

From Version 4.0.0 on RegionUtil-methods have a new signature. For example:

RegionUtil.setBorderBottom(BorderStyle.DOUBLE,
            CellRangeAddress.valueOf("A1:B7"), sheet);

answered Dec 07 '18 at 09:54

Martin Pabst

861
11
9

score 1 · Answer 8 · answered Apr 20 '17 at 15:50

To create a border in Apache POI you should...

1: Create a style

final XSSFCellStyle style = workbook.createCellStyle();

2: Then you have to create the border

style.setBorderBottom( new XSSFColor(new Color(235,235,235));

 3: Then you have to set the color of that border

style.setBottomBorderColor( new XSSFColor(new Color(235,235,235));

4: Then apply the style to a cell

cell.setCellStyle(style);

Add borders to cells in POI generated Excel File

8 Answers8

XSSF

BorderStyle

BorderColor

Linked