Easyexcel导出复选框
第一步:添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.4</version>
</dependency>
第二步:示例代码
public class CreateExcelCheckbox {
public static void main(String[] args) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.getCTWorkbook().addNewFileVersion().setAppName("xl");
workbook.getCTWorkbook().getFileVersion().setLastEdited("4");
workbook.getCTWorkbook().getFileVersion().setLowestEdited("0");
workbook.getCTWorkbook().getFileVersion().setRupBuild("4507");
XSSFSheet sheet = workbook.createSheet();
XSSFVMLDrawing vmlDrawing = getVMLDrawing(sheet);
addCheckboxShapetype(vmlDrawing);
addCheckbox(vmlDrawing, 0, 30, 0, 0, 1, 0, 1, 0, "", true);
FileOutputStream out = new FileOutputStream("D:\\Excel.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
private static XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet) {
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"));
}
}
第三步:运行Main方法
导出Excel,效果如下
第四步:关键代码说明
addCheckbox(XSSFVMLDrawing drawing,int col1, int dx1, int row1, int dy1, int col2, int dx2, int row2, int dy2,String label, boolean checked)
col1,row1 表示左上角的单元格的坐标
dx1,dy1 表示左上角的单元格的偏移量
col2,row2 表示右下角的单元格的坐标
dx2,dy2 表示右下角的单元格的偏移量
label 单元格里的描述