# python -m pip install pypiwin32 import win32com.client Excel = win32com.client.gencache.EnsureDispatch('Excel.Application') win32c = win32com.client.constants Excel.Visible = True wb = Excel.Workbooks.Open('C:/Users/Hakim C/Documents/Python/classeur.xlsx') Sheet1 = wb.Worksheets('Source') def getContiguousRange(fichier, sheet, row, col): bottom = row while sheet.Cells(bottom + 1, col).Value not in [None, '']: bottom = bottom + 1 right = col while sheet.Cells(row, right + 1).Value not in [None, '']: right = right + 1 return sheet.Range(sheet.Cells(row, col), sheet.Cells(bottom, right)).Value myTable = getContiguousRange(fichier = wb, sheet = Sheet1, row = 1, col = 1) myRates = getContiguousRange(fichier = wb, sheet = Sheet1, row = 1, col = 8) calField = [['CA Bonifié']] for a, testMyTable in enumerate(myTable): for b, testMyRates in enumerate(myRates): if a >0 and b > 0: if testMyTable[0] == testMyRates[0]: calField.append( [ testMyTable[ len(testMyTable)-1 ] * ( 1+testMyRates[1] ) ] ) for i, testDataRow in enumerate(calField): for j, testDataItem in enumerate(testDataRow): Sheet1.Cells(i+1,len(testMyTable)+1).Value = testDataItem cl1 = Sheet1.Cells(1,1) cl2 = Sheet1.Cells(len(myTable),len(myTable[0])+1) pivotSourceRange = Sheet1.Range(cl1,cl2) pivotSourceRange.Select() Sheet2 = wb.Sheets.Add (After=wb.Sheets (1)) Sheet2.Name = 'TCD' 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) pivotTable.PivotFields('Service').Orientation = win32c.xlRowField pivotTable.PivotFields('Service').Position = 1 pivotTable.PivotFields('Region').Orientation = win32c.xlPageField pivotTable.PivotFields('Region').Position = 1 pivotTable.PivotFields('Region').CurrentPage = 'IDF' dataField = pivotTable.AddDataField(pivotTable.PivotFields('CA')) dataField.NumberFormat = '# ### €' calculField = pivotTable.AddDataField(pivotTable.PivotFields('CA Bonifié')) calculField.NumberFormat = '# ### €' # wb.SaveCopyAs('C:/Users/Hakim C/Documents/Python/tcd.xlsx') # wb.Close(True) # Excel.Application.Quit()