from openpyxl import Workbook
import openpyxl
import os


# pip install  openpyxl==2.6.4

# Copy range of cells as a nested list
# Takes: start cell, end cell, and sheet you want to copy from.
def copy_range(start_col, start_row, end_col, end_row, sheet):
    range_selected = []
    # Loops through selected Rows
    for i in range(start_row, end_row + 1, 1):
        # Appends the row to a RowSelected list
        row_selected = []
        for j in range(start_col, end_col + 1, 1):
            row_selected.append(sheet.cell(row=i, column=j).value)
        # Adds the RowSelected List and nests inside the rangeSelected
        range_selected.append(row_selected)

    return range_selected


# Paste range
# Paste data from copy_range into template sheet
def paste_range(start_col, start_row, end_col, end_row,
                sheet_receiving, copied_data):
    count_row = 0
    for i in range(start_row, end_row + 1, 1):
        count_col = 0
        for j in range(start_col, end_col + 1, 1):
            sheet_receiving.cell(row=i, column=j).value = \
                copied_data[count_row][count_col]

            count_col += 1
        count_row += 1


def main():
    # output sheet
    output = Workbook()
    output.save("result.xlsx")
    output = openpyxl.load_workbook("result.xlsx")  # Add file name
    output_sheet = output.active

    for i in range(2, 13):
        input = openpyxl.load_workbook("{}.xlsx".format(str(i)),
                                       data_only=True,
                                       read_only=True)
        sheets = input.sheetnames
        sheet = input[sheets[1]]  # select second sheet

        # ==========================
        # name
        # ==========================
        name_col_offset = 2
        name_row_offset = 18
        selected_range = copy_range(start_col=2,
                                    start_row=3,
                                    end_col=2 + name_col_offset,
                                    end_row=3 + name_row_offset,
                                    sheet=sheet)
        paste_range(start_col=1,
                    start_row=-50 + 30 * i,
                    end_col=1 + name_col_offset,
                    end_row=-50 + 30 * i + name_row_offset,
                    sheet_receiving=output_sheet,
                    copied_data=selected_range)

        # ==========================
        #  data
        # ==========================

        data_col_offset = 13
        data_row_offset = 20

        # handle month >=5 excel
        if i >= 5:
            start_col = 8
        else:
            start_col = 7
        selected_range = copy_range(start_col=start_col,
                                    start_row=3,
                                    end_col=start_col + data_col_offset,
                                    end_row=3 + data_row_offset,
                                    sheet=sheet)
        paste_range(start_col=4,
                    start_row=-50 + 30 * i,
                    end_col=4 + data_col_offset,
                    end_row=-50 + 30 * i + data_row_offset,
                    sheet_receiving=output_sheet,
                    copied_data=selected_range)

        output.save("result.xlsx")
        print str(i) + " is done"


if __name__ == '__main__':
    myfile = "result.xlsx"
    if os.path.isfile(myfile):
        os.remove(myfile)

    main()