How to Create and Update Chart with External Data in WinForms XlsIO?
This article explains about creating and updating a chart with external data range using WinForms Excel in C#/VB.NET
Is it possible to create or update chart with external data range?
No, it is not possible.
XlsIO does not support for creating or updating charts with an external data range in XlsIO. However, we have provided a workaround to update the chart data from an external workbook.
To update the chart with an 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 an 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
Conclusion
I hope you enjoyed learning about how to create and update chart with external data in WinForms XlsIO.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!