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

# ExcelUp = win32c.xlUp
# lastrow = Sheet1.Cells(Sheet1.Rows.Count, 'A').End(ExcelUp).Row + 1
# monTableau=[]
# for i in range(1,lastrow):
	# if Sheet1.Range('A' + str(i)+':'+'D'+str(i)).Value is not None: #j'aimerais rendre cette étape =universelle
		# monTableau.append(list(Sheet1.Range('A' + str(i)+':'+'D'+str(i)).Value)) #idem

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
	
monTableau = getContiguousRange(fichier = wb, sheet = Sheet1, row = 1, col = 1)
		

cl1 = Sheet1.Cells(1,1)
# cl2 = Sheet1.Cells(len(monTableau),len(monTableau[0][0])) #eviter le [0][0]
cl2 = Sheet1.Cells(len(monTableau),len(monTableau[0]))

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'
# PivotTable.PivotFields('Trimestre').Orientation = win32c.xlColumnField 
# PivotTable.PivotFields('Trimestre').Position = 1
# PivotTable.PivotFields('Trimestre').Subtotals = [False, False, False, False, False, False, False, False, False, False, False, False]

DataField = PivotTable.AddDataField(PivotTable.PivotFields('CA'))
DataField.NumberFormat = '# ### €' #format

# wb.SaveCopyAs('C:/Users/Hakim C/Documents/Python/tcd.xlsx') 
# wb.Close(True)
# Excel.Application.Quit()