Importing Excel files into GridGroupingControl

This article will explain how to easily import Excel files into GridGroupingControl in ASP.NET (Classic).

Concepts:

• 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.

ASPX

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:

<syncfusion:gridgroupingcontrol id="GridGroupingControl1" runat="server" showgroupdroparea="False" autoformat="Office 2007 Blue" width="500">
<tabledescriptor allowfilter="False" allownew="false" allowedit="false">
</tabledescriptor>
</syncfusion:gridgroupingcontrol>

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:

C#

using Syncfusion.XlsIO;
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[0];

//Reads data from spreadsheet.
DataTable customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

return customersTable;   // return this as datasource to grid
}

VB.NET

Imports Syncfusion.XlsIO
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 customersTable
' return this as datasource to grid
End Function

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:

C#

protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{
this.GridGroupingControl1.DataSource = ExcelData();
this.GridGroupingControl1.DataBind();
}
}

VB.NET

Protected Sub Page_Load(sender As Object, e As EventArgs) 
If Not IsPostBack Then
Me.GridGroupingControl1.DataSource = ExcelData()
Me.GridGroupingControl1.DataBind()
End If
End Sub

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.