22,209
社区成员
发帖
与我相关
我的任务
分享
cn = ConAcc
ExporToExcel “SQL 查询字符串”
Public Function ExporToExcel(strOpen As String)
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
StbInfo ("正在联系EXCEL,准备创建并定义工作表...")
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
On Error Resume Next
With Rs_Data
If .State = adStateOpen Then
.Close
End If
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = strOpen
.Open
End With
StbInfo ("正在向excel的工作表中添加数据...请稍候...")
With Rs_Data
If .RecordCount < 1 Then
MsgBox "没有记录可以导出,请确认数据源记录是否为空!", vbInformation, "错误:"
StbInfo ("记录为空,不能导出。")
Exit Function
End If
'记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count
End With
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a2"))
With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icolcount + 1)).Font.Name = "微软雅黑"
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 14
.Range(.Cells(1, 2), .Cells(1, Icolcount)).Font.Bold = True
'.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount + 1)).Font.Size = 10
.Columns.Width = 300
'标题字体加粗
.Range(.Cells(2, 1), .Cells(Irowcount + 2, Icolcount)).Borders.LineStyle = xlContinuous
.Range(.Cells(2, 1), .Cells(Irowcount + 2, Icolcount)).Font.Name = "微软雅黑"
.Range(.Cells(2, 1), .Cells(Irowcount + 2, Icolcount)).Font.Size = 9
'.Range(.Cells(2, 1), .Cells(Irowcount + 2, Icolcount)).Font.Color = vbRed
'设表格边框样式
End With
xlApp.Application.Visible = True
If Prt = True Then xlApp.Worksheets.PrintPreview
xlApp.DisplayAlerts = False
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
xlApp.Quit
End Function
Public Function ExporToExcel(strOpen As String, Optional FormatingSwitch As Boolean, Optional FormatingColumns As String, Optional ColorScaleSwitch As Boolean, Optional ColorScaleRange As String)
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
StbInfo ("正在联系EXCEL,准备创建并定义工作表...")
Dim xlApp As New excel.Application
Dim xlBook As excel.workbook
Dim xlSheet As excel.Worksheet
Dim xlQuery As excel.QueryTable
On Error Resume Next
With Rs_Data
If .State = adStateOpen Then
.Close
End If
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = strOpen
.Open
End With
StbInfo ("正在向excel的工作表中添加数据...请稍候...")
With Rs_Data
If .RecordCount < 1 Then
MsgBox "没有记录可以导出,请确认数据源记录是否为空!", vbInformation, "错误:"
Exit Function
End If
'记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count
End With
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True
'添加查询语句,导入EXCEL数据
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
xlQuery.FieldNames = True '显示字段名
xlQuery.Refresh
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icolcount + 1)).Font.Name = "微软雅黑"
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Size = 10
.Range(.Cells(1, 2), .Cells(1, Icolcount)).Font.Bold = True
.Columns.Width = 300
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Font.Name = "微软雅黑"
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Font.Size = 9
'开启应用TableStyle
.Range("A1").Select
.ListObjects.Add(xlSrcRange, .Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)), , xlYes).Name = _
"HansonTempTable"
.Range("HansonTempTable[#All]").Select
.ListObjects("HansonTempTable").TableStyle = _
"TableStyleMedium5"
'开启首行分隔
.Rows("2:2").Select
xlApp.ActiveWindow.FreezePanes = True
'开启ConditionalFormattings
If FormatingSwitch = True Then
.Columns(FormatingColumns).Select
xlApp.Selection.FormatConditions.AddDatabar
xlApp.Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
xlApp.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With xlApp.Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueLowestValue
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With xlApp.Selection.FormatConditions(1).BarColor
.Color = 5920255
.TintAndShade = 0
End With
End If
If ColorScaleSwitch = True Then
.Columns(ColorScaleRange).Select
xlApp.Selection.FormatConditions.AddColorScale ColorScaleType:=3
xlApp.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 13011546
.TintAndShade = 0
End With
End If
End With
xlApp.Range("A1").Select
xlApp.Application.StatusBar = "Data from Hanson e-Pull System exported.Total Row Counts:" & Irowcount
xlApp.Application.Visible = True
xlApp.DisplayAlerts = False
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
xlApp.Quit
StbInfo ("导出成功!")
End Function
--导出excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.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')