EXCEL中使用powerpivot 处理ORACLE等等的数据,通常几百万行,并且对其进行数据分析的速度就像处理分析几百行数据一样快.要不把整个数据库的数据弄进出,然后切片机处理。
-------------------------------------------------------------------------------------------------------------------
或者可采用限制结果记录集
或者SELECT TOP 50 PERCENT
或者.RecordCount .AbsolutePosition.AbsolutePage.PageCount.PageSize
-------------------------------------------------------------------------------------------------------------------
还是附上一个函数
把两个数据集发送给同一个工作簿中的两个不同的电子表格
Function SendMoreThanOneRecordset()
'setp1:Declare the Objects and variables You work with
Dim xl As Excel.Application
Dim xlwkbk As Excel.workbook
Dim xlsheet As Excel.worksheet
Dim C As Integer
Dim I As Integer
'Step2:Start a new recordset ,create a new instance of Excel ,start a workbook
Set MyRecordset = New ADODB.Recordset
Set xl = New Excel.Application
Set xlwkbk =xl.workbooks.Add
'step3:Make the instance of Excel visible
Xl.visible = True
'Step4:Assign a dataset to the recordset object
MyRecordset.open "PvTblFeed",CurrentProject.Connection, adopenstatic
'Step5:Add a new worksheet and name it
Set xlsheet = xlwkbk.worksheet.Add
Xlsheet.name = "Pivot Table Feed"
'Step6: Copy the records to the active Excel sheet starting
'with cell A2 in order to leave room for the column headings
With xlsheet
Xl.Range("A2").CopyFromRecordset MyRecordset
End with
'Step 7:Enumerate through the field in the recordset and
'add column heading names to the spreadsheet
C = 1
For I = 0 To MyRecordset.fields.count - 1
Xl.ActiveSheet.Cell(1,C).Value = MyRecordset.Fidlds(i).name
C = C + 1
Next I
'Step 8 :Close the current recordset and repeat steps 4-7 for a new recordset
MyRecordset.close
MyRecordset.open "MainSummary",CurrentProject.connection ,adopenstatic
Set xlsheet = xlwkbk.worksheets.Add
Xlsheet.Name = "Main Summary"
With xlsheet
Xl.Range("A2").CopyFromRecordset MyRecordset
End with
C = 1
For I = 0 To MyRecordset.Fidlds.Count - 1
Xl.Activesheet.cell(1,c).Value = MyRecordset.Fidlds(i).name
C = C + 1
Next I
'step 9 :Memory Clean up
Set MyRecordset = Nothing
Set xl = Nothing
Set xlwkbk = Nothing
Set xlsheet = Nothing
End Function