How to create and update the chart with external data range using XlsIO?

2 mins read

This article explains about creating and updating chart with external data range using XlsIO in C#/VB.NET

Is it possible to create or update chart with external data range?

No. It is not possible.

XlsIO does not have support for creating or updating data with external data range in XlsIO. However, we have provided a workaround to update the chart data from the external workbook.

To update the chart with external data range, you need to follow the below steps.

Steps to update chart with external data

Open the input file which contains only and update the data manually.

Then open both the Excel files containing data (DataWorkbook) and chart with data (ChartWorkbook) using XlsIO application.

IWorkbook dataWorkbook,chartWorkbook;
 
//Open existing workbook with data entered
Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
Stream chartStream = assembly.GetManifestResourceStream("XlsIOSample.ChartWorkbook.xlsx");
Stream dataStream = assembly.GetManifestResourceStream("XlsIOSample.DataWorkbook.xlsx");
 
chartWorkbook = application.Workbooks.Open(chartStream);
dataWorkbook = application.Workbooks.Open(dataStream);

Get the data range from “DataWorkbook” and copy it to the “ChartWorkbook” in the chart’s data range.
```
//Here, A1:B6 is the data for the chart
dataSheet["A1:B6"].CopyTo(dataChartSheet["A1:B6"]);
```

Save the Excel file contains chart. Now the data will be updated.
Whenever the XlsIO application is executed, the chart data range will be copied to “ChartWorkbook” and its chart will be updated.

To know more about charts in XlsIO, please refer the documentation.

Download input files

Download Complete Sample

The following C#/VB.NET complete code snippet shows how to update chart with external data range in XlsIO.

using Syncfusion.XlsIO;
using System.IO;
using System.Reflection;
 
namespace XlsIO_Sample
{
    class Program
    {
        public static void Main(string[] args)
        {
            //Instantiate the spreadsheet creation engine
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                IWorkbook dataWorkbook,chartWorkbook;
 
                //Open existing workbook with data entered
                Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
                Stream chartStream = assembly.GetManifestResourceStream("XlsIOSample.ChartWorkbook.xlsx");
                Stream dataStream = assembly.GetManifestResourceStream("XlsIOSample.DataWorkbook.xlsx");
 
                chartWorkbook = application.Workbooks.Open(chartStream);
                dataWorkbook = application.Workbooks.Open(dataStream);
 
                IWorksheet dataSheet = dataWorkbook.Worksheets[0];
                IWorksheet dataChartSheet = chartWorkbook.Worksheets[0];
 
                //Here, A1:B6 is the data for the chart
                dataSheet["A1:B6"].CopyTo(dataChartSheet["A1:B6"]);
                
                //Saving the PDF document
                Stream stream = File.Create("Output.xlsx");
                chartWorkbook.SaveAs(stream);
             }
        }
    }
}

Imports Syncfusion.XlsIO
Imports System.IO
Imports System.Reflection
 
Namespace XlsIO_Sample
 
    Class Program
 
        Public Shared Sub Main(ByVal args As String())
            'Instantiate a spreadsheet creation engine
            Using excelEngine As ExcelEngine = New ExcelEngine()
 
                Dim application As IApplication = excelEngine.Excel
                Dim dataWorkbook, chartWorkbook As IWorkbook
 
                'Opening existing workbook with data entered
                Dim assembly As Assembly = GetType(Program).GetTypeInfo().Assembly
                Dim chartStream As Stream = assembly.GetManifestResourceStream("XlsIO_Sample.ChartWorkbook.xlsx")
                Dim dataStream As Stream = assembly.GetManifestResourceStream("XlsIO_Sample.DataWorkbook.xlsx")
 
                chartWorkbook = application.Workbooks.Open(chartStream)
                dataWorkbook = application.Workbooks.Open(dataStream)
 
                Dim dataSheet As IWorksheet = dataWorkbook.Worksheets(0)
                Dim dataChartSheet As IWorksheet = chartWorkbook.Worksheets(0)
 
                'The "A1:B6" contains the data for the chart
                dataSheet("A1:B6").CopyTo(dataChartSheet("A1:B6"))
 
                'Save and close the workbook
                Dim stream As Stream = File.Create("Output.xlsx")
                chartWorkbook.SaveAs(stream)
            End Using
        End Sub
    End Class
End Namespace

Did you find this information helpful?

Yes

Comments (0)