We have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() , Aggregate functions in sql server and Identity column in sql server. Today I am going to explain you regarding how to import/upload excel file and display it using OLEDB.
I was seating and was thinking what to post today. But one of my friend was looking for solution about how to import/upload excel file in asp.net with C#. And i give him a following solution.
Step: 1: First we need to create one excel file like below:
Step:2: We will Create one page for upload excel file like:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UploadExcel.aspx.cs" Inherits="DBCNEW.UploadExcel" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Upload and Display Excel file</title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td>Select File:</td> <td> <asp:FileUpload ID="FileUpload" runat="server" /> </td> <td> <asp:Button ID="btnupload" runat="server" Text="Upload" OnClick="btnupload_Click" /></td> </tr> </table> <table> <tr> <td> <asp:GridView ID="GrdExcelData" runat="server"></asp:GridView> </td> </tr> </table> </div> </form> </body> </html>
Step-3 : Now open code behind code and add following namespaces
using System.Data; using System.IO; using System.Data.OleDb;
Step-4: After that add below code at btnupload click event
string connString = ""; string strFileType = Path.GetExtension(FileUpload.FileName).ToLower(); string path = FileUpload.PostedFile.FileName; //Connection String for Excel Workbook if (strFileType.Trim() == ".xls") { connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (strFileType.Trim() == ".xlsx") { connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } string query = "SELECT * FROM [Sheet1$]"; //This is query by which we can select record by excel file. We have to provide sheet name with $ sign. OleDbConnection conn = new OleDbConnection(connString); if (conn.State == ConnectionState.Closed) conn.Open(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count > 0) { GrdExcelData.DataSource = dt; GrdExcelData.DataBind(); da.Dispose(); conn.Close(); conn.Dispose(); }
Step-5: Run application and you will get following result:
You can also download Demo code by Clicking Me.
string path = Server.MapPath(FileUpload.PostedFile.FileName);
FileUpload.SaveAs(path);
Use Server.MapPath for asp.net production website to avoid unwanted errors.
Try some basic function with uploaded file like Save,SaveAs,Delete,etc. And let me know in comment, if you facing any problem. I will try to upload about it.
Thanks 🙂