How export Grid into excel file

[Timo]

I used the following code to export Grid, but I got the following exception: 'System.NullReferenceException: 'Object reference not set to an instance of an object.'

public ActionResult ExportToExcel(string GridModel)

        {

            ExcelExport exp = new ExcelExport();

            var lst = new List<QuestionViewModel>();

            //foreach (DataRow row in result.Rows)

            //{

            //    ls.Add(new QuestionViewModel() { QuestionId=row["questionid"].ToString(),CreationDate=row["CreationDate"].ToString(),Link=row["Link"].ToString(),CommentOwnerId=row["commentOwnerId"].ToString()});

            //}

            for (int i = 0; i < 100; i++)

            {

                lst.Add(new QuestionViewModel() { QuestionId = i.ToString(), CommentOwnerId = i.ToString(), CreationDate = DateTime.Now.ToString(), Link = "<a>https://www.nuget.org/packages/Microsoft.Xaml.Behaviors.Uwp.Managed/</a>" });

            }

            

            List<QuestionViewModel> DataSource = lst;

            GridProperties gridProp = ConvertGridObject(GridModel);

            GridExcelExport excelExp = new GridExcelExport();

            excelExp.FileName = "QuestionFollow_"+DateTime.Now.ToString("yyyy-MM-dd")+".xlsx";

            excelExp.Excelversion = ExcelVersion.Excel2010;

            excelExp.Theme = "flat-saffron";

            return exp.Export(gridProp, DataSource, excelExp);

        }

        private GridProperties ConvertGridObject(string gridProperty)

        {

            GridProperties gridProp = new GridProperties();

            gridProp = (GridProperties)JsonConvert.DeserializeObject(gridProperty, typeof(GridProperties));

            return gridProp;

        }

[Farveen Sulthana Thameeztheen Basha]

Hi Xavier, 

Thanks for contacting Syncfusion Support. 

We have checked your query and we have already discussed about  System.NullReferenceException: Object reference not set to an instance of an object  in our Syncfusion Knowledge base while Exporting. For your convenience please find the KB in following link. 

https://www.syncfusion.com/kb/3018/null-exception-while-exporting 

Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T 

[Timo]

Hi Farveen Sulthana Thameeztheen Basha,

I've made a code sample for you. There're two issues in it.

1) The Grid doesn't show data in browser.

2) When I export to excel file, the link column cannot be saved as a hypelink format. I need to save the link column as a Hypelink in excel.

Code Sample 

[Farveen Sulthana Thameeztheen Basha]

Hi Xavier,

 

Query #1) The Grid doesn't show data in browser.

 

We can reproduce your reported problem. This problem occurs by default on ASP.Net core sample.To overcome this we need to add the below code in startup.cs  page as like given below.

 

public void ConfigureServices(IServiceCollection services)

        {

            // Add framework services.

            services.AddMvc().AddXmlSerializerFormatters()

            .AddJsonOptions(x =>

             {

                 x.SerializerSettings.ContractResolver = null;

                  x.SerializerSettings.NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore;

             });

            services.AddCors(options => options.AddPolicy("AllowAll", p => p.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod()));

        }

 

Query #2  When I export to excel file, the link column cannot be saved as a hyperlink format. I need to save the link column as a Hyperlink in excel.

 

We have checked your query and we suspect that you need to Export Hyperlink column. i.e.Template column. In your code example, you have not  handled the Template column using  server side events such as  ExcelColumnTemplateInfo,WordColumnTemplateInfo, PdfColumnTemplateInfo.

Please refer to the code example:-

 

 

Server side:-

 

       public void ColumnTemplateExportToExcel(string GridModel)

        {

            ExcelExport exp = new ExcelExport();

            var DataSource = new NorthwindDataContext().EmployeeViews.Take(8).ToList();

            GridProperties obj = ConvertGridObject3(GridModel);

            obj.ExcelColumnTemplateInfo = ExceltemplateInfo;

            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, true, "flat-saffron");

        }

 

       public void ExceltemplateInfo(object currentCell, object row)

        {

            IRange range = (IRange)currentCell;

            object templatevalue;

            foreach (var ds in row.GetType().GetProperties())

            {

                if (range.Value.ToString().Contains(ds.Name))

                {

                    templatevalue = row.GetType().GetProperty(ds.Name).GetValue(row, null);

                    range.Value = range.Value.Replace(ds.Name, templatevalue.ToString());

                    var regex = new Regex("<a [^>]*rel='nofollow' href=(?:'(?<rel='nofollow' href>.*?)')|(?:\"(?<rel='nofollow' href>.*?)\")", RegexOptions.IgnoreCase);

                    var urls = regex.Matches(range.Value.ToString()).OfType<Match>().Select(m => m.Groups["rel='nofollow' href"].Value).SingleOrDefault();

                    IHyperLink hyperlink = (range.Parent as Syncfusion.XlsIO.Implementation.WorksheetImpl).HyperLinks.Add(range);

                    hyperlink.Type = ExcelHyperLinkType.Url;

                    hyperlink.TextToDisplay = templatevalue.ToString();

                    hyperlink.Address = urls;

                }

            }

        }

 

We have provided support for column Template Exporting from version 15.4.0.17. Please refer to the link:- 

https://help.syncfusion.com/js/release-notes/v15.4.0.17?type=all#ejGrid-features

 

Please refer to the documentation Link:-

https://help.syncfusion.com/aspnet-core/grid/exporting#columntemplate-exporting

 

Please get back to us if you need any further assistance.

Regards,

Farveen sulthana T

 

[Timo]

Could you please give me a simple code sample?

Your code seemed not work.

I got the error "Argument 1: cannot convert from 'Syncfusion.JavaScript.Models.GridProperties' to 'Syncfusion.JavaScript.DataVisualization.Models.ChartProperties'".

[Manivannan Padmanaban]

Hi Xavier,

Query: "Argument 1: cannot convert from 'Syncfusion.JavaScript.Models.GridProperties' to 'Syncfusion.JavaScript.DataVisualization.Models.ChartProperties'".

The above mentioned issue will occur when the dependencies are overloaded we suggest you to remove the multiple model reference.

For hyperlink exporting we suggest you to use the column template. For your convenience we have created the sample please refer attached file.

Please get  back to us, if you need further assistance.

Regards,

Manivannan Padmanaban.

 

 

Attachment: SyncfusionASPNETCoreWebApplication2_2b88e84b.7z

[Timo]

Hi Xavier,

Query: "Argument 1: cannot convert from 'Syncfusion.JavaScript.Models.GridProperties' to 'Syncfusion.JavaScript.DataVisualization.Models.ChartProperties'".

The above mentioned issue will occur when the dependencies are overloaded we suggest you to remove the multiple model reference.

For hyperlink exporting we suggest you to use the column template. For your convenience we have created the sample please refer attached file.

Please get  back to us, if you need further assistance.

Regards,

Manivannan Padmanaban.

 

 

Attachment: SyncfusionASPNETCoreWebApplication2_2b88e84b.7z

Hi Manivannan Padmanaban

Your code sample did not work. The Link column did not show as HypeLink in exported excel file.

See screenshot: Screenshot.png 

Best Regards,

Xavier

[Manivannan Padmanaban]

Hi Xavier, 

 

We appreciate your patience we have achieved your requirement by using the column template of ejgrid. Please refer the below code example, 

 

// Index page 

<script type="text/x-jsrender" id="columnTemplate"> 

     

    <a rel='nofollow' href="https://www.nuget.org/packages/Microsoft.Xaml.Behaviors.Uwp.Managed">{{:Link}}</a> 

 

</script> 

// Controller page 

private void InitializeGrid(GridProperties gridModel) 

        { 

 

            .. 

 

 

            Column column4 = new Column() { HeaderText = "Link", Template = "#columnTemplate"  }; 

 

            List<Column> columns = new List<Column>() { column1, column2, column3, column4 }; 

 

            gridModel.Columns = columns; 

            gridModel.ExportToExcelAction = "/Home/ExportToExcel"; 

              …………………….. 

 

        } 

public ActionResult ExportToExcel(string GridModel) 

        { 

            …………………… 

           excelExp.IsTemplateColumnIncluded = true; 

            gridProp.ExcelColumnTemplateInfo = ExceltemplateInfo; 

 

            return exp.Export(gridProp, DataSource, excelExp); 

        } 

 

        public void ExceltemplateInfo(object currentCell, object row) 

        { 

            IRange range = (IRange)currentCell; 

            object templatevalue; 

            foreach (var ds in row.GetType().GetProperties()) 

            { 

                if (range.Value.ToString().Contains(ds.Name)) 

                { 

                    templatevalue = row.GetType().GetProperty(ds.Name).GetValue(row, null); 

                    range.Value = range.Value.Replace(ds.Name, templatevalue.ToString()); 

                    var urls = range.Value.Split(new Char[] { '<', '>' })[4]; //To get the hyperlink 

                    IHyperLink hyperlink = (range.Parent as Syncfusion.XlsIO.Implementation.WorksheetImpl).HyperLinks.Add(range); 

                    hyperlink.Type = ExcelHyperLinkType.Url; 

                    hyperlink.TextToDisplay = urls; 

                    hyperlink.Address = urls; 

                } 

            } 

        } 

 

         

      

Output: 

 

For your convenience we have created the sample please refer the below link, 

http://www.syncfusion.com/downloads/support/directtrac/general/7z/SyncfusionASPNETCoreWebApplication2-1695765165.7z  

Please get back to us, if you need further assistance. 

Regards, 

Manivannan Padmanaban.