Shapes in spreadsheet document

Extracting information about all shapes in an Excel document

Search() method searches watermarks of all mentioned types, but in some cases, it’s necessary to analyze only one class of Excel objects. Following code sample shows how to get information about all the shapes in an Excel document.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetGetShapesInformation

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                              
// Specify an absolute or relative path to your document. Ex: @"C:\Docs\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                            
                                                                                                                
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                  
for (SpreadsheetWorksheet worksheet : content.getWorksheets())                                                  
{                                                                                                               
    for (SpreadsheetShape shape : worksheet.getShapes())                                                        
    {                                                                                                           
        System.out.println(shape.getAutoShapeType());                                                           
        System.out.println(shape.getMsoDrawingType());                                                          
        System.out.println(shape.getText());                                                                    
        if (shape.getImage() != null)                                                                           
        {                                                                                                       
            System.out.println(shape.getImage().getWidth());                                                    
            System.out.println(shape.getImage().getHeight());                                                   
            System.out.println(shape.getImage().getBytes().length);                                             
        }                                                                                                       
                                                                                                                
        System.out.println(shape.getId());                                                                      
        System.out.println(shape.getAlternativeText());                                                         
        System.out.println(shape.getX());                                                                       
        System.out.println(shape.getY());                                                                       
        System.out.println(shape.getWidth());                                                                   
        System.out.println(shape.getHeight());                                                                  
        System.out.println(shape.getRotateAngle());                                                             
        System.out.println(shape.isWordArt());                                                                  
        System.out.println(shape.getName());                                                                    
    }                                                                                                           
}                                                                                                               
                                                                                                                
watermarker.close();                                                                                            

Removing a particular shape

You can also remove a particular shape from the worksheet as shown in the below code sample.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetRemoveShape

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                                  
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"   
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                                
                                                                                                                    
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                      
                                                                                                                    
// Remove shape by index                                                                                            
content.getWorksheets().get_Item(0).getShapes().removeAt(0);                                                        
                                                                                                                    
// Remove shape by reference                                                                                        
content.getWorksheets().get_Item(0).getShapes().remove(content.getWorksheets().get_Item(0).getShapes().get_Item(0));
                                                                                                                    
watermarker.save("spreadsheet.xlsx");                                                                     
                                                                                                                    
watermarker.close();                                                                                                

Removing shapes with particular text formatting

You can also find and remove the shapes with a particular text formatting from an Excel document as shown in the below code sample.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetRemoveTextShapesWithParticularTextFormatting

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                                    
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"     
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                                  
                                                                                                                      
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                        
for (SpreadsheetWorksheet section : content.getWorksheets())                                                          
{                                                                                                                     
    for (int i = section.getShapes().getCount() - 1; i >= 0; i--)                                                     
    {                                                                                                                 
        for (FormattedTextFragment fragment : section.getShapes().get_Item(i).getFormattedTextFragments())            
        {                                                                                                             
            if (fragment.getForegroundColor().equals(Color.getRed()) && fragment.getFont().getFamilyName() == "Arial")
            {                                                                                                         
                section.getShapes().removeAt(i);                                                                      
                break;                                                                                                
            }                                                                                                         
        }                                                                                                             
    }                                                                                                                 
}                                                                                                                     
                                                                                                                      
watermarker.save("spreadsheet.xlsx");                                                                       
                                                                                                                      
watermarker.close();                                                                                                  

Using GroupDocs.Watermark for Java, you can also remove/replace hyperlink associated with a particular shape or chart inside an Excel document. Use following code sample to achieve this functionality.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetRemoveHyperlinks

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                             
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
                                                                                                                 
// Replace hyperlink                                                                                             
content.getWorksheets().get_Item(0).getCharts().get_Item(0).setHyperlink("https://www.aspose.com/");             
content.getWorksheets().get_Item(0).getShapes().get_Item(0).setHyperlink("https://www.groupdocs.com/");          
                                                                                                                 
// Remove hyperlink                                                                                              
content.getWorksheets().get_Item(1).getCharts().get_Item(0).setHyperlink(null);                                  
content.getWorksheets().get_Item(1).getShapes().get_Item(0).setHyperlink(null);                                  
                                                                                                                 
watermarker.save("spreadsheet.xlsx");                                                                  
                                                                                                                 
watermarker.close();                                                                                             
Warning
This feature is also supported for charts.

Replacing text for particular shapes

Since version 17.12, GroupDocs.Watermark supports replacing text for particular shapes in an Excel Worksheet. Following code sample shows the usage of this feature.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetReplaceTextForParticularShapes

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                             
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
for (SpreadsheetShape shape : content.getWorksheets().get_Item(0).getShapes())                                   
{                                                                                                                
    if (shape.getText() == "© Aspose 2016")                                                                      
    {                                                                                                            
        shape.setText("© GroupDocs 2017");                                                                       
    }                                                                                                            
}                                                                                                                
                                                                                                                 
watermarker.save("spreadsheet.xlsx");                                                                  
                                                                                                                 
watermarker.close();                                                                                             

Replacing text for particular shapes with formatted text

You can also replace the text of the shapes with formatted text as shown in the following code sample.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetReplaceTextWithFormattingForParticularShapes

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                                                          
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"                           
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                                                        
                                                                                                                                            
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                                              
for (SpreadsheetShape shape : content.getWorksheets().get_Item(0).getShapes())                                                              
{                                                                                                                                           
    if (shape.getText() == "© Aspose 2016")                                                                                                 
    {                                                                                                                                       
        shape.getFormattedTextFragments().clear();                                                                                          
        shape.getFormattedTextFragments().add("© GroupDocs 2017", new Font("Calibri", 19, FontStyle.Bold), Color.getRed(), Color.getAqua());
    }                                                                                                                                       
}                                                                                                                                           
                                                                                                                                            
watermarker.save("spreadsheet.xlsx");                                                                                             
                                                                                                                                            
watermarker.close();                                                                                                                        

Replacing shape image

GroupDocs.Watermark also allows you to replace the image of the particular shapes in an Excel Worksheet as shown in the following code sample. This feature is supported since version 17.12.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetReplaceImageOfParticularShapes

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                             
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
                                                                                                                 
File file = new File("test.png");                                                                         
byte[] imageBytes = new byte[(int) file.length()];                                                               
FileInputStream inputStream = new FileInputStream(file);                                                         
inputStream.read(imageBytes);                                                                                    
inputStream.close();                                                                                             
                                                                                                                 
for (SpreadsheetShape shape : content.getWorksheets().get_Item(0).getShapes())                                   
{                                                                                                                
    if (shape.getImage() != null)                                                                                
    {                                                                                                            
        shape.setImage(new SpreadsheetWatermarkableImage(imageBytes));                                           
    }                                                                                                            
}                                                                                                                
                                                                                                                 
watermarker.save("spreadsheet.xlsx");                                                                  
                                                                                                                 
watermarker.close();                                                                                             

Setting background image for particular shapes

Since version 17.12, GroupDocs.Watermark enables you to set the background image for the particular shapes in an Excel Worksheet. Following code sample shows the usage of this feature.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetSetBackgroundImageForParticularShapes

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                             
                                                                                                                 
File imageFile = new File("test.png");                                                                    
byte[] imageBytes = new byte[(int) imageFile.length()];                                                          
InputStream imageInputStream = new FileInputStream(imageFile);                                                   
imageInputStream.read(imageBytes);                                                                               
imageInputStream.close();                                                                                        
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
for (SpreadsheetShape shape : content.getWorksheets().get_Item(0).getShapes())                                   
{                                                                                                                
    if (shape.getText() == "© Aspose 2016")                                                                      
    {                                                                                                            
        shape.getImageFillFormat().setBackgroundImage(new SpreadsheetWatermarkableImage(imageBytes));            
        shape.getImageFillFormat().setTransparency(0.5);                                                         
        shape.getImageFillFormat().setTileAsTexture(true);                                                       
    }                                                                                                            
}                                                                                                                
                                                                                                                 
watermarker.save("spreadsheet.xlsx");                                                                  
                                                                                                                 
watermarker.close();                                                                                             

Updating shape properties

Since version 17.12, GroupDocs.Watermark also provides the feature of modifying properties (setX(), setY(), setWidth(), setHeight(), setRotateAngle() or setAlternativeText()) of particular shapes in an Excel Worksheet. Following code sample shows how to use this feature.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetUpdateShapeProperties

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Specify an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker("spreadsheet.xlsx", loadOptions);                             
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
for (SpreadsheetShape shape : content.getWorksheets().get_Item(0).getShapes())                                   
{                                                                                                                
    if (shape.getText() == "© Aspose 2019")                                                                      
    {                                                                                                            
        shape.setAlternativeText("watermark");                                                                   
        shape.setRotateAngle(30);                                                                                
        shape.setX(200);                                                                                         
        shape.setY(200);                                                                                         
        shape.setWidth(400);                                                                                     
        shape.setHeight(100);                                                                                    
    }                                                                                                            
}                                                                                                                
                                                                                                                 
watermarker.save("spreadsheet.xlsx");                                                                  
                                                                                                                 
watermarker.close();