This article will explain how to easily import Excel files into GridGroupingControl in ASP.NET (Classic).
- Users specify the location of the Excel Worksheet which needs to be imported.
- Specified Excel file is read by the application into a DataTable and bound into the GridGroupingControl.
Here, a simple Grid configuration is used to showcase the demo. The following code example illustrates how to define the GridGroupingControl in an ASP.NET webpage:
Converting Excel data to DataTable
The ExportDataTable() method of Syncfusion XlSIO is used to convert Excel information to DataTable information.
The following code illustrates the conversion process:
private DataTable ExcelData()
//Step 1 : Instantiates the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiates the excel application object.
IApplication application = excelEngine.Excel;
//Opens an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = application.Workbooks.Open(Request.PhysicalApplicationPath + "/Northwind.xls");
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets;
//Reads data from spreadsheet.
DataTable customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
return customersTable; // return this as datasource to grid
Private Function ExcelData() As DataTable
'Step 1 : Instantiates the spreadsheet creation engine.
Dim excelEngine As New ExcelEngine()
'Step 2 : Instantiates the excel application object.
Dim application As IApplication = excelEngine.Excel
'Opens an existing spreadsheet which will be used as a template for generating the new spreadsheet.
'After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
Dim workbook As IWorkbook = application.Workbooks.Open(Request.PhysicalApplicationPath + "/Northwind.xls")
'The first worksheet object in the worksheets collection is accessed.
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Read data from spreadsheet.
Dim customersTable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
' return this as datasource to grid
Binding Excel data to GridGroupingControl
Finally, the converted DataTable file can be set as DataSource to GridGroupingControl. You can refer to the below codes to perform this task:
protected void Page_Load(object sender, EventArgs e)
this.GridGroupingControl1.DataSource = ExcelData();
Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Me.GridGroupingControl1.DataSource = ExcelData()
The output appears as follows:
Figure 1: Sample Excel Data
Figure 2: GridGroupingControl with Imported Excel
You can also download the source code in C# and VB.Net using this link: Sample.zip
Content Contributor: Silambarasan | Content Editor: Diljith H.