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. Image for the cookie policy date
  1. Home
  2. Forum
  3. ASP.NET MVC
  4. Grid Export using Datatable

Grid Export using Datatable

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, false, false, "flat-saffron");

Is there a way around it using DataTable

13 Replies
PK Prasanna Kumar Viswanathan Syncfusion Team November 2, 2016 12:54 PM UTC

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 
 


IG indrani Gajjarapu November 2, 2016 02:49 PM UTC

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

PK Prasanna Kumar Viswanathan Syncfusion Team November 3, 2016 11:57 AM UTC

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 
 


IG indrani Gajjarapu November 3, 2016 12:31 PM UTC

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();
 
 
       }

PK Prasanna Kumar Viswanathan Syncfusion Team November 4, 2016 10:59 AM UTC

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 
 


IG indrani Gajjarapu November 4, 2016 11:30 AM UTC

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();
 
 
       }

PK Prasanna Kumar Viswanathan Syncfusion Team November 7, 2016 11:53 AM UTC

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 
 


IG indrani Gajjarapu November 7, 2016 03:53 PM UTC

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

PK Prasanna Kumar Viswanathan Syncfusion Team November 8, 2016 12:09 PM UTC

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 

 


IG indrani Gajjarapu November 8, 2016 12:46 PM UTC

Hi,

I have replicated the issue in a sample project.

Please have a look.

Thanks

Attachment: SyncfusionMvcApplication1_59e8a6b6.7z

PK Prasanna Kumar Viswanathan Syncfusion Team November 9, 2016 01:50 PM UTC

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 


JU Jaya Uradhanda replied to Prasanna Kumar Viswanathan June 19, 2017 02:50 PM UTC

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. 


PK Prasanna Kumar Viswanathan Syncfusion Team June 20, 2017 10:53 AM UTC

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 


Loader.
Live Chat Icon For mobile
Up arrow icon