Skip to content

Commit d12fe37

Browse files
SeshuTechieSeshu Thanneeru
SeshuTechie
and
Seshu Thanneeru
authored
Excel Cell Border Example (eugenp#11472)
Co-authored-by: Seshu Thanneeru <seshukumar.thanneeru@thoughtdata.com>
1 parent 23244cf commit d12fe37

2 files changed

Lines changed: 167 additions & 0 deletions

File tree

‎apache-poi/src/main/java/com/baeldung/poi/excel/cellstyle/CellBordersHandler.java‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
package com.baeldung.poi.excel.cellstyle;
2+
3+
import org.apache.poi.ss.usermodel.*;
4+
import org.apache.poi.ss.util.CellRangeAddress;
5+
import org.apache.poi.ss.util.RegionUtil;
6+
7+
public class CellBordersHandler {
8+
9+
public void setRegionBorder(CellRangeAddress region, Sheet sheet, BorderStyle borderStyle) {
10+
RegionUtil.setBorderTop(borderStyle, region, sheet);
11+
RegionUtil.setBorderBottom(borderStyle, region, sheet);
12+
RegionUtil.setBorderLeft(borderStyle, region, sheet);
13+
RegionUtil.setBorderRight(borderStyle, region, sheet);
14+
}
15+
16+
public void setRegionBorderWithColor(CellRangeAddress region, Sheet sheet, BorderStyle borderStyle, short color) {
17+
RegionUtil.setTopBorderColor(color, region, sheet);
18+
RegionUtil.setBottomBorderColor(color, region, sheet);
19+
RegionUtil.setLeftBorderColor(color, region, sheet);
20+
RegionUtil.setRightBorderColor(color, region, sheet);
21+
RegionUtil.setBorderTop(borderStyle, region, sheet);
22+
RegionUtil.setBorderBottom(borderStyle, region, sheet);
23+
RegionUtil.setBorderLeft(borderStyle, region, sheet);
24+
RegionUtil.setBorderRight(borderStyle, region, sheet);
25+
}
26+
27+
public void setCrazyBorder(CellRangeAddress region, Sheet sheet) {
28+
RegionUtil.setTopBorderColor(IndexedColors.RED.index, region, sheet);
29+
RegionUtil.setBottomBorderColor(IndexedColors.GREEN.index, region, sheet);
30+
RegionUtil.setLeftBorderColor(IndexedColors.BLUE.index, region, sheet);
31+
RegionUtil.setRightBorderColor(IndexedColors.VIOLET.index, region, sheet);
32+
RegionUtil.setBorderTop(BorderStyle.DASH_DOT, region, sheet);
33+
RegionUtil.setBorderBottom(BorderStyle.DOUBLE, region, sheet);
34+
RegionUtil.setBorderLeft(BorderStyle.DOTTED, region, sheet);
35+
RegionUtil.setBorderRight(BorderStyle.SLANTED_DASH_DOT, region, sheet);
36+
}
37+
}

‎apache-poi/src/test/java/com/baeldung/poi/excel/cellstyle/CellBorderHandlerUnitTest.java‎

Lines changed: 130 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,130 @@
1+
package com.baeldung.poi.excel.cellstyle;
2+
3+
import org.apache.poi.ss.usermodel.*;
4+
import org.apache.poi.ss.util.CellRangeAddress;
5+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
6+
import org.junit.*;
7+
8+
import java.io.IOException;
9+
import java.net.URISyntaxException;
10+
import java.nio.file.Paths;
11+
12+
import static org.junit.Assert.assertEquals;
13+
14+
public class CellBorderHandlerUnitTest {
15+
private static final String FILE_NAME = "cellstyle/CellStyleHandlerTest.xlsx";
16+
private static final int SHEET_INDEX = 0;
17+
18+
private static CellBordersHandler cellBordersHandler;
19+
private static Workbook workbook;
20+
21+
@BeforeClass
22+
public static void setup() throws URISyntaxException, IOException {
23+
String fileLocation = Paths.get(ClassLoader.getSystemResource(FILE_NAME).toURI()).toString();
24+
cellBordersHandler = new CellBordersHandler();
25+
workbook = new XSSFWorkbook(fileLocation);
26+
createRowsAndCells(workbook);
27+
}
28+
29+
private static void createRowsAndCells(Workbook workbook) {
30+
Sheet sheet = workbook.getSheetAt(SHEET_INDEX);
31+
for (int rowIndex = 0; rowIndex < 10; rowIndex++) {
32+
Row row = sheet.getRow(rowIndex);
33+
if (row == null) {
34+
row = sheet.createRow(rowIndex);
35+
}
36+
for (int colIndex = 0; colIndex < 10; colIndex++) {
37+
Cell cell = row.getCell(colIndex);
38+
if (cell == null) {
39+
row.createCell(colIndex);
40+
}
41+
}
42+
}
43+
}
44+
45+
@Test
46+
public void givenWorkbookCell_whenSetRegionBorder() {
47+
Sheet sheet = workbook.getSheetAt(SHEET_INDEX);
48+
49+
CellRangeAddress region = new CellRangeAddress(1, 1, 1, 1);
50+
cellBordersHandler.setRegionBorder(region, sheet, BorderStyle.THICK);
51+
52+
Row row = sheet.getRow(1);
53+
Cell cell = row.getCell(1);
54+
assertEquals(cell.getCellStyle().getBorderTop(), BorderStyle.THICK);
55+
assertEquals(cell.getCellStyle().getBorderBottom(), BorderStyle.THICK);
56+
assertEquals(cell.getCellStyle().getBorderLeft(), BorderStyle.THICK);
57+
assertEquals(cell.getCellStyle().getBorderRight(), BorderStyle.THICK);
58+
}
59+
60+
@Test
61+
public void givenWorkbookCell_whenSetRegionBorderWithColor() {
62+
Sheet sheet = workbook.getSheetAt(SHEET_INDEX);
63+
64+
CellRangeAddress region = new CellRangeAddress(1, 1, 3, 3);
65+
cellBordersHandler.setRegionBorderWithColor(region, sheet, BorderStyle.THICK, IndexedColors.MAROON.index);
66+
67+
Row row = sheet.getRow(1);
68+
Cell cell = row.getCell(1 + 2);
69+
assertEquals(cell.getCellStyle().getBorderTop(), BorderStyle.THICK);
70+
assertEquals(cell.getCellStyle().getBorderBottom(), BorderStyle.THICK);
71+
assertEquals(cell.getCellStyle().getBorderLeft(), BorderStyle.THICK);
72+
assertEquals(cell.getCellStyle().getBorderRight(), BorderStyle.THICK);
73+
assertEquals(cell.getCellStyle().getTopBorderColor(), IndexedColors.MAROON.index);
74+
assertEquals(cell.getCellStyle().getBottomBorderColor(), IndexedColors.MAROON.index);
75+
assertEquals(cell.getCellStyle().getLeftBorderColor(), IndexedColors.MAROON.index);
76+
assertEquals(cell.getCellStyle().getRightBorderColor(), IndexedColors.MAROON.index);
77+
}
78+
79+
@Test
80+
public void givenWorkbookCell_whenSetCrazyBorder() {
81+
Sheet sheet = workbook.getSheetAt(SHEET_INDEX);
82+
83+
CellRangeAddress region = new CellRangeAddress(1, 1, 5, 5);
84+
cellBordersHandler.setCrazyBorder(region, sheet);
85+
86+
Row row = sheet.getRow(1);
87+
Cell cell = row.getCell(5);
88+
assertEquals(cell.getCellStyle().getBorderTop(), BorderStyle.DASH_DOT);
89+
assertEquals(cell.getCellStyle().getBorderBottom(), BorderStyle.DOUBLE);
90+
assertEquals(cell.getCellStyle().getBorderLeft(), BorderStyle.DOTTED);
91+
assertEquals(cell.getCellStyle().getBorderRight(), BorderStyle.SLANTED_DASH_DOT);
92+
assertEquals(cell.getCellStyle().getTopBorderColor(), IndexedColors.RED.index);
93+
assertEquals(cell.getCellStyle().getBottomBorderColor(), IndexedColors.GREEN.index);
94+
assertEquals(cell.getCellStyle().getLeftBorderColor(), IndexedColors.BLUE.index);
95+
assertEquals(cell.getCellStyle().getRightBorderColor(), IndexedColors.VIOLET.index);
96+
}
97+
98+
@Test
99+
public void givenWorkbookRegion_whenSetRegionBorder() {
100+
Sheet sheet = workbook.getSheetAt(SHEET_INDEX);
101+
102+
CellRangeAddress region = new CellRangeAddress(3, 5, 1, 5);
103+
cellBordersHandler.setRegionBorder(region, sheet, BorderStyle.MEDIUM);
104+
105+
Row row = sheet.getRow(3);
106+
Cell cell = row.getCell(1);
107+
assertEquals(cell.getCellStyle().getBorderTop(), BorderStyle.MEDIUM);
108+
assertEquals(cell.getCellStyle().getBorderLeft(), BorderStyle.MEDIUM);
109+
}
110+
111+
@Test
112+
public void givenWorkbookRegion_whenSetRegionBorderWithColor() {
113+
Sheet sheet = workbook.getSheetAt(SHEET_INDEX);
114+
115+
CellRangeAddress region = new CellRangeAddress(7, 8, 1, 5);
116+
cellBordersHandler.setRegionBorderWithColor(region, sheet, BorderStyle.MEDIUM, IndexedColors.ORANGE.index);
117+
118+
Row row = sheet.getRow(7);
119+
Cell cell = row.getCell(1);
120+
assertEquals(cell.getCellStyle().getBorderTop(), BorderStyle.MEDIUM);
121+
assertEquals(cell.getCellStyle().getBorderLeft(), BorderStyle.MEDIUM);
122+
assertEquals(cell.getCellStyle().getTopBorderColor(), IndexedColors.ORANGE.index);
123+
assertEquals(cell.getCellStyle().getLeftBorderColor(), IndexedColors.ORANGE.index);
124+
}
125+
126+
@AfterClass
127+
public static void close() throws IOException {
128+
workbook.close();
129+
}
130+
}

0 commit comments

Comments
 (0)