Where is the ASP MVC help to import an exel file

[Megatron]

Hi, there is a smaple here for old classic ASP http://help.syncfusion.com/aspnetmvc-classic/grid/how-to/how-to-import-an-excelxlsx-file-into-grid but I cannot find how to do in asp MVC , can you please help me find it.

[Saravanan Arunachalam]

Hi Megatron,

If you need to import the excel sheet into the Grid, read the data from excel sheet and fill into the DataTable. Then you can bind the DataTable to Grid control. Please refer to the below code example.

[View]

@(Html.EJ().Grid<ExcelImport.OrdersView>("FlatGrid")

        .Datasource((System.Data.DataTable)ViewBag.dataSource)

        . . .

))

[Controller]

public class GridController : Controller

    {

        //

        // GET: /Grid/

        public ActionResult GridFeatures()

        {

            //Loaded the datatable into the ViewBag.

            ViewBag.dataSource = ExcelGridImport();

            return View();

        }

        private System.Data.DataTable ExcelGridImport()

        {

            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            //Open the workbook from mentioned path

            IWorkbook workbook = application.Workbooks.Open(Request.PhysicalApplicationPath + "/ExportSheet.xlsx");

            IWorksheet empSheet = workbook.Worksheets[0];

            //Records in excel file are fill into the datatable

            System.Data.DataTable customersTable = empSheet.ExportDataTable(empSheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

            return customersTable;

        }

    }

We have created a sample that can be downloaded from the below link.

http://www.syncfusion.com/downloads/support/forum/121656/ze/ExcelImport-817728501

Regards,

Saravanan A.

[Megatron]

Appreciate the sample, how do I ensure the data is strongly type to an EF entity.
I also want to validate and let the user know if there is a problem with the data -> let him correct it in batch edit mode, then commit.

[Saravanan Arunachalam]

Hi Megatron,

In the previous update, we have bound the data table to the Grid control. And we understood from your query, you need to bind the stongly typed data to the Grid control and we have created a sample for this requirement that you can download from the below link.

http://www.syncfusion.com/downloads/support/forum/121656/ze/ExcelImport1-1469816170

In the above sample, we have converted the dataTable into list data with strongly typed by using Linq query with View-Model class (Orders). Using the “UrlAdaptor” of DataManager we have bound the list of data to the Grid control and performs the CRUD operation to the Grid control with “Batch” edit mode. Please refer to the below code example.

[View]

@(Html.EJ().Grid<ExcelImport.OrdersView>("FlatGrid")

        .Datasource(ds => ds.URL("BatchDataSource").BatchURL("BatchUpdate").Adaptor(AdaptorType.UrlAdaptor))

.EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Batch); })

. . .

)

[Controller]

//Bind the List of Data to the Grid control

public ActionResult BatchDataSource(Syncfusion.JavaScript.DataManager dm)

        {

            IEnumerable DataSource = GetAllRecords().ToList();

            BatchDataResult result = new BatchDataResult();

            DataOperations obj = new DataOperations();

            if (dm.Skip != 0)

            {

                DataSource = obj.PerformSkip(DataSource, dm.Skip);

            }

            if (dm.Take != 0)

            {

                DataSource = obj.PerformTake(DataSource, dm.Take);

            }

            result.result = DataSource;

            result.count = GetAllRecords().Count();

            return Json(result, JsonRequestBehavior.AllowGet);

        }

//Convert the Data table to list

public List<Orders> GetAllRecords()

        {

           

            System.Data.DataTable dt = ExcelGridImport();

            if (order.Count == 0)

            {

                order = (from DataRow dr in dt.Rows

                         select new Orders()

                             {

                                 OrderID = Convert.ToInt32(dr["Order ID"]),

                                 OrderDate = Convert.ToDateTime(dr["Order Date"]),

                                 CustomerID = dr["Customer ID"].ToString(),

                                 EmployeeID = Convert.ToInt32(dr["Employee ID"]),

                                 Freight = Convert.ToDecimal(dr["Freight"]),

                                 ShipCity = dr["Ship City"].ToString()

                             }).ToList();

            }

            return order;

        }

[View-Model class]

public class Orders

    {

        public int OrderID { get; set; }

        public int EmployeeID { get; set; }

        public string CustomerID { get; set; }

        public DateTime OrderDate { get; set; }

        public string ShipCity { get; set; }

        public Decimal Freight { get; set; }

                 

    }

You can validate the each Grid columns by using “ValidationRules” property of Grid control and refer to the below code example and online documentation link.

@(Html.EJ().Grid<ExcelImport.OrdersView>("FlatGrid")

        . . .

        .Columns(col =>

        {

            col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(75).ValidationRules(v => v.AddRule("required", true).AddRule("number", true)).Add();

            col.Field("CustomerID").HeaderText("Customer ID").Width(80).ValidationRules(v => v.AddRule("required", true).AddRule("minlength", 3)).Add();

            . . .

        }))

http://help.syncfusion.com/js/grid/editing#column-validation

If it does not meet your requirement, please provide the clear details regarding your query so that we could provide you a response as early as possible.

Regards,

Saravanan A.