我同意@ChipsLetten
1.-从SQL你可以使用这样的东西：
---- This is the sum for each day
SELECT
t.DOT, SUM(t.Amount)
---- You can add an extra Column with the SUM by Dates
/*
t.Dot, t.Category, t.Item, t.Amount
, SUM(t.Amount) OVER(PARTITION BY t.Dot ORDER BY t.Dot) [SumTrans]
*/
FROM Transactions t
GROUP BY t.DOT
-- If you add the Column you must change "GROUP BY"
-- GROUP BY t.Dot, t.Category, t.Item, t.Amount
添加列可以使用 DOT 和 SumTrans 系列来创建图表
2.-使用数据透视表甚至更好，您可以克隆您的Recordset以创建一个数据透视表，允许您通过DOT进行SUM数量
*Wkb is a Workbooks object, Wks is a Worksheet object*
Dim oRstChrt As ADODB.Recordset, oStrm As ADODB.Stream
Dim oChrt As Chart
'' Copy Recordset for the pivot table cache
Set oRstChrt = New ADODB.Recordset
Set oStrm = New ADODB.Stream
'' Create an alternative Recordset for the new pivot table
oRst.Save oStrm
oRstChrt.Open oStrm
'' Set Recordset to cache for a pivot table
Set objPivotCache = _
Wkb.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion14)
'' Recordset with Data
Set objPivotCache.Recordset = oRstChrt
'' Assign Range for the pivot table and Name it.
With objPivotCache
.CreatePivotTable TableDestination:=Wks.Range([Cell]), TableName:=[PivotTable_Name]
End With
然后放置你需要的字段，在这种情况下 DOT 和 Amount
With Wks.PivotTables([PivotTable_Name])
With .PivotFields("DOT")
.Orientation = xlRowField
.Position = 1
End With
'' You can create a second level, but
'' if the field is not collapse, your chart will change,
'' so keep the field collapse
'' To Collapse the field put this
'' Wks.PivotTables([PivotTable_Name]). _
'' PivotFields([PivotTableField]).ShowDetail = False
'With .PivotFields("Item")
'.Orientation = xlRowField
'.Position = 2
'End With
'' This allows you to SUM the Amount field
.AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End
'' After the Pivot Table was created you can create your chart
'' Set the object
Set oChrt = Charts.Add
'' Give a name and format
With oChrt
.Name = "[Chart_Name]"
.ChartType = xlColumnClustered '' you could change to 3D
'' Source Range in this case from the pivot table
.SetSourceData Source:=Wks.PivotTables([PivotTable_Name]).TableRange2, _
PlotBy:=xlRows
'' Format chart
.HasTitle = True
.ChartTitle.Text = "Total Transactions"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
' .Axes(xlValue, xlPrimary).HasTitle = True
' .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Transactions"
End With
如果觉得《java recordset_使用VBA Excel代码中的RecordSet数据创建图表》对你有帮助，请点赞、收藏，并留下你的观点哦！
