python数据透视_使用python win32com创建数据透视图

is there anyone can give a example code for creating a pivotchart in excel?

I have got an example to create a pivottable with pivottable, it works , while when I try to create a pivotchart in the same ways, it will always failed!

import win32com.client

import os

Excel = win32com.client.gencache.EnsureDispatch('Excel.Application')

win32c = win32com.client.constants

wb = Excel.Workbooks.Add()

Sheet1 = wb.Worksheets.Add()

TestData = [['Country','Name','Gender','Sign','Amount'],

['CH','Max' ,'M','Plus',123.4567],

['CH','Max' ,'M','Minus',-23.4567],

['CH','Max' ,'M','Plus',12.2314],

['CH','Max' ,'M','Minus',-2.2314],

['CH','Sam' ,'M','Plus',453.7685],

['CH','Sam' ,'M','Minus',-53.7685],

['CH','Sara','F','Plus',777.666],

['CH','Sara','F','Minus',-77.666],

['DE','Hans','M','Plus',345.088],

['DE','Hans','M','Minus',-45.088],

['DE','Paul','M','Plus',222.455],

['DE','Paul','M','Minus',-22.455]]

for i, TestDataRow in enumerate(TestData):

for j, TestDataItem in enumerate(TestDataRow):

Sheet1.Cells(i+2,j+4).Value = TestDataItem

cl1 = Sheet1.Cells(2,4)

cl2 = Sheet1.Cells(2+len(TestData)-1,4+len(TestData[0])-1)

PivotSourceRange = Sheet1.Range(cl1,cl2)

PivotSourceRange.Select()

Sheet2 = wb.Worksheets("Sheet1")

cl3=Sheet2.Cells(4,1)

PivotTargetRange= Sheet2.Range(cl3,cl3)

PivotTableName = 'ReportPivotTable'

PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange, Version=win32c.xlPivotTableVersion14)

PivotTable = PivotCache.CreatePivotTable(TableDestination=PivotTargetRange, TableName=PivotTableName, DefaultVersion=win32c.xlPivotTableVersion14)

PivotChart = PivotCache.CreatePivotChart(ChartDestination="Sheet1")

Excel.Visible = 1

cwd = os.getcwd()

wb.SaveAs(os.path.join(cwd,'test.xlsx'))

Excel.Application.Quit()

it will always report the error:

Traceback (most recent call last):

File "excelpivotchart.py", line 41, in

PivotChart = PivotCache.CreatePivotChart(ChartDestination="Sheet1")

File "C:\Python27\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x8\PivotCache.py", line 36, in CreatePivotChart

, XlChartType, Left, Top, Width, Height

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

解决方案

I managed to make it work

import win32com.client

import os

Excel = win32com.client.gencache.EnsureDispatch('Excel.Application')

win32c = win32com.client.constants

wb = Excel.Workbooks.Add()

Sheet1 = wb.Worksheets.Add()

TestData = [['Country','Name','Gender','Sign','Amount'],

['CH','Max' ,'M','Plus',123.4567],

['CH','Max' ,'M','Minus',-23.4567],

['CH','Max' ,'M','Plus',12.2314],

['CH','Max' ,'M','Minus',-2.2314],

['CH','Sam' ,'M','Plus',453.7685],

['CH','Sam' ,'M','Minus',-53.7685],

['CH','Sara','F','Plus',777.666],

['CH','Sara','F','Minus',-77.666],

['DE','Hans','M','Plus',345.088],

['DE','Hans','M','Minus',-45.088],

['DE','Paul','M','Plus',222.455],

['DE','Paul','M','Minus',-22.455]]

for i, TestDataRow in enumerate(TestData):

for j, TestDataItem in enumerate(TestDataRow):

Sheet1.Cells(i+2,j+4).Value = TestDataItem

cl1 = Sheet1.Cells(2,4)

cl2 = Sheet1.Cells(2+len(TestData)-1,4+len(TestData[0])-1)

PivotSourceRange = Sheet1.Range(cl1,cl2)

PivotSourceRange.Select()

Sheet2 = wb.Worksheets.Add()

cl3=Sheet2.Cells(4,1)

PivotTargetRange= Sheet2.Range(cl3,cl3)

PivotTableName = 'ReportPivotTable'

PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase,SourceData=PivotSourceRange, Version=win32c.xlPivotTableVersion14)

PivotTable = PivotCache.CreatePivotTable(TableDestination=PivotTargetRange, TableName=PivotTableName, DefaultVersion=win32c.xlPivotTableVersion14)

#Properties from a recorded macro I made, I didn't take the time to determine which attribute is really important and I don't think I will.

PivotTable.ColumnGrand = True

PivotTable.HasAutoFormat = True

PivotTable.DisplayErrorString = False

PivotTable.DisplayNullString = True

PivotTable.EnableDrilldown = True

PivotTable.ErrorString = ""

PivotTable.MergeLabels = False

PivotTable.NullString = ""

PivotTable.PageFieldOrder = 2

PivotTable.PageFieldWrapCount = 0

PivotTable.PreserveFormatting = True

PivotTable.RowGrand = True

PivotTable.SaveData = True

PivotTable.PrintTitles = False

PivotTable.RepeatItemsOnEachPrintedPage = True

PivotTable.TotalsAnnotation = False

PivotTable.CompactRowIndent = 1

PivotTable.InGridDropZones = False

PivotTable.DisplayFieldCaptions = True

PivotTable.DisplayMemberPropertyTooltips = False

PivotTable.DisplayContextTooltips = True

PivotTable.ShowDrillIndicators = True

PivotTable.PrintDrillIndicators = False

PivotTable.AllowMultipleFilters = False

PivotTable.SortUsingCustomLists = True

PivotTable.FieldListSortAscending = False

PivotTable.ShowValuesRow = False

PivotTable.CalculatedMembersInFilters = False

PivotTable.PivotFields('Name').Orientation = win32c.xlRowField

PivotTable.PivotFields('Name').Position = 1

PivotTable.PivotFields('Gender').Orientation = win32c.xlPageField

PivotTable.PivotFields('Gender').Position = 1

PivotTable.PivotFields('Gender').CurrentPage = 'M'

PivotTable.PivotFields('Country').Orientation = win32c.xlColumnField

PivotTable.PivotFields('Country').Position = 1

PivotTable.PivotFields('Country').Subtotals = [False, False, False, False, False, False, False, False, False, False, False, False]

PivotTable.PivotFields('Sign').Orientation = win32c.xlColumnField

PivotTable.PivotFields('Sign').Position = 2

DataField = PivotTable.AddDataField(PivotTable.PivotFields('Amount'))

DataField.NumberFormat = '#\'##0.00'

#Creating the PivotChart

chart=Sheet2.Shapes.AddChart2(201)

#to change XlChartType,cf.https://msdn.microsoft.com/fr-fr/vba/excelvba/articles/shapes-addchart2-method-excel

#For example, you can do chart=Sheet2.Shapes.AddChart2(201,4) for xlLine chart

Excel.Visible = 1

The result :

Hope this helps.

Regards