以下是我以前百度找的資料 希望對(duì)你有用 你讀取DataGridView到DataGrid然后直接調(diào)用函數(shù)即可
成都創(chuàng)新互聯(lián)公司專(zhuān)注于惠來(lái)網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠(chéng)為您提供惠來(lái)營(yíng)銷(xiāo)型網(wǎng)站建設(shè)，惠來(lái)網(wǎng)站制作、惠來(lái)網(wǎng)頁(yè)設(shè)計(jì)、惠來(lái)網(wǎng)站官網(wǎng)定制、微信小程序服務(wù)，打造惠來(lái)網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供惠來(lái)網(wǎng)站排名全網(wǎng)營(yíng)銷(xiāo)落地服務(wù)。
Public Function ExportXLsD(ByVal datagrid As DataGrid) ', ByVal Title As String)
'Dim Mytable As New DataTable
'Mytable = CType(datagrid.DataSource, DataTable)
If mytable Is Nothing Then
MessageBox.Show("沒(méi)有記錄不能導(dǎo)出數(shù)據(jù)", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Function
End If
If mytable.Rows.Count 0 Then
Dim MyFileName As String
Dim FileName As String
With SaveFileDialog1
.AddExtension = True '如果用戶忘記添加擴(kuò)展名，將自動(dòng)家上
.DefaultExt = "xls" '默認(rèn)擴(kuò)展名
.Filter = "Excel文件(*.xls)|*.xls"
.Title = "文件保存到"
If .ShowDialog = DialogResult.OK Then
FileName = .FileName
End If
End With
MyFileName = Microsoft.VisualBasic.Right(FileName, 4)
If MyFileName = "" Then
Exit Function
End If
If MyFileName = ".xls" Or MyFileName = ".XLS" Then
Dim FS As FileStream = New FileStream(FileName, FileMode.Create)
Dim sw As StreamWriter = New StreamWriter(FS, System.Text.Encoding.Default)
sw.WriteLine(vbTab FileName vbTab Date.Now)
Dim i, j As Integer
Dim str As String = ""
For i = 0 To mytable.Columns.Count - 1
str = mytable.Columns(i).Caption
sw.Write(str vbTab)
Next
sw.Write(vbCrLf)
For j = 0 To mytable.Rows.Count - 1
For i = 0 To mytable.Columns.Count - 1
Dim strColName, strRow As String
strRow = IIf(mytable.Rows(j).Item(i) Is DBNull.Value, "", mytable.Rows(j).Item(i))
sw.Write(strRow vbTab)
Next
sw.Write(vbLf)
Next
sw.Close()
FS.Close()
MessageBox.Show("數(shù)據(jù)導(dǎo)出成功！", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Exit Function
End If
Else
MessageBox.Show("沒(méi)有記錄不能導(dǎo)出數(shù)據(jù)", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Function
Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
Dim saveExcel As SaveFileDialog
saveExcel = New SaveFileDialog
saveExcel.Filter = "Excel文件(.xls)|*.xls"
Dim filename As String
If saveExcel.ShowDialog = Windows.Forms.DialogResult.Cancel Then Exit Sub
filename = saveExcel.FileName
Dim excel As Excel.Application
excel = New Excel.Application
excel.DisplayAlerts = False
excel.Workbooks.Add(True)
excel.Visible = False
Dim i As Integer
For i = 0 To DataGridView1.Columns.Count - 1
excel.Cells(1, i + 1) = DataGridView1.Columns(i).HeaderText
Next
'設(shè)置標(biāo)題
Dim j As Integer
For i = 0 To DataGridView1.Rows.Count - 1 '填充數(shù)據(jù)
For j = 0 To DataGridView1.Columns.Count - 1
excel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value
Next
Next
excel.Workbooks(1).SaveCopyAs(filename) '保存
Me.Close()
End Sub
我有現(xiàn)在的DLL，可以直接調(diào)用DLL的方法就解決這些問(wèn)題。要DLL嗎？我不想把源碼發(fā)出來(lái)。有點(diǎn)多。
一行一行的insert into到另一個(gè)數(shù)據(jù)庫(kù)就行了。一般不使用批量寫(xiě)入，字符串太長(zhǎng)
剛好寫(xiě)了個(gè)Helper類(lèi)，你試驗(yàn)一下DataTable2Exce(這個(gè)方法代碼如下：
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Public MustInherit Class ExcelHelper
Private Shared Function buildConnStr(excelFilePath As String) As String
Dim excelFileInfo As New System.IO.FileInfo(excelFilePath)
Dim constr As String
If excelFileInfo.Extension = ".xlsx" Then
constr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'", excelFilePath)
Else
constr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", excelFilePath)
End If
Return constr
End Function
'將datatable導(dǎo)入到excel
Public Shared Function DataTable2Excel(dt As DataTable, excelFilePath As String) As Boolean
If File.Exists(excelFilePath) Then
Throw New Exception("該文件已經(jīng)存在！")
End If
If dt.TableName.Trim.Length = 0 Or dt.TableName.ToLower = "table" Then
dt.TableName = "Sheet1"
End If
Dim colCount As Integer = dt.Columns.Count
Dim pa(colCount - 1) As OleDb.OleDbParameter
Dim tableStructStr As String = "Create Table " dt.TableName "("
Dim connString As String = buildConnStr(excelFilePath)
Dim objconn As New OleDbConnection(connString)
Dim objcmd As New OleDbCommand
objcmd.Connection = objconn
Dim dataTypeList As New ArrayList
dataTypeList.Add("System.Decimal")
dataTypeList.Add("System.Double")
dataTypeList.Add("System.Int16")
dataTypeList.Add("System.Int32")
dataTypeList.Add("System.Int64")
dataTypeList.Add("System.Single")
Dim i As Integer = 0
For Each col As DataColumn In dt.Columns
If dataTypeList.IndexOf(col.GetType.ToString) 0 Then
pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.Double)
objcmd.Parameters.Add(pa(i))
If i + 1 = colCount Then
tableStructStr += col.ColumnName + " double)"
Else
tableStructStr += col.ColumnName + " double,"
End If
Else
pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.VarChar)
objcmd.Parameters.Add(pa(i))
If i + 1 = colCount Then
tableStructStr += col.ColumnName + " VarChar)"
Else
tableStructStr += col.ColumnName + " VarChar,"
End If
End If
i += 1
Next
Try
objcmd.CommandText = tableStructStr
If objconn.State = ConnectionState.Closed Then objconn.Open()
objcmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
End Try
Dim InsertSql_1 As String = "Insert into " + dt.TableName + " ("
Dim InsertSql_2 As String = " Values ("
Dim InsertSql As String = ""
For colID As Integer = 0 To colCount - 1 Step 1
If colID + 1 = colCount Then
InsertSql_1 += dt.Columns(colID).ColumnName ")"
InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ")"
Else
InsertSql_1 += dt.Columns(colID).ColumnName + ","
InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ","
End If
Next
InsertSql = InsertSql_1 + InsertSql_2
For rowID As Integer = 0 To dt.Rows.Count - 1 Step 1
For colID = 0 To dt.Columns.Count - 1
If pa(colID).DbType = DbType.Double And dt.Rows(rowID)(colID).ToString.Trim = "" Then
pa(colID).Value = 0
Else
pa(colID).Value = dt.Rows(rowID)(colID).ToString.Trim
End If
Next
Try
objcmd.CommandText = InsertSql
objcmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
End Try
Next
Try
If objconn.State = ConnectionState.Open Then objconn.Close()
Catch exp As Exception
Throw exp
End Try
Return True
End Function
' 獲取Excel文件數(shù)據(jù)表列表Sheets
Public Shared Function GetExcelTables(ExcelFileName As String) As ArrayList
'Dim sheets As New List(Of String)
'conn.Open()
'Dim dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
'For Each r In dt.Rows
' sheets.Add(r("TABLE_NAME"))
'Next
'conn.Close()
'Return sheets
Dim dt As DataTable
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
Return Nothing
End If
Dim tableList As New ArrayList
Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))
Try
conn.Open()
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
Catch ex As Exception
Throw ex
End Try
For i As Integer = 0 To dt.Rows.Count - 1
Dim tableName As String = dt.Rows(i)(2).ToString.Trim.TrimEnd("$")
If tableList.IndexOf(tableName) 0 Then tableList.Add(tableName)
Next
End Using
Return tableList
End Function
'將Excel文件導(dǎo)出至DataTable(第一行作為表頭)
Public Shared Function InputFromExcel(ExcelFileName As String, TableName As String) As DataTable
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
End If
Dim tableList As ArrayList = GetExcelTables(ExcelFileName)
If tableList.IndexOf(TableName) 0 Then
TableName = tableList(0).ToString.Trim
End If
Dim dt As New DataTable
Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))
Dim cmd As New OleDbCommand("select * from [" TableName "$]", conn) '調(diào)試是否需要$
Dim adapter As New OleDbDataAdapter(cmd)
Try
If conn.State = ConnectionState.Closed Then conn.Open()
adapter.Fill(dt)
Catch ex As Exception
Throw ex
Finally
If conn.State = ConnectionState.Open Then conn.Close()
End Try
Return dt
End Function
'查詢excel文件中的一個(gè)數(shù)據(jù)
Public Shared Function ReadOneDataFromExcel(ExcelFileName As String, TableName As String, sql As String) As Object
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
End If
Dim tableList As ArrayList = GetExcelTables(ExcelFileName)
If tableList.IndexOf(TableName) 0 Then
TableName = tableList(0).ToString.Trim
End If
Dim dt As New DataTable
Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))
Dim cmd As New OleDbCommand(sql, conn) '調(diào)試是否需要$
Dim ret As Object
Try
If conn.State = ConnectionState.Closed Then conn.Open()
ret = cmd.ExecuteScalar()
Catch ex As Exception
Throw ex
Finally
If conn.State = ConnectionState.Open Then conn.Close()
End Try
Return ret
End Function
'獲取Excel文件指定數(shù)據(jù)表的數(shù)據(jù)列表columnNames
Public Shared Function GetExcelTableColumns(ExcelFileName As String, TableName As String) As ArrayList
Dim dt As DataTable
If Not File.Exists(ExcelFileName) Then
Throw New Exception("指定的Excel文件不存在")
Return Nothing
End If
Dim ColList As New ArrayList
Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))
Try
conn.Open()
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName, Nothing})
Catch ex As Exception
Throw ex
End Try
For i As Integer = 0 To dt.Rows.Count - 1
Dim ColName = dt.Rows(i)("Column_Name").ToString().Trim()
ColList.Add(ColName)
Next
End Using
Return ColList
End Function
End Class
重慶分公司
重慶分公司
