Export chart to excel

[Laura Jordan]

Hi,

I am using the Chart Column control and I need to export the chart to an excel file. Could you help me with that?

Really appreciate the support,

Thanks

[Dharanidharan Dharmasivam]

Hi Laura, 

 

We have analyzed your query and prepared a sample with respect to your requirement. In this, we have exported the chart on a button click. Find the code snippet below. 

 

ASP.NET MVC 

<input type="button" value="Excel Export" onclick="excelExport()" /> 

 

function excelExport() { 

           chart = $("#container").ejChart("instance"); 

            exporting = chart.model.exportSettings; 

                   exporting.fileName = "Chart"; 

                   exporting.orientation = "portrait"; 

                   exporting.angle = 0; 

                   exporting.action = "ExportChart" 

                   exporting.type ="xlsx"; 

                   exporting.mode = "server"; 

                   chart.export(); 

        } 

//Controller 

public void ExportChart(string Data, string ChartModel) 

        { 

           //... 

        } 

 

Screenshot for excel export: 

 

 

Sample for reference can be find from below link. 

http://www.syncfusion.com/downloads/support/forum/129372/ze/excelExport-1782466976  

 

Thanks, 

Dharani. 

[Laura Jordan]

Hi,

I am getting the following error when tried to export:

A potentially dangerous Request.Form value was detected from the client (ChartModel="...ies.name# <br/> #point.x# : #p...").

Description: ASP.NET has detected data in the request that is potentially dangerous because it might include HTML markup or script. The data might represent an attempt to compromise the security of your application, such as a cross-site scripting attack. If this type of input is appropriate in your application, you can include code in a web page to explicitly allow it. For more information, see http://go.microsoft.com/fwlink/?LinkID=212874. 

Exception Details: System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (ChartModel="...ies.name# <br/> #point.x# : #p...").

This is my code

@(Html.EJ().Chart("cumplimiento")

    .PrimaryYAxis(pr => pr.LabelFormat("{value}%").Range(ra => ra.Max(100).Min(0))/*.Title(tl => tl.Text("Montos"))*/)

    .CommonSeriesOptions(cr => cr.Type(SeriesType.Column).EnableAnimation(true).Marker(mr => mr.DataLabel(dt => dt.Visible(true).TextPosition(TextPosition.Middle).HorizontalTextAlignment(Syncfusion.JavaScript.DataVisualization.TextAlignment.Center).Font(fn => fn.Size("13px").FontWeight(ChartFontWeight.Bold).Color("rgb(2, 2, 2)").FontFamily("Segoe UI"))))

                            .Tooltip(tt => tt.Visible(true).Format("#series.name# <br/> #point.x# : #point.y#")))

                            //.Tooltip(tt => tt.Visible(true).Format("#point.x# : #point.y# #series.name# ")))

                            .Series(sr =>

                            {

                                sr.Points(pt =>

                                {

                                    pt.X("Meta").Y(Convert.ToDouble(100)).Fill("rgb(89, 244, 66)").Add();

                                    pt.X("Cobrado").Y(Convert.ToDouble(Model.PorcientoCobrado.Value.ToString("0.00"))).Fill("rgb(53, 194, 252)").Add();

                                    pt.X("Por Cobrar").Y(Convert.ToDouble(Model.PorCientoPendiente.Value.ToString("0.00"))).Fill("rgb(255, 0, 0)").Add();

                                }).Name("")/*Fill("rgb(115, 230, 0)")*/.Add();

                                //sr.Points(pt =>

                                //{

                                //    pt.X("Cobrado").Y(Convert.ToDouble(Model.CobradoTotal)).Add();

                                //}).Name("Cobrado").Fill("rgb(53, 194, 252)")/*.Fill("rgba(135,206,235,1)")*/.Add();

                                //sr.Points(pt =>

                                //{

                                //    pt.X("Por Cobrar").Y(Convert.ToDouble(Model.PendienteTotal)).Add();

                                //}).Name("Por Cobrar").Fill("rgb(255, 0, 0)")/*.Fill("rgba(135,206,235,1)")*/.Add();

                            })

                            .Load("loadTheme")

                            .IsResponsive(true)

                            .DisplayTextRendering("OndataLabel")

                            .Title(t => t.Text("% Cumplimiento"))

                            .Size(sz => sz.Height("350").Width("500"))

                            .Legend(lg => { lg.Visible(false).Position(LegendPosition.Top); })

                            .EnableCanvasRendering(true)

                        )

[Laura Jordan]

Hi,

I am getting the following error when trying to export:

A potentially dangerous Request.Form value was detected from the client (ChartModel="...ies.name# <br/> #point.x# : #p...").

Description: ASP.NET has detected data in the request that is potentially dangerous because it might include HTML markup or script. The data might represent an attempt to compromise the security of your application, such as a cross-site scripting attack. If this type of input is appropriate in your application, you can include code in a web page to explicitly allow it. For more information, see http://go.microsoft.com/fwlink/?LinkID=212874. 

Exception Details: System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (ChartModel="...ies.name# <br/> #point.x# : #p...").

THIS IS MY CODE:

@(Html.EJ().Chart("container")

          .PrimaryYAxis(pr => pr.LabelFormat("{value}%").Range(ra => ra.Max(100).Min(0))/*.Title(tl => tl.Text("Montos"))*/)

          .CommonSeriesOptions(cr => cr.Type(SeriesType.Column).EnableAnimation(true).Marker(mr => mr.DataLabel(dt =>                 dt.Visible(true).TextPosition(TextPosition.Middle).HorizontalTextAlignment(Syncfusion.JavaScript.DataVisualization.TextAlignment.Center).Font(fn => fn.Size("13px").FontWeight(ChartFontWeight.Bold).Color("rgb(2, 2, 2)").FontFamily("Segoe UI"))))

          .Tooltip(tt => tt.Visible(true).Format("#series.name# <br/> #point.x# : #point.y#")))

         .Series(sr =>

                {

                                sr.Points(pt =>

                                {

                                    pt.X("Meta").Y(Convert.ToDouble(100)).Fill("rgb(89, 244, 66)").Add();

                                    pt.X("Cobrado").Y(Convert.ToDouble(Model.PorcientoCobrado.Value.ToString("0.00"))).Fill("rgb(53, 194, 252)").Add();

                                    pt.X("Por Cobrar").Y(Convert.ToDouble(Model.PorCientoPendiente.Value.ToString("0.00"))).Fill("rgb(255, 0, 0)").Add();

                                }).Name("")/*Fill("rgb(115, 230, 0)")*/.Add();

                            })            

             .Load("loadTheme")

             .IsResponsive(true)

             .DisplayTextRendering("OndataLabel")

             .Title(t => t.Text("% Cumplimiento"))

             .Size(sz => sz.Height("350").Width("500"))

             .Legend(lg => { lg.Visible(false).Position(LegendPosition.Top); })

             .EnableCanvasRendering(true)

            )

<input type="button" value="Excel Export" onclick="excelExport()" />

<script>

function excelExport() { 

           chart = $("#container").ejChart("instance"); 

            exporting = chart.model.exportSettings; 

                   exporting.fileName = "Chart"; 

                   exporting.orientation = "portrait"; 

                   exporting.angle = 0; 

                   exporting.action = "ExportChart" 

                   exporting.type ="xlsx"; 

                   exporting.mode = "server"; 

                   chart.export(); 

        }

</script>

//Controller

public void ExportChart(string Data, string ChartModel)

        {

            ChartProperties obj = ConvertChartObject(ChartModel);

            string type = obj.ExportSettings.Type.ToString().ToLower();

            string fileName = obj.ExportSettings.FileName;

            string orientation = obj.ExportSettings.Orientation.ToString();

            if (type == "xlsx")       // to export chart as excel

            {

                List<ExportChartData> chartData = new List<ExportChartData>();

                //chartData.Add(new ExportChartData("META", ));

                //chartData.Add(new ExportChartData("COBRADO", 12));

                //chartData.Add(new ExportChartData("POR COBRAR", 18));

                ExcelExport exp = new ExcelExport();

                exp.Export(obj, (IEnumerable)chartData, fileName + ".xlsx", ExcelVersion.Excel2010, null, null);

            }

        }

        private ChartProperties ConvertChartObject(string ChartModel)

        {

            JavaScriptSerializer serializer = new JavaScriptSerializer();

            IEnumerable div = (IEnumerable)serializer.Deserialize(ChartModel, typeof(IEnumerable));

            ChartProperties chartProp = new ChartProperties();

            foreach (KeyValuePair<string, object> ds in div)

            {

                var property = chartProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);

                if (property != null)

                {

                    Type type = property.PropertyType;

                    string serialize = serializer.Serialize(ds.Value);

                    object value = serializer.Deserialize(serialize, type);

                    property.SetValue(chartProp, value, null);

                }

            }

            return chartProp;

        }

    }

    public class ExportChartData

    {

        public ExportChartData(string xval, double yvalue1)

        {

            this.Xvalue = xval;

            this.YValue1 = yvalue1;

        }

        public string Xvalue

        {

            get;

            set;

        }

        public double YValue1

        {

            get;

            set;

        }

    }

Really appreciate the support,

Thanks.

[Dharanidharan Dharmasivam]

Hi Laura, 

 

Sorry for the inconvenience. 

 

In the attached code snippet, we found that you have used <br/> tag in the Tooltip Format property. ASP.NET MCV will throw this error from server side, if any HTML tags were used as value to string property. Please refer the below link for more details. 

http://stackoverflow.com/questions/81991/a-potentially-dangerous-request-form-value-was-detected-from-the-client 

Hence to avoid this, we suggest you to use Tooltip Template instead of tooltip format, in which you can display the values in multiple lines using <br/> tag. Find the code snippet below. 

ASP.NET MVC: 

<div id="tooltipTemplate" style="display: none;background-color:white;border:1px solid black; padding:5px"> 

       #point.x#<br />#point.y# 

</div> 

@(Html.EJ().Chart("container") 

         //... 

         .Series(sr => 

                  { 

                      sr .Tooltip(tl => tl.Visible(true).Template("tooltipTemplate")) .Add(); 

                  }) 

) 

Modified sample for reference can be find from the below link. 

http://www.syncfusion.com/downloads/support/forum/129372/ze/excelExport2093713758  

 

Thanks, 

Dharani. 

[Laura Jordan]

Hi,

Now it works as expected! One more question: Can I export 3 charts in the same excel file?

Thanks for the support.

[Saravana Kumar Kanagavel]

Hi Laura, 

Thanks for your patience. 

We have analyzed your query and prepared a sample to export 3 charts in a same excel file. Please find the code snippet below 

[JS] 

   function excelExport() { //button click event triggered 

        chart = $("#container").ejChart("instance"); 

        chart.export('xlsx', 'ExportChart', true); 

   } 

[CS] 

   // .. 

  foreach (string chartProperty in ChartModel) 

  { 

          // .. 

     if (count > 1) 

       exp.Export((obj as ChartProperties), (IEnumerable)chartData, "Export.xlsx", 

       ExcelVersion.Excel2010, export, this.mainWork, MultipleExportType.AppendToSheet, null, null); 

     else 

       this.mainWork = exp.Export((obj as ChartProperties), (IEnumerable)chartData, "Export.xlsx", 

       ExcelVersion.Excel2010, true, null, null); 

     export = (count == (len - 1)) ? false : true; 

  } 

 

In the above code, we are triggering button click event. In the event, we are getting chart instance and called the export public method by passing, export type, action name and multiple export arguments. Then the server side export method will be called. In that method, if it is first chart then we need to store the work book which got returned for further processing. And for upcoming charts, we need to pass the saved work book. So that all the charts will be exported in the same excel file. 

 

And, we have attached the prepared sample in the below location. 

Sample Link: http://www.syncfusion.com/downloads/support/forum/129372/ze/Sample592903390 

  

Please let us know if you have any concern.  

Thanks, 

Saravana Kumar K