org.docx4j.openpackaging.packages.SpreadsheetMLPackage Java Examples

@Override
public void inlineToXlsx(SpreadsheetMLPackage pkg, WorksheetPart worksheetPart, Cell newCell, Object paramValue, Matcher matcher) {
    try {
        Image image = new Image(paramValue, matcher);
        if (image.isValid()) {
            BinaryPartAbstractImage imagePart = BinaryPartAbstractImage.createImagePart(pkg, worksheetPart, image.imageContent);
            CTOneCellAnchor anchor = new CTOneCellAnchor();
            anchor.setFrom(new CTMarker());
            CellReference cellReference = new CellReference("", newCell.getR());
            anchor.getFrom().setCol(cellReference.getColumn() - 1);
            anchor.getFrom().setRow(cellReference.getRow() - 1);
            anchor.setExt(new CTPositiveSize2D());
            anchor.getExt().setCx(XlsxUtils.convertPxToEmu(image.width));
            anchor.getExt().setCy(XlsxUtils.convertPxToEmu(image.height));
            newCell.setV(null);
            putImage(worksheetPart, pkg, imagePart, anchor);
        }
    } catch (Exception e) {
        throw new ReportFormattingException("An error occurred while inserting bitmap to xlsx file", e);
    }
}
 

public void addWatermark() throws Exception{
	File f = new File("E:/提取英文名.xlsx");
	SpreadsheetMLPackage pkg = SpreadsheetMLPackage.load(f);
	int size = pkg.getWorkbookPart().getContents().getSheets().getSheet().size();
	for (int i=0;i<size;i++) {
		WorksheetPart worksheet = pkg.getWorkbookPart().getWorksheet(i);
		createBgPic(pkg, worksheet);
	}
	pkg.save(new File(fileName));
	System.out.println("\n\n done .. " + fileName);
}
 

/**
 * 使用水印图片作为excel背景，达到水印效果<但打印时不会生效>
 * @param pkg
 * @param worksheet
 * @throws Exception
 * @throws IOException
 */
private void createBgPic(SpreadsheetMLPackage pkg, WorksheetPart worksheet) throws Exception, IOException {
	CTSheetBackgroundPicture ctSheetBackgroundPicture = org.xlsx4j.jaxb.Context.getsmlObjectFactory().createCTSheetBackgroundPicture();
	worksheet.getContents().setPicture(ctSheetBackgroundPicture);
	
	BinaryPartAbstractImage imagePart = BinaryPartAbstractImage.createImagePart(pkg, worksheet, FileUtils.readFileToByteArray(new File(outMarkPic)));
	Relationship sourceRelationship = imagePart.getSourceRelationships().get(0);
	String imageRelID = sourceRelationship.getId();
	ctSheetBackgroundPicture.setId(imageRelID);
}
 

public static Document create(SpreadsheetMLPackage thePackage) {
    Document document = new Document();
    document.thePackage = thePackage;
    RelationshipsPart rp = thePackage.getRelationshipsPart();
    document.traverse(null, rp);

    return document;
}
 

private void putImage(WorksheetPart worksheetPart, SpreadsheetMLPackage pkg, BinaryPartAbstractImage imagePart, CTOneCellAnchor anchor) throws Docx4JException {
    PartName drawingPart = new PartName(StringUtils.replaceIgnoreCase(worksheetPart.getPartName().getName(),
            "worksheets/sheet", "drawings/drawing"));
    String imagePartName = imagePart.getPartName().getName();
    Part part = pkg.getParts().get(drawingPart);
    if (part != null && !(part instanceof Drawing))
        throw new ReportFormattingException("Wrong Class: not a Drawing");
    Drawing drawing = (Drawing) part;
    int currentId = 0;
    if (drawing == null) {
        drawing = new Drawing(drawingPart);
        drawing.setContents(new org.docx4j.dml.spreadsheetdrawing.CTDrawing());
        Relationship relationship = worksheetPart.addTargetPart(drawing);
        org.xlsx4j.sml.CTDrawing smlDrawing = new org.xlsx4j.sml.CTDrawing();
        smlDrawing.setId(relationship.getId());
        smlDrawing.setParent(worksheetPart.getContents());
        worksheetPart.getContents().setDrawing(smlDrawing);
    } else {
        currentId = drawing.getContents().getEGAnchor().size();
    }

    CTPicture picture = new CTPicture();

    CTBlipFillProperties blipFillProperties = new CTBlipFillProperties();
    blipFillProperties.setStretch(new CTStretchInfoProperties());
    blipFillProperties.getStretch().setFillRect(new CTRelativeRect());
    blipFillProperties.setBlip(new CTBlip());
    blipFillProperties.getBlip().setEmbed("rId" + (currentId + 1));
    blipFillProperties.getBlip().setCstate(STBlipCompression.PRINT);

    picture.setBlipFill(blipFillProperties);

    CTNonVisualDrawingProps nonVisualDrawingProps = new CTNonVisualDrawingProps();
    nonVisualDrawingProps.setId(currentId + 2);
    nonVisualDrawingProps.setName(imagePartName.substring(imagePartName.lastIndexOf("/") + 1));
    nonVisualDrawingProps.setDescr(nonVisualDrawingProps.getName());

    CTNonVisualPictureProperties nonVisualPictureProperties = new CTNonVisualPictureProperties();
    nonVisualPictureProperties.setPicLocks(new CTPictureLocking());
    nonVisualPictureProperties.getPicLocks().setNoChangeAspect(true);
    CTPictureNonVisual nonVisualPicture = new CTPictureNonVisual();

    nonVisualPicture.setCNvPr(nonVisualDrawingProps);
    nonVisualPicture.setCNvPicPr(nonVisualPictureProperties);

    picture.setNvPicPr(nonVisualPicture);

    CTShapeProperties shapeProperties = new CTShapeProperties();
    CTTransform2D transform2D = new CTTransform2D();
    transform2D.setOff(new CTPoint2D());
    transform2D.setExt(new CTPositiveSize2D());
    shapeProperties.setXfrm(transform2D);
    shapeProperties.setPrstGeom(new CTPresetGeometry2D());
    shapeProperties.getPrstGeom().setPrst(STShapeType.RECT);
    shapeProperties.getPrstGeom().setAvLst(new CTGeomGuideList());

    picture.setSpPr(shapeProperties);

    anchor.setPic(picture);
    anchor.setClientData(new CTAnchorClientData());

    drawing.getContents().getEGAnchor().add(anchor);

    Relationship rel = new Relationship();
    rel.setId("rId" + (currentId + 1));
    rel.setType(Namespaces.IMAGE);
    rel.setTarget(imagePartName);

    drawing.getRelationshipsPart().addRelationship(rel);
    RelationshipsPart relPart = drawing.getRelationshipsPart();
    pkg.getParts().remove(relPart.getPartName());
    pkg.getParts().put(relPart);
    pkg.getParts().remove(drawing.getPartName());
    pkg.getParts().put(drawing);
}
 

protected void init() {
    try {
        template = Document.create(SpreadsheetMLPackage.load(reportTemplate.getDocumentContent()));
        result = Document.create(SpreadsheetMLPackage.load(reportTemplate.getDocumentContent()));
        result.getWorkbook().getCalcPr().setCalcMode(STCalcMode.AUTO);
        result.getWorkbook().getCalcPr().setFullCalcOnLoad(true);
        marshaller = XmlCopyUtils.createMarshaller(Context.jcSML);
        unmarshaller = XmlCopyUtils.createUnmarshaller(Context.jcSML);
    } catch (Exception e) {
        throw wrapWithReportingException(String.format("An error occurred while loading template [%s]", reportTemplate.getDocumentName()), e);
    }
}
 

private void compareFiles(String resultPath, String etalonPath) throws Docx4JException {
    Document result = Document.create(SpreadsheetMLPackage.load(new File(resultPath)));
    Document etalon = Document.create(SpreadsheetMLPackage.load(new File(etalonPath)));

    List<Document.SheetWrapper> resultWorksheets = result.getWorksheets();
    List<Document.SheetWrapper> etalonWorksheets = etalon.getWorksheets();

    for (int i = 0; i < resultWorksheets.size(); i++) {
        Document.SheetWrapper resultWorksheet = resultWorksheets.get(i);
        Document.SheetWrapper etalonWorksheet = etalonWorksheets.get(i);

        List<Row> resultRows = resultWorksheet.getWorksheet().getContents().getSheetData().getRow();
        List<Row> etalonRows = etalonWorksheet.getWorksheet().getContents().getSheetData().getRow();
        for (int j = 0, rowSize = resultRows.size(); j < rowSize; j++) {
            Row resultRow = resultRows.get(j);
            Row etalonRow = etalonRows.get(j);
            List<Cell> resultCells = resultRow.getC();
            List<Cell> etalonCells = etalonRow.getC();
            for (int i1 = 0, cSize = etalonCells.size(); i1 < cSize; i1++) {
                Cell resultCell = resultCells.get(i1);
                Cell etalonCell = etalonCells.get(i1);
                if (resultCell.getF() != null) {
                    Assert.assertEquals(etalonCell.getF().getValue(), resultCell.getF().getValue());
                } else {
                    Assert.assertEquals(etalonCell.getV(), resultCell.getV());
                }
            }
        }
    }
}
 

public SpreadsheetMLPackage getPackage() {
    return thePackage;
}
 

@Override
public void inlineToXlsx(SpreadsheetMLPackage pkg, WorksheetPart worksheetPart, Cell newCell, Object paramValue, Matcher matcher) {
    throw new UnsupportedOperationException("Inline html content to XSLX is not supported");
}
 

protected void writeToOutputStream(SpreadsheetMLPackage mlPackage, OutputStream outputStream) throws Docx4JException {
    Save save = new Save(mlPackage);
    save.save(outputStream);
}
 

/**
 * Inline content to xlsx template
 */
void inlineToXlsx(SpreadsheetMLPackage pkg, WorksheetPart worksheetPart, Cell newCell, Object paramValue, Matcher matcher);