.NET + Excel + SQL 的导入导出相关操作

--从Excel文件中查询--已经试验。

--A.先开启服务。

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

--B.执行。sheet1--Excel中对应的页

select * into table1 from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=G:\a.xls',sheet1$)

--当初在程序中执行这一句话一直出错，原来驱动写错了，应该是oledb

以下来自：CSDN->BBS某贴

--SQL SERVER 和EXCEL的数据导入导出

--1、在SQL SERVER里查询Excel数据:

-- ======================================================

SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="G:\a.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

--下面是个查询的示例，它通过用于Jet 的OLE DB 提供程序查询Excel 电子表格。

SELECT *

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

-------------------------------------------------------------------------------------------------

--2、将Excel的数据导入SQL server :

-- ======================================================

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

--实例:

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

-------------------------------------------------------------------------------------------------

--3、将SQL SERVER中查询到的数据导成一个Excel文件

-- ======================================================

--T-SQL代码：

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

--参数：S 是SQL服务器名；U是用户；P是密码

--说明：还可以导出文本文件等多种格式

--实例:

EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

--在VB6中应用ADO导出EXCEL文件代码：

Dim cn As New ADODB.Connection

cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

------------------------------------------------------------------------------------------------

--4、在SQL SERVER里往Excel插入数据:

-- ======================================================

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

--T-SQL代码：

INSERT INTO

OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',

'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]

(bestand, produkt) VALUES (20, 'Test')

SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=d:\inetpub\wwwroot\a.xls;User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

以下来自：http://www.cnblogs.com/MeChecksV/articles/960793.html

--从Excel文件中,导入数据到SQL数据库中

/*===================================================================*/

--如果接受数据导入的表已经存在

insert into 表select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

--如果导入数据并生成表

select * into 表from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

/*===================================================================*/

--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:

insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

select * from 表

--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:

--导出表的情况

EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名out "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'

--导出查询的情况

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'

/*--说明:

c:\test.xls 为导入/导出的Excel文件名.

sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.

--*/

--下面是导出真正Excel文件的方法:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_exporttb]

/*--数据导出EXCEL

导出表中的数据到Excel,包含字段名,文件为真正的Excel文件

,如果文件不存在,将自动创建文件

,如果表不存在,将自动创建表

基于通用性考虑,仅支持导出标准数据类型

--邹建2003.10(引用请保留此信息)--*/

/*--调用示例

p_exporttb @tbname='地区资料',@path='c:\',@fname='aa.xls'

--*/

create proc p_exporttb

@tbname sysname, --要导出的表名

@path nvarchar(1000), --文件存放目录

@fname nvarchar(250)='' --文件名,默认为表名

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int

declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测

if isnull(@fname,'')='' set @fname=@tbname+'.xls'

--检查文件是否已经存在

if right(@path,1)<>'\' set @path=@path+'\'

create table #tb(a bit,b bit,c bit)

set @sql=@path+@fname

insert into #tb exec master..xp_fileexist @sql

--数据库创建语句

set @sql=@path+@fname

if exists(select 1 from #tb where a=1)

set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'

+';CREATE_DB="'+@sql+'";DBQ='+@sql

else

set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'

+';DATABASE='+@sql+'"'

--连接数据库

exec @err=sp_oacreate 'adodb.connection',@obj out

if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr

if @err<>0 goto lberr

/*--如果覆盖已经存在的表,就加上下面的语句

--创建之前先删除表/如果存在的话

select @sql='drop table ['+@tbname+']'

exec @err=sp_oamethod @obj,'execute',@out out,@sql

--*/

--创建表的SQL

select @sql='',@fdlist=''

select @fdlist=@fdlist+',['+a.name+']'

,@sql=@sql+',['+a.name+'] '

+case

when b.name like '%char'

then case when a.length>255 then 'memo'

else 'text('+cast(a.length as varchar)+')' end

when b.name like '%int' or b.name='bit' then 'int'

when b.name like '%datetime' then 'datetime'

when b.name like '%money' then 'money'

when b.name like '%text' then 'memo'

else b.name end

FROM syscolumns a left join systypes b on a.xtype=b.xusertype

where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')

and object_id(@tbname)=id

select @sql='create table ['+@tbname

+']('+substring(@sql,2,8000)+')'

,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql

if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1

;DATABASE='+@path+@fname+''',['+@tbname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)

return

lberr:

exec sp_oageterrorinfo 0,@src out,@desc out

lbexit:

select cast(@err as varbinary(4)) as 错误号

,@src as 错误源,@desc as 错误描述

select @sql,@constr,@fdlist

--贴出程序：

1.有错的程序

private void Button1_Click(object sender, System.EventArgs e)

{

string strPath=Server.MapPath("../a.xls");

File1.PostedFile.SaveAs(strPath);

string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '"+ strPath +"';Extended Properties=Excel 8.0";

OleDbConnection cnnxls = new OleDbConnection (mystring);

OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);

DataSet myDs =new DataSet();

myDa.Fill(myDs);

DataGrid1.DataSource=myDs.Tables[0];

DataGrid1.DataBind();

}

//提示错误：对路径“d:\inetpub\wwwroot\a.xls”的访问被拒绝。

// 异常详细信息: System.UnauthorizedAccessException: 对路径“d:\inetpub\wwwroot\a.xls”的访问被拒绝。

// ASP.NET 未被授权访问所请求的资源。请考虑授予 ASP.NET 请求标识访问此资源的权限。ASP.NET 有一个在应用程序没有模拟时使用的基进程标识(通常，在 IIS 5 上为 {MACHINE}\ASPNET，在 IIS 6 上为网络服务)。如果应用程序正在通过 <identity impersonate="true"/> 模拟，则标识将为匿名用户(通常为 IUSR_MACHINENAME)或经过身份验证的请求用户。

// 若要授予 ASP.NET 对文件的写访问权，请在资源管理器中右击该文件，选择“属性”，然后选择“安全”选项卡。单击“添加”添加适当的用户或组。突出显示 ASP.NET 帐户，选中所需访问权限对应的框。

我已经修改权限了(其他程序调用时没有修改权限也可以!),不知道怎么回事.

2.ok

private void Page_Load(object sender, System.EventArgs e)

{

// 在此处放置用户代码以初始化页面

string strFileName = @"D:\Inetpub\wwwroot\a.xls";

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + strFileName + ";" +

"Extended Properties=Excel 8.0;";

OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$] where 员工编号=1001", strConn);

DataSet ExcelDs = new DataSet();

try

{

ExcelDA.Fill(ExcelDs);

DataGrid1.DataSource=ExcelDs.Tables[0];

DataGrid1.DataBind();

}

catch (Exception err)

{

throw err;

}

3.ok.这种方法上面介绍哦了.

private void Page_Load(object sender, System.EventArgs e)

{

// 在此处放置用户代码以初始化页面

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

　 "Data Source=" + Server.MapPath("../a.xls") + ";" +

　 "Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.

　 OleDbConnection objConn = new OleDbConnection(sConnectionString);

　 // Open connection with the database.

　 objConn.Open();

　 // The code to follow uses a SQL SELECT command to display the data from the worksheet.

　 // Create new OleDbCommand to return data from worksheet.

　 OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1", objConn);

　 // Create new OleDbDataAdapter that is used to build a DataSet

　 // based on the preceding SQL SELECT statement.

　 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

　 // Pass the Select command to the adapter.

　 objAdapter1.SelectCommand = objCmdSelect;

　 // Create new DataSet to hold information from the worksheet.

　 DataSet objDataset1 = new DataSet();

　 // Fill the DataSet with the information from the worksheet.

　 objAdapter1.Fill(objDataset1, "XLData");

　 // Bind data to DataGrid control.

　 DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;

　 DataGrid1.DataBind();

　 // Clean up objects.

　 objConn.Close();

}