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工作簿中创建命名样式，然后在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.

需要所有架构ooxml-schemas-1.3.jar的完整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屋！