java - Apply Fill Colors and Borders to Excel Range Using Apache POI

Question

I am working on converting an Excel VBA Script to Java using Apache POI and I am stuck. In VBA it is easy to apply both Fill Colors and Borders to cells in succession. But in POI it seems almost impossible without creating hundreds of XSSFCellStyles [(Every Color Used) x (Every Border Combination) x (Every Font Used)]. I am trying to recreate this spreadsheet, which is currently produced via VBA: Spreadsheet Image

The code below is intended to begin formatting by filling the top two rows gray and adding the outside borders. I've broken the code up into three classes (please excuse any sloppy code or noob mistakes. Still on week 2 of learning Java):

1. Main Class:

   public class CreateExcel {
   
   public static void createExcel(String[] args) throws IOException {
   XSSFWorkbook workbook = new XSSFWorkbook();
   XSSFSheet resultsSheet = workbook.createSheet("Results");
   ExcelMethods format = new ExcelMethods();
   ExcelStyles style = new ExcelStyles();
   
   format.formatRange(workbook, resultsSheet, style.fillPMEDarkGray(workbook), 1, 2, 2, 14);
   format.formatRange(workbook, resultsSheet, style.borderLeft(workbook), 1, 2, 2, 1);
   format.formatRange(workbook, resultsSheet, style.borderRight(workbook), 1, 2, 16, 1);
   format.formatRange(workbook, resultsSheet, style.borderTop(workbook), 1, 1, 2, 14);
   format.formatRange(workbook, resultsSheet, style.borderBottom(workbook), 2, 1, 2, 14);
   
   try (FileOutputStream fileOut = new FileOutputStream("C:<file location>/workbook.xlsx")) {
       workbook.write(fileOut);
   
           }
       }
   }
   

2. A Class with the Format Cells Loop:

   public class ExcelMethods {
   
   public void formatRange(XSSFWorkbook workbook,
                           XSSFSheet sheet,
                           XSSFCellStyle style,
                           int rowStart,
                           int numRows,
                           int columnStart,
                           int numColumns) {
       for (int i = rowStart; i <= rowStart + numRows; i++) {
           XSSFRow row = sheet.createRow(i);
           for (int j = columnStart; j <= columnStart + numColumns; j++) {
               XSSFCell cell = row.createCell(j);
               cell.setCellStyle(style);
           }
       }
   }
   

3. A Class with the Styles Defined:

   public class ExcelStyles{
   
   public XSSFCellStyle fillPMEDarkGray(XSSFWorkbook workbook) {
       XSSFColor pmeDarkGray = new XSSFColor(new java.awt.Color(128, 128, 128));
       XSSFCellStyle fillCell = workbook.createCellStyle();
       fillCell.setFillForegroundColor(pmeDarkGray);
       fillCell.setFillPattern(SOLID_FOREGROUND);
   
       return fillCell;
   }
   
   public XSSFCellStyle borderLeft(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderLeft(BorderStyle.THICK);
   
       return cellBorder;
   }
   
   public XSSFCellStyle borderRight(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderRight(BorderStyle.THICK);
   
       return cellBorder;
   }
   
   public XSSFCellStyle borderTop(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderTop(BorderStyle.THICK);
   
       return cellBorder;
   }
   
   public XSSFCellStyle borderBottom(XSSFWorkbook workbook) {
       XSSFCellStyle cellBorder = workbook.createCellStyle();
       cellBorder.setBorderBottom(BorderStyle.THICK);
   
       return cellBorder;
       }
   }
   

Through combing Stack Overflow, POI API Documents, etc, I've come to understand that a cell can only have a single XSSFCellStyle, so trying to add a border to an already-filled cell gets rid of the fill. Thus, my code just produces a set of cells filled black.

It seems strange to me that Apache POI can't do what VBA does so easily. I know I must be missing something / setting up the code wrong / etc.

As an aside, I have also tried using PropertyTemplate.drawBorders as described in the Apache POI API and I couldn't get that to work either - same result.

I'm hoping someone has come up with a clever way around this as I am at my wit's end.

Thanks for your help!

Answer 1

OK, let's have an example of how to create your sample table using apache poi without monkeying around using the low level objects.

The main approach is using CellUtil and PropertyTemplate for creating the needed cell styles. So we do not need creating each single needed cell style manually (each different colored edge of an bordered range for example). But we are nevertheless save not creating too much cell styles (one cell style for each cell for example).

But as mentioned in Not able to set custom color in XSSFCell Apache POI already, PropertyTemplate as well as CellUtil and RegionUtil are be based on ss.usermodel level only and not on xssf.usermodel level. But org.apache.poi.ss.usermodel.CellStyle does not know something about a setFillForegroundColor(Color color) until now. It only knows setFillForegroundColor(short bg). So ss.usermodel level simply cannot set a Color as fill foreground color until now. Only a short (a color index) is possible. This we must have in mind and only set cell fills using IndexedColors. Else it fails.

Example code, commented to describing what it does:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.PropertyTemplate;

import java.util.Map;
import java.util.HashMap;

public class CreateExcelCellStyles {

 public static CellStyle getPreferredCellStyle(Cell cell) {
  // a method to get the preferred cell style for a cell
  // this is either the already applied cell style
  // or if that not present, then the row style (default cell style for this row)
  // or if that not present, then the column style (default cell style for this column)
  CellStyle cellStyle = cell.getCellStyle();
  if (cellStyle.getIndex() == 0) cellStyle = cell.getRow().getRowStyle();
  if (cellStyle == null) cellStyle = cell.getSheet().getColumnStyle(cell.getColumnIndex());
  if (cellStyle == null) cellStyle = cell.getCellStyle();
  return cellStyle;
 }

 public static void main(String[] args) throws Exception {

  //the data
  Object[][] data = new Object[][]{
   {null, "Returns", "Benchmark 1", null, null, null, "Benchmark 2", null, null, null, "Benchmark 3", null, null, null},
   {null, null, "PME Plus", null, null, "Direct", "PME Plus", null, null, "Direct", "PME Plus", null, null, "Direct"}, 
   {null, null, "PME IRR", "IRR", "KS PME", "Alpha", "PME IRR", "IRR", "KS PME", "Alpha", "PME IRR", "IRR", "KS PME", "Alpha"}, 
   {"1 Year", .17, .162, .162, 1.01, .007, .191, .191, .99, -.018, .192, .192, .99, -.018}, 
   {"3 Year", null, null, null, null, null, null, null, null, null, null, null, null, null}, 
   {"5 Year", null, null, null, null, null, null, null, null, null, null, null, null, null}, 
   {"10 Year", null, null, null, null, null, null, null, null, null, null, null, null, null}, 
   {"20 Year", .103, .051, .059, 1.17, .048, .071, .074, 1.11, .03, .062, .066, 1.14, .037}, 
   {"Since Inception", .109, .062, .066, 1.15, .041, .079, .08, 1.10, .027, .073, .074, 1.12, .031}, 
  };

  //we need PropertyTemplate later
  PropertyTemplate propertyTemplate = new PropertyTemplate();

  //we need properties map for cell styles later
  Map<String, Object> properties;

  //creating workbook
  Workbook workbook = new XSSFWorkbook();

  //we need DataFormat later
  DataFormat format = workbook.createDataFormat();

  //creating default font
  Font defaultFont = workbook.createFont();
  defaultFont.setFontName("Calibri");
  defaultFont.setFontHeightInPoints((short)12);

  //we need font in bold and white for headings
  Font defaultFontWhite = workbook.createFont();
  defaultFontWhite.setFontName("Calibri");
  defaultFontWhite.setFontHeightInPoints((short)12);
  defaultFontWhite.setBold(true);
  defaultFontWhite.setColor(IndexedColors.WHITE.getIndex());

  //creating default cell style having default font
  CellStyle defaultCellStyle = workbook.createCellStyle();
  defaultCellStyle.setFont(defaultFont);

  //we need percent style for numbers later
  CellStyle percent = workbook.createCellStyle();
  percent.cloneStyleFrom(defaultCellStyle);
  percent.setDataFormat(format.getFormat("0.0%"));
  percent.setAlignment(HorizontalAlignment.CENTER);

  //we need user defined number style having unit "x" for numbers later
  CellStyle doubleX = workbook.createCellStyle();
  doubleX.cloneStyleFrom(defaultCellStyle);
  doubleX.setDataFormat(format.getFormat("0.00\x"));
  doubleX.setAlignment(HorizontalAlignment.CENTER);

  //creating sheet
  Sheet sheet = workbook.createSheet();

  //set default column styles
  sheet.setDefaultColumnStyle(0, defaultCellStyle); //first column A = default
  for (int c = 1; c < 14; c++) sheet.setDefaultColumnStyle(c, percent); //columns B to N = percent; some will be overridden later
  sheet.setDefaultColumnStyle(4, doubleX); //column E = user defined number style having unit "x"
  sheet.setDefaultColumnStyle(8, doubleX); //column I = user defined number style having unit "x"
  sheet.setDefaultColumnStyle(12, doubleX); //column M = user defined number style having unit "x"

  //put data in sheet
  int r = 0;
  for (Object[] rowdata : data) {
   Row row = sheet.createRow(r++);
   int c = 0;
   for (Object celldata : rowdata) {
    Cell cell = row.createCell(c++);
    if (celldata instanceof String) cell.setCellValue((String)celldata);
    else if (celldata instanceof Double) cell.setCellValue((Double)celldata);
    cell.setCellStyle(getPreferredCellStyle(cell)); //get preferred cell style from column style
   }
  }

  //add merged regions
  sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 5));
  sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 9));
  sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 13));
  sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));
  sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 8));
  sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 12));

  sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
  CellUtil.setVerticalAlignment(CellUtil.getCell(CellUtil.getRow(0, sheet), 0), VerticalAlignment.CENTER);
  sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1));
  CellUtil.setVerticalAlignment(CellUtil.getCell(CellUtil.getRow(0, sheet), 1), VerticalAlignment.CENTER);

  //styling the table headings (rows 1 to 3)
  for (int rw = 0; rw < 3; rw++) {
   Row row = sheet.getRow(rw);
   for (int c = 0; c < 14; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.GREY_50_PERCENT.getIndex()); //do using only IndexedColors for fills
    //properties.put(CellUtil.FONT, defaultFontWhite.getIndex()); //up to apache poi 3.17
    properties.put(CellUtil.FONT, defaultFontWhite.getIndexAsInt()); //since apache poi 4.0.0
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //do using CellUtil for **add** new properties to already applied cell styles
   }
  }
  propertyTemplate.drawBorders(new CellRangeAddress(0, 2, 0, 13), BorderStyle.MEDIUM, BorderExtent.ALL); //since we have merged regions we can simply drawing all borders here

  //styling the table body (rows 4 to 9)
  for (int rw = 3; rw < 9; rw++) {
   Row row = sheet.getRow(rw);

   properties = new HashMap<String, Object>();
   properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
   properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LEMON_CHIFFON.getIndex());
   CellUtil.setCellStyleProperties(CellUtil.getCell(row, 0), properties); //column A

   properties = new HashMap<String, Object>();
   properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
   properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.PALE_BLUE.getIndex());
   CellUtil.setCellStyleProperties(CellUtil.getCell(row, 1), properties); //column B

   for (int c = 2; c < 6; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_GREEN.getIndex());
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //columns C:F
   }
   for (int c = 6; c < 10; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_ORANGE.getIndex());
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //columns G:J
   }
   for (int c = 10; c < 14; c++) {
    properties = new HashMap<String, Object>();
    properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    CellUtil.setCellStyleProperties(CellUtil.getCell(row, c), properties); //columns K:N
   }
  }
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 0, 0), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around A4:A9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 1, 1), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around B4:B9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 2, 5), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around C4:F9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 6, 9), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around G4:J9
  propertyTemplate.drawBorders(new CellRangeAddress(3, 8, 10, 13), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); //outside border around K4:N9

  //apply the PropertyTemplate borders
  propertyTemplate.applyBorders(sheet);

  //resizing the columns A:N
  for (int c = 0; c < 14; c++) sheet.autoSizeColumn(c, true);

  //writing the result
  FileOutputStream out = new FileOutputStream("CreateExcelCellStyles.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }

}

Result: