上传excel到VB.net？ [英] Upload excel to VB.net?

问题描述

我正在使用Microsoft Visual Studio 2005



如何将excel文件上传到Visual Basic中以显示其内容？



然后如何在sql上传它？



没有oledb的东西？

I'm using Microsoft Visual Studio 2005

How do I upload an excel file into Visual Basic that can display it's content?

then how do I upload it in sql?

something without oledb?

推荐答案

请阅读：从.NET应用程序访问Microsoft Office数据 [< a href =http://msdn.microsoft.com/en-us/library/ms971514.aspxtarget =_ blanktitle =New Window> ^ ]并且：如何使用ADO.NET在Visual Basic .NET中检索和修改Excel工作簿中的记录 [ ^ ]。现在，您应该知道您需要做的就是使用ADO.NET。



步骤：

1）创建 OleDbConnection [ ^ ]并使用正确的ConnectionString [ ^ ]。

2）创建 OleDbCommand [ ^ ]并将数据加载到 OleDbDataReader [ ^ ]

3）创建< a href =http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection%28v=vs.110%29.aspx> SqlConnection [ ^ ]并使用正确的 ConnectionString 连接到SQL Server实例[ ^ ]

4）使用 SqlBulkCopy [ ^ ]类将数据（从OleDbDataReader）写入SQL服务器。

5）关闭已打开的连接



这就是全部。试试！

Please, read this: Accessing Microsoft Office Data from .NET Applications[^] and this: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^]. Now, you should know that all you need to do is to use ADO.NET.

Steps to do:
1) Create OleDbConnection[^] and connect to MS Excel file using proper ConnectionString[^].
2) Create OleDbCommand[^] and load data into OleDbDataReader[^]
3) Create SqlConnection[^] and connect to the SQL Server instance using proper ConnectionString[^]
4) Use SqlBulkCopy[^] class to write data (from OleDbDataReader) into SQL Server.
5) Close opened connections

That's all. Try!

两个步骤或解决方案

1.将excel导入datagridview

2.将网格导入数据库



或



导入excel两个..



第一个看起来更好，因为你可以用excel加载网格，然后你可以手动修改这个..

之后你可以将它保存到数据库。



以下显示了如何使用excel数据加载网格。



dgvdetails是datagridview

Two steps or solutions
1. Import excel to datagridview
2. import grid to database

or

Import excel in both..

First one looks better because you can load grid with excel then you can manually able to do correction in this..
After that you can save that to database.

Following shows how to load grid with excel data.

dgvdetails is datagridview

Private Sub Import_To_Grid()
    Dim FilePath As String = "C:\Item Master-April2013.xlsx"
    Dim conStr As String = ""
    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    conStr = String.Format(conStr, FilePath)
    Dim connExcel As New OleDbConnection
    connExcel.ConnectionString = conStr
    Dim cmdExcel As New OleDbCommand()
    Dim oda As New OleDbDataAdapter()
    cmdExcel.Connection = connExcel

    'Get the name of First Sheet
    connExcel.Open()
    Dim Exceldt As New DataTable()
    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(Exceldt)
    connExcel.Close()

    'Bind Data to GridView
    dgvDetails.DataSource = Exceldt
    'BindDataToCmbClass() 'binddata to class for filter
    'cmb_userclass.SelectedIndex = 0
End Sub

以下显示如何保存网格数据特定的列和条件

r作为整数

sdcmd作为sqlcommand与您的查询和连接。我相信，至少你知道这一点。



因为我不知道你的列.. InvoiceNo，SrNo，ProductID，参数，数量是列。 />

Following shows how to save data of grid with specific columns and conditions
r as integer
sdcmd as sqlcommand with your query and connection. I Believe, at least you know this.

As i don't know your columns.. InvoiceNo,SrNo,ProductID,Parameters,Quantity are the columns.

            For r = 0 To dgvDetails.RowCount - 1
                If Val(dgvDetails.Rows(r).Cells("SrNo").Value) <> 0 Then
                    sdCmd.Parameters.Clear()
                    sdCmd.Parameters.Add("@InvoiceNo", OleDb.OleDbType.WChar).Value = r
                    sdCmd.Parameters.Add("@SrNo", OleDb.OleDbType.Numeric).Value = dgvDetails.Rows(r).Cells("SrNo").Value
                    sdCmd.Parameters.Add("@ProductID", OleDb.OleDbType.WChar).Value = dgvDetails.Rows(r).Cells("ProductID").Value
                    sdCmd.Parameters.Add("@Particulars", OleDb.OleDbType.WChar).Value = dgvDetails.Rows(r).Cells("Particulars").Value
                    sdCmd.Parameters.Add("@Quantity", OleDb.OleDbType.Currency).Value = dgvDetails.Rows(r).Cells("Quantity").Value
                End If
sdCmd.ExecuteNonQuery()
            Next

这篇关于上传excel到VB.net？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！