Importing Excel files into GridGroupingControl | Syncfusion Blogs
Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (173).NET Core  (29).NET MAUI  (199)Angular  (107)ASP.NET  (51)ASP.NET Core  (82)ASP.NET MVC  (89)Azure  (40)Black Friday Deal  (1)Blazor  (211)BoldSign  (13)DocIO  (24)Essential JS 2  (106)Essential Studio  (200)File Formats  (63)Flutter  (132)JavaScript  (219)Microsoft  (118)PDF  (80)Python  (1)React  (98)Streamlit  (1)Succinctly series  (131)Syncfusion  (892)TypeScript  (33)Uno Platform  (3)UWP  (4)Vue  (45)Webinar  (50)Windows Forms  (61)WinUI  (68)WPF  (157)Xamarin  (161)XlsIO  (35)Other CategoriesBarcode  (5)BI  (29)Bold BI  (8)Bold Reports  (2)Build conference  (8)Business intelligence  (55)Button  (4)C#  (146)Chart  (127)Cloud  (15)Company  (443)Dashboard  (8)Data Science  (3)Data Validation  (8)DataGrid  (62)Development  (618)Doc  (8)DockingManager  (1)eBook  (99)Enterprise  (22)Entity Framework  (5)Essential Tools  (14)Excel  (37)Extensions  (22)File Manager  (6)Gantt  (18)Gauge  (12)Git  (5)Grid  (31)HTML  (13)Installer  (2)Knockout  (2)Language  (1)LINQPad  (1)Linux  (2)M-Commerce  (1)Metro Studio  (11)Mobile  (497)Mobile MVC  (9)OLAP server  (1)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (42)Performance  (12)PHP  (2)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (12)Road Map  (12)Scheduler  (52)Security  (3)SfDataGrid  (9)Silverlight  (21)Sneak Peek  (31)Solution Services  (4)Spreadsheet  (11)SQL  (10)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (12)Tips and Tricks  (112)UI  (379)Uncategorized  (68)Unix  (2)User interface  (68)Visual State Manager  (2)Visual Studio  (31)Visual Studio Code  (17)Web  (582)What's new  (319)Windows 8  (19)Windows App  (2)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)

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:

image001

Figure 1: Sample Excel Data

image002

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.

Tags:

Share this post:

Popular Now

Be the first to get updates

Subscribe RSS feed