Read and Import Excel File into Data. Set or Data. Table using C# and VB. Net in ASP. Nethow to upload Excel File and then read and import its data into Data. Set or Data. Table using C# and VB. Net in ASP. Net Web Application. The imported Excel File data is then displayed in ASP. Net Grid. View control.
In one of my articles I explained How to Read Excel using ADO. Net. Concept. 1. User browses and selects an Excel Workbook. ![]() ![]() User selects whether Header row is present in Excel Sheet or not using radio buttons. User uploads the Excel Workbook. This article shows how to read SharePoint List data and display it in a HTML page using jQuery & SPservices dynamically. This document provides an overview of many of the new features for ASP.NET that are included in the.NET Framework 4 and in Visual Studio 2010. Home of the Microsoft ASP.NET development community. Download Visual Studio, post to the forums, read ASP.NET blogs and learn about ASP.NET. Here Mudassar Ahmed Khan has explained how to read and display data from a sheet of an Excel file (.xls and.xlsx) and display it in ASP.Net using C# and VB.Net. ![]() Uploaded File is read by the application into a Data. Set and displayed on the web page using Grid. View Control. 5. Grid. View has paging enabled so that user can view the records easily. Connection Strings. Since there Excel 9. Excel 2. 00. 7 use different providers I have placed two connection strings keys in the Web. Config. < connection. Strings> < addname ="Excel. Con. String" connection. String="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source={0}; Extended Properties='Excel 8. HDR={1}'"/> < addname ="Excel. Con. String" connection. String="Provider=Microsoft. ACE. OLEDB. 1. 2. Data Source={0}; Extended Properties='Excel 8. HDR={1}'"/> < /connection. Strings> You will notice above there are two connection strings one for Excel 9. Microsoft Jet driver and another one is for Excel 2. Microsoft Ace driver. I have used Placeholders for Data Source {0} and the HDR {1} property so that I can easily replace fill it in the front end based on the Excel File Selected and the Headers property selected by the user through the checkbox respectively. Front End design. The front end design of the web page contains a File. Upload Control, a Button which will be used to upload the Excel File, Radio. Button. List for the user to select whether headers are present or not by default Yes is selected and finally the Grid. View control in which I have set Allow. Paging property to true.< asp: File. Upload. ID="File. Upload. 1"runat="server"/> < asp: Button. ID="btn. Upload"runat="server"Text="Upload" On. Click="btn. Upload_Click"/> < br/> < asp: Label. ID="Label. 1"runat="server"Text="Has Header ?"/> < asp: Radio. Button. List. ID="rb. HDR"runat="server"> < asp: List. Item. Text="Yes"Value="Yes"Selected="True"> < /asp: List. Item> < asp: List. Item. Text="No"Value="No"> < /asp: List. Item> < /asp: Radio. Button. List> < asp: Grid. View. ID="Grid. View. On. Page. Index. Changing="Page. Index. Changing"Allow. Paging="true"> < /asp: Grid. View> Uploading and Reading the Excel Sheet. Next comes the part to upload the file and reading it. As you will notice in the aspx I have added On. Click event handler to the Upload button which will be triggered when the upload button is clicked. When the upload button is clicked the uploaded File is saved to a Folder whose path is defined in the App Settings section in the Web. Config using the following key< app. Settings> < addkey ="Folder. Path"value ="Files/"/> < /app. Settings > Once the File is saved in the folder the Import_To_Grid method is called up which is described later. Below is the code snippet for the Upload button event handler. C#protectedvoid btn. Upload_Click(object sender, Event. Args e){ if (File. Upload. 1. Has. File) { string File. Name = Path. Get. File. Name(File. Upload. Posted. File. File. Name); string Extension = Path. Get. Extension(File. Upload. 1. Posted. File. File. Name); string Folder. Path = Configuration. Manager. App. Settings["Folder. Path"]; string File. Path = Server. Map. Path(Folder. Path + File. Name); File. Upload. 1. Save. As(File. Path); Import_To_Grid(File. Path, Extension, rb. HDR. Selected. Item. Text); }}VB. Net. Protected. Sub btn. Upload_Click(By. Val sender As. Object, By. Val e As System. Event. Args) If File. Upload. 1. Has. File Then Dim File. Name As. String = Path. Get. File. Name(File. Upload. 1. Posted. File. File. Name) Dim Extension As. String = Path. Get. Extension(File. Upload. Posted. File. File. Name) Dim Folder. Path As. String = Configuration. Manager. App. Settings("Folder. Path") Dim File. Path As. String = Server. Map. Path(Folder. Path + File. Name) File. Upload. 1. Save. As(File. Path) Import_To_Grid(File. Path, Extension, rb. HDR. Selected. Item. Text) End. If. End. Sub. Now the saved Excel file is read using OLEDB. Based on the extension I decide the driver to be used in order to read the excel file and also whether Excel will be read along with header row or not based on the Radio. Button. List. All these values are filled in the placeholders of the connection string. After that I read the schema of the Excel Workbook in order to find out the Sheet Name of the first sheet. Once I get that I fire a select query on the first Excel sheet and fill a datatable which is then passed to the Grid. View as data source. You can refer the complete function below. C#privatevoid Import_To_Grid(string File. Path, string Extension, string is. HDR){ string con. Str=""; switch (Extension) { case". Excel 9. 7- 0. 3 con. Str = Configuration. Manager. Connection. Strings["Excel. 03. Con. String"] . Connection. String; break; case". Excel 0. 7 con. Str = Configuration. Manager. Connection. Strings["Excel. 07. Con. String"] . Connection. String; break; } con. Str = String. Format(con. Str, File. Path, is. HDR); Ole. Db. Connection conn. Excel = new. Ole. Db. Connection(con. Str); Ole. Db. Command cmd. Excel = new. Ole. Db. Command(); Ole. Db. Data. Adapter oda = new. Ole. Db. Data. Adapter(); Data. Table dt = new. Data. Table(); cmd. Excel. Connection = conn. Excel; //Get the name of First Sheet conn. Excel. Open(); Data. Table dt. Excel. Schema; dt. Excel. Schema = conn. Excel. Get. Ole. Db. Schema. Table(Ole. Db. Schema. Guid. Tables, null); string Sheet. Name = dt. Excel. Schema. Rows[0]["TABLE_NAME"]. To. String(); conn. Excel. Close(); //Read Data from First Sheet conn. Excel. Open(); cmd. Excel. Command. Text = "SELECT * From [" + Sheet. Name + "]"; oda. Select. Command = cmd. Excel; oda. Fill(dt); conn. Excel. Close(); //Bind Data to Grid. View Grid. View. Caption = Path. Get. File. Name(File. Path); Grid. View. 1. Data. Source = dt; Grid. View. 1. Data. Bind(); }. VB. Net. Private. Sub Import_To_Grid(By. Val File. Path As. String, By. Val Extension As. String, By. Val is. HDR As. String) Dim con. Str As. String = "" Select. Case Extension Case". Excel 9. 7- 0. 3 con. Str = Configuration. Manager. Connection. Strings("Excel. 03. Con. String") _ . Connection. String Exit. Select Case". Excel 0. 7 con. Str = Configuration. Manager. Connection. Strings("Excel. 07. Con. String") _ . Connection. String Exit. Select End. Select con. Str = String. Format(con. Str, File. Path, is. HDR) Dim conn. Excel As. New Ole. Db. Connection(con. Str) Dim cmd. Excel As. New Ole. Db. Command() Dim oda As. New Ole. Db. Data. Adapter() Dim dt As. New Data. Table() cmd. Excel. Connection = conn. Excel 'Get the name of First Sheet conn. Excel. Open() Dim dt. Excel. Schema As Data. Table dt. Excel. Schema = conn. Excel. Get. Ole. Db. Schema. Table(Ole. Db. Schema. Guid. Tables, Nothing) Dim Sheet. Name As. String = dt. Excel. Schema. Rows(0)("TABLE_NAME"). To. String() conn. Excel. Close() 'Read Data from First Sheet conn. Excel. Open() cmd. Excel. Command. Text = "SELECT * From [" & Sheet. Name & "]" oda. Select. Command = cmd. Excel oda. Fill(dt) conn. Excel. Close() 'Bind Data to Grid. View Grid. View. Caption = Path. Get. File. Name(File. Path) Grid. View. 1. Data. Source = dt Grid. View. 1. Data. Bind()End. Sub. Pagination in Grid. View. Now in order to implement paging we will need to read the excel sheet each time and then rebind datatable to the Grid. View. Refer below. C#protectedvoid Page. Index. Changing(object sender, Grid. View. Page. Event. Args e){ string Folder. Path = Configuration. Manager. App. Settings["Folder. Path"] ; string File. Name = Grid. View. Caption; string Extension = Path. Get. Extension(File. Name); string File. Path = Server. Map. Path(Folder. Path + File. Name); Import_To_Grid(File. Path, Extension, rb. HDR. Selected. Item. Text); Grid. View. 1. Page. Index = e. New. Page. Index; Grid. View. 1. Data. Bind(); }VB. Net. Protected. Sub Page. Index. Changing(By. Val sender As. Object, By. Val e As Grid. View. Page. Event. Args) Dim Folder. Path As. String = Configuration. Manager. App. Settings("Folder. Path") Dim File. Name As. String = Grid. View. 1. Caption Dim Extension As. String = Path. Get. Extension(File. Name) Dim File. Path As. String = Server. Map. Path(Folder. Path + File. Name) Import_To_Grid(File. Path, Extension, rb. HDR. Selected. Item. Text) Grid. View. Page. Index = e. New. Page. Index Grid. View. 1. Data. Bind()End. Sub. The figure below displays the Grid. View populated from the excel workbook that is uploaded using the File. Upload Control. Important thing to note that always close the connections after reading the Excel Workbook to avoid the following error. Server Error in 'ASP. Net' Application. The process cannot access the file 'F: \Read. Excel. To. Grid\Files\Excel. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System. IO. IOException: The process cannot access the file 'F: \Read. Excel. To. Grid\Files\Excel. You can download the source code in VB. Net and C# using the link below. Read. Excel. To. Grid.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2016
Categories |