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