XSSF Excel Named Styles

Question

I'm currently using the Apache POI library to generate excel files in Java.

Here's what I'm wondering: In excel, it's possible to create new cell styles which will be added to the workbook. These styles are reusable and can be selected from the styles table.

Using Apache POI, you can do something similar when constructing a workbook. You can create a new XSSFCellstyle, which is attached to the workbook, and can be applied to as many cells as you want. However, these styles are not reusable. If I open the resulting workbook in excel, and change one of the cell styles, I will never be able to change it back to the unnamed style I generated in XSSF. These styles are not added to the styles table of the workbook.

I'm just wondering, is there any means of creating named styles in an apache POI workbook, which can then be seen and resused after the document is opened in excel?

EDIT: On further investigation there appears to be a way to do this using HSSF, we can use:

cellStyle.setUserStyleName("Header")

I can't find any info on an XSSF equivalent though. Anyone know if it's possible?

Axel Richter Axel Richter · Accepted Answer · 2017-09-29T13:36:41

This is not as easy as it should, if Office OpenXML file format *.xlsx is used. But the following works for me.

The full jar of all of the schemas ooxml-schemas-1.3.jar is needed as mentioned in apache poi FAQ-N10025.

import java.io.FileOutputStream;
import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.model.StylesTable;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTStylesheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellStyles;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellStyle;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellStyleXfs;

import java.lang.reflect.Field;

public class CreateExcelNamedXSSFCellStyle {

 static void setNamedCellStyle(XSSFCellStyle style, String name) throws Exception {

  Field _stylesSource = XSSFCellStyle.class.getDeclaredField("_stylesSource"); 
  _stylesSource.setAccessible(true); 
  StylesTable stylestable = (StylesTable)_stylesSource.get(style);
  CTStylesheet ctstylesheet = stylestable.getCTStylesheet();

  CTCellStyles ctcellstyles = ctstylesheet.getCellStyles();

  CTXf ctxfcore = style.getCoreXf();

  if (ctcellstyles == null) {
   ctcellstyles = ctstylesheet.addNewCellStyles();
   ctcellstyles.setCount(2);

   CTCellStyle ctcellstyle = ctcellstyles.addNewCellStyle(); //CellStyle for default built-in cell style
   ctcellstyle.setXfId(0);
   ctcellstyle.setBuiltinId(0);

   ctcellstyle = ctcellstyles.addNewCellStyle();
   ctcellstyle.setXfId(1);
   ctcellstyle.setName(name);

   ctxfcore.setXfId(1);
  } else {
   long stylescount = ctcellstyles.getCount();
   ctcellstyles.setCount(stylescount+1);

   CTCellStyle ctcellstyle = ctcellstyles.addNewCellStyle();
   ctcellstyle.setXfId(stylescount);
   ctcellstyle.setName(name);

   ctxfcore.setXfId(stylescount);
  }

  CTXf ctxfstyle = CTXf.Factory.newInstance();  
  ctxfstyle.setNumFmtId(ctxfcore.getNumFmtId());
  ctxfstyle.setFontId(ctxfcore.getFontId());
  ctxfstyle.setFillId(ctxfcore.getFillId());
  ctxfstyle.setBorderId(ctxfcore.getBorderId());

  stylestable.putCellStyleXf(ctxfstyle);

 }

 static XSSFCellStyle getNamedCellStyle(XSSFWorkbook workbook, String name) {
  StylesTable stylestable = workbook.getStylesSource();
  CTStylesheet ctstylesheet = stylestable.getCTStylesheet();
  CTCellStyles ctcellstyles = ctstylesheet.getCellStyles();
  if (ctcellstyles != null) {
   int i = 0;
   XSSFCellStyle style = null;
   while((style = stylestable.getStyleAt(i++)) != null) {
    CTXf ctxfcore = style.getCoreXf();
    long xfid = ctxfcore.getXfId();
    for (CTCellStyle ctcellstyle : ctcellstyles.getCellStyleList()) {
     if (ctcellstyle.getXfId() == xfid && name.equals(ctcellstyle.getName())) {
      return style;
     }
    }
   }
  }
  return workbook.getCellStyleAt(0); //if nothing found return default cell style 
 }

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

  XSSFWorkbook workbook = new XSSFWorkbook();
  //XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("Mappe1.xlsx"));

  XSSFCellStyle style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(style, "My Custom Style 1");

  style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(style, "My Custom Style 2");

  style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(style, "My Custom Style 3");

  XSSFSheet sheet = workbook.createSheet("TestSheet");
  XSSFRow row = sheet.createRow(0);
  for (int i = 0; i < 3; i++) {
   XSSFCell cell = row.createCell(i);
   style = getNamedCellStyle(workbook, "My Custom Style " + (i+1));
   cell.setCellStyle(style);
  }

  row = sheet.createRow(2);
  XSSFCell cell = row.createCell(0);
  style = getNamedCellStyle(workbook, "not found");
  cell.setCellStyle(style);

  workbook.write(new FileOutputStream("CreateExcelNamedXSSFCellStyle.xlsx"));
  workbook.close();

 }
}

XSSF Excel Named Styles

1 Answers