使用VB .NET更新excel文件 [英] Update excel file using VB .NET

问题描述

我试图更新excel文件中的列。

目前我的代码只更新一个时间。

我想一次更新很多列。



当我尝试运行时，我在UPDATE语句中出现语法错误





有人能帮帮我吗？



我是什么尝试过：

Hi,

I trying to update column in excel file.
At the moment I have code which update only one at the time.
I would like to update many column at one time.

When I try to run this I have Syntax error in UPDATE Statement


Could anyone help me ?

What I have tried:

'Create the connection string to connect to the Microsoft Excel Workbook
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FORM\FORM.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""
Dim updateStatement As String = "UPDATE [DATA$] SET LINE = '" + NAMEASSOCIATEINPUT.Text + "',TIME = '" + NAMEASSOCIATEINPUT.Text + "'"

'Create a connection object to connect to the Excel Workbook
Dim connection As New OleDbConnection(connectionString)
'Create a command object that will execute the update statement
Dim command As New OleDbCommand(updateStatement, connection)
'Open the connection, execute the statement and close the connection
connection.Open()
command.ExecuteNonQuery()
connection.Close()
'Dispose of the connection and command objects
connection.Dispose()
command.Dispose()
'Call the RefreshData routine so that you can see that data was indeed updated.

推荐答案

SET LINE =' + NAMEASSOCIATEINPUT.Text + '，TIME =' + NAMEASSOCIATEINPUT.Text + '

' 创建连接到Excel工作簿的连接对象
Dim connection A s 新 OleDbConnection（connectionString）
' 创建一个将执行更新语句的命令对象
Dim 命令 As 新 OleDbCommand（updateStatement，connection）
' 打开连接，执行语句并关闭连接
connection.Open（）
command.ExecuteNonQuery（）
connection.Close（）
' 处理连接和命令对象
connection.Dispose（）
command.Dispose （）
' 调用RefreshData例程，以便您可以看到数据确实已更新。

SET LINE = '" + NAMEASSOCIATEINPUT.Text + "',TIME = '" + NAMEASSOCIATEINPUT.Text + "'" 'Create a connection object to connect to the Excel Workbook Dim connection As New OleDbConnection(connectionString) 'Create a command object that will execute the update statement Dim command As New OleDbCommand(updateStatement, connection) 'Open the connection, execute the statement and close the connection connection.Open() command.ExecuteNonQuery() connection.Close() 'Dispose of the connection and command objects connection.Dispose() command.Dispose() 'Call the RefreshData routine so that you can see that data was indeed updated.

您的代码是SQL注入nerable。请阅读我的过去的答案 [ ^ ]至得到更多细节。



您的连接字符串错误：

Your code is SQL injection vulnerable. Please read my past answer[^] to get more details.

Your connection string is wrong:

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FORM\FORM.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""

因为文件的路径不完整！您必须提供完整的文件名，包括完整路径。



请阅读此处提供的注释： Microsoft ACE OLEDB 12.0连接字符串 - ConnectionStrings.com [ ^ ]

这篇关于使用VB .NET更新excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！