Set the Z-Order Index for a Shape Object in Aspose Cells for Java to send the shape behind text

Question

I added a shape object to the existing contents of an Excel worksheet using Aspose Cells for Java like so:

Shape shape = worksheet.getShapes().addTextEffect(...);

Now, I'd like to send the shape behind the text that already existed in the worksheet. I looked up there is the setZOrderPosition method on the Shape object.

However, it accepts an integer value, and I can't seem to find an enum or a magic number or a sentinel that indicates Send to Back like we had the msoSendToBack constant in the VSTO/VBA object models.

Besides, their documentation offers no explanation of the values for this method: https://apireference.aspose.com/net/cells/aspose.cells.drawing/shape/properties/zorderposition

So, like all other drawing API, I presume that the Z Order value is relative to the Z-Order of other content on the worksheet.

So, I tried specifying -1 to provide the lowest possible value.

shape.setZOrderPosition(-1);

That threw an IndexOutOfBoundsException or some such, from which, I infer that the valid values for Z-Order are zero-based.

I provided the value 0, hoping it would be the lowest and would do the job, but appears not.

shape.setZOrderPosition(0);

The shape still overlays existing text.

How do I set the Z-Order index for the shape object properly so it goes behind the text?

Answer 1

I think you should set wordart shape's transparency to respective value accordingly so the text in those cells should be shown precisely. See the following sample code for your reference: e.g Sample code:

........

// Get the first default sheet
Worksheet sheet = workbook.getWorksheets().get(0);

// Add Watermark
Shape wordart = sheet.getShapes().addTextEffect(MsoPresetTextEffect.TEXT_EFFECT_1, "CONFIDENTIAL",
        "Arial Black", 50, false, true, 18, 8, 1, 1, 130, 800);

// Get the fill format of the word art
FillFormat wordArtFormat = wordart.getFill();

// Set the color
wordArtFormat.setOneColorGradient(Color.getRed(), 0.2, GradientStyleType.HORIZONTAL, 2);

// Set the transparency
wordArtFormat.setTransparency(0.9);

// Make the line invisible
LineFormat lineFormat = wordart.getLine();
lineFormat.setWeight(0.0);

..........

I am working as Support developer/ Evangelist at Aspose.

Answer 2

Please use Shape.setZOrderPosition() method for your needs. It will work if you also change the z-order position of the all or affected shapes.

Please see the following sample code for a reference. I have attached an image which shows the input Excel file used in this code and output Excel file generated by the code.

As you can see inside the image, Heart is now behind the Text Box.

Java

//Load your workbook
Workbook wb = new Workbook(dirPath + "sample.xlsx");

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Get shape 1 and 2
Shape sh = ws.getShapes().get(0);
Shape sh1 = ws.getShapes().get(1);


//Set the Z-Order Position, first shape to 1 and second shape to 0
sh.setZOrderPosition(1);
sh1.setZOrderPosition(0);

//Save the workbook
wb.save(dirPath + "output.xlsx");

Screenshot

Note: I am working as Developer Evangelist at Aspose

Answer 3

Aspose.Cells has implemented the feature (i.e. Send Shape Front or Back) in version 17.9 which you can download from Aspose website downloads section. Here is a sample code for your reference.

Java

Workbook workbook = new Workbook();

Worksheet sheet = workbook.getWorksheets().get(0);

ShapeCollection shapes = workbook.getWorksheets().get(0).getShapes(); 

//Add first textbox
sheet.getTextBoxes().add(3, 3, 100, 100);

TextBox tb1 = sheet.getTextBoxes().get(0);
tb1.setText("This is first textbox.");

System.out.println(tb1.getZOrderPosition());

//Add second textbox
sheet.getTextBoxes().add(5, 3, 100, 100);

TextBox tb2 = sheet.getTextBoxes().get(1);
tb2.setText("This is second textbox.");

System.out.println(tb2.getZOrderPosition());

//Add second textbox
sheet.getTextBoxes().add(7, 3, 100, 100);

TextBox tb3 = sheet.getTextBoxes().get(2);
tb3.setText("This is third textbox.");

System.out.println(tb3.getZOrderPosition());

//Bring first textbox to front
tb1.toFrontOrBack(3);

//Send third textbox to back
tb3.toFrontOrBack(-1);

workbook.save(dirPath + "output.xlsx");

Note: I am working as Developer Evangelist at Aspose