Import/Upload Excel file in asp.net with C#

ASP.NET, C#, Tutorials Leave a comment

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:

TestExcel

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:

ExcelDataDisplay

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 🙂

Leave a Reply