titrajh
چهارشنبه 02 آبان 1397, 12:50 عصر
سلام
ببخشید اگه جای اشتباهی تاپیک میزنم
من یه برنامه از دوستانی که تو همین انجمن ارائه کردن برداشتم که باهاش کارکنم
اونم تبدیل اکسل به دیتای Sql هست ولی هنگام اجرا
خطای Incorrect syntax near '/' رو میده نمیدونم چیکارش کنم
لطفا راهنمایی بفرمائید دوستان
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
Imports System.Text
Imports System.Drawing
Imports System.Data
Imports System.ComponentModel
Imports System.Collections.Generic
Imports System
Public Class Form1
Dim TotalSheets As Integer, TotalCols As Integer, TotalRows As Integer
Public Sub New()
InitializeComponent()
End Sub
Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
SelectExcelFile()
End Sub
Private Sub SelectExcelFile()
Dim OFD As New OpenFileDialog()
Dim ExApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
cboSheet.Items.Clear()
txtAddress.Text = String.Empty
OFD.Filter = "Excel Files (*.xlsx)|*.xlsx|All Files(*.*)|*.*"
OFD.InitialDirectory = "c:"
OFD.FileName = "Select File"
OFD.Title = "Select excel file.."
If OFD.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
Dim exFile As String = OFD.FileName
txtAddress.Text = exFile
Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCultu re
System.Threading.Thread.CurrentThread.CurrentCultu re = New System.Globalization.CultureInfo("en-US")
Dim MyWorkbook As Excel.Workbook = ExApp.Workbooks.Open(exFile, 0, False, 5, "", "", _
False, Excel.XlPlatform.xlWindows, "", True, False, 0, _
True, False, False)
TotalSheets = MyWorkbook.Worksheets.Count
For Each worksheet As Excel.Worksheet In MyWorkbook.Worksheets
cboSheet.Items.Add(worksheet.Name)
Next
MyWorkbook.Close(False, False, False)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
End Sub
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
If (txtAddress.Text <> String.Empty) AndAlso (cboSheet.SelectedIndex <> -1) AndAlso (txtTable.Text <> String.Empty) Then
ExportData(txtTable.Text)
End If
End Sub
Private Sub ExportData(ByVal TableName As String)
Dim query As String = Nothing
Dim dt As New DataTable()
dt = GetData(txtAddress.Text, cboSheet.SelectedItem.ToString())
Try
Dim con As New SqlConnection("Data Source=AMK-PC\SQLEXPRESS;Initial Catalog=Anb;Persist Security Info=True;Integrated Security=true")
Dim cmd As New SqlCommand()
cmd.Connection = con
'ساخت جدول مورد نظر در اسکیوال
query = "create table [" + TableName + "](" + dt.Columns(0).ToString().Replace(" ", "_") + " varchar(200)"
Dim i As Integer = 1
While i < TotalCols
Dim ActualField As String = dt.Columns(i).ToString()
Dim FinalField As String = ActualField.Replace(" ", "_")
query &= "," + FinalField + " varchar(200)"
i += 1
End While
query &= ")"
cmd.CommandText = query
con.Open()
cmd.ExecuteNonQuery()
con.Close()
'کپی اطلاعات جدول انتخاب شده
query = String.Empty
con.Open()
Dim ins As Integer = 0
While ins < TotalRows
query = "insert into [" + TableName + "] values ('" + dt.Rows(ins)(0)
Dim co As Integer = 1
While co < TotalCols
query &= "','" + dt.Rows(ins)(co).ToString()
co += 1
End While
query &= "')"
cmd.CommandText = query
cmd.ExecuteNonQuery()
ins += 1
End While
con.Close()
MessageBox.Show("انتقال رکورد ها با موفقیت انجام شد")
Catch ex As Exception
MessageBox.Show("خطا:" + ex.Message)
End Try
End Sub
Private Function GetData(ByVal ExcelFile As String, ByVal SheetName As String) As DataTable
Dim constr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0"
Dim olecmd As String = "select * from [" + SheetName + "$]"
Dim con As New OleDbConnection(constr)
Dim cmd As New OleDbCommand(olecmd, con)
Dim da As New OleDbDataAdapter()
Dim dt As New DataTable()
con.Open()
da.SelectCommand = cmd
da.Fill(dt)
TotalCols = dt.Columns.Count
TotalRows = dt.Rows.Count
con.Close()
Return dt
End Function
End Class
ببخشید اگه جای اشتباهی تاپیک میزنم
من یه برنامه از دوستانی که تو همین انجمن ارائه کردن برداشتم که باهاش کارکنم
اونم تبدیل اکسل به دیتای Sql هست ولی هنگام اجرا
خطای Incorrect syntax near '/' رو میده نمیدونم چیکارش کنم
لطفا راهنمایی بفرمائید دوستان
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
Imports System.Text
Imports System.Drawing
Imports System.Data
Imports System.ComponentModel
Imports System.Collections.Generic
Imports System
Public Class Form1
Dim TotalSheets As Integer, TotalCols As Integer, TotalRows As Integer
Public Sub New()
InitializeComponent()
End Sub
Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
SelectExcelFile()
End Sub
Private Sub SelectExcelFile()
Dim OFD As New OpenFileDialog()
Dim ExApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
cboSheet.Items.Clear()
txtAddress.Text = String.Empty
OFD.Filter = "Excel Files (*.xlsx)|*.xlsx|All Files(*.*)|*.*"
OFD.InitialDirectory = "c:"
OFD.FileName = "Select File"
OFD.Title = "Select excel file.."
If OFD.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
Dim exFile As String = OFD.FileName
txtAddress.Text = exFile
Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCultu re
System.Threading.Thread.CurrentThread.CurrentCultu re = New System.Globalization.CultureInfo("en-US")
Dim MyWorkbook As Excel.Workbook = ExApp.Workbooks.Open(exFile, 0, False, 5, "", "", _
False, Excel.XlPlatform.xlWindows, "", True, False, 0, _
True, False, False)
TotalSheets = MyWorkbook.Worksheets.Count
For Each worksheet As Excel.Worksheet In MyWorkbook.Worksheets
cboSheet.Items.Add(worksheet.Name)
Next
MyWorkbook.Close(False, False, False)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
End Sub
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
If (txtAddress.Text <> String.Empty) AndAlso (cboSheet.SelectedIndex <> -1) AndAlso (txtTable.Text <> String.Empty) Then
ExportData(txtTable.Text)
End If
End Sub
Private Sub ExportData(ByVal TableName As String)
Dim query As String = Nothing
Dim dt As New DataTable()
dt = GetData(txtAddress.Text, cboSheet.SelectedItem.ToString())
Try
Dim con As New SqlConnection("Data Source=AMK-PC\SQLEXPRESS;Initial Catalog=Anb;Persist Security Info=True;Integrated Security=true")
Dim cmd As New SqlCommand()
cmd.Connection = con
'ساخت جدول مورد نظر در اسکیوال
query = "create table [" + TableName + "](" + dt.Columns(0).ToString().Replace(" ", "_") + " varchar(200)"
Dim i As Integer = 1
While i < TotalCols
Dim ActualField As String = dt.Columns(i).ToString()
Dim FinalField As String = ActualField.Replace(" ", "_")
query &= "," + FinalField + " varchar(200)"
i += 1
End While
query &= ")"
cmd.CommandText = query
con.Open()
cmd.ExecuteNonQuery()
con.Close()
'کپی اطلاعات جدول انتخاب شده
query = String.Empty
con.Open()
Dim ins As Integer = 0
While ins < TotalRows
query = "insert into [" + TableName + "] values ('" + dt.Rows(ins)(0)
Dim co As Integer = 1
While co < TotalCols
query &= "','" + dt.Rows(ins)(co).ToString()
co += 1
End While
query &= "')"
cmd.CommandText = query
cmd.ExecuteNonQuery()
ins += 1
End While
con.Close()
MessageBox.Show("انتقال رکورد ها با موفقیت انجام شد")
Catch ex As Exception
MessageBox.Show("خطا:" + ex.Message)
End Try
End Sub
Private Function GetData(ByVal ExcelFile As String, ByVal SheetName As String) As DataTable
Dim constr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0"
Dim olecmd As String = "select * from [" + SheetName + "$]"
Dim con As New OleDbConnection(constr)
Dim cmd As New OleDbCommand(olecmd, con)
Dim da As New OleDbDataAdapter()
Dim dt As New DataTable()
con.Open()
da.SelectCommand = cmd
da.Fill(dt)
TotalCols = dt.Columns.Count
TotalRows = dt.Rows.Count
con.Close()
Return dt
End Function
End Class