import excel vb.net

 Import Excel using vb.net  
 --------------------------  
 step 1):  
 ---------  
 <connectionStrings>  
  <add name ="Excel03ConString"  
     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};  
              Extended Properties='Excel 8.0;HDR={1}'"/>  
  <add name ="Excel07ConString"  
     connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};  
              Extended Properties='Excel 8.0;HDR={1}'"/>  
 </connectionStrings>  
 step 2):  
 ---------  
 <asp:FileUpload ID="FileUpload1" runat="server" />  
 <asp:Button ID="btnUpload" runat="server" Text="Upload"  
       OnClick="btnUpload_Click" />  
 <br />  
 <asp:Label ID="Label1" runat="server" Text="Has Header ?" />  
 <asp:RadioButtonList ID="rbHDR" runat="server">  
   <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >  
   </asp:ListItem>  
   <asp:ListItem Text = "No" Value = "No"></asp:ListItem>  
 </asp:RadioButtonList>  
 <asp:GridView ID="GridView1" runat="server"  
 OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true">  
 </asp:GridView>  
 step 3):  
 -----------  
 inapp setting put it this  
 <appSettings>  
  <add key ="FolderPath" value ="Files/"/>  
 </appSettings >  
 step 4):  
 --------  
 Once the File is saved in the folder the Import_To_Grid method is called up which is described later.   
 Below is the code snippet for the Upload button event handle  
 Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)  
  If FileUpload1.HasFile Then  
    Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)  
    Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)  
    Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")  
    Dim FilePath As String = Server.MapPath(FolderPath + FileName)  
    FileUpload1.SaveAs(FilePath)  
    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)  
  End If  
 End Sub  
 step 5):  
 --------  
 Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)  
  Dim conStr As String = ""  
  Select Case Extension  
    Case ".xls"  
       'Excel 97-03  
       conStr = ConfigurationManager.ConnectionStrings("Excel03ConString") _  
            .ConnectionString  
       Exit Select  
    Case ".xlsx"  
       'Excel 07  
       conStr = ConfigurationManager.ConnectionStrings("Excel07ConString") _  
            .ConnectionString  
       Exit Select  
  End Select  
  conStr = String.Format(conStr, FilePath, isHDR)  
  Dim connExcel As New OleDbConnection(conStr)  
  Dim cmdExcel As New OleDbCommand()  
  Dim oda As New OleDbDataAdapter()  
  Dim dt As New DataTable()  
  cmdExcel.Connection = connExcel  
  'Get the name of First Sheet  
  connExcel.Open()  
  Dim dtExcelSchema As DataTable  
  dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)  
  Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()  
  connExcel.Close()  
  'Read Data from First Sheet  
   connExcel.Open()  
   cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"  
   oda.SelectCommand = cmdExcel  
   oda.Fill(dt)  
   connExcel.Close()  
   'Bind Data to GridView  
   GridView1.Caption = Path.GetFileName(FilePath)  
   GridView1.DataSource = dt  
   GridView1.DataBind()  
 End Sub  
 last step  
 -------------:)  
 Pagination in GridView  
 Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)  
   Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")  
   Dim FileName As String = GridView1.Caption  
   Dim Extension As String = Path.GetExtension(FileName)  
   Dim FilePath As String = Server.MapPath(FolderPath + FileName)  
   Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)  
   GridView1.PageIndex = e.NewPageIndex  
   GridView1.DataBind()  
 End Sub  
 happy coding   
 for more coding pages visit http://scriptquery.blogspot.in/   

3:51 AM Share: