How to convert Syncfusion's WinForms chart to Excel chart using XlsIO

1 min read

This article explains how to convert windows chart to Excel chart using XlsIO in C#/VB.NET

How to convert windows chart to Excel chart?

Windows chart is an easily configurable, presentation quality business chart control. XlsIO has support for creating and modifying Excel charts inside a workbook. We can convert the Essential chart to Excel Chart using XlsIO by getting the data from the Essential chart control and providing necessary values to the Excel chart.

The below screenshot shows the Syncfusion windows chart.

Windows Chart

To convert windows chart to Excel chart, you need to follow the below steps.

Steps to convert Windows chart to Excel chart

We need chart data to create a chart in Excel document using XlsIO. So, first we need to get chart data from windows chart control.

//Getting X and Y axis data and Saving in Excel document
worksheet.Range[i,1].Number = this.windowsChart.Series[0].Points[i-1].X; 
worksheet.Range[i,2].Number = this.windowsChart.Series[0].Points[i-1].YValues[0];

The Excel chart is created with given data.

//Create a chart worksheet
IChart chart = workbook.Charts.Add("Essential Chart");
 
//Specify the title of the Chart
chart.ChartTitle = windowsChart.Text;
chart.ChartTitleArea.Size = windowsChart.Font.Size;
chart.PrimaryCategoryAxis.Title = windowsChart.PrimaryYAxis.Title;
chart.PrimaryValueAxis.Title = windowsChart.PrimaryXAxis.Title;
 
//Initialize a new series instance and add it to the series collection of the chart 
IChartSerie series = chart.Series.Add();
 
//Specify the chart type of the series
series.SerieType = ExcelChartType.Bar_Stacked;
 
//Specify the name of the series.This will be displayed as the text of the legend
series.Name = "Sales Performance";
 
//Specify the value ranges for the series
series.Values = worksheet.Range["B1:B5"];
 
//Specify the category labels for the series
series.CategoryLabels = worksheet.Range["A1:A5"];
 
 
//Legend settings
chart.Legend.TextArea.Size = windowsChart.Legend.Font.Size * 1.5;
chart.Legend.X = 2400;
chart.Legend.Y = 750;
            
//Grid lines
chart.PrimaryCategoryAxis.HasMajorGridLines = windowsChart.PrimaryYAxis.DrawGrid;

The chart control skin styles can be used for Excel chart by getting equivalent chart area and plot area styles.

To know more about creating Excel chart using XlsIO, please refer the documentation.

Download Complete Sample

The following C#/VB.NET complete code snippet shows how to convert Syncfusion Windows chart to Excel chart using XlsIO.

//Instantiate the spreadsheet creation engine
ExcelEngine excelEngine = new ExcelEngine();
 
//Instantiate the excel application object
IApplication application = excelEngine.Excel;
 
//The workbook is created
IWorkbook workbook = application.Workbooks.Create(1);
 
//The first worksheet object in the worksheets collection is accessed
IWorksheet worksheet = workbook.Worksheets[0];
            
//Getting data points from chart control
for (int i = 1; i <= windowsChart.Series[0].Points.Count; i++)
{
    worksheet.Range[i, 1].Number = windowsChart.Series[0].Points[i - 1].X;
    worksheet.Range[i, 2].Number = windowsChart.Series[0].Points[i - 1].YValues[0];
}
 
//Create a chart worksheet
IChart chart = workbook.Charts.Add("Essential Chart");
 
//Specify the title of the Chart
chart.ChartTitle = windowsChart.Text;
chart.ChartTitleArea.Size = windowsChart.Font.Size;
chart.PrimaryCategoryAxis.Title = windowsChart.PrimaryYAxis.Title;
chart.PrimaryValueAxis.Title = windowsChart.PrimaryXAxis.Title;
 
//Initialize a new series instance and add it to the series collection of the chart 
IChartSerie series = chart.Series.Add();
 
//Specify the chart type of the series
series.SerieType = ExcelChartType.Bar_Stacked;
 
//Specify the name of the series.This will be displayed as the text of the legend
series.Name = "Sales Performance";
 
//Specify the value ranges for the series
series.Values = worksheet.Range["B1:B5"];
 
//Specify the category labels for the series
series.CategoryLabels = worksheet.Range["A1:A5"];
 
//Legend settings
chart.Legend.TextArea.Size = windowsChart.Legend.Font.Size * 1.5;
chart.Legend.X = 2400;
chart.Legend.Y = 750;
            
//Grid lines
chart.PrimaryCategoryAxis.HasMajorGridLines = windowsChart.PrimaryYAxis.DrawGrid;
           
//Make the chart as active sheet
chart.Activate();
 
//Format Series
series.SerieFormat.Fill.ForeColor= windowsChart.Series[0].BackColor;
 
// Set chart area styles
if (windowsChart.BackInterior.GradientColors.Count > 0)
{
    if (windowsChart.BackInterior.GradientColors.Count == 1)
    {
        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient;
 
        chart.ChartArea.Fill.GradientStyle = GetXlsIOStyle(windowsChart.BackInterior.GradientStyle);
 
        chart.ChartArea.Fill.GradientColorType = ExcelGradientColor.OneColor;
 
        chart.ChartArea.Fill.ForeColor = windowsChart.BackInterior.ForeColor;
    }
 
    if (windowsChart.BackInterior.GradientColors.Count == 2)
    {
        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient;
 
        chart.ChartArea.Fill.GradientStyle = GetXlsIOStyle(windowsChart.BackInterior.GradientStyle);
 
        chart.ChartArea.Fill.GradientColorType = ExcelGradientColor.TwoColor;
 
        chart.ChartArea.Fill.BackColor = windowsChart.BackInterior.BackColor;
 
        chart.ChartArea.Fill.ForeColor = windowsChart.BackInterior.ForeColor;
    }
 
    if (windowsChart.BackInterior.GradientColors.Count > 2)
    {
        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient;
 
        chart.ChartArea.Fill.GradientStyle = GetXlsIOStyle(windowsChart.BackInterior.GradientStyle);
 
        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient;
        chart.ChartArea.Fill.GradientColorType = ExcelGradientColor.MultiColor;
        GradientStops result = new GradientStops();
        GradientStopImpl stop1;
        int colorStart = 0, colorStop = 10000;
        foreach (System.Drawing.Color color in windowsChart.BackInterior.GradientColors)
        {
            stop1 = new GradientStopImpl(color, colorStart, colorStop);
            (chart.ChartArea.Fill as ShapeFillImpl).GradientStops.Add(stop1);
            colorStart = colorStop;
            colorStop += 10000;
        }
    }
}
 
// set plot area styles
if (windowsChart.ChartInterior.GradientColors.Count > 0)
{
    if (windowsChart.ChartInterior.GradientColors.Count == 1)
    {
        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient;
 
        chart.PlotArea.Fill.GradientStyle = GetXlsIOStyle(windowsChart.ChartInterior.GradientStyle);
 
        chart.PlotArea.Fill.GradientColorType = ExcelGradientColor.OneColor;
 
        chart.PlotArea.Fill.ForeColor = windowsChart.ChartInterior.ForeColor;
    }
 
    if (windowsChart.ChartInterior.GradientColors.Count == 2)
    {
        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient;
 
        chart.PlotArea.Fill.GradientStyle = GetXlsIOStyle(windowsChart.ChartInterior.GradientStyle);
 
        chart.PlotArea.Fill.GradientColorType = ExcelGradientColor.TwoColor;
 
        chart.PlotArea.Fill.BackColor = windowsChart.ChartInterior.BackColor;
 
        chart.PlotArea.Fill.ForeColor = windowsChart.ChartInterior.ForeColor;
    }
 
    if (windowsChart.ChartInterior.GradientColors.Count > 2)
    {
        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient;
 
        chart.PlotArea.Fill.GradientStyle = GetXlsIOStyle(windowsChart.ChartInterior.GradientStyle);
 
        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient;
        chart.PlotArea.Fill.GradientColorType = ExcelGradientColor.MultiColor;
        GradientStops result = new GradientStops();
        GradientStopImpl stop1;
        int colorStart = 0, colorStop = 10000; // Can be changed
        foreach (System.Drawing.Color color in windowsChart.ChartInterior.GradientColors)
        {
            stop1 = new GradientStopImpl(color, colorStart, colorStop);
            (chart.PlotArea.Fill as ShapeFillImpl).GradientStops.Add(stop1);
            colorStart = colorStop;
            colorStop += 10000;
        }
    }
}
 
workbook.SaveAs("Sample.xlsx");
workbook.Close();
      
// Provides XlsIO equivalent gradient style from Windows Forms chart
public ExcelGradientStyle GetXlsIOStyle(GradientStyle style)
{
   ExcelGradientStyle xlsIoStyle = ExcelGradientStyle.Diagonl_Down;
   switch (style)
   {
       case GradientStyle.Horizontal:
       xlsIoStyle = ExcelGradientStyle.Vertical;
       break;
       case GradientStyle.Vertical:
       xlsIoStyle = ExcelGradientStyle.Horizontal;
       break;
       case GradientStyle.ForwardDiagonal:
       xlsIoStyle = ExcelGradientStyle.Diagonl_Up;
       break;
       case GradientStyle.BackwardDiagonal:
       xlsIoStyle = ExcelGradientStyle.Diagonl_Down;
       break;
       case GradientStyle.PathEllipse:
       xlsIoStyle = ExcelGradientStyle.From_Center;
       break;
       case GradientStyle.PathRectangle:
       xlsIoStyle = ExcelGradientStyle.From_Corner;
       break;
   }
   return xlsIoStyle;
}

'Instantiate the spreadsheet creation engine
Dim excelEngine As ExcelEngine = New ExcelEngine
 
'Instantiate the excel application object
Dim application As IApplication = excelEngine.Excel
 
'The workbook is created
Dim workbook As IWorkbook = application.Workbooks.Create(1)
 
'The first worksheet object in the worksheets collection is accessed
Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
Dim i As Integer = 1
Do While (i <= Me.windowsChart.Series(0).Points.Count)    worksheet.Range(i, 1).Number = Me.windowsChart.Series(0).Points((i - 1)).X    worksheet.Range(i, 2).Number = Me.windowsChart.Series(0).Points((i - 1)).YValues(0)    i = (i + 1)LoopDim chart As IChart = workbook.Charts.Add("Essential Chart")
' Specify the title of the Chartchart.ChartTitle = Me.windowsChart.Text
chart.ChartTitleArea.Size = windowsChart.Font.Sizechart.PrimaryCategoryAxis.Title = Me.windowsChart.PrimaryYAxis.Titlechart.PrimaryValueAxis.Title = Me.windowsChart.PrimaryXAxis.Title
Dim series As IChartSerie = chart.Series.Add
' Specify the chart type of the series. series.SerieType = ExcelChartType.Bar_Stacked
' Specify the name of the series. This will be displayed as the text of the legendseries.Name = "Sales Performance"
' Specify the value ranges for the series. series.Values = worksheet.Range("B1:B5")
' Specify the Category labels for the series. series.CategoryLabels = worksheet.Range("A1:A5")
' Legend settings
chart.Legend.TextArea.Size = windowsChart.Legend.Font.Size * 1.5
chart.Legend.X = 2400
chart.Legend.Y = 750
            
' Grid lines
chart.PrimaryCategoryAxis.HasMajorGridLines = windowsChart.PrimaryYAxis.DrawGrid
           
' Make the chart as active sheet
chart.Activate
 
' Format Series
series.SerieFormat.Fill.ForeColor= windowsChart.Series(0).BackColor
 
' Set chart area styles
If (Me.windowsChart.BackInterior.GradientColors.Count > 0) Then    If (Me.windowsChart.BackInterior.GradientColors.Count = 1) Then        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient        chart.ChartArea.Fill.GradientStyle = GetXlsIOStyle(Me.windowsChart.BackInterior.GradientStyle)        chart.ChartArea.Fill.GradientColorType = ExcelGradientColor.OneColor        chart.ChartArea.Fill.ForeColor = Me.windowsChart.BackInterior.ForeColor    End If        If (Me.windowsChart.BackInterior.GradientColors.Count = 2) Then        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient        chart.ChartArea.Fill.GradientStyle = GetXlsIOStyle(Me.windowsChart.BackInterior.GradientStyle)        chart.ChartArea.Fill.GradientColorType = ExcelGradientColor.TwoColor        chart.ChartArea.Fill.BackColor = Me.windowsChart.BackInterior.BackColor        chart.ChartArea.Fill.ForeColor = Me.windowsChart.BackInterior.ForeColor    End If        If (Me.windowsChart.BackInterior.GradientColors.Count > 2) Then        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient        chart.ChartArea.Fill.GradientStyle = GetXlsIOStyle(Me.windowsChart.BackInterior.GradientStyle)        chart.ChartArea.Fill.FillType = ExcelFillType.Gradient        chart.ChartArea.Fill.GradientColorType = ExcelGradientColor.MultiColor        Dim result As GradientStops = New GradientStops        Dim stop1 As GradientStopImpl        Dim colorStop As Integer = 10000        Dim colorStart As Integer = 0        For Each color As System.Drawing.Color In Me.windowsChart.BackInterior.GradientColors            stop1 = New GradientStopImpl(color, colorStart, colorStop)            CType(chart.ChartArea.Fill,ShapeFillImpl).GradientStops.Add(stop1)            colorStart = colorStop            colorStop = (colorStop + 10000)        Next    End If    End If
 
' Set plot area stylesIf (Me.windowsChart.ChartInterior.GradientColors.Count > 0) Then    If (Me.windowsChart.ChartInterior.GradientColors.Count = 1) Then        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient        chart.PlotArea.Fill.GradientStyle = GetXlsIOStyle(Me.windowsChart.ChartInterior.GradientStyle)        chart.PlotArea.Fill.GradientColorType = ExcelGradientColor.OneColor        chart.PlotArea.Fill.ForeColor = Me.windowsChart.ChartInterior.ForeColor    End If        If (Me.windowsChart.ChartInterior.GradientColors.Count = 2) Then        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient        chart.PlotArea.Fill.GradientStyle = GetXlsIOStyle(Me.windowsChart.ChartInterior.GradientStyle)        chart.PlotArea.Fill.GradientColorType = ExcelGradientColor.TwoColor        chart.PlotArea.Fill.BackColor = Me.windowsChart.ChartInterior.BackColor        chart.PlotArea.Fill.ForeColor = Me.windowsChart.ChartInterior.ForeColor    End If        If (Me.windowsChart.ChartInterior.GradientColors.Count > 2) Then        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient        chart.PlotArea.Fill.GradientStyle = GetXlsIOStyle(Me.windowsChart.ChartInterior.GradientStyle)        chart.PlotArea.Fill.FillType = ExcelFillType.Gradient        chart.PlotArea.Fill.GradientColorType = ExcelGradientColor.MultiColor        Dim result As GradientStops = New GradientStops        Dim stop1 As GradientStopImpl        Dim colorStop As Integer = 10000        Dim colorStart As Integer = 0
       For Each color As System.Drawing.Color In Me.windowsChart.ChartInterior.GradientColors            stop1 = New GradientStopImpl(color, colorStart, colorStop)            CType(chart.PlotArea.Fill,ShapeFillImpl).GradientStops.Add(stop1)            colorStart = colorStop            colorStop = (colorStop + 10000)        Next    End If    End If
workbook.SaveAs("Sample.xlsx")workbook.Close' Provides XlsIO equivalent gradient style from Windows Forms chartPublic Function GetXlsIOStyle(ByVal style As GradientStyle) As ExcelGradientStyle
  Dim xlsIoStyle As ExcelGradientStyle = ExcelGradientStyle.Diagonl_Down  Select Case (style)      Case GradientStyle.Horizontal        xlsIoStyle = ExcelGradientStyle.Vertical      Case GradientStyle.Vertical        xlsIoStyle = ExcelGradientStyle.Horizontal      Case GradientStyle.ForwardDiagonal         xlsIoStyle = ExcelGradientStyle.Diagonl_Up      Case GradientStyle.BackwardDiagonal         xlsIoStyle = ExcelGradientStyle.Diagonl_Down      Case GradientStyle.PathEllipse         xlsIoStyle = ExcelGradientStyle.From_Center      Case GradientStyle.PathRectangle         xlsIoStyle = ExcelGradientStyle.From_Corner   End Select           Return xlsIoStyleEnd Function

The below screenshot shows the output generated by the above code.

Did you find this information helpful?

Yes

Comments (0)

How to convert Syncfusion's WinForms chart to Excel chart using XlsIO

Access denied