使用VBA Excel代码中的RecordSet数据创建图表

2 回答

• 1

  我同意@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
  

  回复于 2024-04-27T07:00:18+08:00
• 0

  你有两个选择：

  • 使用SUM和GROUP BY运行另一个SQL查询以获得总计 . 这是您图表的数据 .

  • 在Excel中使用数据透视表 . 这将为您计算总计并绘制图表 .

  回复于 2024-04-27T07:00:18+08:00