如何使用VB.NET将数据从SQL服务器导出到excel文件 [英] How to export data from SQL server to excel file using VB.NET

问题描述

我想将数据从数据库导出到Excel文件。这是我使用MS Access时的代码：

  Dim  My_Connection 作为 新 System.Data.OleDb.OleDbConnection 
  Dim  sql 作为 字符串 
  Dim  cmd  As  System.Data.OleDb.OleDbCommand 
 
 My_Connection.ConnectionString =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c：\ myDB.mdb; Persist Security Info = False; Jet OLEDB：Database Password = myPassword 
 My_Connection.Open（）
 
 sql =   SELECT product_ID，client_ID INTO [Excel 12.0; DATABASE = E：\ test.xls]。[EXPORT] FROM Orders 
 cmd = 新 System.Data.OleDb。的OleDbCommand（ sql，My_Connection）
 cmd.ExecuteNonQuery（）

此代码完美无缺。我的电脑里没有Excel。我创建了xls文件，我在另一台计算机上使用Excel或Apache Calc打开。

但我将数据库移动到SQL Server 2014.



< b>我尝试了什么：



我改变了我的代码：

  Dim  My_Connection  As  新 System.Data.SqlClient.SqlConnection 
  Dim  sql  As  字符串 
  Dim  cmd  As  System.Data。 SqlClient.SqlCommand 
 
 My_Connection.ConnectionString =  数据源= localhost;初始目录= myDB; Integrated Security = false; uid = sa; password = myPassword; 
 My_Connection.Open（）
 
 sql =   SELECT product_ID，cli ent_ID INTO [Excel 12.0; DATABASE = E：\test.xls]。[EXPORT] FROM Products 
 cmd = 新系统。 Data.SqlClient.SqlCommand（sql，My_Connection）
 cmd.ExecuteNonQuery（）

当我尝试运行时，我只是收到错误消息：

指定的模式名称 Excel 12.0; DATABASE = E：\ test.xls 要么不存在，要么您无权使用它。

解决方案

从这里开始： Excel连接字符串 - ConnectionStrings.com [ ^ ]

你会找到一个连接字符串和命令文本的示例。例如：

Quote：

SQL语法SELECT [列名一]，[列名二] FROM [第一张

。即excel工作表名称后跟

并用[]括号括起来。

正确的连接字符串是：

 Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c：\ myFolder \ myExcel2007file.xlsx; 
扩展属性=Excel 12.0; HDR = YES; 

将数据插入Excel的命令文本是：

  INSERT   INTO  [EXPORT 

I want to export data from database to Excel file. This is my code when I use MS Access:

Dim My_Connection As New System.Data.OleDb.OleDbConnection
Dim sql As String
Dim cmd As System.Data.OleDb.OleDbCommand
    
My_Connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myDB.mdb;Persist Security Info=False;Jet OLEDB:Database Password=myPassword"
My_Connection.Open()

sql = "SELECT product_ID, client_ID INTO [Excel 12.0;DATABASE=E:\test.xls].[EXPORT] FROM Orders"
cmd = New System.Data.OleDb.OleDbCommand(sql, My_Connection)
cmd.ExecuteNonQuery()

This code work perfectly. I don't have Excel in my PC. I created xls file that I open on another computer with Excel or Apache Calc.
But I move database to SQL Server 2014.

What I have tried:

I change my code:

Dim My_Connection As New System.Data.SqlClient.SqlConnection
Dim sql As String
Dim cmd As System.Data.SqlClient.SqlCommand

My_Connection.ConnectionString = "Data source=localhost;Initial Catalog=myDB;Integrated Security=false;uid=sa;password=myPassword;"
My_Connection.Open()

sql = "SELECT product_ID, client_ID INTO [Excel 12.0;DATABASE=E:\test.xls].[EXPORT] FROM Products"
cmd = New System.Data.SqlClient.SqlCommand(sql, My_Connection)
cmd.ExecuteNonQuery()

When I try to run I just get the error message:
The specified schema name "Excel 12.0;DATABASE=E:\test.xls" either does not exist or you do not have permission to use it.

解决方案

Start here: Excel connection strings - ConnectionStrings.com[^]
There you'll find an examples of connection-string and command text. For example:

Quote:

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One

". I.e. excel worksheet name followed by a "

" and wrapped in "[" "]" brackets.

A proper connection string is:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0;HDR=YES";

A command-text to insert data into Excel is:

INSERT INTO [EXPORT

这篇关于如何使用VB.NET将数据从SQL服务器导出到excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！