1. Home
  2. Forum
  3. ASP.NET MVC - EJ 2
  4. How to load grid & filter with user selected Excel or CSV file as data source

How to load grid & filter with user selected Excel or CSV file as data source

Hello, I am trying to use the Synfusion ASP grid and treegrid.

Can you please show me how to let the user select a source file (he is in a chrome browser, and he selects upload, CSV file, which has headers).
  1. How can I load that in the grid from the file at runtime
  2. How can I set the cols headers from the CSV/excel file first row. If  there are more than 5, I want them to be deselected but available.
  3. How can I set up the filters for each column with headers based on the col. data type at runtime.
  4. How can we allow user to select total on one col.
  5. How to expot the query to server
  6. How to make the SF grid/tree UI bootstrap responsive? in the comments please indicate which Syncfusion js/css file are specific to the grid (for _pageSpecificLayout) vs _layout Master for JS2 version.

Thanks
Mega

9 Replies
TS Thavasianand Sankaranarayanan Syncfusion Team November 13, 2018 11:32 AM UTC

Hi Megatron, 
 
Thanks for contacting Syncfusion support. 
 
Query 1, 2, 3,4, 5:  
 
Before proceeding to your query we need  the following clarifications for better assistance. 
 
  1. Whether you are using the ej 1 Grid control or ej 2 Grid control ?
  2. Are you want to accomplish your requirement in Grid control or tree Grid control.
  3. There is no Tree Grid component in ej 2.
 
Query 6: How to make the SF grid/tree UI bootstrap responsive? in the comments please indicate which Syncfusion js/css file are specific to the grid (for _pageSpecificLayout) vs _layout Master for JS2 version. 
 
We need to refer the following CSS files for ej 1 Grid. 
 
ej.responsive.css 
ejgrid.responsive.css 
 
In ej 2 Grid no need to refer the additional script/CSS files for responsive mode. 
 
We suggest you to use the below blog for the responsive in angular grid. 
   
 
Regards, 
Thavasianand S. 


ME Megatron November 13, 2018 07:25 PM UTC

  1. Whether you are using the ej 1 Grid control or ej 2 Grid control ?  - ej 2 (ASP MVC, what would be different in Core)
  2. Are you want to accomplish your requirement in Grid control or tree Grid control. - I was trying both, lets do ej 2 Grid since Tree grid is not available in ej 1
  3. There is no Tree Grid component in ej 2.  -- then Can you show me how to do the tree grid in ej1
To calrify - these two are page specific for the page with EF grid
ej.responsive.css 
ejgrid.responsive.css

TS Thavasianand Sankaranarayanan Syncfusion Team November 14, 2018 12:18 PM UTC

Hi Megatron, 
 
Query 1: Accomplish my requirement in ej 2 Grid component. 

We have prepared a custom sample based on  your requirement in which We have stored the uploaded excel fil using our Syncfusion Excel library and bind excel content to grid by using the dataSource property of grid in Uploader Success event. Please refer to the below code example, Documentation link and sample link. 

[index.cshtml] 
@Html.EJS().Grid("LocalData").Columns(col => 
{ 
    col.Type("checkbox").Width("80").Add();  
    col.Field("Order ID").HeaderText("Order ID").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Width("120").Add(); 
    .  .  . 
 
}).AllowPaging().AllowFiltering().Render() 
 
@Html.EJS().Uploader("UploadFiles").AutoUpload(false).AsyncSettings(new Syncfusion.EJ2.Inputs.UploaderAsyncSettings { SaveUrl = "/Home/Save" }).Success("success").Render() 
 
 
<script> 
    function success(args) { 
        var grid = document.getElementsByClassName('e-grid')[0].ej2_instances[0]; 
        var griddata = JSON.parse(args.e.target.responseText); 
        grid.dataSource = griddata; 
    } 
</script> 

[HomeController.cs] 
using Syncfusion.XlsIO; 
using System.Data; 
 
.   .  .  
public string Save() 
        { 
            string directoryPath = "D:/App_Data/TempData/"; 
 
            if (!Directory.Exists(directoryPath)) 
                Directory.CreateDirectory(directoryPath); 
            try 
            { 
                if (System.Web.HttpContext.Current.Request.Files.AllKeys.Length > 0) 
                { 
                    for (int i = 0; i < System.Web.HttpContext.Current.Request.Files.AllKeys.Length; ++i) 
                    { 
                        var httpPostedFile = System.Web.HttpContext.Current.Request.Files[i]; 
 
                        if (httpPostedFile != null) 
                        { 
                            filePath = Path.Combine(directoryPath, httpPostedFile.FileName); 
 
                            if (!System.IO.File.Exists(filePath)) 
                            { 
                                using (var fileStream = new FileStream(filePath, FileMode.Create)) 
                                { 
                                    ExcelEngine excelEngine = new ExcelEngine(); 
 
                                    //Loads or open an existing workbook through Open method of IWorkbooks  
                                    fileStream.Position = 0; 
                                    IWorkbook workbook = excelEngine.Excel.Workbooks.Open(httpPostedFile.InputStream); 
                                    IWorksheet worksheet = workbook.Worksheets[0]; 
 
                                    // Read data from the worksheet and Export to the DataTable.  
 
                                    DataTable table = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); 
 
                                    ViewBag.data = JsonConvert.SerializeObject(table, Formatting.Indented, new JsonSerializerSettings { Converters = new[] { new Newtonsoft.Json.Converters.DataTableConverter() } }); 
 
                                } 
                                return (ViewBag.data); 
                            } 
                            else 
                            { 
                                return ("File already exists"); 
                            } } 
                    } 
                }       
 return ("No file in request"); ; 
            } 
            catch (Exception e) 
            { 
                return e.Message; 
            } 
        } 

                             https://help.syncfusion.com/file-formats/xlsio/faq#how-to-open-an-excel-file-from-stream  
                              https://ej2.syncfusion.com/documentation/api/grid#datasource  

 
Query 2: Can you show me how to do the tree grid in ej 1? 
 
We have prepared the simple TreeGrid sample in Asp.Net Mvc platform for your reference, please find the sample from below link  
Also, find more details about the features in TreeGrid control from below links  
 
Query 3: Two specific files for grid 

When we you are using ej 2 Grid component then we no need to use this two specific files in ej 2 component. 
 
Regards, 
Thavasianand S. 


ME Megatron November 15, 2018 06:36 PM UTC

Hello, thanks for the sample, I was unable to get it to work with the CSV datasource.
The headers would not load,
and I was not able to see the fitlers configured, also what is the function to get the query sting back on the server?
Is the query string Sql or link string?
Regds

MS Madhu Sudhanan P Syncfusion Team November 16, 2018 12:33 PM UTC

Hi Megatron, 

We have checked the sample with the CSV file but the Grid gets the data fine with header. Please refer to the below screenshot. 

 

Could you please provide the below details that will be helpful for us to provide a better solution as early as possible. 
  1. Share your Essential JavaScript 2 package version.
  2. I was not able to see the filters configured
Are you facing issue in Grid filtering action? 
  1. what is the function to get the query sting back on the server? Is the query string Sql or link string?
Could you please provide more detail on your query. 
 
Regards, 
Madhu Sudhanan P 


ME Megatron November 16, 2018 07:20 PM UTC


Clarification: I wanted to load CSV file headers as grid option. But sometime there are no headers & other times it could be two rows, is it possible to tell EJ2 grid before uploading which & how many rows have the headers? Here its failing for me. To solve this, maybe provide a TextBox number -> Select Number of Header Rows

Saving actual query string from grid, clarification, after performing a query, I want to export that query string like in EJ1 grid (it was a feature) where you could get the query string back on the server side from the grid. I am referreing to the actual Query filter operations, for e.g. sort col Name, find between X & Y col date,

thanks

TS Thavasianand Sankaranarayanan Syncfusion Team November 20, 2018 11:46 AM UTC

Hi Megatron, 

Query #1: is it possible to tell EJ2 grid before uploading which & how many rows have the headers?   

We have analyzed your query but unfortunately we could not get your actual requirement form the shared information. Could you please share the below details that will be helpful for us to provide a better solution. 

  1. Do you want to assign the Grid headers from the response generated from the uploaded CSV file.
  2. Do you want to set stacked header for Grid component.

Query #2:  I want to export that query string like in EJ1 grid (it was a feature) where you could get the query string back on the server side from the grid.  

We have achieved your requirement by using the processQuery() method of UrlAdaptor which will return a string for the corresponding grid query. Please refer to the below code example and sample link. 

[index.cshtml] 
@Html.EJS().Button("State").Content("Grid_State").Render() 
@Html.EJS().Grid("Grid").DataSource((IEnumerable<Object>)ViewBag.datasource).Columns(col => 
{ 
   col.Field("OrderID").HeaderText("Order ID").Add(); 
   .  .  . 
}).AllowSorting().AllowPaging().AllowFiltering().Render() 
 
 
<script> 
    document.getElementById('State').onclick = (e) => { 
        var grid = document.getElementsByClassName('e-grid')[0].ej2_instances[0]; 
        var dataManager = new ej.data.DataManager({adaptor: new ej.data.UrlAdaptor }); 
        // generating grid query  
        var query = grid.renderModule.data.generateQuery() 
        // process the query which can be bound to “DataManagerRequest” class in server 
        var result = dataManager.adaptor.processQuery(dataManager, query); 
 
        var ajax = new ej.base.Ajax({ 
            url: "/Home/gridState",  
            type: "POST", 
            contentType: "application/json", 
            data: result.data, 
        }); 
        ajax.send().then(function (data) { 
            console.log(JSON.parse(data)); 
        }).catch(function (xhr) { 
            console.log("Fail") 
        }); 
    }</script> 

[HomeController.cs] 
public ActionResult gridState(DataManagerRequest dm) 
       { 
             
            return Json(dm); 
        } 

 



Regards, 
Thavasianand S. 


MI Muhammd Irfan August 24, 2021 10:34 AM UTC

Sir I am using sf uploader , it does not get physical path of file

my code is below.

<SfDialog Width="500" @bind-Visible="@IsVisible" IsModal="true">

        <DialogTemplates>

            <Content>

                <SfUploader AutoUpload="false" AllowMultiple=false AllowedExtensions=".xls, .xlsx">

                    <UploaderEvents ValueChange="OnChange"></UploaderEvents>

                </SfUploader>

            </Content>

        </DialogTemplates>

    </SfDialog>



@code{

private async void OnChange( UploadChangeEventArgs args )

{

var file = args.Files[0];

var path = file.FileInfo.Name;
}

}


SN Sevvandhi Nagulan Syncfusion Team August 25, 2021 08:11 AM UTC

Hi Muhammd,  


Greetings from Syncfusion support. 


You cannot get the selected file path in both client side and server side due to security reasons. Refer to the following common links for more information.  





Kindly get back to us for further assistance. 


Regards,  
Sevvandhi N 


Loader.
Up arrow icon