导出带图片的Excel报表
先引用 Microsoft.Office.Tools.Excel.v4.0.Utilities.dll程序集,然后在后台页面引用using Excel = Microsoft.Office.Interop.Excel;
Function getcolname(ByVal intcol As Long)
intcol = intcol - 1
getcolname = IIf(intcol \ 26, Chr(64 + intcol \ 26), "") & Chr(65 + intcol Mod 26)
End Function
Sub 批量生成图表()
'
' 批量生成图表
' by marty
' 原始数据需要保存在sheet1中
x = 0
y = 0
Sheets.Add After:=Sheets(Sheets.Count)
'ActiveSheet.Name = "Bug图表"
'获取sheet1中总的列数
intcol = Sheets("Sheet1").Range("IV1").End(xlToLeft).Column
strCol = getcolname(intcol)
'生成图表
For i = 2 To 6
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C" & i & ":" & strCol & i)
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$C$1:$" & strCol & "$1"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!$B$" & i
'设置x轴的类型为文本坐标轴,而不是时间坐标轴
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
Next i
'调整每个图表的位置
For Each Chart In ActiveSheet.ChartObjects
Chart.Activate
ActiveChart.ChartArea.Select
Chart.Top = y * 206
Chart.Left = x * 606
Chart.Height = 200
Chart.Width = 600
x = x + 1
If x >= 1 Then
x = 0
y = y + 1
End If
Next Chart
End Sub
---------------------
版权声明:本文为CSDN博主「close_marty」的原创文章,遵循CC 4.0 by-sa版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/close_marty/article/details/37909967