from openpyxl import load_workbook

wb = load_workbook("/Users/aloua/Downloads/xyl/test1/stats.xlsx",  data_only=True)
ws = wb.active

originalsheet = wb['original']
copy = wb.copy_worksheet(originalsheet)

#originalsheet = wb.get_sheet_by_name('original')

def caculatesum(type, year, countryname):
    diarysum = 0
    for row in range(1,copy.max_row + 1):
        curtype = copy.cell(row, 3).value
        curyear = copy.cell(row, 4).value
        curcountryname = copy.cell(row, 1).value
        if curyear == year and curtype == type and curcountryname == countryname:
            diarysum += copy.cell(row, 5).value
    return diarysum


countries = []

for country in originalsheet['A']:
    if country.value not in countries:
        countries.append(country.value)
print countries

sheetlist = []
for country in countries:
    newsheet = wb.create_sheet(country)
    sheetlist.append(newsheet)


# print wb.sheetnames

types = []

for type in originalsheet['C']:
    if type.value not in types:
        types.append(type.value)
print types

for sheet in sheetlist:
    print 'get information for {0}'.format(sheet.title)
    startRow = 1
    startCol = 1
    sheet.cell(startRow, startCol).value = sheet.title
    startCol += 1
    for i,type in enumerate(types):
      sheet.cell(startRow, i + startCol).value = type
    startRow += 1
    for year in range(2007, 2018):
        j = 1
        sheet.cell(startRow, j).value = year
        j += 1
        for i,type in enumerate(types):
          sheet.cell(startRow, i + j).value = caculatesum(type, year, sheet.title)
        startRow +=1

wb.save("/Users/aloua/Downloads/xyl/test1/test.xlsx")