Tuesday, 8 November 2011

excel to sql

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
<style type="text/css">
tr.sectiontableentry1 td,
tr.sectiontableentry2 td {
padding: 4px;
tr.sectiontableentry1 td {
padding: 8px 5px;
background: url(hline.gif) repeat-x bottom;
tr.sectiontableentry2 td {
padding: 8px 5px;
background: url(hline.gif) repeat-x bottom #F2F2F2;
<form id="form1" runat="server">
<table style="padding: 5px; font-size: 11px;" align="center" border="0">
<strong>Please Select Excel file containing job details…</strong>
<div style="background: url(hline.gif) repeat-x bottom #F2F2F2;padding: 8px 5px;border-bottom: 1px solid #ccc;">
<asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>&nbsp;&nbsp;
<asp:Button ID="btnUpload" runat="server" Text="Upload" /><br />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True"
<asp:GridView ID="grvExcelData" runat="server">
<RowStyle CssClass="sectiontableentry2" />
<AlternatingRowStyle CssClass="sectiontableentry1" />
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Partial Class Default3
    Inherits System.Web.UI.Page
    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If (txtFilePath.HasFile) Then
            Dim conn As OleDbConnection
            Dim cmd As OleDbCommand
            Dim da As OleDbDataAdapter
            Dim ds As DataSet
            Dim query As String
            Dim connString As String = ""
            Dim strFileName As String = DateTime.Now.ToString("ddMMyyyy_HHmmss")
            Dim strFileType As String = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower()

            'Check file type
            If strFileType.Trim = ".xls" Or strFileType.Trim = ".xlsx" Then
                txtFilePath.SaveAs(Server.MapPath("~/" & strFileName & strFileType))
                lblMessage.Text = "Only excel files allowed"
                lblMessage.ForeColor = Drawing.Color.Red
                lblMessage.Visible = True
                Exit Sub
            End If

            Dim strNewPath As String = Server.MapPath("~/" & strFileName & strFileType)
            Dim fileinfo As New System.IO.FileInfo(strNewPath)
            'Connection String to Excel Workbook
            If strFileType.Trim = ".xls" Then
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
            ElseIf strFileType.Trim = ".xlsx" Then
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
            End If

            query = "SELECT * FROM [Sheet1$]"

            'Create the connection object
            conn = New OleDbConnection(connString)
            'Open connection
            If conn.State = ConnectionState.Closed Then conn.Open()
            'Create the command object
            cmd = New OleDbCommand(query, conn)
            da = New OleDbDataAdapter(cmd)
            ds = New DataSet()

            grvExcelData.DataSource = ds.Tables(0)

            Dim strConnection As String = "Data Source=local;Initial Catalog=roja;Persist Security Info=True;User ID=sa;Password=123;Min Pool Size=40;Max Pool Size=600"
            Dim dReader As OleDbDataReader
            dReader = cmd.ExecuteReader()
            Dim sqlBulk As New SqlBulkCopy(strConnection)
            'Give your Destination table name
            sqlBulk.DestinationTableName = "Excel_table"

            lblMessage.Text = "Please select an excel file first"
            lblMessage.ForeColor = Drawing.Color.Red
            lblMessage.Visible = True
        End If
    End Sub
End Class