让最终用户从sql server 2005 到出数据到Excel

Question

要求，想做个batch 文件，让用户输入2个参数，导出3个视图。不定期的导出数据到excel。
1。SQLCMD，导出到excel格式不好，都是一列
2。DTS 不知道怎么让客户使用它（不希望让他们操作数据库）
3。BCP 应该在哪里执行？  我在SQL Server Management Studio里 new query里执行，没有看到结果
EXEC   master..xp_cmdshell   'bcp   库名.dbo.表名out   c:\Temp.xls   -c   -q   -S"servername"   -U"sa"   -P""'   
4。执行这个提示错误信息，Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test2.xls', 'SELECT * FROM [Sheet1$]')SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

Answer 1

给你段VB代码，是将一个数据集的数据导到excel中的。你只要传入数据集和一个字符串即可。Public Function ExportGridToExcel(ObjectRst As ADODB.Recordset, ExcelFileHead As String) As Long
       Dim RowCount As Long
       Dim ColCount As Long
       
       Dim objExcel As Excel.Application
       Dim objField As Field
       Dim objWorkbook As Excel.workBook
       Dim objWorkSheet As Excel.Worksheet
       
       'Screen.MousePointer = vbHourglass
       
       On Error GoTo ErrorProcessSection
       
       Set objExcel = New Excel.Application
       Set objExcel = New Excel.Application
       objExcel.Interactive = True
       If objExcel.Visible = Not False Then
          objExcel.Visible = Not True
       End If
       objExcel.WindowState = xlMaximized
       Set objWorkbook = objExcel.Workbooks.Add
       Set objWorkSheet = objWorkbook.Worksheets.Add
       
       objWorkSheet.PageSetup.CenterHeader = ExcelFileHead
       objWorkSheet.PageSetup.LeftHeader = "Date/Time : " & Format(Now, "YYYY/MM/DD HH:NN:SS")
       
       With objWorkSheet.Columns
            .Font.Size = 10
            .HorizontalAlignment = xlCenter
       End With
       
       ColCount = 1
       For Each objField In ObjectRst.Fields
                objWorkSheet.Cells(1, ColCount).Value = objField.Name
                objWorkSheet.Cells(1, ColCount).Interior.ColorIndex = 33
                objWorkSheet.Cells(1, ColCount).Font.Bold = True
                objWorkSheet.Cells(1, ColCount).BorderAround xlContinuous
                ColCount = ColCount + 1
       Next objField
       ObjectRst.MoveFirst
       RowCount = 2
       Do
          Select Case ObjectRst.EOF()
             Case True
               Exit Do
             Case False
               ColCount = 1
               For Each objField In ObjectRst.Fields
                    If IsNull(ObjectRst.Fields(objField.Name).Value) Then
                        Debug.Print ObjectRst.Fields(objField.Name).Value
                        objWorkSheet.Cells(RowCount, ColCount).Value = "-"
                        Debug.Print objWorkSheet.Cells(RowCount, ColCount).Value
                    Else
                        Debug.Print ObjectRst.Fields(objField.Name).Value
                        objWorkSheet.Cells(RowCount, ColCount).Value = Trim(ObjectRst.Fields(objField.Name).Value & vbNullString)
                        Debug.Print objWorkSheet.Cells(RowCount, ColCount).Value
                    End If
               ColCount = ColCount + 1
               Next objField
               ObjectRst.MoveNext
               RowCount = RowCount + 1
          End Select
       Loop
 
        ColCount = 1
        For Each objField In ObjectRst.Fields
                 objWorkSheet.Columns(ColCount).AutoFit
                 ColCount = ColCount + 1
        Next objField
         objExcel.Visible = True
        Exit Function      
ErrorProcessSection:       
        Select Case Err.Number
           Case 0
           Case Else
             MsgBox "蹲ア毖" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, _
                    vbOKOnly + vbCritical, "蹲ア毖"
             objWorkbook.Close
             objExcel.Quit
             Set objField = Nothing
             Set objWorkSheet = Nothing
             Set objWorkbook = Nothing
             Set objExcel = Nothing
        End Select
End Function

Answer 2

不会VB,C# 初学有没有办法用batch来做，这样用户操作容易

Answer 3

需要导出一个文件的不同sheet中

Answer 4

〉〉我在SQL Server Management Studio里 new query里执行，没有看到结果 
都是导出到服务器的硬盘上的。

Answer 5

/*===================================================================*/
--如果从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文件的工作表名,一般要加上$才能正常使用.
--*/

Answer 6

/*--数据导出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 lberrexec @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 returnselect @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 lberrexec @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)
returnlberr:
 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

Answer 7

can't creat p_exporttb,my database is sql server 2005error:
Cannot add rows to sysdepends for the current object because it depends on the missing object 'sp_oacreate'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'sp_oamethod'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'sp_oamethod'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'sp_oadestroy'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'sp_oageterrorinfo'. The object will still be created.

Answer 8

这样应用类的东西，最好不要通过数据库来解决。
建议采用程序处理或者reportingservices做报表来实现用户的需求。

Answer 9

很多服务器为了安全，xp_cmdshell都是被禁掉的！

Answer 10

我打算写.net程序，来完成这些功能。 如果谁有现成的例子，感激不尽

Answer 11

先将其导入到DataGrid中，然后从DataGrid导出。C#代码如下
    //将GridView里的内容转到EXCEL
    protected void Button2_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw); 
        Response.Write(sw.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {    }