We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Grid Export using Datatable

Thread ID:

Created:

Updated:

Platform:

Replies:

127182 Nov 1,2016 05:43 AM Jun 20,2017 06:53 AM ASP.NET MVC 13
loading
Tags: Grid
indrani Gajjarapu
Asked On November 1, 2016 05:43 AM

Hi,

I am binding the grid with DataTable. And while exporting, i have converted my Datatable to list as follows

"DataTable.AsEnumerable().ToList()". which gives me an exception.

I have tried test data with a collection which works fine..

var testObj = new List<dynamic>() { new { Forename = "Forename", Surname = "surname" }, new { Forename = "Forename1", Surname = "surname1" } };
           
           exp.Export(obj, testObj, "Export.xlsx"ExcelVersion.Excel2016, falsefalse"flat-saffron");

Is there a way around it using DataTable

Prasanna Kumar Viswanathan [Syncfusion]
Replied On November 2, 2016 08:54 AM

Hi Indrani, 
 
Thanks for contacting Syncfusion support.  
 
We have provided the “DataTable Support for exporting” from v14.1.0.41. So if you are using older version, please upgrade to the new version 
 
We have created a sample in v14.3.0.52 which can be downloaded from the below location.  
 
Find the code example:  
 
 
@(Html.EJ().Grid<object>("DataTableGrid") 
    .Datasource((DataTable)ViewBag.dataTable) 
    .AllowPaging() 
    .ToolbarSettings(toolBar => toolBar.ShowToolbar().ToolbarItems(items => 
      { 
          items.AddTool(ToolBarItems.ExcelExport); 
          items.AddTool(ToolBarItems.WordExport); 
      })) 
    .Columns(col => 
                { 
                    ------------------------ 
               }) 
    ) 
 
----------------------------------------------- 
 
public void ExportToExcel(string GridModel) 
        { 
            ExcelExport exp = new ExcelExport(); 
            GetGridDT(); 
            var DataSource = _dTable; 
            GridProperties obj = ConvertGridObject(GridModel); 
            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron"); 
        } 
 
  

Regards, 
Prasanna Kumar N.S.V 
 


indrani Gajjarapu
Replied On November 2, 2016 10:49 AM

Hi,

I did update Syncfusion and i'm on the version 14.3.0.49 and i cant open your project.

I have checked for the updates in control panel but there are none.

Can you recompile the project in 14.3.0.49 version.

Also if the version is not the problem, I still get the exception.

Please find my code and exception details attached.

Attachment: Export_fbc4fa1c.zip

Prasanna Kumar Viswanathan [Syncfusion]
Replied On November 3, 2016 07:57 AM

Hi Indrani, 

        Queries 
                                                 Response 

Can you recompile the project in 14.3.0.49 version.” 


Yes, we recompile the project in 14.3.0.49 version and download the project from the below link 




Exception details” 


We checked your screenshot and mentioned exception will occurred when the columns bound to the Grid are not defined in the Grid dataSource, and are passed to the exporting method in Code behind. 
 
For more information, refer the below knowledgebase documentation 
 

If you still face the same issue, please reproduced the issue in the attached sample and sent back to us. 



Regards, 
Prasanna Kumar N.S.V 
 


indrani Gajjarapu
Replied On November 3, 2016 08:31 AM

Hi Prasanna Kumar,

Thanks for your quick response. 

The problem is i have got some dynamic columns. There might be n no.of columns for english. So I am adding a column for each english subject using the foreach loop as below.

The Grid exports fine if i comment this code. Except for 3 columns, my whole grid is populated dynamically. How could i export grid in this scenario.

Also i want the colours of the cells to be exported. I have used QueryCellInfo to colour the cells. Please let me know the workaround code for this.

Thank you.
foreach (var item in Model.EnglishSubjects)
     {
        col.Field(item.SubjectID + ".Display").HeaderText(Model.Table.Columns[item.SubjectID.ToString()].Caption).HeaderTooltip(item.SubjectName).Add();
 
 
       }

Prasanna Kumar Viswanathan [Syncfusion]
Replied On November 4, 2016 06:59 AM

Hi Indrani, 

       Queries 
                                                Response 

“Null Exception” 


According to your code example, we found that you have used ForeignKey column in the grid. So, to avoid the mentioned issue please refer the below documentation. 



i want the colours of the cells to be exported” 


To customize the style for the exported grid, use the AutoFormat class. With the autoFormat class, you can provide required color to the grid content,  row background or border color. 

Find the code example and sample: 


private GridProperties ConvertGridObject(string gridProperty) 
        { 
            ----------------- 
           GridProperties gridProp = new GridProperties(); 
            GridExtensions ext = new GridExtensions(); 
            AutoFormat auto = new AutoFormat(); 
            ext.SetTheme(auto, "flat-saffron"); 
            auto.ContentBgColor = Color.Blue; 
            gridProp.AutoFormat = auto; 
            --------------------- 
       } 



For more information refer the below document. 


You can customize the particular cell or particular row of exporting files using server events. 

For more information, refer the below document.  




Regards, 
Prasanna Kumar N.S.V 
 


indrani Gajjarapu
Replied On November 4, 2016 07:30 AM

Hi,

Thanks for your reply.

Auto Format class works for the cell colours. 

I am not using foreign key columns in my samples. Data is coming from same source.

Each column is of type CellValue, which is a custom class with 3 properties, Display, Value and Tag. The grid handles this by creating a column for each property. So if we had a column called English, On the grid we have to use the field English.Display. This works fine and allows us to use the .Value property to set cell formatting.

The problem here is, while exporting the grid, it checks for the column with the extension ".Display" which doesn't exist. How can we get this to export properly?
foreach (var item in Model.EnglishSubjects)
     {
        col.Field(item.SubjectID + ".Display").HeaderText(Model.Table.Columns[item.SubjectID.ToString()].Caption).HeaderTooltip(item.SubjectName).Add();
 
 
       }

Prasanna Kumar Viswanathan [Syncfusion]
Replied On November 7, 2016 06:53 AM

Hi Indrani, 

According to your code example you have used complex binding column in your sample. So, we checked in our sample with a complex binding column and we did not face the null exception while exporting a Grid.  

Find the code example and sample:  

 
@(Html.EJ().Grid<MvcApplication14.OrdersView>("FlatGrid") 
            .Datasource((IEnumerable<object>)ViewBag.dataSource) 
            .AllowPaging() 
             .ToolbarSettings(toolBar => toolBar.ShowToolbar().ToolbarItems(items => 
                        { 
                            items.AddTool(ToolBarItems.ExcelExport); 
                            items.AddTool(ToolBarItems.WordExport); 
                            items.AddTool(ToolBarItems.PdfExport); 
                        })) 
        .Columns(col => 
        { 
            col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Visible(false).Width(75).Add(); 
            col.Field("Name.NameNew").HeaderText("Name").Width(80).ValidationRules(v => v.AddRule("required", true).AddRule("minlength", 3)).Add(); 
            ----------------- 
       }) 
        ) 


To reproduce the mentioned issue, we need the following details, 

1.  Are you using array of complex object in the Grid? 

2. Code example of a Grid. 

3. Essential Studio Version details. 

4. If possible, replicate the issue in the attached sample.  

Regards, 
Prasanna Kumar N.S.V 
 


indrani Gajjarapu
Replied On November 7, 2016 10:53 AM

Hi,

I am using complex object. I have attached code of my view and controller.

Essential studio version is 14.3.0.49

There is no code difference between your sample code and mine, Except that i am using foreach loops to generate columns. But i dont think that is the problem because i have another grid which don't have any foreach loops but still gives me the same exception.

Attachment: Code_aed933e6.zip

Prasanna Kumar Viswanathan [Syncfusion]
Replied On November 8, 2016 07:09 AM

Hi Indrani, 

We checked in our sample with your code example and we did not face null exceptions while exporting to Grid. 

Find the code example and sample: 


@(Html.EJ().Grid<MvcApplication14.OrdersView>("FlatGrid") 
            .Datasource((DataTable)ViewBag.dataTable) 
            .EnableAltRow(false) 
            .---------------------- 
       .ShowStackedHeader() 
        .StackedHeaderRows(row => 
         { 
               ----------------------------------- 
         }) 
        .AllowPaging(true) 
        .PageSettings(p => p.PageCount(5).PageSize(10)) 
        .IsResponsive(true) 
        .EnableResponsiveRow(true) 
        .FilterSettings(filter => { filter.FilterType(FilterType.Menu); }) 
        .AllowTextWrap() 
        .TextWrapSettings(wrap => { wrap.WrapMode(WrapMode.Both); }) 
        .ShowColumnChooser() 
        .AllowScrolling() 
        .ScrollSettings(col => { col.Height("auto").Width("100%"); }) 
        .MinWidth(1250) 
        .Columns(col => 
        { 
            ------------------------------- 
       }) 
        ) 


Please provide the first row of data that you are binded to the grid, it may help us to find out the root cause of the issue. If possible, replicate the issue in the attached sample.  

Regards, 
Prasanna Kumar N.S.V 

 


indrani Gajjarapu
Replied On November 8, 2016 07:46 AM

Hi,

I have replicated the issue in a sample project.

Please have a look.

Thanks

Attachment: SyncfusionMvcApplication1_59e8a6b6.7z

Prasanna Kumar Viswanathan [Syncfusion]
Replied On November 9, 2016 08:50 AM

Hi Indrani, 
We can reproduce the mentioned issue in the attached sample. The mentioned issue is reproduced due to the complex data in datatable. We considered this requirement as an improvement and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates. 

Regards, 
Prasanna Kumar N.S.V 


Jaya Uradhanda
Replied On June 19, 2017 10:50 AM

Hi Indrani, 
We can reproduce the mentioned issue in the attached sample. The mentioned issue is reproduced due to the complex data in datatable. We considered this requirement as an improvement and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates. 

Regards, 
Prasanna Kumar N.S.V 


Hi Indrani / SyncFusionTeam,

I have the same scenario that Indrani had, do we have a solution for this yet. While populating the Grid, i do not have any idea what the class looks like. Obviously, while exporting, i do not know the type of the class to serialize the Data Source. 

Please let me know if you had any new feature to support this issue or a workaround for the same.

Thanks,
Jaya. 


Prasanna Kumar Viswanathan [Syncfusion]
Replied On June 20, 2017 06:53 AM

Hi Jaya, 

Query : “While populating the Grid, i do not have any idea what the class looks like. Obviously, while exporting, i do not know the type of the class to serialize the Data Source.” 

We have already discussed above the query in the below knowledgebase documentation. So, please find more information from the below documentation. 


Regards, 
Prasanna Kumar N.S.V 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;