将记录插入excel工作表而不硬编码列名，应插入结果集中的所有列-OLEDB [英] insert the record into excel sheet without hardcoding the column name , All column from the result set should be inserted - OLEDB
问题描述
请仍然我在寻找合适的解决方案，以在服务器上为C#控制台应用程序创建Excel工作表.该服务器没有MS Office应用程序.我在VB中有一些下面给出的代码我也想转换成C# 你能建议我如何在不对列名进行硬编码的情况下插入excel工作表吗?
Please still I am looking for the proper solution to create the excel sheet in the server for a c# console application . The server doesnot have MS office application. I have some code as given below in VB and I would like to convert into C# also please can you advise me how to insert into excel sheet without hardcoding the column name
导入 System.Data.OleDb
Imports System.Data.OleDb
导入 System.Data
Imports System.Data
公共 Class Form1
Public Class Form1
' ~~>创建按钮
私有 Sub Button1_Click(发送者为 对象，例如: EventArgs)句柄 Button1.点击
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
; Dim olecon As OleDbConnection = New OleDbConnection
Dim olecon As OleDbConnection = New OleDbConnection
; Dim olecmd As OleDbCommand = New OleDbCommand
Dim olecmd As OleDbCommand = New OleDbCommand
; Dim FilePath As 字符串 ="C:\ Temp \";
Dim FilePath As String = "C:\Temp\"
; Dim FileName As 字符串 ="EmployeeDatabase.xlsx"
Dim FileName As String = "EmployeeDatabase.xlsx"
; '~~>构造您的连接字符串
; Dim connstring As 字符串 ="Provider = Microsoft.ACE.OLEDB.12.0;" &
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
; nbsp; b ; "数据 Source =< &文件路径和文件名和 &
"Data Source=" & FilePath & FileName & ";" &
; nbsp; b ; "扩展 属性=" Excel 12.0 Xml； HDR =是；"
"Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
; olecon.ConnectionString = connstring
; olecon.Open()
; olecmd.Connection = olecon
; '~~>创建表的命令
; olecmd.CommandText =从员工A中选择A.Code，A.name，其中A.Hidden ='N'"
; olecmd.ExecuteNonQuery()
; '~~>如何添加 所有记录，包括没有定义的标题 列名.列标题不是固定的.所有列和记录都应插入
'~~> how to add All record including header without define the column name . The column heading is not fixed. All the column and record should be inserted
; olecmd.CommandText ="INSERT INTO Sheet1(来自查询的所有记录)"
; olecmd.ExecuteNonQuery()
; '~~>关闭连接
; olecon.Close()
; '~~>通知用户
; MessageBox.Show("文件 &文件名和"已成功创建，并已放置在" & FilePath)
MessageBox.Show("The file " & FileName & " has been created successfully and has been placed in " & FilePath)
结束 Sub
End Sub
' ~~>关闭按钮
私有 Sub Button2_Click(发送者为 对象，例如: EventArgs)句柄 Button2.点击
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
; Me.Close()
; End Sub
End Sub
End Class
End Class
请提出任何建议
谢谢
Pol
Pol
polachan
推荐答案
嗨polachan，
Hi polachan,
谢谢您在这里发布.
这是C#的代码.
private void button1_Click(object sender, EventArgs e)
{
OleDbConnection olecon = new OleDbConnection();
OleDbCommand olecmd = new OleDbCommand();
string FilePath = @"C:\Temp\";
string FileName = "EmployeeDatabase.xlsx";
//Construct your connection string
string connstring = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + FilePath + FileName + ";Extended Properties=Excel 12.0 Xml;HDR=YES";
olecon.ConnectionString = connstring;
olecon.Open();
olecmd.Connection = olecon;
// Command to create the table
olecmd.CommandText = "Select A.Code,A.name from employee A Where A.Hidden =’N’";
olecmd.ExecuteNonQuery();
//how to add All record including header without define the column name . The column heading is not fixed. All the column and record should be inserted
olecmd.CommandText = "INSERT INTO Sheet1 ( All the record from the query)";
olecmd.ExecuteNonQuery();
// Close the connection
olecon.Close();
// Inform User
MessageBox.Show("The file " + FileName + " has been created successfully and has been placed in " + FilePath + ".");
}
private void button2_Click(object sender, EventArgs e)
{
Me.Close();//I do not know the Me in your code, hence I do not do the change for Me.
}>> 如何添加
>>how to add All record including header without define the column name . The column heading is not fixed. All the column and record should be inserted
我编写了一个简单的代码来尝试.但是我不能在没有定义列名的情况下插入.
I make a simple code to try. But I could not insert without define the column name.
您可以更改"HDR = No".并在插入时尝试以下代码.
You could change the "HDR=No". And try the following code when you insert.
olecmd.CommandText = "Insert into [Sheet1
(id，name)values('5'，'e')'";
(id,name) values('5','e')";
或者您可以c 在excel中创建新工作表.这是用于创建名为Sheet6的新工作表并将数据插入其中的示例代码.
string commandTxt = "CREATE TABLE Sheet6(Id char(255), Name char(255), BirthDate date)";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand(commandTxt ,connection);
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Sheet6 (Id, Name, BirthDate) values (‘AAA’, ‘Andrew’, ’12/4/1955′)";
command.ExecuteNonQuery();
connection.Close();我在Blod中可以使用的字符串替换位置.它是动态的.您可以输入所需的牧民.
For more details, please refer to the link.
我希望这会对您有所帮助.
I hope this would be helpful to you.
如果还有其他问题，请随时与我们联系.
If you have something else, please feel free to contact us.
最好的问候，
温迪
这篇关于将记录插入excel工作表而不硬编码列名，应插入结果集中的所有列-OLEDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
