Monday 17 November 2014

Upload And Read Excel Data/(.csv/.xls)File into DataSet and display in Gridview in Asp.Net using C#/VB.NET

Hi friends,in this article I will explain about how to upload Excel File and then read and import its data into DataSet or DataTable using C# and VB.Net in ASP.Net Web Application.
I already explained in the previous articles about Creating Dynamic CSS Menu From Database SQL Server in ASP.Net Using C#.Net/VB.NETJQuery: Send (Pass) Data (Values) from one page to another using Form Post and Search GridView records (data) on TextBox KeyPress using jQuery in ASP.NET using C#/VB.NET

The imported Excel File data is then displayed in ASP.Net GridView control.

ASP.NET:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Upload And Read Excel Data/File into DataSet in Asp.Net using C#/VB.NET</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    <asp:FileUpload ID="FileUpload1" runat="server" />&nbsp;&nbsp;
                </td>
                <td>
                    <asp:Button ID="Button_Upload" runat="server" Text="Upload Template" OnClick="Button_Upload_Click"
                        Width="190px" OnClientClick="return confirm('Are you sure you want to upload');" />
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:GridView ID="gvData" runat="server" BackColor="White" BorderColor="#CC9966"
                        AutoGenerateColumns="false" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"
                        Font-Names="Georgia" Font-Size="Small" Width="475px">
                        <Columns>
                            <asp:BoundField HeaderText="User ID" DataField="User_ID" />
                            <asp:BoundField HeaderText="UserName" DataField="UserName" />
                            <asp:BoundField HeaderText="Gender" DataField="Gender" />
                            <asp:BoundField HeaderText="Country" DataField="Country" />
                        </Columns>
                        <FooterStyle BackColor="Tan" />
                        <AlternatingRowStyle BackColor="#E6E6E1" />
                        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

C#:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.Odbc;

public partial class ReadDataFromExcel : System.Web.UI.Page
{
    protected void Button_Upload_Click(object sender, EventArgs e)
    {
        if ((FileUpload1.PostedFile != null))
        {
            if (!string.IsNullOrEmpty(FileUpload1.PostedFile.FileName))
            {
                string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string FolderPath = "ExcelFiles";
                string FilePath = Server.MapPath("~/" + FolderPath + "\\" + FileName);
                FileUpload1.SaveAs(FilePath);
                DataSet ds_ExcelData = ReadExcelData(FilePath);
                gvData.DataSource = ds_ExcelData.Tables[1];
                gvData.DataBind();
            }
        }

    }

    public DataSet ReadExcelData(string fileName)
    {
        string strCon = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=" + fileName;
        OdbcConnection con = new OdbcConnection(strCon);
        DataSet ds = new DataSet();
        con.Open();
        DataTable dt_SheetNames = con.GetSchema("Tables");
        ds.Tables.Add(dt_SheetNames);
        for (int i = 0; i < dt_SheetNames.Rows.Count; i++)
        {
            OdbcCommand cmd = new OdbcCommand(@"SELECT * FROM [" + dt_SheetNames.Rows[i]["TABLE_NAME"].ToString() + "]");
            cmd.Connection = con;
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            da.Fill(ds, dt_SheetNames.Rows[i]["TABLE_NAME"].ToString());
        }
        con.Close();
        return ds;
    }
}

VB.NET:
Imports System.Collections.Generic
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports System.Configuration
Imports System.Data
Imports System.Data.Odbc

Partial Public Class ReadDataFromExcelVB
    Inherits System.Web.UI.Page
    Protected Sub Button_Upload_Click(sender As Object, e As EventArgs)
        If (FileUpload1.PostedFile IsNot Nothing) Then
            If Not String.IsNullOrEmpty(FileUpload1.PostedFile.FileName) Then
                Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
                Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
                Dim FolderPath As String = "ExcelFiles"
                Dim FilePath As String = Server.MapPath(Convert.ToString((Convert.ToString("~/") & FolderPath) + "\") & FileName)
                FileUpload1.SaveAs(FilePath)
                Dim ds_ExcelData As DataSet = ReadExcelData(FilePath)
                gvData.DataSource = ds_ExcelData.Tables(1)
                gvData.DataBind()
            End If
        End If

    End Sub

    Public Function ReadExcelData(fileName As String) As DataSet
        Dim strCon As String = Convert.ToString("Driver={Microsoft Excel Driver (*.xls)};DBQ=") & fileName
        Dim con As New OdbcConnection(strCon)
        Dim ds As New DataSet()
        con.Open()
        Dim dt_SheetNames As DataTable = con.GetSchema("Tables")
        ds.Tables.Add(dt_SheetNames)
        For i As Integer = 0 To dt_SheetNames.Rows.Count - 1
            Dim cmd As New OdbcCommand("SELECT * FROM [" + dt_SheetNames.Rows(i)("TABLE_NAME").ToString() + "]")
            cmd.Connection = con
            Dim da As New OdbcDataAdapter(cmd)
            da.Fill(ds, dt_SheetNames.Rows(i)("TABLE_NAME").ToString())
        Next
        con.Close()
        Return ds
    End Function
End Class

The output of above code as shown in the below figure.

When you choose excel file and click on Upload Template then excel data will be shown in GridView.

You can download the code by clicking on the below Download image. 

No comments:

Post a Comment

© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.