用EXCEL来打印报表并生成折线图




 

 

Private Sub PrintFunc()

on error goto PrintFunc_Err

Dim Ct As Object

Set ex = CreateObject("Excel.Application")

增加工作表

ex.Workbooks.Add

输出数据

ex.Range("A1").Value = "系列1"

ex.Range("A2").Value = 8

ex.Range("A3").Value = 16

ex.Range("A4").Value = 7

ex.Range("A5").Value = 22

ex.Range("A6").Value = 13

ex.Range("A7").Value = 15

ex.Range("A8").Value = 11

ex.Range("B1").Value = "系列2"

ex.Range("B2").Value = 5

ex.Range("B3").Value = 6

ex.Range("B4").Value = 17

ex.Range("B5").Value = 10

ex.Range("B6").Value = 3

ex.Range("B7").Value = 9

ex.Range("B8").Value = 16

设定表格样式 

ex.Range("A1", "B8").Borders.LineStyle = xlContinuous

ex.Cells(, 1).ColumnWidth = 25

ex.Cells(, 2).ColumnWidth = 25

’插入折线图

Set Ct = ex.Worksheets("sheet1").ChartObjects.Add(0, 130, 350, 250)

Ct.Chart.ChartWizard Source:=ex.Worksheets("sheet1").Range("A2:B8"), _

gallery:=xlLine, Title:="New Chart"

ex.Visible = True

打印预揽

ex.ActiveWindow.SelectedSheets.PrintPreview

关闭EXCEL

ex.Visible = False

ex.Worksheets("sheet1").ChartObjects.Delete

ex.ActiveWorkbook.Close savechanges:=False

PrintFunc_Err:

Set ex = Nothing

Set Ct = Nothing

end Sub