#-*-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)