Upload excel values into database by mapping the fields
UploadItems.aspx
<%@ Page Language="VB" MasterPageFile="~/MainPage.master" AutoEventWireup="false" CodeFile="UploadItemscsv.aspx.vb" Inherits="UploadItems" title="Upload Items CSV" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<style type="text/css">
.style3
{
height: 216px;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContainer" Runat="Server">
<fieldset ><legend>Add Items into Data Base through CSV </legend>
<table width="100%">
<tr>
<td align="center" colspan="2" >
<asp:Label ID="lblMsg" runat="server" Font-Bold="True" CssClass="Err"></asp:Label>
</td>
</tr>
<tr>
<td align="center" colspan="2" >
</td>
</tr>
<tr>
<td align="right" width="50%">
Upload CSV File :
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td align="center">
<asp:Button ID="btnSave" runat="server" Text="Upload" Width="74px"
CssClass="button" />
</td>
</tr>
<tr>
<td align="right" width="50%">
</td>
<td align="center">
</td>
</tr>
<tr>
<td>
<asp:UpdatePanel ID="updFieldMapping" runat="server">
<ContentTemplate>
<fieldset><legend>Mapping DateBase Fields</legend>
<table width="100%">
<tr>
<td align="left">
<asp:Label ID="Label1" runat="server" Font-Bold="True"
Text="Excel Sheet Fields"></asp:Label>
</td>
<td>
</td>
<td>
<asp:Label ID="Label2" runat="server" Font-Bold="True" Text="Data Base Fields"></asp:Label>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:ListBox ID="lstExcelFields" runat="server" Height="250px" Width="145px">
</asp:ListBox>
</td>
<td class="style3" width="10">
</td>
<td>
<asp:ListBox ID="lstDBFields" runat="server" Height="250px" Width="145px">
<asp:ListItem>Item Name</asp:ListItem>
</asp:ListBox>
</td>
<td class="style3" width="10">
<asp:Button ID="btnGo" runat="server" Text="Go" Width="50px" CssClass="button" />
</td>
<td valign="top">
<table>
<tr>
<td>
<asp:TextBox ID="txtName" runat="server" ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
</table>
</td>
</tr>
</table>
</fieldset>
</ContentTemplate>
</asp:UpdatePanel>
</td>
<td>
<fieldset><legend>Uploaded CSV File</legend>
<asp:Panel ID="ExcelListpane" runat='server' Width="100%" ScrollBars="Auto"
Height="250px">
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="100%">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<EmptyDataTemplate>
<asp:Label ID="emptymsg" runat="server" CssClass="Err" Text="No data found"></asp:Label>
</EmptyDataTemplate>
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</asp:Panel>
<asp:Button
ID="btnSelectAll" runat="server" Text="Select All" CssClass="button"
Enabled="False" />
<asp:Button
ID="btnDeSelectAll" runat="server" Text="De Select All" CssClass="button"
Enabled="False" />
<asp:Button ID="btnSaveInDb" runat="server" Text="Save" CssClass="button"
Enabled="False" />
</fieldset>
</td>
</tr>
<tr>
<td colspan="2">
</td>
</tr>
</table>
</fieldset>
</asp:Content>
UploadItems.aspx.vb
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Partial Class UploadItems
Inherits System.Web.UI.Page
Dim str As String = ""
'Dim mycon As New MySqlConnection(ConfigurationManager.ConnectionStrings("INVConnectionString").ConnectionString)
'Dim mycon As New SqlConnection("Data Source=whurray;Initial Catalog=whurrayDB;User ID=ray;Password=ray;Pooling=False")
'Dim mycom As New MySqlCommand
'Dim ds As New DataSet
'Dim dr As DataRow
'Dim ada As MySqlDataAdapter
'Dim strMemId As String
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
If FileUpload1.HasFile Then
Dim fileExt As String
fileExt = System.IO.Path.GetExtension(FileUpload1.FileName)
If fileExt = ".csv" Or fileExt = ".CSV" Then
'Make variables needed
Dim objStream As Stream
Dim fileLen As Integer
'Get the length of the file.
fileLen = FileUpload1.PostedFile.ContentLength
'Create a byte array to hold the contents of the file.
Dim Input(fileLen) As Byte
'Move byte array into a stream, then start it at beginning and pass it to method.
objStream = FileUpload1.PostedFile.InputStream
objStream.Read(Input, 0, fileLen)
objStream.Position = 0
'Manage File
importToDG(objStream)
lblMsg.Visible = True
SaveUploadedFile()
lblMsg.Text = "Csv File Uploaded Successfully with " & GridView1.Rows.Count & " records."
'lblcount.Text = "Records found : " & GridView1.Rows.Count
' btnSaveInDb.Visible = True
'btnSelectAll.Visible = True
btnSelectAll.Enabled = True
btnDeSelectAll.Enabled = True
btnSaveInDb.Enabled = True
Else
lblMsg.Visible = True
lblMsg.Text = "Please upload csv files"
Exit Sub
End If
End If
Catch ex As Exception
lblMsg.Visible = True
lblMsg.Text = ex.ToString
End Try
End Sub
Private Sub importToDG(ByVal fileStream As Stream)
Dim streamRdr As StreamReader
Dim fileLines As String()
Dim line As String
streamRdr = New StreamReader(fileStream)
Dim myDTable As DataTable = New DataTable
Dim myDRow As DataRow = myDTable.NewRow
'Dim ItemName, PhoneNo As New DataColumn
'ItemName.ColumnName = "Item Name"
'PhoneNo.ColumnName = "PhoneNo"
'EmailID.ColumnName = "EmailID"
'Address.ColumnName = "Address"
'City.ColumnName = "City"
'State.ColumnName = "State"
'Country.ColumnName = "Country"
'CompanyName.ColumnName = "CompanyName"
'PhoneNo1.ColumnName = "PhoneNo1"
'PhoneNo2.ColumnName = "PhoneNo2"
'PhoneNo3.ColumnName = "PhoneNo3"
'PhoneNo4.ColumnName = "PhoneNo4"
'myDTable.Columns.Add(ItemName)
'myDTable.Columns.Add(PhoneNo)
'myDTable.Columns.Add(EmailID)
'myDTable.Columns.Add(Address)
'myDTable.Columns.Add(City)
'myDTable.Columns.Add(State)
'myDTable.Columns.Add(Country)
'myDTable.Columns.Add(CompanyName)
'myDTable.Columns.Add(PhoneNo1)
'myDTable.Columns.Add(PhoneNo2)
'myDTable.Columns.Add(PhoneNo3)
'myDTable.Columns.Add(PhoneNo4)
'Now set the streamreader back to beginning.
streamRdr.DiscardBufferedData()
streamRdr.BaseStream.Seek(0, SeekOrigin.Begin)
streamRdr.BaseStream.Position = 0
Dim i As Integer = 0 'Skip the first row.
While Not streamRdr.EndOfStream
line = streamRdr.ReadLine 'Read first line
fileLines = line.Split(",") 'Split the line up by the delimeter
If i = 0 Then
For j As Integer = 0 To fileLines.Length - 1
lstExcelFields.Items.Add(fileLines(j))
myDTable.Columns.Add(New DataColumn(fileLines(j)))
Next
End If
If i > 0 Then
myDRow.ItemArray = fileLines
myDTable.Rows.Add(myDRow)
myDRow = myDTable.NewRow
'Else
' myDRow.ItemArray = fileLines
' myDTable.Rows.Add(myDRow)
' myDTable.Columns.Add(fileLines(0))
' myDTable.Columns.Add(fileLines(1))
' myDTable.Columns.Add(fileLines(2))
End If
i += 1
End While
GridView1.DataSource = myDTable
GridView1.DataBind()
End Sub
Public Sub GetData()
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & str & ";Extended Properties=Excel 12.0"
' Create the connection object
Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
Try
' Open connection
oledbConn.Open()
' Create OleDbCommand object and select data from worksheet Sheet3
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
' Create new OleDbDataAdapter
Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()
oleda.SelectCommand = cmd
' Create a DataSet which will hold the data extracted from the worksheet.
Dim ds As DataSet = New DataSet()
' Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds, "Sheet1")
' Bind the data to the GridView
GridView1.DataSource = ds.Tables(0).DefaultView
GridView1.DataBind()
Catch ex As Exception
lblMsg.Visible = True
lblMsg.Text = ex.Message
Finally
' Close connection
oledbConn.Close()
End Try
End Sub
Protected Sub btnSelectAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
Try
Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
For j As Integer = 0 To GridView1.Rows.Count - 1
GridView1.SelectedIndex = j
checkbox1 = GridView1.SelectedRow.FindControl("checkbox1")
checkbox1.Checked = True
Next
Catch ex As Exception
lblMsg.Text = ex.ToString
lblMsg.Visible = True
End Try
End Sub
Protected Sub btnSaveInDb_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveInDb.Click
If ViewState.Count < 1 Then
lblMsg.Text = "Please Map the Item Name field"
Return
End If
Try
Dim uploaditems As Integer = 0
Dim existsitems As Integer = 0
Dim selecteditems As Integer = 0
Dim skipeditems As Integer = 0
Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
For j As Integer = 0 To GridView1.Rows.Count - 1
'GridView1.SelectedIndex = j
checkbox1 = GridView1.Rows(j).FindControl("checkbox1")
'GridView1.Rows(j).FindControl("checkbox1").
If checkbox1.Checked Then
selecteditems += 1
Dim str As String = GridView1.Rows(j).Cells(ViewState("str1")).Text
If GridView1.Rows(j).Cells(ViewState("str1")).Text.Length > 3 And str <> " " Then
Dim len As Integer = GridView1.Rows(j).Cells(ViewState("str1")).Text.Length
If clsItems.AddItem(GridView1.Rows(j).Cells(ViewState("str1")).Text) = 1 Then
uploaditems += 1
Else
existsitems += 1
End If
Else
skipeditems += 1
End If
End If
Next
lblMsg.Text = selecteditems & " Items Selected, " & uploaditems & " items Uploaded," & existsitems & " Items existed, " & skipeditems & " Items skipped"
Catch ex As Exception
lblMsg.Text = ex.ToString
lblMsg.Visible = True
End Try
End Sub
Protected Sub btnDeSelectAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDeSelectAll.Click
Try
Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
For j As Integer = 0 To GridView1.Rows.Count - 1
GridView1.SelectedIndex = j
checkbox1 = GridView1.SelectedRow.FindControl("checkbox1")
checkbox1.Checked = False
Next
Catch ex As Exception
lblMsg.Text = ex.ToString
lblMsg.Visible = True
End Try
End Sub
Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
If lstDBFields.SelectedIndex = 0 Then
txtName.Text = lstExcelFields.SelectedItem.Text & " --> " & lstDBFields.SelectedItem.Text
Session.Add("strj1", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'Session("str1") = lstExcelFields.SelectedIndex + 1
ViewState("str1") = lstExcelFields.SelectedIndex + 1
End If
'If lstDBFields.SelectedIndex = 2 Then
' txtPhoneNo.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj2", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 3 Then
' txtEmailId.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj3", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 4 Then
' txtAddress.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj4", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 5 Then
' txtCity.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj5", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 6 Then
' txtState.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj6", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 7 Then
' txtCountry.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj7", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 8 Then
' txtCompanyName.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj8", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 9 Then
' txtPhoneNo1.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj9", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 10 Then
' txtPhoneNo2.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj10", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 11 Then
' txtPhoneNo3.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj11", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 12 Then
' txtPhoneNo4.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj12", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
End Sub
Public Sub SaveUploadedFile()
If FileUpload1.HasFile Then
FileUpload1.SaveAs((Server.MapPath(".\Uploads") & "\") & FileUpload1.FileName)
str = Server.MapPath(".\Uploads") & "\" & FileUpload1.FileName
End If
End Sub
End Class
UploadItems.aspx
<%@ Page Language="VB" MasterPageFile="~/MainPage.master" AutoEventWireup="false" CodeFile="UploadItemscsv.aspx.vb" Inherits="UploadItems" title="Upload Items CSV" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<style type="text/css">
.style3
{
height: 216px;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContainer" Runat="Server">
<fieldset ><legend>Add Items into Data Base through CSV </legend>
<table width="100%">
<tr>
<td align="center" colspan="2" >
<asp:Label ID="lblMsg" runat="server" Font-Bold="True" CssClass="Err"></asp:Label>
</td>
</tr>
<tr>
<td align="center" colspan="2" >
</td>
</tr>
<tr>
<td align="right" width="50%">
Upload CSV File :
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td align="center">
<asp:Button ID="btnSave" runat="server" Text="Upload" Width="74px"
CssClass="button" />
</td>
</tr>
<tr>
<td align="right" width="50%">
</td>
<td align="center">
</td>
</tr>
<tr>
<td>
<asp:UpdatePanel ID="updFieldMapping" runat="server">
<ContentTemplate>
<fieldset><legend>Mapping DateBase Fields</legend>
<table width="100%">
<tr>
<td align="left">
<asp:Label ID="Label1" runat="server" Font-Bold="True"
Text="Excel Sheet Fields"></asp:Label>
</td>
<td>
</td>
<td>
<asp:Label ID="Label2" runat="server" Font-Bold="True" Text="Data Base Fields"></asp:Label>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:ListBox ID="lstExcelFields" runat="server" Height="250px" Width="145px">
</asp:ListBox>
</td>
<td class="style3" width="10">
</td>
<td>
<asp:ListBox ID="lstDBFields" runat="server" Height="250px" Width="145px">
<asp:ListItem>Item Name</asp:ListItem>
</asp:ListBox>
</td>
<td class="style3" width="10">
<asp:Button ID="btnGo" runat="server" Text="Go" Width="50px" CssClass="button" />
</td>
<td valign="top">
<table>
<tr>
<td>
<asp:TextBox ID="txtName" runat="server" ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
</table>
</td>
</tr>
</table>
</fieldset>
</ContentTemplate>
</asp:UpdatePanel>
</td>
<td>
<fieldset><legend>Uploaded CSV File</legend>
<asp:Panel ID="ExcelListpane" runat='server' Width="100%" ScrollBars="Auto"
Height="250px">
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="100%">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<EmptyDataTemplate>
<asp:Label ID="emptymsg" runat="server" CssClass="Err" Text="No data found"></asp:Label>
</EmptyDataTemplate>
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</asp:Panel>
<asp:Button
ID="btnSelectAll" runat="server" Text="Select All" CssClass="button"
Enabled="False" />
<asp:Button
ID="btnDeSelectAll" runat="server" Text="De Select All" CssClass="button"
Enabled="False" />
<asp:Button ID="btnSaveInDb" runat="server" Text="Save" CssClass="button"
Enabled="False" />
</fieldset>
</td>
</tr>
<tr>
<td colspan="2">
</td>
</tr>
</table>
</fieldset>
</asp:Content>
UploadItems.aspx.vb
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Partial Class UploadItems
Inherits System.Web.UI.Page
Dim str As String = ""
'Dim mycon As New MySqlConnection(ConfigurationManager.ConnectionStrings("INVConnectionString").ConnectionString)
'Dim mycon As New SqlConnection("Data Source=whurray;Initial Catalog=whurrayDB;User ID=ray;Password=ray;Pooling=False")
'Dim mycom As New MySqlCommand
'Dim ds As New DataSet
'Dim dr As DataRow
'Dim ada As MySqlDataAdapter
'Dim strMemId As String
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
If FileUpload1.HasFile Then
Dim fileExt As String
fileExt = System.IO.Path.GetExtension(FileUpload1.FileName)
If fileExt = ".csv" Or fileExt = ".CSV" Then
'Make variables needed
Dim objStream As Stream
Dim fileLen As Integer
'Get the length of the file.
fileLen = FileUpload1.PostedFile.ContentLength
'Create a byte array to hold the contents of the file.
Dim Input(fileLen) As Byte
'Move byte array into a stream, then start it at beginning and pass it to method.
objStream = FileUpload1.PostedFile.InputStream
objStream.Read(Input, 0, fileLen)
objStream.Position = 0
'Manage File
importToDG(objStream)
lblMsg.Visible = True
SaveUploadedFile()
lblMsg.Text = "Csv File Uploaded Successfully with " & GridView1.Rows.Count & " records."
'lblcount.Text = "Records found : " & GridView1.Rows.Count
' btnSaveInDb.Visible = True
'btnSelectAll.Visible = True
btnSelectAll.Enabled = True
btnDeSelectAll.Enabled = True
btnSaveInDb.Enabled = True
Else
lblMsg.Visible = True
lblMsg.Text = "Please upload csv files"
Exit Sub
End If
End If
Catch ex As Exception
lblMsg.Visible = True
lblMsg.Text = ex.ToString
End Try
End Sub
Private Sub importToDG(ByVal fileStream As Stream)
Dim streamRdr As StreamReader
Dim fileLines As String()
Dim line As String
streamRdr = New StreamReader(fileStream)
Dim myDTable As DataTable = New DataTable
Dim myDRow As DataRow = myDTable.NewRow
'Dim ItemName, PhoneNo As New DataColumn
'ItemName.ColumnName = "Item Name"
'PhoneNo.ColumnName = "PhoneNo"
'EmailID.ColumnName = "EmailID"
'Address.ColumnName = "Address"
'City.ColumnName = "City"
'State.ColumnName = "State"
'Country.ColumnName = "Country"
'CompanyName.ColumnName = "CompanyName"
'PhoneNo1.ColumnName = "PhoneNo1"
'PhoneNo2.ColumnName = "PhoneNo2"
'PhoneNo3.ColumnName = "PhoneNo3"
'PhoneNo4.ColumnName = "PhoneNo4"
'myDTable.Columns.Add(ItemName)
'myDTable.Columns.Add(PhoneNo)
'myDTable.Columns.Add(EmailID)
'myDTable.Columns.Add(Address)
'myDTable.Columns.Add(City)
'myDTable.Columns.Add(State)
'myDTable.Columns.Add(Country)
'myDTable.Columns.Add(CompanyName)
'myDTable.Columns.Add(PhoneNo1)
'myDTable.Columns.Add(PhoneNo2)
'myDTable.Columns.Add(PhoneNo3)
'myDTable.Columns.Add(PhoneNo4)
'Now set the streamreader back to beginning.
streamRdr.DiscardBufferedData()
streamRdr.BaseStream.Seek(0, SeekOrigin.Begin)
streamRdr.BaseStream.Position = 0
Dim i As Integer = 0 'Skip the first row.
While Not streamRdr.EndOfStream
line = streamRdr.ReadLine 'Read first line
fileLines = line.Split(",") 'Split the line up by the delimeter
If i = 0 Then
For j As Integer = 0 To fileLines.Length - 1
lstExcelFields.Items.Add(fileLines(j))
myDTable.Columns.Add(New DataColumn(fileLines(j)))
Next
End If
If i > 0 Then
myDRow.ItemArray = fileLines
myDTable.Rows.Add(myDRow)
myDRow = myDTable.NewRow
'Else
' myDRow.ItemArray = fileLines
' myDTable.Rows.Add(myDRow)
' myDTable.Columns.Add(fileLines(0))
' myDTable.Columns.Add(fileLines(1))
' myDTable.Columns.Add(fileLines(2))
End If
i += 1
End While
GridView1.DataSource = myDTable
GridView1.DataBind()
End Sub
Public Sub GetData()
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & str & ";Extended Properties=Excel 12.0"
' Create the connection object
Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
Try
' Open connection
oledbConn.Open()
' Create OleDbCommand object and select data from worksheet Sheet3
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
' Create new OleDbDataAdapter
Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()
oleda.SelectCommand = cmd
' Create a DataSet which will hold the data extracted from the worksheet.
Dim ds As DataSet = New DataSet()
' Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds, "Sheet1")
' Bind the data to the GridView
GridView1.DataSource = ds.Tables(0).DefaultView
GridView1.DataBind()
Catch ex As Exception
lblMsg.Visible = True
lblMsg.Text = ex.Message
Finally
' Close connection
oledbConn.Close()
End Try
End Sub
Protected Sub btnSelectAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
Try
Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
For j As Integer = 0 To GridView1.Rows.Count - 1
GridView1.SelectedIndex = j
checkbox1 = GridView1.SelectedRow.FindControl("checkbox1")
checkbox1.Checked = True
Next
Catch ex As Exception
lblMsg.Text = ex.ToString
lblMsg.Visible = True
End Try
End Sub
Protected Sub btnSaveInDb_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveInDb.Click
If ViewState.Count < 1 Then
lblMsg.Text = "Please Map the Item Name field"
Return
End If
Try
Dim uploaditems As Integer = 0
Dim existsitems As Integer = 0
Dim selecteditems As Integer = 0
Dim skipeditems As Integer = 0
Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
For j As Integer = 0 To GridView1.Rows.Count - 1
'GridView1.SelectedIndex = j
checkbox1 = GridView1.Rows(j).FindControl("checkbox1")
'GridView1.Rows(j).FindControl("checkbox1").
If checkbox1.Checked Then
selecteditems += 1
Dim str As String = GridView1.Rows(j).Cells(ViewState("str1")).Text
If GridView1.Rows(j).Cells(ViewState("str1")).Text.Length > 3 And str <> " " Then
Dim len As Integer = GridView1.Rows(j).Cells(ViewState("str1")).Text.Length
If clsItems.AddItem(GridView1.Rows(j).Cells(ViewState("str1")).Text) = 1 Then
uploaditems += 1
Else
existsitems += 1
End If
Else
skipeditems += 1
End If
End If
Next
lblMsg.Text = selecteditems & " Items Selected, " & uploaditems & " items Uploaded," & existsitems & " Items existed, " & skipeditems & " Items skipped"
Catch ex As Exception
lblMsg.Text = ex.ToString
lblMsg.Visible = True
End Try
End Sub
Protected Sub btnDeSelectAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDeSelectAll.Click
Try
Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
For j As Integer = 0 To GridView1.Rows.Count - 1
GridView1.SelectedIndex = j
checkbox1 = GridView1.SelectedRow.FindControl("checkbox1")
checkbox1.Checked = False
Next
Catch ex As Exception
lblMsg.Text = ex.ToString
lblMsg.Visible = True
End Try
End Sub
Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
If lstDBFields.SelectedIndex = 0 Then
txtName.Text = lstExcelFields.SelectedItem.Text & " --> " & lstDBFields.SelectedItem.Text
Session.Add("strj1", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'Session("str1") = lstExcelFields.SelectedIndex + 1
ViewState("str1") = lstExcelFields.SelectedIndex + 1
End If
'If lstDBFields.SelectedIndex = 2 Then
' txtPhoneNo.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj2", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 3 Then
' txtEmailId.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj3", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 4 Then
' txtAddress.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj4", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 5 Then
' txtCity.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj5", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 6 Then
' txtState.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj6", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 7 Then
' txtCountry.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj7", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 8 Then
' txtCompanyName.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj8", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 9 Then
' txtPhoneNo1.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj9", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 10 Then
' txtPhoneNo2.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj10", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 11 Then
' txtPhoneNo3.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj11", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
'If lstDBFields.SelectedIndex = 12 Then
' txtPhoneNo4.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
' Session.Add("strj12", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
'End If
End Sub
Public Sub SaveUploadedFile()
If FileUpload1.HasFile Then
FileUpload1.SaveAs((Server.MapPath(".\Uploads") & "\") & FileUpload1.FileName)
str = Server.MapPath(".\Uploads") & "\" & FileUpload1.FileName
End If
End Sub
End Class
Comments
Post a Comment