导入/导出Excel
1.--从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
/*===================================================================*/
--如果接受数据导入的表已经存在
OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 '
, ' Excel 5.0;HDR=YES;DATABASE=c: est.xls ' ,sheet1$)
--如果导入数据并生成表
OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 '
, ' Excel 5.0;HDR=YES;DATABASE=c: est.xls ' ,sheet1$)
/*===================================================================*/
2.--从SQL数据库中,导出数据到Excel:
--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
, ' Excel 5.0;HDR=YES;DATABASE=c: est.xls ' ,sheet1$)
select * from 表
--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
--导出查询的情况
/*--说明:
c:/test.xls 为导入/导出的Excel文件名.
sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.
--*/
3.--建立存储过程,导出真正的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 ]
GO
/*--数据导出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 ) = '' -- 文件名,默认为表名
as
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
go
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 ]
GO
/*--数据导出EXCEL
导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/*--调用示例
p_exporttb @sqlstr='select * from 地区资料'
,@path='c:',@fname='aa.xls',@sheetname='地区资料'
--*/
create proc p_exporttb
@sqlstr varchar ( 8000 ), -- 查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar ( 1000 ), -- 文件存放目录
@fname nvarchar ( 250 ), -- 文件名
@sheetname varchar ( 250 ) = '' -- 要创建的工作表名,默认为文件名
as
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 = ' temp.xls '
if isnull ( @sheetname , '' ) = '' set @sheetname = replace ( @fname , ' . ' , ' # ' )
-- 检查文件是否已经存在
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
-- 创建表的SQL
declare @tbname sysname
set @tbname = ' ##tmp_ ' + convert ( varchar ( 38 ), newid ())
set @sql = ' select * into [ ' + @tbname + ' ] from( ' + @sqlstr + ' ) a '
exec ( @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 tempdb..syscolumns a left join tempdb..systypes b on a.xtype = b.xusertype
where b.name not in ( ' image ' , ' uniqueidentifier ' , ' sql_variant ' , ' varbinary ' , ' binary ' , ' timestamp ' )
and a.id = ( select id from tempdb..sysobjects where name = @tbname )
if @@rowcount = 0 return
select @sql = ' create table [ ' + @sheetname
+ ' ]( ' + 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
;DATABASE= ' + @path + @fname + ''' ,[ ' + @sheetname + ' $]) '
exec ( ' insert into ' + @sql + ' ( ' + @fdlist + ' ) select ' + @fdlist + ' from [ ' + @tbname + ' ] ' )
set @sql = ' drop table [ ' + @tbname + ' ] '
exec ( @sql )
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
go
4.--在.net中导出到Excel的简单实例.
(1).添加引用Microsoft Excel 11.0 Object Library (在COM中)
(2).在web.config的上<system.web>里加
// 此句为了使用户能在本地打开文档,identify属性规定了身份验证的模式,一般情况下为false,这样安全性较高
(3).在Text.aspx上加Button1控件
(4).在cs页面添加 using Microsoft.Office.Interop.Excel;
(5).添加单击事件,在这里处理将数据库中的数据导入excel,代码如下:
private System.Data.DataTable GetData()
{
SqlConnection conn= new SqlConnection(@"Server=XJIE;Initial Catalog=Northwind;Uid=sa;Pwd=xjie;");
SqlDataAdapter adapter= new SqlDataAdapter("select CompanyName 用户名,ContactTitle 联系主题,Address 住宅地址,ContactName 联系人,Phone 电话,City 城市 from Customers order by CustomerID desc",conn);
DataSet ds= new DataSet();
try
{
adapter.Fill(ds,"Customer");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
return ds.Tables[0];
}
private void Button1_Click( object sender, System.EventArgs e)
{
Application excel= new Application();
int rowIndex=1;
int colIndex=0;
excel.Application.Workbooks.Add(true);
DataTable table=GetData();
//将所得到的表的列名,赋值给单元格
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
//同样方法处理数据
foreach(DataRow row in table.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
excel.Visible=true;
}