Picture Shapes

You can insert pictures on cells of a spreadsheet by using the AddPicture method of the IShapes interface. The method allows you to add a picture at a specific location or to a specific range. The IPictureFormat interface in DsExcel allows users to customize and format pictures while working in a spreadsheet.

Refer to the following example code when working with picture in DsExcel:

// Add a picture through stream
string path = @"Images\flower.jpg";
FileStream stream = System.IO.File.Open(path, FileMode.Open);
IShape picture = worksheet.Shapes.AddPicture(stream, ImageType.JPG, 480, 10, 100, 100);

// Add a picture through file at specific location
// IShape picture = worksheet.getShapes().addPicture(@"Images\flower.jpg", 480, 10, 100, 100);

// Add a picture to a specific range 
// IShape pictureInRange = worksheet.getShapes().addPicture("flower.jpg", worksheet.Range["D3:F5"]);

// Fill the inserted picture
picture.getFill().solid();
picture.getFill().getColor().setRGB(Color.AliceBlue);
//Customize the inserted picture
picture.getPictureFormat().getCrop().setPictureWidth(80);

Refer to the below example code to assign a name to a picture.

// create a new workbook
Workbook workbook = new Workbook();
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Create shape with custom name
IShape shape = worksheet.getShapes().addPicture("Custom Name to Image", "image.png", 10, 10, 250, 150);

// Get the picture name
System.out.println(shape.getName().toString());

// save to an excel file
workbook.save("PictureName.xlsx");

Linked Picture

Linked Picture, also known as Camera shape or picture, refers to a real-time dynamic snapshot of the copied range. That is, as values in the copied cell range change, the same is automatically reflected in its snapshot as well. In Microsoft Excel, this feature is provided through Special Paste option named "Linked Picture". This feature is especially useful in case of dashboards and reports as you can display dynamically changing images and can even resize them according to the available space.

DsExcel Java allows you to create these dynamic linked pictures through addCameraPicture method of the IShape interface. This method accepts the source range of the linked picture and position coordinates of the target range with respect to the document as parameters. You can also specify the cell range where you want to add the linked picture using another overload of this method. As the linked picture is also just another picture, it can be resized and formatted similar to any other picture.

Workbook workbook = new Workbook();
workbook.getActiveSheet().getRange("A1").getInterior().setColor(Color.GetBlue());
workbook.getActiveSheet().getRange("B5").getInterior().setColor(Color.GetYellow());
//Add linked picture at a specific position
IShape shape = workbook.getActiveSheet().getShapes().addCameraPicture("=$A$1:$B$4", 100, 100);
////Add linked picture at a specific cell range
IShape shapeRange = workbook.getActiveSheet().getShapes().addCameraPicture("=$A$1:$B$5", worksheet.setRange["G1:H5"]);
        
workbook.save("LinkedPicture.xlsx");

Note: The target range and the linked picture to be added must exist in the same worksheet. Otherwise, it results into an InvalidOperationException.

Format Linked Picture

You can set whether the linked picture should display with a transparent background by setting the IPictureFormat.setTransparentBackground method to true.

// Set transparent background
shape.getPictureFormat().setTransparentBackground(true);
// Set degree of transparency
shape.getPictureFormat().setTransparency(0.8);

You can also convert a linked picture to a usual static picture by setting the IPictureFormat.setReference method to null. Whereas, when this property is set to a reference for a normal picture, it is converted into a linked picture. For detailed implementation of converting a normal picture to a linked picture or vice versa, see online demo.

Picture Transparency

DsExcel supports controlling the transparency of an image by providing setTransparency method in IPictureFormat interface. The value of transparency can vary between 0.0 (opaque) to 1.0 (clear).

Refer to the following example code to set the transparency of an image.

// Create a new workbook
Workbook workbook = new Workbook();

// Use sheet index to get worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add a picture
IShape picture = worksheet.getShapes().addPicture("Image.png", 20, 20, 490, 120);

// Set picture's transparency as 60%.
picture.getPictureFormat().setTransparency(0.6);

// Save to an excel file
workbook.save("ImageTransparency.xlsx");