1. Home
  2. Forum
  3. ASP.NET Core - EJ 2
  4. Upload a Excel file in Razor Pages and show data in DataGrid

Upload a Excel file in Razor Pages and show data in DataGrid

Hello,


I'm having difficulties with this scenario. I need in ASP. CORE Razor Pages upload a excel file with the Upload File control, read that file with XLSIO engine and display the data in a DataGrid in Model BindProperty.


Regards,



4 Replies
SP Sureshkumar P Syncfusion Team August 3, 2021 12:59 PM UTC

Hi Arsenio, 

Greetings from Syncfusion support. 

Query 1: I need in ASP. CORE Razor Pages upload a excel file with the Upload File control 
Answer: 
              We can upload the excel files using our file upload component. We can also restrict the other files by using allowedExtensions property. 

Please find the code example here: 
@{ 
    var asyncSettings = new Syncfusion.EJ2.Inputs.UploaderAsyncSettings { SaveUrl = "https://ej2.syncfusion.com/services/api/uploadbox/Save", RemoveUrl = "https://ej2.syncfusion.com/services/api/uploadbox/Remove" }; 
} 
<ejs-uploader id="uploadFiles" asyncSettings="@asyncSettings" allowedExtensions=" .xls, .xlsx" autoUpload="false"></ejs-uploader> 
  
Query 2: read that file with XLSIO engine 
Answer: 
              You need to install Syncfusion.XlsIO.Net.Core NuGet package in your application and use the code snippet provided in below code snippet, to read or load the existing Excel document into IWorkbook of Syncfusion XlsIO.  
Code snippet provided in below link shows how to save the Excel document.  

Query 3: display the data in a DataGrid in Model BindProperty 
Answer: 
              By default, the EJ2 Grid expects JSON data to create the component and perform the data actions in it. We are unable to load the excel file directly into the Grid. We suggest you to convert this excel file into JSON data and bind this converted JSON data to the Grid.  
Regards, 
Sureshkumar P 


AR Arsenio August 3, 2021 03:03 PM UTC

Do you have an example of how to bind JSON data of the converted excel file in the Grid ?


Also, in the upload control, this would call a post handler in the page ?


Regards,


BC Berly Christopher Syncfusion Team August 4, 2021 02:26 PM UTC

Hi Filip, 
  
Query 1: 
Do you have an example of how to bind JSON data of the converted excel file in the Grid ? 
  
Response: 
We will validate and update the details on the requested requirement in two business days (6th August 2021).  
  
Query 2: 
Also, in the upload control, this would call a post handler in the page ? 
  
Response: 
  
As per the requested requirement, we have prepared the sample by handling the save and remove handler in the application itself. 
  
Regards, 
Berly B.C 


BC Berly Christopher Syncfusion Team August 5, 2021 10:33 AM UTC

Hi Filip, 

Query 1: 

Do you have an example of how to bind JSON data of the converted excel file in the Grid ?  

Response: 

You can bind the Excel file data to the Essential JavaScript 2 DataGrid by using the below way in which We have used Uploader component to import the Excel file and 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. The Grid will auto generate the columns from the datasource if the columns are not provided at initial rendering. Please refer to the below code example, Documentation link and sample link.  

[index.cshtml]  
  
<ejs-grid id="Grid" dataBound="dataBound" allowPaging="true" allowFiltering="true" allowGrouping="true" allowsorting="true"   
toolbar="@(new List<string>(){"Add", "Edit", "Delete", "Update", "Cancel" })">   
</ejs-grid>   
   
<ejs-uploader id="uploader" autoUpload="false" asyncSettings="@asyncSettings" success="success" ></ejs-uploader>   
   
<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>   
  
 
[Controller.cs]  
  
using Syncfusion.XlsIO;   
using System.Data;   
   
.   .  .    
{   
public async Task<IActionResult> Save()   
        {   
            string filePath = "App_Data/TempData/";   
            string directoryPath = Path.Combine(new FileInfo(filePath).Directory.FullName);   
   
            if (!Directory.Exists(directoryPath))   
                Directory.CreateDirectory(directoryPath);   
   
            try   
            {   
                if (HttpContext.Request.Form.Files.Count > 0)   
                {   
                    for (int i = 0; i < HttpContext.Request.Form.Files.Count; ++i)   
                    {   
                        IFormFile httpPostedFile = HttpContext.Request.Form.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))   
                                {   
                                    await httpPostedFile.CopyToAsync(fileStream);   
                                    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.OpenReadStream());   
                                    IWorksheet worksheet = workbook.Worksheets[0];   
   
                                    // Read data from the worksheet and Export to the DataTable.   
   
                                    DataTable table = worksheet.ExportDataTable(2,1,worksheet.Rows.Length, worksheet.Columns.Length, ExcelExportDataTableOptions.ColumnNames);   
                                    string JSONString = string.Empty;   
                                    JSONString = JsonConvert.SerializeObject(table);   
                                    ViewBag.data = JsonConvert.SerializeObject(table, Formatting.Indented, new JsonSerializerSettings { Converters = new[] { new Newtonsoft.Json.Converters.DataTableConverter() } });   
                                    //return View();   
                                }   
                                return Ok(ViewBag.data);   
                            }   
                            else   
                            {   
                                return BadRequest("File already exists");   
                            }}}}   
                return BadRequest("No file in request"); ;   
            }   
            catch (Exception e)   
            {   
                return BadRequest(e.Message);   
            }   
        }   
 

Help documentation :    
   
     
      

Regards, 
Berly B.C

 


Loader.
Up arrow icon