将Excel Sheet上传到数据库vb.net，sql server [英] Upload Excel Sheet into database vb.net, sql server

问题描述

大家好，



我正在使用VB.net桌面应用程序。我想将Excel工作表上传到数据库（sql server），并在Gridview中显示数据。

是否有任何IO属性？



如果有，请给我解决方案。



先谢谢。

解决方案

首先你需要将excel数据存储在datatable中，

你可以将数据存储在datatable中作为



string constring =Provider = Microsoft.Jet.OLEDB.4.0 ;数据源= C：\ MySpreadsheet.XLS;扩展属性=Excel 8.0; HDR =是; IMEX = 2

datatable dt = new datatable（）;

Oledbconnection con = new oledbconnection（constring）;

oledbdataadapter da = new oledbdataadapter（select * from sheet1，con）

da.fill（dt） ;

然后

使用for / while循环将数据表数据存储到sql中。





或

看到这些链接你可能会有所了解

http://www.daniweb.com/software-development/vbnet/threads/385368/how-to-import-exel -file-to-sql-server-using-vb.net [ ^ ]



http：// www。 aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx [ ^ ]

您可以直接显示来自Excel的数据：



http://vb.net-informations.com/datagridview/vb.net_datagridview_import.htm [<一个href =http://vb.net-informations.com/datagridview/vb.net_datagridview_import.htmtarget =_ blanktitle =New Window> ^ ]



将数据从excel导入到sql server：



将数据从Excel导入到SQL Server [ ^ ]



显示来自sql server的数据gridview：



http： //www.aspnettutorials.com/tutorials/database/db-grid-aspnet2-vb.aspx [ ^ ]

 私有  Sub  browseXLfile_Click（sender  As   Object ，e  As  EventArgs）句柄 browseXLfile.Click 
  Dim  ofd  As  新 OpenFileDialog 
 
 如果 ofd.ShowDialog（）<> System.Windows.Forms.DialogResult.OK 然后 退出  Sub  
 
  Dim  nme  As   String  = ofd.FileName 
  Dim  safename  As  字符串 = ofd.SafeFileName 
 safename = safename.Substring（ 0 ，safename.LastIndexOf（ 。））
导入（nme，dgv，safename）
 结束  Sub  
 
 公共 共享 功能导入（ ByVal  FileName  As  字符串， ByVal  dgv 作为 DataGridView， ByVal  safefilename 作为 字符串）作为 布尔 
 尝试 
 
 
  Dim  MyConnection  As  System.Data.OleDb.OleDbConnection 
  Dim  DtSet  As  System.Data.DataSet 
  Dim  MyCommand  As  System.Data.OleDb.OleDbDataAdapter 
 MyConnection = 新 System.Data.OleDb .OleDbConnection（  provider = Microsoft.Jet.OLEDB.4.0; Data Sour ce = + FileName +  ;扩展属性= Excel 8.0;）
 '  MyConnection = New System.Data.OleDb.OleDbConnection（provider = Microsoft.Jet.OLEDB.4.0; Data Source ='c：\ vb.net-informations.xls';扩展属性= Excel 8.0;） 
 MyCommand = 新系统.Data.OleDb.OleDbDataAdapter（  select * from [Sheet1 

Hi all,

I am working on VB.net Desktop Application. I want to upload Excel sheet to Database(sql server),And Display data in Gridview.
Is there any IO property for that?

Please give me solution if You have.

Thanks in Advance.

解决方案

first you have to store excel data in datatable,
you can store data in datatable as

string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
datatable dt=new datatable();
Oledbconnection con=new oledbconnection(constring);
oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
da.fill(dt);
then
store datatable data into sql using for/while loop.


or
see these links you may get some idea
http://www.daniweb.com/software-development/vbnet/threads/385368/how-to-import-exel-file-to-sql-server-using-vb.net[^]

http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx[^]

You can directly show data from excel:

http://vb.net-informations.com/datagridview/vb.net_datagridview_import.htm[^]

Import data from excel to sql server:

Import Data from Excel to SQL Server[^]

Display data from sql server in gridview:

http://www.aspnettutorials.com/tutorials/database/db-grid-aspnet2-vb.aspx[^]

 Private Sub browseXLfile_Click(sender As Object, e As EventArgs) Handles browseXLfile.Click
        Dim ofd As New OpenFileDialog

        If ofd.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then Exit Sub

        Dim nme As String = ofd.FileName
        Dim safename As String = ofd.SafeFileName
        safename = safename.Substring(0, safename.LastIndexOf("."))
        Import(nme, dgv, safename)
    End Sub

Public Shared Function Import(ByVal FileName As String, ByVal dgv As DataGridView, ByVal safefilename As String) As Boolean
        Try


            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;")
            'MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1

这篇关于将Excel Sheet上传到数据库vb.net，sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！