Programmatically Add Linked Pictures in Excel XLSX Using C# .NET and Java

When working with data in an Excel file, often, it is required to display the content of a cell or range of cells to another location in real time. That said, data change in the actual range should be automatically reflected in the other location without manual intervention to update the data. A few examples include:

• a live data feed
• data snapshot that changes corresponding to a value in some other cell (like a dropdown menu or sorting field)
• an assembled output sheet consisting of data snapshots from other worksheets
• building a header that does not change when changes happen on columns like hiding/unhiding, width change, and many more.

In such a situation, the Linked Pictures feature in Excel proves useful. It takes a real-time snapshot of a data range or visual data representation and is updated automatically when the source is changed.

This blog will show how to work with Linked Pictures programmatically using the GcExcel API for C#, .NET 6, and Java.

Want to Try it out? Download GrapeCity Documents Today!

Use-case

The CFO of your company has an upcoming board meeting and needs several reports from your team. You are asked to prepare a summary report so that your executive can easily show data by country, category as well as the top 10 performing products. These reports must be dynamic (if an item changes, the charts/graphs/data must also change). All of the data to create these reports is contained on other worksheets within the Excel file. The summary report must show the updated values from the visuals or data ranges that include the following:

• A Pivot Table containing the sales details about the top-selling products is on a “Top 10 Products” sheet.
• Simple Range of cells having information about the sales figures in different countries on a “Sales by Country” sheet.
• Bar Chart showing the sales summary by product categories on a “Sales by Category” sheet.

Image showing the Excel file with Sales data and summary on separate sheets

Image showing the output summary sheet

There are several ways to create these data visualizations/summaries to meet the above requirement, including

1. A copy-paste of the visuals and data range
2. Use of the Cross-reference formula
3. Copy content as Linked Pictures

An analysis of each of the above methods shows that, although simple, the “Copy/Paste” approach would require manual effort to update each time the data changes. The “Cross-reference formulas” approach would take a lot of effort to fetch individual pieces of data and copy the style and formatting. Thus, the approach of “Linked Pictures”, whereby the pictures of the data are linked to the actual data (and thus will automatically change when data changes), seems suitable here.

Understanding the Linked Picture API in GcExcel

To add a Linked picture using GcExcel, use the AddCameraPicture method of the IShape interface. It comes with several overloads taking the following parameters:

Note: In the upcoming release, the AddCameraPicture method will have a few new overloads that will accept IRange rather than location and size to position the picture directly along the specified cell range.

Adding Linked Picture for a Chart

As mentioned in the API description above, to add a linked picture for the chart, use the range reference of the cells spanned by the chart object. If you know the cell range, you can add the range string to the AddCameraPicture method. Otherwise, you can use the TopLeftCell and BottomRightCell methods from the IShape interface, as shown in the code snippets below:

C# .NET

//Link a chart
IWorksheet byCategory = workbook.Worksheets["Sales by Category"];
byCategory.SheetView.DisplayGridlines = false;
IShape chart = byCategory.Shapes[0];
var rangeChart = chart.TopLeftCell + ":" + chart.BottomRightCell;
overview.Shapes.AddCameraPicture(rangeChart, 0, 40, 380, 230);

Java

//Link a chart
IWorksheet byCategory = workbook.getWorksheets().get("Sales by Category");
byCategory.getSheetView().setDisplayGridlines(false);
IShape chart = byCategory.getShapes().get(0);
var rangeChart = chart.getTopLeftCell() + ":" + chart.getBottomRightCell();
overview.getShapes().addCameraPicture(rangeChart, 0, 40, 380, 230);

Adding Linked Picture for a Pivot Table

To add a Linked Picture for a Pivot Table, get the cell range for which you want to take the snapshot. IPivotTable interface provides several options to get the range of Pivot cells, such as TableRange1,TableRange2, DataBodyRange, RowRange, ColumnRange, and PageRange, corresponding to the range you want to focus on at the time.

We wanted to get the whole of the Pivot table range. Thus we used the TableRange1 property with the AddCameraPicture method, as shown in the code snippet below:

C# .NET

//Link a pivot
IWorksheet top10 = workbook.Worksheets["Top 10 Products"];
IPivotTable pivot  = top10.PivotTables[0];
var rangePivot = pivot.TableRange1;
overview.Shapes.AddCameraPicture(rangePivot.ToString(), 390, 40, 230, 230);

Java

//Link a pivot
IWorksheet top10 = workbook.getWorksheets().get("Top 10 Products");
IPivotTable pivot  = top10.getPivotTables().get(0);
var rangePivot = pivot.getTableRange1();
overview.getShapes().addCameraPicture(rangePivot.toString(), 390, 40, 230, 230);

Adding a Linked Picture for a Range

To add a Linked picture for a simple data range, get the range reference using the GetUsedRange method with the AddCameraPicture method, as depicted in the code below:

C# .NET

//Link a range containing formula
IWorksheet byCountry = workbook.Worksheets["Sales by Country"];
IRange range = byCountry.GetUsedRange();
overview.Shapes.AddCameraPicture(range.ToString(), 630, 40, 230, 230);

Java

//Link a range containing formula
IWorksheet byCountry = workbook.getWorksheets().get("Sales by Country");
IRange range = byCountry.getUsedRange();
overview.getShapes().addCameraPicture(range.toString(), 630, 40, 230, 230);

Download the samples - .NET | Java

Conclusion

Although there are a variety of ways to display data visualizations in Excel, the Linked Picture is a good alternative feature in excel to use to ensure the image is updated based on the most recent data. Whether the data resides on a single worksheet, multiple worksheets, or even different Excel files, utilizing Linked Pictures is an efficient way to work with live snapshots of a data range.

Have a look at more samples and documentation.

Try and work with Linked Pictures in Excel files using GcExcel and share your experience with us. 

Want to Try it out? Download GrapeCity Documents Today!