Category / Section
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.
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