import csv
import os
import xlrd
from xlwt import XFStyle
from xlrd import open_workbook
from xlwt import Workbook
from xlrd.sheet import empty_cell

rb = open_workbook('andmefail_Tooma_1924.xls',formatting_info=False)

nimi=os.path.basename('andmefail_Tooma_1924.xls')
#print (nimi)
eraldi=nimi.split("_")
#print (eraldi)
jaam=eraldi[1]
#print(jaam)

fmt = XFStyle()
fmt.num_format_str = '00'

for a, name in enumerate(rb.sheet_names()):

    rs = rb.sheet_by_index(a)
    new_book = Workbook()
    new_sheet = new_book.add_sheet('Sheet 1', cell_overwrite_ok=True)

    for col in range(1,(rs.nrows-4)):

        new_sheet.write(col-1,0,jaam)

    for col in range(rs.ncols)[:26]:
        for row in range(rs.nrows)[1:-4]:
            if col == 2 or col == 3:
                new_sheet.write(row, col, number, fmt)
            else:
                new_sheet.write(row, col, number)
            new_sheet.write(row-1, col+1, rs.cell(row, col).value)

    new_book.save(jaam +"_"+str(name) + ".xls")