Is there are a way to use default Excel cell styles (see below) in Apache POI, similar to default data formats in

CodePudding user response：

If "a way to use default Excel cell styles" means to be able to style cells using a named cell style using apache poi, then no, this is not possible.

The cell style templates are exactly this, templates for cell styles. If chosen in Excel GUI, the GUI creates a cell style on workbook level according to that chosen template and applies that cell style to the cell then. There is no possibility to store only the name of the cell style into the Excel file and then to expect that Excel styles the cell accordingly. Apache poi would must do the same. But then it would must know what the single templates are defining. But this seems not documented anywhere. CellStyle Class states:

Annex H contains a listing of cellStyles whose corresponding formatting records are implied rather than explicitly saved in the file. In this case, a builtinId attribute is written on the cellStyle record, but no corresponding formatting records are written.

But I was not able to find that Annex H anywhere. Maybe I am only too stupid to find it.

The only thing we could do is to create own custom named cell styles, which then can be used.

And, if we get the builtinId of built in cell style templates somehow , then we could creating cell styles to overwrite those built in cell style templates. But to get the builtinId of built in cell style templates only is possible by trial/error as they are not documented:

Create a Excel file which uses some of the built in cell style templates. Save that file as *.xlsx file. Then unzip that *.xlsx file and have a look into /xl/styles.xml. There see what builtinId they have:

<cellStyles count="4">
 <cellStyle name="Gut" xfId="1" builtinId="26"/>
 <cellStyle name="Neutral" xfId="3" builtinId="28"/>
 <cellStyle name="Schlecht" xfId="2" builtinId="27"/>
 <cellStyle name="Standard" xfId="0" builtinId="0"/>
</cellStyles>

Following complete example does both. It creates three named custom cell styles which are visible in the list of the cell style templates then.

It also overwrites the named cell style having builtinId="26". This is the cell style named "Good".

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;

public class CreateExcelNamedXSSFCellStyle {

 static void setNamedCellStyle(XSSFWorkbook workbook, XSSFCellStyle style, String name, long builtinId) throws Exception {
  
  StylesTable stylestable = workbook.getStylesSource();
  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);
   if (builtinId > -1) ctcellstyle.setBuiltinId(builtinId);

   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();

  //The following creates three named custom cell styles
  IndexedColorMap colorMap = workbook.getStylesSource().getIndexedColors();
  XSSFCellStyle style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new byte[]{(byte)255, 0, 0}, colorMap));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(workbook, style, "My Custom Style 1", -1);

  style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new byte[]{0, (byte)255, 0}, colorMap));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(workbook, style, "My Custom Style 2", -1);

  style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new byte[]{0, 0, (byte)255}, colorMap));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(workbook, style, "My Custom Style 3", -1);

  //The following uses the custom named cell styles
  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);
  
  //The following overwrites the named cell style having builtinId="26". This is the cell style named "Good".
  style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new byte[]{(byte)198, (byte)239, (byte)206}, colorMap));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(workbook, style, "Good", 26);
  cell = row.createCell(1);
  style = getNamedCellStyle(workbook, "Good");
  cell.setCellStyle(style);

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

 }
}