HSSFWorkbook wb = new HSSFWorkbook("Prabhjot XL File.xls");
HSSFSheet sheet = wb.getSheetAt(sheetNumber);
HSSFCellStyle style = wb.createCellStyle();
row = sheet.createRow(0); //Creating First Row
cell = row.createCell(0); //Creating First Cell
//Changing Font :
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)8);
font.setFontName("Arial");
style.setFont(font);
//Changing Border Color and Style :
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLUE.getIndex());
//Changing Cell color
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); // Explained in detail below.
cell.setCellStyle(style); //Setting Cell Style.
cell.setCellValue("Hi"); //Setting Cell Value.
//Adding Validation For Cell values from 0-9999 only
DVConstraint dvConstraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,DVConstraint.OperatorType.BETWEEN, "0", "9999");
HSSFDataValidation dataValidation;
CellRangeAddressList addressList;
addressList = new CellRangeAddressList(startRow, endRow, startCell, endCell);
dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setErrorStyle(HSSFDataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Invalid Input", "This cell can contain only whole numbers between 0-9999.");
sheet.addValidationData(dataValidation);
//Adding Conditional Formatting To The Cell. Format when cell's value is not equal to 0.
HSSFSheetConditionalFormatting sheetCF=sheet.getSheetConditionalFormatting();
HSSFConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.NOT_EQUAL, "0",null );
rule.createPatternFormatting().setFillBackgroundColor(HSSFColor.LIGHT_ORANGE.index); // Create pattern with Orange background
CellRangeAddress[] cra = {new CellRangeAddress(startRow, endRow, startCell, endCell) }; // Define a region containing this cell
sheetCF.addConditionalFormatting(cra, rule); // Apply Conditional Formatting rule defined above to the regions
The issue with changing cell color is that Excel does not support all the colors which we usually pick through a color picker. To change the cell color through POI we need to pass a "short" data type value to the "style.setFillForegroundColor()" function. But who knows what value belongs to which color? I mean, can you guess which of CORNFLOWER_BLUE or LIGHT_CORNFLOWER_BLUE or LIGHT_BLUE or AQUA will look good on the cell? So here is the list of the values and the colors they resemble.
