使用 Apache POI (Java) 在 XLSX 中创建复选框

Microsoft Excel 中可能有两种类型的控件.有遗留表单控件和 ActiveX 控件.创建遗留表单控件是可能的.ActiveX 控件要复杂得多.

There are two types of controls possible in Microsoft Excel. There are the legacy form controls and ActiveX controls. Creating legacy form controls would be possible. ActiveX controls wold be much more complex.

旧表单控件存储在每张纸的 VML 绘图中.Apache poi 有一个 XSSFVMLDrawing 类已经是因为单元格注释也部分存储在 VML 绘图中.但是通常在 apache poi 中，该类并不完整，并且其方法的可访问性被设置为使得该类无法轻松扩展.似乎 apache poi 开发人员经常明确希望阻止扩展他们的类.

The legacy form controls are stored in VML drawings per sheet. Apache poi has a XSSFVMLDrawing class already because cell comments also are partially stored in VML drawings. But as often in apache poi the class is not complete and the accessibility of it's methods is set so that the class cannot easy extended. Seems as if apache poi developers often explicitly wants to prevent extension of their classes.

对于旧的 Checkbox 控件，我们需要一个复选框形状类型，然后在此 VML 绘图中使用复选框形状.

For legacy Checkbox controls we need a checkbox shape type and then checkbox shapes in this VML drawing.

以下代码创建了两个遗留复选框.

Following code creates two legacy checkboxes.

方法 XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet) 从工作表中获取 VML 绘图，或者如果不存在则创建一个新的绘图.

The method XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet) gets the VML drawing from the sheet or creates a new one if not already present.

方法 void addCheckboxShapetype(XSSFVMLDrawing drawing) 添加一个新的复选框形状类型到绘图中.

The method void addCheckboxShapetype(XSSFVMLDrawing drawing) adds a new checkbox shape type to the drawing.

方法 void addCheckbox(XSSFVMLDrawing drawing, int col1, int dx1, int row1, int dy1, int col2, int dx2, int row2, int dy2, String label, boolean checked) 增加了一个具有给定位置和标签的绘图的新复选框形状，并且具有 Checked 元素，选中时包含 1，未选中时包含 0.

The method void addCheckbox(XSSFVMLDrawing drawing, int col1, int dx1, int row1, int dy1, int col2, int dx2, int row2, int dy2, String label, boolean checked) adds a new checkbox shape to the drawing having the given position and label and has a Checked element which contains either 1 when checked or 0 when not checked.

完整示例:

import java.io.*;

import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.xmlbeans.*;

import org.apache.poi.xssf.usermodel.*;

import com.microsoft.schemas.vml.*;
import com.microsoft.schemas.office.excel.CTClientData;

import java.lang.reflect.Field;
import javax.xml.namespace.QName;

import java.util.List;

class CreateExcelLegacyDrawingControls {

 private static XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet) throws Exception {
  XSSFVMLDrawing drawing = null;
  if (sheet.getCTWorksheet().getLegacyDrawing() != null) { 
   String legacyDrawingId = sheet.getCTWorksheet().getLegacyDrawing().getId();
   drawing = (XSSFVMLDrawing)sheet.getRelationById(legacyDrawingId);
  } else {
   int drawingNumber = sheet.getPackagePart().getPackage()
    .getPartsByContentType(XSSFRelation.VML_DRAWINGS.getContentType()).size() + 1;
   POIXMLDocumentPart.RelationPart rp = 
    sheet.createRelationship(XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber, false);
   drawing = rp.getDocumentPart();
   String rId = rp.getRelationship().getId();
   sheet.getCTWorksheet().addNewLegacyDrawing().setId(rId);
  }
  return drawing;
 }

 private static void addCheckboxShapetype(XSSFVMLDrawing drawing) throws Exception {
  String shapeTypeId = "_x0000_t201";
  CTShapetype shapetype = CTShapetype.Factory.newInstance();
  shapetype.setId(shapeTypeId);
  shapetype.setCoordsize("21600,21600");
  shapetype.setSpt(201);
  shapetype.setPath2("m,l,21600r21600,l21600,xe");

  Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
  _items.setAccessible(true);
  @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
  List<XmlObject> items = (List<XmlObject>)_items.get(drawing);

  Field _qnames = XSSFVMLDrawing.class.getDeclaredField("_qnames");
  _qnames.setAccessible(true);
  @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
  List<QName> qnames = (List<QName>)_qnames.get(drawing);

  items.add(shapetype);
  qnames.add(new QName("urn:schemas-microsoft-com:vml", "shapetype"));
 }

 private static void addCheckbox(XSSFVMLDrawing drawing,
  int col1, int dx1, int row1, int dy1, int col2, int dx2, int row2, int dy2, 
  String label, boolean checked) throws Exception {

  String shapeTypeId = "_x0000_t201";

  Field _shapeId = XSSFVMLDrawing.class.getDeclaredField("_shapeId");
  _shapeId.setAccessible(true);
  int shapeId = (int)_shapeId.get(drawing); 
  _shapeId.set(drawing, shapeId + 1);

  CTShape shape = CTShape.Factory.newInstance();
  shape.setId("_x0000_s" + shapeId);
  shape.setType("#" + shapeTypeId);
  shape.setFilled(com.microsoft.schemas.vml.STTrueFalse.F);
  shape.setStroked(com.microsoft.schemas.vml.STTrueFalse.F);
  String textboxHTML = 
   "<div style='text-align:left'>"
  +"<font face=\"Tahoma\" size=\"160\" color=\"auto\">" + label + "</font>"
  +"</div>";
  CTTextbox[] textboxArray = new CTTextbox[1];
  textboxArray[0] = CTTextbox.Factory.parse(textboxHTML);
  textboxArray[0].setStyle("mso-direction-alt:auto");
  textboxArray[0].setSingleclick(com.microsoft.schemas.office.office.STTrueFalse.F);
  shape.setTextboxArray(textboxArray);
  CTClientData cldata = shape.addNewClientData();
  cldata.setObjectType(com.microsoft.schemas.office.excel.STObjectType.CHECKBOX);
  cldata.addNewMoveWithCells();
  cldata.addNewSizeWithCells();
  cldata.addNewAnchor().setStringValue(
   "" + col1 + ", " + dx1 + ", " + row1 + ", " +dy1 + ", " + col2 + ", " + dx2 + ", " + row2 + ", " + dy2
  );
  cldata.addAutoFill(com.microsoft.schemas.office.excel.STTrueFalseBlank.FALSE);
  cldata.addAutoLine(com.microsoft.schemas.office.excel.STTrueFalseBlank.FALSE);
  cldata.addTextVAlign("Center");
  cldata.addNoThreeD(com.microsoft.schemas.office.excel.STTrueFalseBlank.TRUE);

  cldata.addChecked((checked)?java.math.BigInteger.valueOf(1):java.math.BigInteger.valueOf(0));

  Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
  _items.setAccessible(true);
  @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
  List<XmlObject> items = (List<XmlObject>)_items.get(drawing);

  Field _qnames = XSSFVMLDrawing.class.getDeclaredField("_qnames");
  _qnames.setAccessible(true);
  @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
  List<QName> qnames = (List<QName>)_qnames.get(drawing);

  items.add(shape);
  qnames.add(new QName("urn:schemas-microsoft-com:vml", "shape"));

 }

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

  XSSFWorkbook workbook  = new XSSFWorkbook();

  //following is necessary to be textboxHTML of the CTShape compatible with Excel 2007.
  //<fileVersion appName="xl" lastEdited="4" lowestEdited="0" rupBuild="4507"/>
  workbook.getCTWorkbook().addNewFileVersion().setAppName("xl");
  workbook.getCTWorkbook().getFileVersion().setLastEdited("4");
  workbook.getCTWorkbook().getFileVersion().setLowestEdited("0");
  workbook.getCTWorkbook().getFileVersion().setRupBuild("4507");

  XSSFSheet sheet = workbook.createSheet();
  XSSFCell cell = sheet.createRow(5).createCell(5);
/*
  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
  anchor.setCol1(cell.getColumnIndex());
  anchor.setCol2(cell.getColumnIndex()+1);
  anchor.setRow1(cell.getRow().getRowNum());
  anchor.setRow2(cell.getRow().getRowNum()+3);
  XSSFComment comment = drawing.createCellComment(anchor);
  XSSFRichTextString str = workbook.getCreationHelper().createRichTextString("Hello, World!");
  comment.setString(str);
  comment.setAuthor("Apache POI");
  cell.setCellComment(comment);
*/
  XSSFVMLDrawing vmlDrawing = getVMLDrawing(sheet);
  addCheckboxShapetype(vmlDrawing);
  addCheckbox(vmlDrawing, 1, 0, 1, 0, 3, 0, 2, 0, "Checkbox 1", true);
  addCheckbox(vmlDrawing, 1, 0, 2, 0, 3, 0, 3, 0, "Checkbox 2", false);

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

 }

}

请注意，对于上述解决方案，如 FAQ N10025.

Please note, for above solution the full jar of all of the schemas ooxml-schemas-1.4.jar (lower versions for older releases) is needed as mentioned in FAQ N10025.

注意:这将适用于 apache poi 4.1.2 但如果使用 apache poi 5.0.0 则无效.原因:Apache POI 将 XSSFVMLDrawing 的代码改了，所以它不再有 _items 和 _qnames 字段.但也没有提供除评论外添加新项目的方法.

Note: This will work up to apache poi 4.1.2 but not if apache poi 5.0.0 is used. Reason: Apache POI has canged code of XSSFVMLDrawing so it does not have fields _items and _qnames anymore. But is also not provides methods to add new items except comments.