How to apply border styles to NPOI workbook cells

Question

I didn't find this information anywhere on the internet so thought I would post here as I found the answer myself.

I want to create cells in a spreadsheet, using NPOI, that have borders, and it isn't obvious how to do that.

score 11 · Accepted Answer · answered Sep 27 '17 at 11:01

Here is some sample code for creating a cell style to apply to cells so that they have a border.

// create workbook, sheet and a row
HSSFWorkbook wb = new HSSFWorkbook();
var sheet = wb.CreateSheet("Sheet1");
var row = sheet.CreateRow(0);

// create font style
HSSFFont myFont = (HSSFFont)wb.CreateFont();
myFont.FontHeightInPoints = (short)11;
myFont.FontName = "Tahoma";

// create bordered cell style
HSSFCellStyle borderedCellStyle = (HSSFCellStyle)wb.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
borderedCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
borderedCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium;
borderedCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;

// create standard cell style
HSSFCellStyle standardCellStyle = (HSSFCellStyle)wb.CreateCellStyle();
standardCellStyle.SetFont(myFont);

// create cell and apply bordered style
var cell = row.CreateCell(0);
cell.SetCellValue("I have a border");
cell.CellStyle = borderedCellStyle;

// create cell and apply standard stlye
var cell = row.CreateCell(1);
cell.SetCellValue("I have NO border");
cell.CellStyle = standardCellStyle;

If you are not using HSSF, or are possibly processing either HSSF or XSSF you could cast to ICellStyle: ICellStyle borderedCellStyle = (ICellStyle)wb.CreateCellStyle(); — usr-bin-drinking, Feb 21 '19 at 16:13

score 2 · Answer 2 · answered Sep 27 '17 at 14:05

For XSSF file. Take this approach

XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Demo");
XSSFCellStyle defaultStyle = (XSSFCellStyle)workbook.CreateCellStyle();
defaultStyle.WrapText = true;
defaultStyle.Alignment = HorizontalAlignment.Left;
defaultStyle.VerticalAlignment = VerticalAlignment.Top;
defaultStyle.BorderBottom = BorderStyle.Thin;
defaultStyle.BorderTop = BorderStyle.Thin;
defaultStyle.BorderLeft = BorderStyle.Thin;
defaultStyle.BorderRight = BorderStyle.Thin;

sheet.SetColumnWidth(colIndex,   100);  
sheet.SetDefaultColumnStyle(colIndex, defaultStyle);

How to apply border styles to NPOI workbook cells

2 Answers2