我需要在XSSFSheet中创建一个Excel复选框,但是在Java Apache POI库(4.0.1)中,我没有发现任何明显的类/方法,也没有任何示例。有什么建议吗?
iamhero3333 回答:使用Apache POI(Java)在XLSX中创建复选框
Microsoft Excel
中可能有两种控件。有旧版表单控件和ActiveX
控件。创建遗留表单控件是可能的。 ActiveX
控件要复杂得多。
旧版表单控件存储在每张VML图纸中。 Apache poi
已经具有XSSFVMLDrawing类,因为单元格注释也部分存储在VML绘图中。但是,在apache poi
中,该类通常不完整,并且设置了其方法的可访问性,因此该类无法轻松扩展。似乎apache poi
开发人员经常明确希望阻止其类的扩展。
对于旧版Checkbox
控件,我们需要一个复选框形状类型,然后在此VML绘图中需要复选框形状。
以下代码创建两个旧的复选框。
方法XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet)
从图纸中获取VML绘图,或者如果尚不存在,则创建一个新的绘图。
方法void addCheckboxShapetype(XSSFVMLDrawing 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。
完整示例:
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,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();
workbook.getCTWorkbook().addNewFileVersion().setAppName("xl");
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,3,2,"Checkbox 1",true);
addCheckbox(vmlDrawing,"Checkbox 2",false);
FileOutputStream out = new FileOutputStream("Excel.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
}
请注意,对于上述解决方案,如FAQ N10025中所述,需要所有模式ooxml-schemas-1.4.jar
(旧版本的较低版本)的完整jar。