XSSF Excel命名样式

Question

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

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

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

我只是想知道，是否有任何方法在apache工作簿中创建名为的样式，然后在excel中打开文档后就可以看到和重新查看这些样式？

编辑:在进一步的调查中，似乎有一种使用HSSF的方法，我们可以使用：

cellStyle.setUserStyleName("Header")

但是我找不到任何关于XSSF等价物的信息。有人知道这是否可能吗？

Answer 1

如果使用Office OpenXML文件格式*.xlsx，这并不像应该的那么容易。但下面这些对我来说很管用。

如ooxml-schemas-1.3.jar中提到的那样，需要所有模式的完整jar。

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

 }
}

适应当前apache poi 5.2.2的代码

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 byte[]{(byte)255, 0, 0}, null));
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  setNamedCellStyle(style, "My Custom Style 1");

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

  style = workbook.createCellStyle();
  style.setFillForegroundColor(new XSSFColor(new byte[]{0, 0, (byte)255}, null));
  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);

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

 }
}