Python uses VBA (8): Excel to create a chart (GO)

#-*-Coding:utf-8-*-

"""

Created on Thu Mar 06 11:22:03 2014

@author: Administrator

"""

Import Win32com.client

From win32com.gen_py import MSOF,MSPP,MSXL

From string Import uppercase

From pandas import Series

#%% to publish constants to the global namespace

g = Globals ()

For C in Dir (msof.constants): g[c] = GetAttr (msof.constants, C)

For C in Dir (mspp.constants): g[c] = GetAttr (mspp.constants, C)

For C in Dir (msxl.constants): g[c] = GetAttr (msxl.constants, C)

#%% the corresponding relational table for generating row names and coordinates

Luc = List (uppercase)

columns = Series (Luc + [I+j for I in Luc for J in Luc]) [: 256],range (1,257))

def cellname (Nrow,ncol):

Return Columns[ncol]+str (Nrow)

#%% Open a test file

application = Win32com.client.Dispatch (' Excel.Application ')

Application. Visible = True

Workbook = Application. Workbooks.Open (R ' C:\sample1.xls ')

Sheets = Workbook. Sheets

Sheet1 = sheets. Item (1)

#%% Getting data range

Nrow = Sheet1. UsedRange.Rows.Count

Ncol = Sheet1. UsedRange.Columns.Count

Rangename = Cellname (+) + ': ' + cellname (nrow,ncol)

Rangename

#%% Creating a chart

Chart = workbook. Charts.add ()

Chart. ChartType = Xlline

Chart. SetSourceData (Sheet1. Range (Rangename))

# Traverse all Series

SeriesCollection = chart. SeriesCollection ()

Seriescollection.count

For I in SeriesCollection:

Print (i)

# Reference Http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

#%% Chart Type description

ChartType = {' xlline ': ' Line chart ',

' xllinemarkersstacked ': ' Stacked data point line chart ',

' xllinestacked ': ' Stacked line chart ',

' Xlpie ': ' Pie chart ',

' Xlpieofpie ': ' Compound pie chart ',

' xlpyramidbarstacked ': ' Stacked bar pyramid ',

' Xlpyramidcol ': ' Three-dimensional column pyramid ',

' xlpyramidcolclustered ': ' Clustered column-shaped pyramid ',

' xlpyramidcolstacked ': ' Stacked column-shaped pyramid ',

' xlPyramidColStacked100 ': ' Percent stacked column pyramid ',

' Xlradar ': ' Radar chart ',

' xlradarfilled ': ' Fill radar chart ',

' Xlradarmarkers ': ' Radar graph of data points ',

' XLSTOCKHLC ': ' Disk high-low-close graph ',

' XLSTOCKOHLC ': ' Open-high-low-close chart ',

' XLSTOCKVHLC ': ' Volume-intraday high-low-close chart ',

' XLSTOCKVOHLC ': ' Volume-open-intraday high-low-close chart ',

' Xlsurface ': ' Three-dimensional surface graph ',

' Xlsurfacetopview ': ' Surface chart (top view) ',

' Xlsurfacetopviewwireframe ': ' Surface chart (top view frame) ',

' Xlsurfacewireframe ': ' Three-dimensional surface chart (frame chart) ',

' Xlxyscatter ': ' Scatter chart ',

' Xlxyscatterlines ': ' polyline scatter chart ',

' Xlxyscatterlinesnomarkers ': ' Countless point polyline scatter plots ',

' Xlxyscattersmooth ': ' Smooth line scatter chart ',

' Xlxyscattersmoothnomarkers ': ' Countless locations smooth line scatter chart ',

' Xl3darea ': ' Three-dimensional area chart ',

' xl3dareastacked ': ' Three-dimensional stacked area chart ',

' xl3dareastacked100 ': ' percent stacked area chart ',

' xl3dbarclustered ': ' Three-dimensional clustered bar graph ',

' xl3dbarstacked ': ' Three-dimensional stacked bar graph ',

' xl3dbarstacked100 ': ' Three-dimensional percentage stacked bar chart ',

' xl3DColumn ': ' Three-dimensional column chart ',

' xl3dcolumnclustered ': ' Three-dimensional clustered column chart ',

' xl3dcolumnstacked ': ' Three-dimensional stacked column chart ',

' xl3dcolumnstacked100 ': ' Three-dimensional percentage stacked column chart ',

' Xl3dline ': ' Three-dimensional line chart ',

' xl3DPie ': ' Three-dimensional pie chart ',

' xl3dpieexploded ': ' Separate three-dimensional pie chart ',

' Xlarea ': ' Area chart ',

' xlareastacked ': ' Stacked area chart ',

' xlAreaStacked100 ': ' percent stacked area chart ',

' xlbarclustered ': ' Clustered bar graph ',

' Xlbarofpie ': ' Compound bar pie chart ',

' xlbarstacked ': ' Stacked bar chart ',

' xlBarStacked100 ': ' percent stacked bar graph ',

' xlbubble ': ' Bubble chart ',

' Xlbubble3deffect ': ' Three-dimensional bubble chart ',

' xlcolumnclustered ': ' Clustered Column chart ',

' xlcolumnstacked ': ' Stacked column chart ',

' xlColumnStacked100 ': ' Percent stacked column chart ',

' xlconebarclustered ': ' Clustered bar cone ',

' xlconebarstacked ': ' Stacked bar cone chart ',

' xlConeBarStacked100 ': ' percent stacked bar cone graph ',

' Xlconecol ': ' Three-dimensional cylindrical cone ',

' xlconecolclustered ': ' Clustered column-shaped cone ',

' xlconecolstacked ': ' Stacked column cone ',

' xlConeColStacked100 ': ' percent stacked cylindrical cone ',

' xlcylinderbarclustered ': ' Clustered Bar-shaped column ',

' xlcylinderbarstacked ': ' Stacked bar cylinder ',

' xlCylinderBarStacked100 ': ' percent stacked bar cylinder ',

' Xlcylindercol ': ' Three-dimensional cylindrical cylinder ',

' xlcylindercolclustered ': ' Clustered column-shaped cone ',

' xlcylindercolstacked ': ' Stacked column cone ',

' xlCylinderColStacked100 ': ' percent stacked column-shaped cylinder ',

' Xldoughnut ': ' Doughnut chart ',

' xldoughnutexploded ': ' Split-type doughnut chart ',

' Xllinemarkers ': ' Data point line Chart ',

' xlLineMarkersStacked100 ': ' percent stacked data point line chart ',

' xlLineStacked100 ': ' percent stacked line chart ',

' xlpieexploded ': ' Split-type pie chart ',

' xlpyramidbarclustered ': ' Clustered Bar pyramid ',

' xlPyramidBarStacked100 ': ' percent stacked bar pyramid '}

Python uses VBA (8): Excel to create a chart (GO)