XSSF Excel 命名样式 [英] XSSF Excel Named Styles

问题描述

我目前正在使用 Apache POI 库在 Java 中生成 excel 文件.

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

这就是我想知道的:在 excel 中，可以创建将添加到工作簿中的新单元格样式.这些样式是可重复使用的，可以从样式表中选择.

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.

使用 Apache POI，您可以在构建工作簿时执行类似的操作.您可以创建一个新的 XSSFCellstyle，它附加到工作簿，并且可以应用于任意数量的单元格.但是，这些样式不可重复使用.如果我在 excel 中打开生成的工作簿，并更改其中一种单元格样式，我将永远无法将其改回我在 XSSF 中生成的未命名样式.这些样式不会添加到工作簿的样式表中.

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.

我只是想知道，有没有办法在 apache POI 工作簿中创建 named 样式，然后在 excel 中打开文档后可以看到并重新使用这些样式?

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?

在进一步调查中似乎有一种方法可以使用 HSSF 来做到这一点，我们可以使用:

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

cellStyle.setUserStyleName("Header")

不过，我找不到有关 XSSF 等效项的任何信息.有人知道这是否可能吗?

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

推荐答案

如果使用 Office OpenXML 文件格式 *.xlsx，这并不像它应该的那么容易.但以下对我有用.

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

需要所有模式的完整 jar ooxml-schemas-1.3.jar，如 apache poi FAQ-N10025.

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 命名样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！