Vfrdtyky

Suppose I have a Charting.Chart:

that I would like to export to an Excel.Workbook.Worksheet so that I can "play" with the data later (for example drag-and-dropping more data on the Excel chart etc.):

Please do not mind the differences in the second chart, something as close as possible to the first one would be the optimal solution

Is there any easy way to export the first chart retaining all its properties or at least the ones accepted by Excel, or do I have to go through every single property? E.g.:

myCht.Title = myUserFormChart.Titles(0).Text

mySeries = myCht.Chart.SeriesCollection.NewSeries()

mySeries.Name = myUserFormChart.Series(0).Name

[...]

Basically you need to some kind of an export function. You want to export your application's chart to an excel worksheet. And you need an easy way to do that. According to System.Windows.Forms.DataVisualization.Charting.Chart class documentation and excel chart object's documentation there isn't an easy way to do the conversion, they have some similarities but you have to go through every single property.

However, if I was you I would use an external 3rd party open-source library like EPPlus which supports many excel features (charts included) in order to create a solid "export to excel" functionality in my application.

It is not as hard as it seems and it can be relative easy to adapt it in various chart types and / or data. An example code is as follows (don't forget to install EPPlus nuget package):

Private Sub ExportToExcel_Click(sender As Object, e As EventArgs) Handles Button5.Click

    Using theExcel As OfficeOpenXml.ExcelPackage = New OfficeOpenXml.ExcelPackage

        Dim theDataWorkSheet As OfficeOpenXml.ExcelWorksheet = theExcel.Workbook.Worksheets.Add("Data")



        With theDataWorkSheet

            'Create the data cells here...

            'You can get the data from your in program arrays or directly from your chart class...

            .Cells("A1").Value = 10

            .Cells("A2").Value = 20

            .Cells("A3").Value = 30



            .Cells("B1").Value = 1

            .Cells("B2").Value = 2

            .Cells("B3").Value = 3

        End With



        Dim theChartWorkSheet As OfficeOpenXml.ExcelWorksheet = theExcel.Workbook.Worksheets.Add("Chart")

        Dim theChart As OfficeOpenXml.Drawing.Chart.ExcelRadarChart = theChartWorkSheet.Drawings.AddChart("Chart", OfficeOpenXml.Drawing.Chart.eChartType.Radar)



        With theChart

            'Manipulate the excel chart here...



            .SetPosition(1, 0, 2, 0)

            .SetSize(400, 400)

            .Series.Add(theDataWorkSheet.Cells(1, 1, 3, 1), theDataWorkSheet.Cells(1, 2, 3, 2))

            .Title.Text = "Chart title"



            'all the other required excel chart properties...

        End With



        'Finally save the excel file...

        theExcel.SaveAs(New IO.FileInfo("c:test.xlsx"))

    End Using

End Sub

Refer to EPPlus documentation for more information.

1. Getting started


2. Shapes, Pictures and Charts

Hope this helps.