org.docx4j.openpackaging.parts.SpreadsheetML.WorksheetPart 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);
}
 

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

public SheetWrapper(WorksheetPart worksheet, String name) {
    this.worksheet = worksheet;
    this.name = name;
}
 

public WorksheetPart getWorksheet() {
    return worksheet;
}
 

@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 List<Cell> copyCells(Range templateRange, BandData bandData, Row newRow, List<Cell> templateCells) {
    List<Cell> resultCells = new ArrayList<>();

    Worksheet resultWorksheet = getWorksheet(newRow);
    for (Cell templateCell : templateCells) {
        checkThreadInterrupted();
        Cell newCell = copyCell(templateCell);

        if (newCell.getF() != null) {
            addFormulaForPostProcessing(templateRange, bandData, newRow, templateCell, newCell);
        }

        resultCells.add(newCell);

        CellReference tempRef = new CellReference(templateRange.getSheet(), templateCell);
        CellReference newRef = new CellReference(templateRange.getSheet(), newCell.getR());
        newRef.move(newRow.getR().intValue(), newRef.getColumn());

        //if we have vertical band or horizontal band right after vertical band - it should be shifted
        //only if there is vertical intersection with vertical band?
        newRef.shift(0, previousRangesRightOffset);

        newCell.setR(newRef.toReference());

        newRow.getC().add(newCell);
        newCell.setParent(newRow);

        WorksheetPart worksheetPart = null;
        for (Document.SheetWrapper sheetWrapper : result.getWorksheets()) {
            Worksheet contents;
            try {
                contents = sheetWrapper.getWorksheet().getContents();
            } catch (Docx4JException e) {
                throw new RuntimeException("Unable to get worksheet contents", e);
            }

            if (contents == resultWorksheet) {
                worksheetPart = sheetWrapper.getWorksheet();
            }
        }

        updateCell(worksheetPart, bandData, newCell);

        Col templateColumn = template.getColumnForCell(templateRange.getSheet(), tempRef);
        Col resultColumn = result.getColumnForCell(templateRange.getSheet(), newRef);

        if (templateColumn != null && resultColumn == null) {
            resultColumn = XmlUtils.deepCopy(templateColumn, Context.jcSML);
            resultColumn.setMin(newRef.getColumn());
            resultColumn.setMax(newRef.getColumn());
            resultColumn.setOutlineLevel(templateColumn.getOutlineLevel());

            resultWorksheet.getCols().get(0).getCol().add(resultColumn);
        }

        hintProcessor.add(tempRef, templateCell, newCell, bandData);

    }
    return resultCells;
}
 

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