import openpyxl
from openpyxl.workbook import workbook
from openpyxl import load_workbook

wb = load_workbook('RCW.xlsx')
sheet = wb.sheetnames # adds sheet names to variable sheet
ws = wb[sheet[0]] # activates the first sheet

#Delete A-D
ws.delete_cols(1,4)
print('Columns A-D Deleted')
print('--------------------------------------------------')
#Delete K-X
ws.delete_cols(11,14)
print('Columns K-X Deleted')
print('--------------------------------------------------')
#Delete L-O
ws.delete_cols(12,4)
print('Columns L-O Deleted')
print('--------------------------------------------------')
#Delete N-P
ws.delete_cols(14,3)
print('Columns N-P Deleted')
print('--------------------------------------------------')
#Delete O-P
ws.delete_cols(15,2)
print('Columns O-P Deleted')
print('--------------------------------------------------')
#Delete P
ws.delete_cols(16,1)
print('Column P Deleted')
print('--------------------------------------------------')
#Delete R-AI
ws.delete_cols(18,18)
print('Columns R-AI Deleted')
print('--------------------------------------------------')
#Delete S-W
ws.delete_cols(19,5)
print('Columns S-W Deleted')
print('--------------------------------------------------')

print('Renaming Headings')
ws['N1'].value = 'Contract End'
# print(ws['AM1'].value)

ws['P1'].value = 'Has FTTP'
#print(ws['AR1'].value)

ws['Q1'].value = 'Greenfield'
#print(ws['AS1'].value)

ws['R1'].value = 'TV Customer'
#print(ws['BL1'].value)
print('--------------------------------------------------')
print('Moving Columns')
print('--------------------------------------------------')
ws.insert_cols(1, amount=1)

col_p = ws['P']
for idx, cell in enumerate(col_p,1):
    ws.cell(row = idx, column = 1).value = cell.value

ws.delete_cols(16, 1)

ws.insert_cols(15, amount=2)

col_fttp = ws['R']
for idx, cell in enumerate(col_fttp,1):
    ws.cell(row = idx, column = 15).value = cell.value

col_greenfield = ws['S']
for idx, cell in enumerate(col_greenfield, 1):
    ws.cell(row=idx, column=16).value = cell.value
print('Cleaning Up')
ws.delete_cols(18, 2)
ws.delete_cols(19, 1)
print('--------------------------------------------------')
num_sheets = int(input("How many Sheets: "))
total_rows = ws.max_row-1 # counts the total rows with data
total = int(total_rows // num_sheets)
total_cols = ws.max_column
print('--------------------------------------------------')
#wb.save("RCW.xlsx")
print('Data Completed')
print('--------------------------------------------------')
print(f"You Have a Total of {total_rows} records\n")

print(f"You have {total} of records for each day")
print('--------------------------------------------------')
print('Splitting Data')


sheet_count = 0

while sheet_count < num_sheets:
        new_sheet = wb.create_sheet("Sheet_"+ str(x))
        sheet_count = sheet_count + 1
        total_to_copy = total
        row_offset = 0
        for i in range(1, total_to_copy):
            for j in range(1, ws.max_column):
                new_sheet.cell(row=i, column=j).value = ws.cell(row=i, column=j).value
                row_offset = total_to_copy + 1


wb.save("RCW.xlsx")