def prepare_emp_sheet(emp1, sheet_key, key, name, fathername): sheet1 = formHfile.copy_worksheet(sheetformh) sheet1.title = sheet_key lastline = sheet1['B18'].value sheet1['B18'] = '' if len(emp1) > 3: lastlinerow = 'B' + str(18 + len(emp1)) else: lastlinerow = 'B18' logging.info(lastlinerow) sheet1[lastlinerow] = lastline from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(emp1, index=False, header=False) for r_idx, row in enumerate(rows, 14): for c_idx, value in enumerate(row, 2): sheet1.cell(row=r_idx, column=c_idx, value=value) sheet1['H5'] = key sheet1['F7'] = name sheet1['F8'] = fathername sheet1.sheet_properties.pageSetUpPr.fitToPage = True
def From_XII(): formXIIfilepath = os.path.join(Goafilespath, 'Form XII Register of leave.xlsx') formXIIfile = load_workbook(filename=formXIIfilepath) logging.info('Form XII file has sheet: ' + str(formXIIfile.sheetnames)) #print(formXIIfile.sheetnames) logging.info('create columns which are now available') data_formXII = data.copy()
def create_formXIX(): formXIXfilepath = os.path.join(karnatakafilespath, 'FormXIX.xlsx') formXIXfile = load_workbook(filename=formXIXfilepath) logging.info('Form XIX file has sheet: ' + str(formXIXfile.sheetnames)) sheetformXIX = formXIXfile['FORM XIX'] logging.info('create columns which are now available') data_formXIX = data.copy() emp_count = len(data_formXIX.index) for i in range(0, emp_count): key = (data_formXIX).iloc[i]['Employee Code'] sheet_key = 'FORM XIX_' + str(key) emp_data = (data_formXIX).iloc[i] emp_data.fillna(value='', inplace=True) sheet1 = formXIXfile.copy_worksheet(sheetformXIX) sheet1.title = sheet_key sheet1['D7'] = contractor_name + ', ' + contractor_address sheet1['D8'] = emp_data['Unit'] + ', ' + emp_data['Branch'] sheet1['D9'] = emp_data['Unit'] + ', ' + emp_data['Branch'] sheet1['D10'] = emp_data['Unit'] + ', ' + emp_data['Branch'] sheet1['D11'] = emp_data['Employee Name'] sheet1['D12'] = emp_data['Gender'] sheet1['D13'] = month + '-' + str(year) sheet1['D14'] = key sheet1['D15'] = emp_data['Days Paid'] sheet1['D16'] = emp_data['Earned Basic'] sheet1['D17'] = emp_data['HRA'] sheet1['D18'] = emp_data['Tel and Int Reimb'] sheet1['D19'] = emp_data['Bonus'] sheet1['D20'] = emp_data['Fuel Reimb'] sheet1['D21'] = emp_data['Corp Attire Reimb'] sheet1['D22'] = emp_data['CCA'] sheet1['D23'] = emp_data['Other Earning'] sheet1['D24'] = emp_data['Total Earning'] sheet1['D25'] = emp_data['Insurance'] sheet1['D26'] = emp_data['P.Tax'] sheet1['D27'] = emp_data['TDS'] sheet1['D28'] = emp_data['Total Deductions'] sheet1['D29'] = emp_data['Net Paid'] formXIXfinalfile = os.path.join(filelocation, 'FormXIX.xlsx') formXIXfile.remove(sheetformXIX) formXIXfile.save(filename=formXIXfinalfile)
def create_ecard(): ecardfilepath = os.path.join(karnatakafilespath, 'Employment card.xlsx') ecardfile = load_workbook(filename=ecardfilepath) logging.info('Employment card file has sheet: ' + str(ecardfile.sheetnames)) sheetecard = ecardfile['Employment card'] logging.info('create columns which are now available') data_ecard = data.copy() emp_count = len(data_ecard.index) for i in range(0, emp_count): key = (data_ecard).iloc[i]['Employee Code'] sheet_key = 'Employment card_' + str(key) emp_data = (data_ecard).iloc[i] emp_data.fillna(value='', inplace=True) sheet1 = ecardfile.copy_worksheet(sheetecard) sheet1.title = sheet_key sheet1['B4'] = contractor_name sheet1['B5'] = '' sheet1['B6'] = '' sheet1['B7'] = '' sheet1['B8'] = emp_data['Department'] sheet1['B9'] = contractor_address sheet1['B10'] = emp_data['Unit'] sheet1['B11'] = emp_data['Registration_no'] sheet1['B12'] = '' sheet1['B13'] = '' sheet1['B14'] = emp_data['Employee Name'] sheet1['B15'] = emp_data['Aadhar Number'] sheet1['B16'] = emp_data['Mobile Tel No.'] sheet1['B17'] = key sheet1['B18'] = emp_data['Designation'] sheet1['B19'] = emp_data['Net Paid'] sheet1['B20'] = emp_data['Date Joined'] ecardfinalfile = os.path.join(filelocation, 'Employment card.xlsx') ecardfile.remove(sheetecard) ecardfile.save(filename=ecardfinalfile)
def Form_II(): formIIfilepath = os.path.join( Goafilespath, 'Form II register of damage or loss.xlsx') formIIfile = load_workbook(filename=formIIfilepath) logging.info('Form II file has sheet: ' + str(formIIfile.sheetnames)) logging.info('create columns which are now available') data_formII = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Department", "attendancefile", "damage_or_loss", "whether_work_showed_cause", "Date of payment ", "num_instalments", "Date of payment ", "remarks" ] data_formII['S.no'] = list(range(1, len(data_formII) + 1)) data_formII["attendancefile"] = "confusing mapping" data_formII[[ "damage_or_loss", "whether_work_showed_cause", "num_instalments", "remarks" ]] = "-----" formII_data = data_formII[columns] formIIsheet = formIIfile['Sheet1'] formIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form II is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formII_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 10): for c_idx, value in enumerate(row, 1): formIIsheet.cell(row=r_idx, column=c_idx, value=value) formIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) formIIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formIIsheet[ 'A4'] = formIIsheet['A4'].value + " : " + data_formII['Unit'][0] formIIfinalfile = os.path.join( filelocation, 'Form II register of damage or loss.xlsx') formIIfile.save(filename=formIIfinalfile)
def Form_XXIII(): formXXIIIfilepath = os.path.join(Goafilespath, 'Form XXIII Register of wages.xlsx') formXXIIIfile = load_workbook(filename=formXXIIIfilepath) logging.info('Form XXIII file has sheet: ' + str(formXXIIIfile.sheetnames)) logging.info('create columns which are now available') data_formXXIII = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Designation", 'Basic', 'Dearness_Allowance', 'Earned Basic', 'Dearness_Allowance_2', 'Other Allowance', 'Overtime', 'FIXED MONTHLY GROSS', 'Salary Advance', 'PF', 'Other Deduction', 'Total Deductions', 'Net Paid', "sign", 'Date of payment ' ] data_formXXIII["sign"] = "" data_formXXIII[[ "Basic", 'Dearness_Allowance', 'Dearness_Allowance_2', "remarks", "Date_of_appoinment" ]] = "didn't get mapping" data_formXXIII['S.no'] = list(range(1, len(data_formXXIII) + 1)) formXXIII_data = data_formXXIII[columns] formXXIIIsheet = formXXIIIfile['Sheet1'] formXXIIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXIII is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXIII_data, index=False, header=False) logging.info('rows taken out from data') formXXIIIsheet.unmerge_cells('P15:R15') for r_idx, row in enumerate(rows, 10): for c_idx, value in enumerate(row, 1): #formXXIIIsheet.cell(row=r_idx, column=c_idx).value=value formXXIIIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) formXXIIIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formXXIIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formXXIIIsheet['P' + str(len(list(rows)) + 10 + 5)].value = "Signature of Employer" formXXIIIsheet.merge_cells('P' + str(len(list(rows)) + 10 + 5) + ':R' + str(len(list(rows)) + 10 + 5)) formXXIIIsheet['P4'] = formXXIIIsheet['P4'].value + " " + str( data_formXXIII['Registration_no'].unique()[0]) formXXIIIsheet['P5'] = formXXIIIsheet['P5'].value + " " + month formXXIIIsheet['A4'] = formXXIIIsheet['A4'].value + " " + str( data_formXXIII['Unit'].unique()[0]) formXXIIIsheet['A5'] = formXXIIIsheet['A5'].value + " " + str( data_formXXIII['Unit'].unique()[0]) + ", " + str( data_formXXIII['Address'].unique()[0]) formXXIIIfinalfile = os.path.join(filelocation, 'Form XXIII Register of wages.xlsx') formXXIIIfile.save(filename=formXXIIIfinalfile)
def create_form_XX(): formXXfilepath = os.path.join(karnatakafilespath, 'FormXX.xlsx') formXXfile = load_workbook(filename=formXXfilepath) logging.info('Form XX file has sheet: ' + str(formXXfile.sheetnames)) logging.info('create columns which are now available') data_formXX = data.copy() data_formXX['S.no'] = list(range(1, len(data_formXX) + 1)) data_formXX['a'] = '---' data_formXX['b'] = '---' data_formXX['c'] = '---' data_formXX['d'] = '---' data_formXX['e'] = '---' data_formXX['f'] = '---' data_formXX['g'] = '---' data_formXX['h'] = '---' data_formXX['i'] = '' formXX_columns = [ 'S.no', 'Employee Name', "Father's Name", 'Designation', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i' ] formXX_data = data_formXX[formXX_columns] formXXsheet = formXXfile['FORM XX'] formXXsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XX is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXX_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 15): for c_idx, value in enumerate(row, 3): formXXsheet.cell(row=r_idx, column=c_idx, value=value) formXXsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formXXsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formXXsheet['C6'].value C6_data = contractline + ' ' + contractor_name + ', ' + contractor_address formXXsheet['C6'] = C6_data locationline = formXXsheet['C7'].value C7_data = locationline + ' ' + data_formXX['Unit'][ 0] + ', ' + data_formXX['Branch'][0] formXXsheet['C7'] = C7_data establine = formXXsheet['C8'].value C8_data = establine + ' ' + data_formXX['Unit'][ 0] + ', ' + data_formXX['Branch'][0] formXXsheet['C8'] = C8_data peline = formXXsheet['C9'].value C9_data = peline + ' ' + data_formXX['Unit'][0] + ', ' + data_formXX[ 'Branch'][0] formXXsheet['C9'] = C9_data formXXfinalfile = os.path.join(filelocation, 'FormXX.xlsx') formXXfile.save(filename=formXXfinalfile)
def Form_XXI(): formXXIfilepath = os.path.join(Goafilespath, 'Form XXI Register of Employment.xlsx') formXXIfile = load_workbook(filename=formXXIfilepath) logging.info('Form XXI file has sheet: ' + str(formXXIfile.sheetnames)) logging.info('create columns which are now available') data_formXXI = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Designation", "Date_of_appoinment" ] data_formXXI_columns = list(data_formXXI.columns) start = data_formXXI_columns.index('Arrears salary') end = data_formXXI_columns.index('Total\r\nDP') columns.extend(data_formXXI_columns[start + 1:end]) less = 31 - len(data_formXXI_columns[start + 1:end]) for i in range(less): columns.extend(["less" + str(i + 1)]) data_formXXI["less" + str(i + 1)] = "" columns.extend(["normal_hours", 'Overtime', "remarks"]) data_formXXI[["normal_hours", "remarks", "Date_of_appoinment"]] = "didn't get mapping" data_formXXI['S.no'] = list(range(1, len(data_formXXI) + 1)) formXXI_data = data_formXXI[columns] formXXIsheet = formXXIfile['Sheet1'] formXXIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXI is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXI_data, index=False, header=False) logging.info('rows taken out from data') formXXIsheet.unmerge_cells('A23:E23') for r_idx, row in enumerate(rows, 14): for c_idx, value in enumerate(row, 1): formXXIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) formXXIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formXXIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formXXIsheet['AE4'] = formXXIsheet['AE4'].value + " " + str( data_formXXI['Registration_no'].unique()[0]) formXXIsheet['A4'] = formXXIsheet['A4'].value + " " + str( data_formXXI['Unit'].unique()[0]) + ", " + str( data_formXXI['Location'].unique()[0]) formXXIsheet['A5'] = formXXIsheet['A5'].value + " " + str( data_formXXI['Unit'].unique()[0]) + ", " + str( data_formXXI['Location'].unique()[0]) formXXIfinalfile = os.path.join(filelocation, 'Form XXI register of Over time.xlsx') formXXIfile.save(filename=formXXIfinalfile)
def Form_VIII(): formVIIIfilepath = os.path.join( Goafilespath, 'Form VIII register of Over time.xlsx') formVIIIfile = load_workbook(filename=formVIIIfilepath) logging.info('Form VIII file has sheet: ' + str(formVIIIfile.sheetnames)) logging.info('create columns which are now available') data_formVIII = data.copy() data_formVIII['Designation_Dept'] = data_formVIII[ "Designation"] + "_" + data_formVIII["Department"] columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Designation_Dept", "attendancefile", "extent_of_overtime", "total_overtime", 'Normal hrs ', 'FIXED MONTHLY GROSS', "overtime_rate", 'Overtime', "ot", 'CHECK CTC Gross', 'Date of payment ' ] data_formVIII['S.no'] = list(range(1, len(data_formVIII) + 1)) data_formVIII[[ "overtime_rate", "attendancefile", "overtime_rate", "ot" ]] = "Didn't find" data_formVIII[["extent_of_overtime", "total_overtime"]] = "----" formVIII_data = data_formVIII[columns] formVIIIsheet = formVIIIfile['Sheet1'] formVIIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form VIII is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formVIII_data, index=False, header=False) logging.info('rows taken out from data') row_copy = dataframe_to_rows(formVIII_data, index=False, header=False) for i in range(len(list(row_copy)) - 2): i += 12 formVIIIsheet.merge_cells('C' + str(i) + ':D' + str(i)) formVIIIsheet.merge_cells('F' + str(i) + ':H' + str(i)) formVIIIsheet.merge_cells('I' + str(i) + ':K' + str(i)) formVIIIsheet.merge_cells('L' + str(i) + ':N' + str(i)) formVIIIsheet.merge_cells('O' + str(i) + ':R' + str(i)) formVIIIsheet.merge_cells('S' + str(i) + ':T' + str(i)) formVIIIsheet.merge_cells('U' + str(i) + ':V' + str(i)) formVIIIsheet.merge_cells('W' + str(i) + ':X' + str(i)) formVIIIsheet.merge_cells('Y' + str(i) + ':Z' + str(i)) formVIIIsheet.merge_cells('AA' + str(i) + ':AB' + str(i)) formVIIIsheet.merge_cells('AC' + str(i) + ':AD' + str(i)) formVIIIsheet.merge_cells('AE' + str(i) + ':AG' + str(i)) c_idx = 0 for r_idx, row in enumerate(rows, 10): row_iterator = zip(row) while True: c_idx += 1 if type(formVIIIsheet.cell( row=r_idx, column=c_idx)).__name__ == 'MergedCell': continue try: value = next(row_iterator)[0] except: c_idx = 0 break formVIIIsheet.cell(row=r_idx, column=c_idx, value=value) formVIIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) formVIIIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formVIIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formVIIIfinalfile = os.path.join( filelocation, 'Form VIII register of Over time.xlsx') formVIIIfile.save(filename=formVIIIfinalfile) formVIIIsheet['Q4'] = "Month ending " + month + " " + str(year) formVIIIfinalfile = os.path.join( filelocation, 'Form VIII register of Over time.xlsx') formVIIIfile.save(filename=formVIIIfinalfile)
def Goa(data, contractor_name, contractor_address, filelocation, month, year): Goafilespath = os.path.join(Statefolder, 'Goa') logging.info('Goa files path is :' + str(Goafilespath)) data.reset_index(drop=True, inplace=True) month_num = monthdict[month] def Form_I(): formIfilepath = os.path.join(Goafilespath, 'Form I register of Fine.xlsx') formIfile = load_workbook(filename=formIfilepath) logging.info('Form I file has sheet: ' + str(formIfile.sheetnames)) logging.info('create columns which are now available') data_formI = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Department", "name&date_of_offence", "cause_against_fine", "FIXED MONTHLY GROSS", "Date of payment ", "Date of payment ", "remarks" ] data_formI['S.no'] = list(range(1, len(data_formI) + 1)) data_formI[["name&date_of_offence", "cause_against_fine", "remarks"]] = "-----" formI_data = data_formI[columns] formIsheet = formIfile['Sheet1'] formIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form I is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formI_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 10): for c_idx, value in enumerate(row, 1): formIsheet.cell(row=r_idx, column=c_idx, value=value) formIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) formIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formIsheet[ 'A4'] = formIsheet['A4'].value + " : " + data_formI['Unit'][0] formIfinalfile = os.path.join(filelocation, 'Form I register of Fine.xlsx') formIfile.save(filename=formIfinalfile) def Form_II(): formIIfilepath = os.path.join( Goafilespath, 'Form II register of damage or loss.xlsx') formIIfile = load_workbook(filename=formIIfilepath) logging.info('Form II file has sheet: ' + str(formIIfile.sheetnames)) logging.info('create columns which are now available') data_formII = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Department", "attendancefile", "damage_or_loss", "whether_work_showed_cause", "Date of payment ", "num_instalments", "Date of payment ", "remarks" ] data_formII['S.no'] = list(range(1, len(data_formII) + 1)) data_formII["attendancefile"] = "confusing mapping" data_formII[[ "damage_or_loss", "whether_work_showed_cause", "num_instalments", "remarks" ]] = "-----" formII_data = data_formII[columns] formIIsheet = formIIfile['Sheet1'] formIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form II is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formII_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 10): for c_idx, value in enumerate(row, 1): formIIsheet.cell(row=r_idx, column=c_idx, value=value) formIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) formIIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formIIsheet[ 'A4'] = formIIsheet['A4'].value + " : " + data_formII['Unit'][0] formIIfinalfile = os.path.join( filelocation, 'Form II register of damage or loss.xlsx') formIIfile.save(filename=formIIfinalfile) def Form_VIII(): formVIIIfilepath = os.path.join( Goafilespath, 'Form VIII register of Over time.xlsx') formVIIIfile = load_workbook(filename=formVIIIfilepath) logging.info('Form VIII file has sheet: ' + str(formVIIIfile.sheetnames)) logging.info('create columns which are now available') data_formVIII = data.copy() data_formVIII['Designation_Dept'] = data_formVIII[ "Designation"] + "_" + data_formVIII["Department"] columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Designation_Dept", "attendancefile", "extent_of_overtime", "total_overtime", 'Normal hrs ', 'FIXED MONTHLY GROSS', "overtime_rate", 'Overtime', "ot", 'CHECK CTC Gross', 'Date of payment ' ] data_formVIII['S.no'] = list(range(1, len(data_formVIII) + 1)) data_formVIII[[ "overtime_rate", "attendancefile", "overtime_rate", "ot" ]] = "Didn't find" data_formVIII[["extent_of_overtime", "total_overtime"]] = "----" formVIII_data = data_formVIII[columns] formVIIIsheet = formVIIIfile['Sheet1'] formVIIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form VIII is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formVIII_data, index=False, header=False) logging.info('rows taken out from data') row_copy = dataframe_to_rows(formVIII_data, index=False, header=False) for i in range(len(list(row_copy)) - 2): i += 12 formVIIIsheet.merge_cells('C' + str(i) + ':D' + str(i)) formVIIIsheet.merge_cells('F' + str(i) + ':H' + str(i)) formVIIIsheet.merge_cells('I' + str(i) + ':K' + str(i)) formVIIIsheet.merge_cells('L' + str(i) + ':N' + str(i)) formVIIIsheet.merge_cells('O' + str(i) + ':R' + str(i)) formVIIIsheet.merge_cells('S' + str(i) + ':T' + str(i)) formVIIIsheet.merge_cells('U' + str(i) + ':V' + str(i)) formVIIIsheet.merge_cells('W' + str(i) + ':X' + str(i)) formVIIIsheet.merge_cells('Y' + str(i) + ':Z' + str(i)) formVIIIsheet.merge_cells('AA' + str(i) + ':AB' + str(i)) formVIIIsheet.merge_cells('AC' + str(i) + ':AD' + str(i)) formVIIIsheet.merge_cells('AE' + str(i) + ':AG' + str(i)) c_idx = 0 for r_idx, row in enumerate(rows, 10): row_iterator = zip(row) while True: c_idx += 1 if type(formVIIIsheet.cell( row=r_idx, column=c_idx)).__name__ == 'MergedCell': continue try: value = next(row_iterator)[0] except: c_idx = 0 break formVIIIsheet.cell(row=r_idx, column=c_idx, value=value) formVIIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) formVIIIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formVIIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formVIIIfinalfile = os.path.join( filelocation, 'Form VIII register of Over time.xlsx') formVIIIfile.save(filename=formVIIIfinalfile) formVIIIsheet['Q4'] = "Month ending " + month + " " + str(year) formVIIIfinalfile = os.path.join( filelocation, 'Form VIII register of Over time.xlsx') formVIIIfile.save(filename=formVIIIfinalfile) #Not complete since not all mapping is provided def From_XII(): formXIIfilepath = os.path.join(Goafilespath, 'Form XII Register of leave.xlsx') formXIIfile = load_workbook(filename=formXIIfilepath) logging.info('Form XII file has sheet: ' + str(formXIIfile.sheetnames)) #print(formXIIfile.sheetnames) logging.info('create columns which are now available') data_formXII = data.copy() #print(sorted(list(data_formXII.columns))) def Form_XXI(): formXXIfilepath = os.path.join(Goafilespath, 'Form XXI Register of Employment.xlsx') formXXIfile = load_workbook(filename=formXXIfilepath) logging.info('Form XXI file has sheet: ' + str(formXXIfile.sheetnames)) logging.info('create columns which are now available') data_formXXI = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Gender", "Designation", "Date_of_appoinment" ] data_formXXI_columns = list(data_formXXI.columns) start = data_formXXI_columns.index('Arrears salary') end = data_formXXI_columns.index('Total\r\nDP') columns.extend(data_formXXI_columns[start + 1:end]) less = 31 - len(data_formXXI_columns[start + 1:end]) for i in range(less): columns.extend(["less" + str(i + 1)]) data_formXXI["less" + str(i + 1)] = "" columns.extend(["normal_hours", 'Overtime', "remarks"]) data_formXXI[["normal_hours", "remarks", "Date_of_appoinment"]] = "didn't get mapping" data_formXXI['S.no'] = list(range(1, len(data_formXXI) + 1)) formXXI_data = data_formXXI[columns] formXXIsheet = formXXIfile['Sheet1'] formXXIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXI is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXI_data, index=False, header=False) logging.info('rows taken out from data') formXXIsheet.unmerge_cells('A23:E23') for r_idx, row in enumerate(rows, 14): for c_idx, value in enumerate(row, 1): formXXIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) formXXIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formXXIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formXXIsheet['AE4'] = formXXIsheet['AE4'].value + " " + str( data_formXXI['Registration_no'].unique()[0]) formXXIsheet['A4'] = formXXIsheet['A4'].value + " " + str( data_formXXI['Unit'].unique()[0]) + ", " + str( data_formXXI['Location'].unique()[0]) formXXIsheet['A5'] = formXXIsheet['A5'].value + " " + str( data_formXXI['Unit'].unique()[0]) + ", " + str( data_formXXI['Location'].unique()[0]) formXXIfinalfile = os.path.join(filelocation, 'Form XXI register of Over time.xlsx') formXXIfile.save(filename=formXXIfinalfile) def Form_XXIII(): formXXIIIfilepath = os.path.join(Goafilespath, 'Form XXIII Register of wages.xlsx') formXXIIIfile = load_workbook(filename=formXXIIIfilepath) logging.info('Form XXIII file has sheet: ' + str(formXXIIIfile.sheetnames)) logging.info('create columns which are now available') data_formXXIII = data.copy() columns = [ 'S.no', "Employee Name", "Father's Name", "Designation", 'Basic', 'Dearness_Allowance', 'Earned Basic', 'Dearness_Allowance_2', 'Other Allowance', 'Overtime', 'FIXED MONTHLY GROSS', 'Salary Advance', 'PF', 'Other Deduction', 'Total Deductions', 'Net Paid', "sign", 'Date of payment ' ] data_formXXIII["sign"] = "" data_formXXIII[[ "Basic", 'Dearness_Allowance', 'Dearness_Allowance_2', "remarks", "Date_of_appoinment" ]] = "didn't get mapping" data_formXXIII['S.no'] = list(range(1, len(data_formXXIII) + 1)) formXXIII_data = data_formXXIII[columns] formXXIIIsheet = formXXIIIfile['Sheet1'] formXXIIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXIII is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXIII_data, index=False, header=False) logging.info('rows taken out from data') formXXIIIsheet.unmerge_cells('P15:R15') for r_idx, row in enumerate(rows, 10): for c_idx, value in enumerate(row, 1): #formXXIIIsheet.cell(row=r_idx, column=c_idx).value=value formXXIIIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) formXXIIIsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formXXIIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) formXXIIIsheet['P' + str(len(list(rows)) + 10 + 5)].value = "Signature of Employer" formXXIIIsheet.merge_cells('P' + str(len(list(rows)) + 10 + 5) + ':R' + str(len(list(rows)) + 10 + 5)) formXXIIIsheet['P4'] = formXXIIIsheet['P4'].value + " " + str( data_formXXIII['Registration_no'].unique()[0]) formXXIIIsheet['P5'] = formXXIIIsheet['P5'].value + " " + month formXXIIIsheet['A4'] = formXXIIIsheet['A4'].value + " " + str( data_formXXIII['Unit'].unique()[0]) formXXIIIsheet['A5'] = formXXIIIsheet['A5'].value + " " + str( data_formXXIII['Unit'].unique()[0]) + ", " + str( data_formXXIII['Address'].unique()[0]) formXXIIIfinalfile = os.path.join(filelocation, 'Form XXIII Register of wages.xlsx') formXXIIIfile.save(filename=formXXIIIfinalfile) Form_I() Form_II() Form_VIII() From_XII() Form_XXI() Form_XXIII()
def create_form_A(): formAfilepath = os.path.join(karnatakafilespath, 'FormA.xlsx') formAfile = load_workbook(filename=formAfilepath) logging.info('Form A file has sheet: ' + str(formAfile.sheetnames)) logging.info('create columns which are now available') data_formA = data.copy() data_formA['S.no'] = list(range(1, len(data_formA) + 1)) data_formA["Nationality"] = '' data_formA['education level'] = '' data_formA['Category address'] = '' data_formA['type of employment'] = '' data_formA['lwf'] = '' data_formA['Service Book No'] = '' formA_columns = [ "S.no", 'Employee Code', 'Employee Name', 'Unit', 'Location', "Gender", "Father's Name", 'Date of Birth', "Nationality", "education level", 'Date Joined', 'Designation', "Category address", "type of employment", 'Mobile Tel No.', 'UAN Number', "PAN Number", "ESIC Number", "lwf", "Aadhar Number", "Bank A/c Number", "Bank Name", "Account Code", "P", "L", "Service Book No", "Date Left", "Reason for Leaving" ] formA_data = data_formA[formA_columns] formAsheet = formAfile['FORM A'] formAsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form A is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formA_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 13): for c_idx, value in enumerate(row, 1): formAsheet.cell(row=r_idx, column=c_idx, value=value) formAsheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) formAsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formAsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) logging.info('') establishment = formAsheet['L6'].value L6_data = establishment + ' ' + data_formA['Unit'][ 0] + ', ' + data_formA['Branch'][0] formAsheet['L6'] = L6_data company = formAsheet['A10'].value A10_data = company + ' ' + data_formA['Unit'][0] + ', ' + data_formA[ 'Branch'][0] formAsheet['A10'] = A10_data formAfinalfile = os.path.join(filelocation, 'FormA.xlsx') formAfile.save(filename=formAfinalfile)
def create_form_B(): formBfilepath = os.path.join(karnatakafilespath, 'FormB.xlsx') formBfile = load_workbook(filename=formBfilepath) logging.info('Form B file has sheet: ' + str(formBfile.sheetnames)) logging.info('create columns which are now available') data_formB = data.copy() data_formB['OT hours'] = 0 data_formB['Pay OT'] = 0 data_formB['basic_and_allo'] = data_formB['Earned Basic'] + data_formB[ 'Other Allowance'] + data_formB['Meal Allowance'] + data_formB[ 'Special Allowance'] + data_formB['Personal Allowance'] data_formB['Other EAR'] = data_formB['Other Reimb'] + data_formB[ 'Arrears'] + data_formB['Other Earning'] + data_formB[ 'Variable Pay'] + data_formB['Stipend'] + data_formB[ 'Consultancy Fees'] data_formB['VPF'] = 0 data_formB['Income Tax'] = 0 data_formB['EMP PF'] = data_formB['PF'] data_formB['BankID'] = '' data_formB['Pay Date'] = '' data_formB['Remarks'] = '' formB_columns = [ 'Employee Code', 'Employee Name', 'FIXED MONTHLY GROSS', 'Days Paid', 'OT hours', 'basic_and_allo', 'Pay OT', 'HRA', 'Tel and Int Reimb', 'Bonus', 'Fuel Reimb', 'Prof Dev Reimb', 'Corp Attire Reimb', 'CCA', 'Leave Encashment', 'Other EAR', 'Total Earning', 'PF', 'ESIC', 'VPF', 'Loan Deduction', 'Loan Interest', 'P.Tax', 'CSR', 'Income Tax', 'Insurance', 'LWF EE', 'Other Deduction', 'TDS', 'Total Deductions', 'Net Paid', 'EMP PF', 'BankID', 'Pay Date', 'Remarks' ] formB_data = data_formB[formB_columns] formBsheet = formBfile['FORM B'] formBsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form B is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formB_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 21): for c_idx, value in enumerate(row, 2): formBsheet.cell(row=r_idx, column=c_idx, value=value) formBsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formBsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formBsheet['B10'].value B10_data = contractline + ' ' + contractor_name + ', ' + contractor_address formBsheet['B10'] = B10_data locationline = formBsheet['B11'].value B11_data = locationline + ' ' + data_formB['Unit'][ 0] + ', ' + data_formB['Branch'][0] formBsheet['B11'] = B11_data establine = formBsheet['B12'].value B12_data = establine + ' ' + data_formB['Unit'][0] + ', ' + data_formB[ 'Branch'][0] formBsheet['B12'] = B12_data peline = formBsheet['B13'].value B13_data = peline + ' ' + data_formB['Unit'][0] + ', ' + data_formB[ 'Branch'][0] formBsheet['B13'] = B13_data monthstart = datetime.date(year, month_num, 1) monthend = datetime.date(year, month_num, calendar.monthrange(year, month_num)[1]) formBsheet['B16'] = 'Wage period From: ' + str( monthstart) + ' to ' + str(monthend) formBfinalfile = os.path.join(filelocation, 'FormB.xlsx') formBfile.save(filename=formBfinalfile)
def Tamilnadu(data,contractor_name,contractor_address,filelocation,month,year): tnfilespath = os.path.join(Statefolder,'Tamilnadu') logging.info('Tamilnadu files path is :'+ str(tnfilespath)) data.reset_index(drop=True, inplace=True) month_num = monthdict[month] print("inside") def Form_p(): # Importing file path of Form P formPFilePath = os.path.join(tnfilespath,'Form P register of deduction.xlsx') formPFile = load_workbook(filename = formPFilePath) logging.info('Form P file has sheet: ' + str(formPFile.sheetnames)) logging.info('create columns which are now available') # Preparing Master data for form P with columns required. data_formP = data.copy() ## Adding placeholder columns where data isnot found data_formP['S.no'] = list(range(1,len(data_formP)+1)) colToAdd = ['Number of Instalments to be recovered', 'Date on which recovery completed','Date of Show Cause Notice' ,'Date on which deduction completed','Act or omission','Date on which fine recovery completed' ,'Signature or thumb impression of the person employed'] data_formP = data_formP.reindex(data_formP.columns.tolist() + colToAdd, axis=1) data_formP.loc[:,colToAdd] = '----' ## Appending columns in order required for FORM P columns = ['S.no','Employee Name','Father\'s Name','Employee Code','Designation','Date of payment ', 'Net Paid','Number of Instalments to be recovered','Date on which recovery completed', 'Damage or Loss','Date of Show Cause Notice','Total Deductions','Number of Instalments to be recovered', 'Date on which deduction completed','Act or omission','Date of Show Cause Notice','Fine', 'Date on which fine recovery completed','Signature or thumb impression of the person employed','Remarks'] formP_data = data_formP[columns] formPsheet = formPFile['Sheet1'] formPsheet.sheet_properties.pageSetUpPr.fitToPage = True print("Here") logging.info('data for form P is ready') # Load data into rows of Form P rows = dataframe_to_rows(formP_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 8): for c_idx, value in enumerate(row, 1): formPsheet.cell(row=r_idx, column=c_idx, value = value) formPsheet.cell(row=r_idx, column=c_idx).fill = PatternFill(fill_type=None) formPsheet.cell(row=r_idx, column=c_idx).font = Font(name ='Bell MT', size = 10) formPsheet.cell(row=r_idx, column=c_idx).alignment = Alignment(horizontal='center', vertical='center', wrap_text = True) border_sides = Side(style='thin') formPsheet.cell(row=r_idx, column=c_idx).border = Border(outline= True, right=border_sides, bottom=border_sides) # Adding Unitfile Unitname and address formPsheet.cell(row=5, column=4, value = 'PLACEHOLDER') # Add variable that needs to be added formPsheet.merge_cells('D5:G5') # Formatting the table formPsheet.merge_cells('A5:C5') formPsheet.merge_cells('D5:T5') formPsheet.merge_cells('A4:T4') formPsheet.row_dimensions[5].height = 30 for row in formPsheet.iter_rows(): for cell in row: cell.alignment = cell.alignment.copy(wrapText=True) for row_id in range(1,7): for col_id in range(1,21): formPsheet.cell(row=row_id, column=col_id).border = Border(left=Side(style='thick'), right=Side(style='thick'), top=Side(style='thick'), bottom=Side(style='thick')) #formPsheet['A4'] = formPsheet['A4'].value + " : " + data_formP['Unit'][0] formPfinalfile = os.path.join(outputPath,'Form P register of deduction.xlsx') formPFile.save(filename = formPfinalfile) def Form_r(): form_data = data.copy() # will be passed inside the func # cls required for form R from main DF # whatever has "-" has no corresponding match # TODO: update as and when you h=get new cols colsRequired = {1:'Employee Name',2:'Gender',3:'Designation',4:'-', 5:'-',6:'Days Paid',7:'-',8:'-',9:'overtime rate',10:'-', 11:'-',12:'-',13:'-',14:'CHECK CTC Gross',15:'FIXED MONTHLY GROSS', 16:'-',17:'-',18:'-',19:'Fine',20:'Net Paid',21:'-',22:'-',} # load form formPath = os.path.join(tnfilespath,'Form R register of wages.xlsx') formfile = load_workbook(filename=formPath) # select sheet to write in and set page properties formsheet = formfile['Sheet1'] formsheet.sheet_properties.pageSetUpPr.fitToPage = True ## initialize variables rownum = 9 # starting row count pushDownIdx = 11 serialNum = 0 # populate ros with required employee information for idx in form_data.index: formsheet.cell(row= rownum, column=1, value=serialNum) for col_idx in colsRequired.keys(): col = colsRequired[col_idx] if col == "-": # if no value exisits populate = "-----" else: # fetch value from dataframe populate = form_data.loc[idx, col] # write to excel print("rownum",rownum, "column", col_idx+1) formsheet.cell(row= rownum, column=col_idx+1, value=populate) formsheet.cell(row= rownum, column=col_idx+1).font =Font(name ='Bell MT', size =10) formsheet.cell(row= rownum, column=col_idx+1).alignment = Alignment(horizontal='center', vertical='center', wrap_text = True) border_sides = Side(style='thin') formsheet.cell(row=rownum, column=col_idx +1).border = Border(outline= True, right=border_sides, bottom=border_sides) # format table for row_id in range(1,rownum+1): for col_id in range(1,24): formsheet.cell(row=row_id, column=col_id).border = Border(left=Side(style='medium'), right=Side(style='medium'), top=Side(style='medium'), bottom=Side(style='medium')) # insery rows to push down the note formsheet.insert_rows(idx=pushDownIdx, amount=1) rownum +=1 pushDownIdx+=1 serialNum +=1 # save file formIfinalfile = os.path.join(outputPath,'Form R register of wages.xlsx') formfile.save(filename=formIfinalfile) def Form_t():# - Form T wages slip form_data = data.copy() # will be passed inside the func # load form formPath = os.path.join(tnfilespath,'Form T wages slip.xlsx') formfile = load_workbook(filename=formPath) # select sheet to write in and set page properties formsheet = formfile['Sheet1'] formsheet.sheet_properties.pageSetUpPr.fitToPage = True # for each employee create a new sheet for idx in form_data.index: empName = form_data.loc[idx, "Employee Name"] print("Employee:", empName) # create a new sheet for employee new = formfile.copy_worksheet(formsheet) new.title = empName # popoluate every cell with required employee information new.cell(row= 4, column=3, value=form_data.loc[idx, "Company Name"])# Name of company new.cell(row= 5, column=3, value=form_data.loc[idx, "Employee Name"]) #. name of employee new.cell(row= 6, column=3, value=form_data.loc[idx, "Father's Name"]) #. Fathers name new.cell(row= 7, column=3, value=form_data.loc[idx, "Designation"]) # employee designation C7 new.cell(row= 8, column=3, value=form_data.loc[idx, "Date Joined"]) # date joined C8 new.cell(row= 11, column=2, value=form_data.loc[idx, "Earned Basic"]) # earned basic B11 new.cell(row= 13, column=2, value=form_data.loc[idx, "HRA"])# House rent allowance B13 new.cell(row= 14, column=2, value=form_data.loc[idx, "Overtime"]) # overtime wages B14 new.cell(row= 16, column=2, value=form_data.loc[idx,"Other Allowance"]) # otehr allowance B16 new.cell(row= 17, column=2, value=form_data.loc[idx, "FIXED MONTHLY GROSS"]) # Gross wages B17 new.cell(row= 12, column=9, value=form_data.loc[idx, "Insurance"]) # employee state insurance I12 new.cell(row= 13, column=9, value=form_data.loc[idx, "Other Deduction"]) # other deductions I13 new.cell(row= 17, column=7, value=form_data.loc[idx, "Net Paid"]) # net paid #G17 # format table A1 - K19 for row_id in range(1,20): for col_id in range(1,12): new.cell(row=row_id, column=col_id).border = Border(left=Side(style='medium'), right=Side(style='medium'), top=Side(style='medium'), bottom=Side(style='medium')) # delete sheet1 sheet2, sheet3 formfile.remove(formfile['Sheet1']) formfile.remove(formfile['Sheet2']) formfile.remove(formfile['Sheet3']) # save file formIfinalfile = os.path.join(outputPath,'Form T wages slip.xlsx') formfile.save(filename=formIfinalfile) ## --------FUNCTION CALL------------------------- Form_p() ## Call this function in the main def Form_r() Form_t()
def create_muster(): musterfilepath = os.path.join(karnatakafilespath, 'Muster.xlsx') musterfile = load_workbook(filename=musterfilepath) logging.info('muster file has sheet: ' + str(musterfile.sheetnames)) logging.info('create columns which are now available') data_muster = data.copy() data_muster['S.no'] = list(range(1, len(data_muster) + 1)) first3columns = ["S.no", 'Employee Code', 'Employee Name'] last2columns = ["Date Left", "Days Paid"] columnstotake = [] days = [ '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31' ] for day in days: for col in data_muster.columns: if col[5:7] == day: columnstotake.append(col) if len(columnstotake) == 28: columnstotake.append('29') columnstotake.append('30') columnstotake.append('31') data_muster['29'] = '' data_muster['30'] = '' data_muster['31'] = '' elif len(columnstotake) == 29: columnstotake.append('30') columnstotake.append('31') data_muster['30'] = '' data_muster['31'] = '' elif len(columnstotake) == 30: columnstotake.append('31') data_muster['31'] = '' muster_columns = first3columns + columnstotake + last2columns muster_data = data_muster[muster_columns] mustersheet = musterfile['Muster'] mustersheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for muster is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(muster_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 18): for c_idx, value in enumerate(row, 2): mustersheet.cell(row=r_idx, column=c_idx, value=value) mustersheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) mustersheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') mustersheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) logging.info('') contractline = mustersheet['B10'].value B10_data = contractline + ' ' + contractor_name + ', ' + contractor_address mustersheet['B10'] = B10_data locationline = mustersheet['B11'].value B11_data = locationline + ' ' + data_muster['Unit'][ 0] + ', ' + data_muster['Branch'][0] mustersheet['B11'] = B11_data establine = mustersheet['B12'].value B12_data = establine + ' ' + data_muster['Unit'][ 0] + ', ' + data_muster['Branch'][0] mustersheet['B12'] = B12_data peline = mustersheet['B13'].value B13_data = peline + ' ' + data_muster['Unit'][0] + ', ' + data_muster[ 'Branch'][0] mustersheet['B13'] = B13_data mustersheet[ 'B4'] = 'Combined Muster Roll-cum-Register of Wages in lieu of ' + month + ' ' + str( year) musterfinalfile = os.path.join(filelocation, 'Muster.xlsx') musterfile.save(filename=musterfinalfile)
def create_wages(): wagesfilepath = os.path.join(karnatakafilespath, 'Wages.xlsx') wagesfile = load_workbook(filename=wagesfilepath) logging.info('wages file has sheet: ' + str(wagesfile.sheetnames)) logging.info('create columns which are now available') data_wages = data.copy() data_wages['S.no'] = list(range(1, len(data_wages) + 1)) data_wages['Site Address'] = '' data_wages['OT hours'] = 0 data_wages['Conv'] = 0 data_wages['Sub Allow'] = 0 data_wages['Fines'] = 0 data_wages['Damages'] = 0 data_wages['Pay mode'] = '' data_wages['Remarks'] = '' wages_columns = [ 'S.no', 'Employee Code', 'Employee Name', 'Gender', 'Designation', 'Department', 'Site Address', 'Date Joined', 'ESIC Number', 'PF Number', 'FIXED MONTHLY GROSS', 'Days Paid', 'OT hours', 'Earned Basic', 'HRA', 'Conv', 'Medical Allowance', 'Tel and Int Reimb', 'Bonus', 'Fuel Reimb', 'Prof Dev Reimb', 'Corp Attire Reimb', 'Special Allowance', 'CCA', 'Other Earning', 'Sub Allow', 'Leave Encashment', 'Total Earning', 'ESIC', 'PF', 'P.Tax', 'TDS', 'CSR', 'Insurance', 'Salary Advance', 'Fines', 'Damages', 'Other Deduction', 'Total Deductions', 'Net Paid', 'Pay mode', 'Bank A/c Number', 'Remarks' ] wages_data = data_wages[wages_columns] wagessheet = wagesfile['Wages'] wagessheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for wages is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(wages_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 21): for c_idx, value in enumerate(row, 2): wagessheet.cell(row=r_idx, column=c_idx, value=value) wagessheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') wagessheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = wagessheet['A10'].value A10_data = contractline + ' ' + contractor_name + ', ' + contractor_address wagessheet['A10'] = A10_data locationline = wagessheet['A11'].value A11_data = locationline + ' ' + data_wages['Unit'][ 0] + ', ' + data_wages['Branch'][0] wagessheet['A11'] = A11_data establine = wagessheet['A12'].value A12_data = establine + ' ' + data_wages['Unit'][0] + ', ' + data_wages[ 'Branch'][0] wagessheet['A12'] = A12_data peline = wagessheet['A13'].value A13_data = peline + ' ' + data_wages['Unit'][0] + ', ' + data_wages[ 'Branch'][0] wagessheet['A13'] = A13_data wagessheet[ 'F4'] = 'Combined Muster Roll-cum-Register of Wages in lieu of ' + month + ' ' + str( year) wagesfinalfile = os.path.join(filelocation, 'Wages.xlsx') wagesfile.save(filename=wagesfinalfile)
def attandance_data(employee_attendance, i): leavelist = list(employee_attendance.columns[( employee_attendance == 'PL').iloc[i]]) empcodeis = employee_attendance.iloc[i]['Employee Code'] logging.info(empcodeis) if 'Leave Type' in leavelist: leavelist.remove('Leave Type') emp1 = pd.DataFrame(leavelist) if len(emp1.index) == 0: defaultemp = { 'emp': (employee_attendance).iloc[i]['Employee Code'], 'startdate': 0, 'enddate': 0, 'days': 0, 'start_date': '-------', 'end_date': '-------' } emp1 = pd.DataFrame(defaultemp, index=[0]) emp1.index = np.arange(1, len(emp1) + 1) emp1['s.no'] = emp1.index emp1.reset_index(drop=True, inplace=True) emp1['from'] = datetime.date(year, month_num, 1) emp1['to'] = datetime.date( year, month_num, calendar.monthrange(year, month_num)[1]) emp1['totaldays'] = calendar.monthrange(year, month_num)[1] emp1['leavesearned'] = ( employee_attendance).iloc[i]['Monthly Increment'] emp1['leavesstart'] = float( employee_attendance.iloc[i]['Opening']) emp1['leavesend'] = emp1.leavesstart - emp1.days emp1 = emp1[[ "s.no", "from", "to", "totaldays", "leavesearned", "leavesstart", "start_date", "end_date", "days", "leavesend" ]] else: logging.info(emp1) emp1.columns = ['Leaves'] emp1['emp'] = (employee_attendance).iloc[i]['Employee Code'] emp1['Leavesdays'] = emp1.Leaves.str[5:7].astype(int) emp1['daysdiff'] = (emp1.Leavesdays.shift(-1) - emp1.Leavesdays).fillna(0).astype(int) emp1['startdate'] = np.where(emp1.daysdiff.shift() != 1, emp1.Leavesdays, 0) emp1['enddate'] = np.where(emp1.daysdiff != 1, emp1.Leavesdays, 0) emp1.drop(emp1[(emp1.startdate == 0) & (emp1.enddate == 0)].index, inplace=True) emp1['startdate'] = np.where(emp1.startdate == 0, emp1.startdate.shift(), emp1.startdate).astype(int) emp1['enddate'] = np.where(emp1.enddate == 0, emp1.enddate.shift(-1), emp1.enddate).astype(int) emp1 = emp1[['emp', 'startdate', 'enddate']] emp1.drop_duplicates(subset='startdate', inplace=True) emp1['days'] = emp1.enddate - emp1.startdate + 1 emp1['start_date'] = [ datetime.date(year, month_num, x) for x in emp1.startdate ] emp1['end_date'] = [ datetime.date(year, month_num, x) for x in emp1.enddate ] emp1.index = np.arange(1, len(emp1) + 1) emp1['s.no'] = emp1.index emp1.reset_index(drop=True, inplace=True) emp1['from'] = datetime.date(year, month_num, 1) emp1['to'] = datetime.date( year, month_num, calendar.monthrange(year, month_num)[1]) emp1['totaldays'] = calendar.monthrange(year, month_num)[1] emp1['leavesearned'] = ( employee_attendance).iloc[i]['Monthly Increment'] emp1['totalleaves'] = float( employee_attendance.iloc[i]['Opening']) emp1['cumdays'] = emp1['days'].cumsum() emp1['leavesend'] = emp1.totalleaves - emp1.cumdays emp1['leavesstart'] = emp1['totalleaves'] emp1 = emp1[[ "s.no", "from", "to", "totaldays", "leavesearned", "leavesstart", "start_date", "end_date", "days", "leavesend" ]] return emp1
def create_form_H_F(form): if form == 'FORM H': formHfilepath = os.path.join(karnatakafilespath, 'FormH.xlsx') if form == 'FORM F': formHfilepath = os.path.join(karnatakafilespath, 'FormF.xlsx') formHfile = load_workbook(filename=formHfilepath) logging.info('file has sheet: ' + str(formHfile.sheetnames)) sheetformh = formHfile[form] logging.info('create columns which are now available') data_formH = data.copy() def attandance_data(employee_attendance, i): leavelist = list(employee_attendance.columns[( employee_attendance == 'PL').iloc[i]]) empcodeis = employee_attendance.iloc[i]['Employee Code'] logging.info(empcodeis) if 'Leave Type' in leavelist: leavelist.remove('Leave Type') emp1 = pd.DataFrame(leavelist) if len(emp1.index) == 0: defaultemp = { 'emp': (employee_attendance).iloc[i]['Employee Code'], 'startdate': 0, 'enddate': 0, 'days': 0, 'start_date': '-------', 'end_date': '-------' } emp1 = pd.DataFrame(defaultemp, index=[0]) emp1.index = np.arange(1, len(emp1) + 1) emp1['s.no'] = emp1.index emp1.reset_index(drop=True, inplace=True) emp1['from'] = datetime.date(year, month_num, 1) emp1['to'] = datetime.date( year, month_num, calendar.monthrange(year, month_num)[1]) emp1['totaldays'] = calendar.monthrange(year, month_num)[1] emp1['leavesearned'] = ( employee_attendance).iloc[i]['Monthly Increment'] emp1['leavesstart'] = float( employee_attendance.iloc[i]['Opening']) emp1['leavesend'] = emp1.leavesstart - emp1.days emp1 = emp1[[ "s.no", "from", "to", "totaldays", "leavesearned", "leavesstart", "start_date", "end_date", "days", "leavesend" ]] else: logging.info(emp1) emp1.columns = ['Leaves'] emp1['emp'] = (employee_attendance).iloc[i]['Employee Code'] emp1['Leavesdays'] = emp1.Leaves.str[5:7].astype(int) emp1['daysdiff'] = (emp1.Leavesdays.shift(-1) - emp1.Leavesdays).fillna(0).astype(int) emp1['startdate'] = np.where(emp1.daysdiff.shift() != 1, emp1.Leavesdays, 0) emp1['enddate'] = np.where(emp1.daysdiff != 1, emp1.Leavesdays, 0) emp1.drop(emp1[(emp1.startdate == 0) & (emp1.enddate == 0)].index, inplace=True) emp1['startdate'] = np.where(emp1.startdate == 0, emp1.startdate.shift(), emp1.startdate).astype(int) emp1['enddate'] = np.where(emp1.enddate == 0, emp1.enddate.shift(-1), emp1.enddate).astype(int) emp1 = emp1[['emp', 'startdate', 'enddate']] emp1.drop_duplicates(subset='startdate', inplace=True) emp1['days'] = emp1.enddate - emp1.startdate + 1 emp1['start_date'] = [ datetime.date(year, month_num, x) for x in emp1.startdate ] emp1['end_date'] = [ datetime.date(year, month_num, x) for x in emp1.enddate ] emp1.index = np.arange(1, len(emp1) + 1) emp1['s.no'] = emp1.index emp1.reset_index(drop=True, inplace=True) emp1['from'] = datetime.date(year, month_num, 1) emp1['to'] = datetime.date( year, month_num, calendar.monthrange(year, month_num)[1]) emp1['totaldays'] = calendar.monthrange(year, month_num)[1] emp1['leavesearned'] = ( employee_attendance).iloc[i]['Monthly Increment'] emp1['totalleaves'] = float( employee_attendance.iloc[i]['Opening']) emp1['cumdays'] = emp1['days'].cumsum() emp1['leavesend'] = emp1.totalleaves - emp1.cumdays emp1['leavesstart'] = emp1['totalleaves'] emp1 = emp1[[ "s.no", "from", "to", "totaldays", "leavesearned", "leavesstart", "start_date", "end_date", "days", "leavesend" ]] return emp1 def prepare_emp_sheet(emp1, sheet_key, key, name, fathername): sheet1 = formHfile.copy_worksheet(sheetformh) sheet1.title = sheet_key lastline = sheet1['B18'].value sheet1['B18'] = '' if len(emp1) > 3: lastlinerow = 'B' + str(18 + len(emp1)) else: lastlinerow = 'B18' logging.info(lastlinerow) sheet1[lastlinerow] = lastline from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(emp1, index=False, header=False) for r_idx, row in enumerate(rows, 14): for c_idx, value in enumerate(row, 2): sheet1.cell(row=r_idx, column=c_idx, value=value) sheet1['H5'] = key sheet1['F7'] = name sheet1['F8'] = fathername sheet1.sheet_properties.pageSetUpPr.fitToPage = True emp_count = len(data_formH.index) emp_dic = dict() for i in range(0, emp_count): key = (data_formH).iloc[i]['Employee Code'] emp_dic[key] = attandance_data(data_formH, i) sheet_key = form + '_' + str(key) name = data_formH[data_formH['Employee Code'] == key]['Employee Name'].values[0] fathername = data_formH[data_formH['Employee Code'] == key]["Father's Name"].values[0] logging.info(name) logging.info(fathername) prepare_emp_sheet(emp_dic[key], sheet_key, key, name, fathername) logging.info(key) logging.info(sheet_key) if form == 'FORM H': formHfinalfile = os.path.join(filelocation, 'FormH.xlsx') if form == 'FORM F': formHfinalfile = os.path.join(filelocation, 'FormF.xlsx') formHfile.remove(sheetformh) formHfile.save(filename=formHfinalfile)
def Form_p(): # Importing file path of Form P formPFilePath = os.path.join(tnfilespath,'Form P register of deduction.xlsx') formPFile = load_workbook(filename = formPFilePath) logging.info('Form P file has sheet: ' + str(formPFile.sheetnames)) logging.info('create columns which are now available') # Preparing Master data for form P with columns required. data_formP = data.copy() ## Adding placeholder columns where data isnot found data_formP['S.no'] = list(range(1,len(data_formP)+1)) colToAdd = ['Number of Instalments to be recovered', 'Date on which recovery completed','Date of Show Cause Notice' ,'Date on which deduction completed','Act or omission','Date on which fine recovery completed' ,'Signature or thumb impression of the person employed'] data_formP = data_formP.reindex(data_formP.columns.tolist() + colToAdd, axis=1) data_formP.loc[:,colToAdd] = '----' ## Appending columns in order required for FORM P columns = ['S.no','Employee Name','Father\'s Name','Employee Code','Designation','Date of payment ', 'Net Paid','Number of Instalments to be recovered','Date on which recovery completed', 'Damage or Loss','Date of Show Cause Notice','Total Deductions','Number of Instalments to be recovered', 'Date on which deduction completed','Act or omission','Date of Show Cause Notice','Fine', 'Date on which fine recovery completed','Signature or thumb impression of the person employed','Remarks'] formP_data = data_formP[columns] formPsheet = formPFile['Sheet1'] formPsheet.sheet_properties.pageSetUpPr.fitToPage = True print("Here") logging.info('data for form P is ready') # Load data into rows of Form P rows = dataframe_to_rows(formP_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 8): for c_idx, value in enumerate(row, 1): formPsheet.cell(row=r_idx, column=c_idx, value = value) formPsheet.cell(row=r_idx, column=c_idx).fill = PatternFill(fill_type=None) formPsheet.cell(row=r_idx, column=c_idx).font = Font(name ='Bell MT', size = 10) formPsheet.cell(row=r_idx, column=c_idx).alignment = Alignment(horizontal='center', vertical='center', wrap_text = True) border_sides = Side(style='thin') formPsheet.cell(row=r_idx, column=c_idx).border = Border(outline= True, right=border_sides, bottom=border_sides) # Adding Unitfile Unitname and address formPsheet.cell(row=5, column=4, value = 'PLACEHOLDER') # Add variable that needs to be added formPsheet.merge_cells('D5:G5') # Formatting the table formPsheet.merge_cells('A5:C5') formPsheet.merge_cells('D5:T5') formPsheet.merge_cells('A4:T4') formPsheet.row_dimensions[5].height = 30 for row in formPsheet.iter_rows(): for cell in row: cell.alignment = cell.alignment.copy(wrapText=True) for row_id in range(1,7): for col_id in range(1,21): formPsheet.cell(row=row_id, column=col_id).border = Border(left=Side(style='thick'), right=Side(style='thick'), top=Side(style='thick'), bottom=Side(style='thick')) #formPsheet['A4'] = formPsheet['A4'].value + " : " + data_formP['Unit'][0] formPfinalfile = os.path.join(outputPath,'Form P register of deduction.xlsx') formPFile.save(filename = formPfinalfile)
def Karnataka(data, contractor_name, contractor_address, filelocation, month, year): karnatakafilespath = os.path.join(Statefolder, 'Karnataka') logging.info('karnataka files path is :' + str(karnatakafilespath)) data.reset_index(drop=True, inplace=True) month_num = monthdict[month] def create_form_A(): formAfilepath = os.path.join(karnatakafilespath, 'FormA.xlsx') formAfile = load_workbook(filename=formAfilepath) logging.info('Form A file has sheet: ' + str(formAfile.sheetnames)) logging.info('create columns which are now available') data_formA = data.copy() data_formA['S.no'] = list(range(1, len(data_formA) + 1)) data_formA["Nationality"] = '' data_formA['education level'] = '' data_formA['Category address'] = '' data_formA['type of employment'] = '' data_formA['lwf'] = '' data_formA['Service Book No'] = '' formA_columns = [ "S.no", 'Employee Code', 'Employee Name', 'Unit', 'Location', "Gender", "Father's Name", 'Date of Birth', "Nationality", "education level", 'Date Joined', 'Designation', "Category address", "type of employment", 'Mobile Tel No.', 'UAN Number', "PAN Number", "ESIC Number", "lwf", "Aadhar Number", "Bank A/c Number", "Bank Name", "Account Code", "P", "L", "Service Book No", "Date Left", "Reason for Leaving" ] formA_data = data_formA[formA_columns] formAsheet = formAfile['FORM A'] formAsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form A is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formA_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 13): for c_idx, value in enumerate(row, 1): formAsheet.cell(row=r_idx, column=c_idx, value=value) formAsheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) formAsheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') formAsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) logging.info('') establishment = formAsheet['L6'].value L6_data = establishment + ' ' + data_formA['Unit'][ 0] + ', ' + data_formA['Branch'][0] formAsheet['L6'] = L6_data company = formAsheet['A10'].value A10_data = company + ' ' + data_formA['Unit'][0] + ', ' + data_formA[ 'Branch'][0] formAsheet['A10'] = A10_data formAfinalfile = os.path.join(filelocation, 'FormA.xlsx') formAfile.save(filename=formAfinalfile) def create_form_B(): formBfilepath = os.path.join(karnatakafilespath, 'FormB.xlsx') formBfile = load_workbook(filename=formBfilepath) logging.info('Form B file has sheet: ' + str(formBfile.sheetnames)) logging.info('create columns which are now available') data_formB = data.copy() data_formB['OT hours'] = 0 data_formB['Pay OT'] = 0 data_formB['basic_and_allo'] = data_formB['Earned Basic'] + data_formB[ 'Other Allowance'] + data_formB['Meal Allowance'] + data_formB[ 'Special Allowance'] + data_formB['Personal Allowance'] data_formB['Other EAR'] = data_formB['Other Reimb'] + data_formB[ 'Arrears'] + data_formB['Other Earning'] + data_formB[ 'Variable Pay'] + data_formB['Stipend'] + data_formB[ 'Consultancy Fees'] data_formB['VPF'] = 0 data_formB['Income Tax'] = 0 data_formB['EMP PF'] = data_formB['PF'] data_formB['BankID'] = '' data_formB['Pay Date'] = '' data_formB['Remarks'] = '' formB_columns = [ 'Employee Code', 'Employee Name', 'FIXED MONTHLY GROSS', 'Days Paid', 'OT hours', 'basic_and_allo', 'Pay OT', 'HRA', 'Tel and Int Reimb', 'Bonus', 'Fuel Reimb', 'Prof Dev Reimb', 'Corp Attire Reimb', 'CCA', 'Leave Encashment', 'Other EAR', 'Total Earning', 'PF', 'ESIC', 'VPF', 'Loan Deduction', 'Loan Interest', 'P.Tax', 'CSR', 'Income Tax', 'Insurance', 'LWF EE', 'Other Deduction', 'TDS', 'Total Deductions', 'Net Paid', 'EMP PF', 'BankID', 'Pay Date', 'Remarks' ] formB_data = data_formB[formB_columns] formBsheet = formBfile['FORM B'] formBsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form B is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formB_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 21): for c_idx, value in enumerate(row, 2): formBsheet.cell(row=r_idx, column=c_idx, value=value) formBsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formBsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formBsheet['B10'].value B10_data = contractline + ' ' + contractor_name + ', ' + contractor_address formBsheet['B10'] = B10_data locationline = formBsheet['B11'].value B11_data = locationline + ' ' + data_formB['Unit'][ 0] + ', ' + data_formB['Branch'][0] formBsheet['B11'] = B11_data establine = formBsheet['B12'].value B12_data = establine + ' ' + data_formB['Unit'][0] + ', ' + data_formB[ 'Branch'][0] formBsheet['B12'] = B12_data peline = formBsheet['B13'].value B13_data = peline + ' ' + data_formB['Unit'][0] + ', ' + data_formB[ 'Branch'][0] formBsheet['B13'] = B13_data monthstart = datetime.date(year, month_num, 1) monthend = datetime.date(year, month_num, calendar.monthrange(year, month_num)[1]) formBsheet['B16'] = 'Wage period From: ' + str( monthstart) + ' to ' + str(monthend) formBfinalfile = os.path.join(filelocation, 'FormB.xlsx') formBfile.save(filename=formBfinalfile) def create_form_XXI(): formXXIfilepath = os.path.join(karnatakafilespath, 'FormXXI.xlsx') formXXIfile = load_workbook(filename=formXXIfilepath) logging.info('Form XXI file has sheet: ' + str(formXXIfile.sheetnames)) logging.info('create columns which are now available') data_formXXI = data.copy() data_formXXI['S.no'] = list(range(1, len(data_formXXI) + 1)) data_formXXI['a'] = '---' data_formXXI['b'] = '---' data_formXXI['c'] = '---' data_formXXI['e'] = '---' data_formXXI['f'] = '---' data_formXXI['g'] = '' formXXI_columns = [ 'S.no', 'Employee Name', "Father's Name", 'Designation', 'a', 'b', 'c', 'FIXED MONTHLY GROSS', 'e', 'f', 'g' ] formXXI_data = data_formXXI[formXXI_columns] formXXIsheet = formXXIfile['FORM XXI'] formXXIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXI is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXI_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 14): for c_idx, value in enumerate(row, 3): formXXIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formXXIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formXXIsheet['C7'].value C7_data = contractline + ' ' + contractor_name + ', ' + contractor_address formXXIsheet['C7'] = C7_data locationline = formXXIsheet['C8'].value C8_data = locationline + ' ' + data_formXXI['Unit'][ 0] + ', ' + data_formXXI['Branch'][0] formXXIsheet['C8'] = C8_data establine = formXXIsheet['C9'].value C9_data = establine + ' ' + data_formXXI['Unit'][ 0] + ', ' + data_formXXI['Branch'][0] formXXIsheet['C9'] = C9_data peline = formXXIsheet['C10'].value C10_data = peline + ' ' + data_formXXI['Unit'][ 0] + ', ' + data_formXXI['Branch'][0] formXXIsheet['C10'] = C10_data formXXIfinalfile = os.path.join(filelocation, 'FormXXI.xlsx') formXXIfile.save(filename=formXXIfinalfile) def create_form_XXII(): formXXIIfilepath = os.path.join(karnatakafilespath, 'FormXXII.xlsx') formXXIIfile = load_workbook(filename=formXXIIfilepath) logging.info('Form XXII file has sheet: ' + str(formXXIIfile.sheetnames)) logging.info('create columns which are now available') data_formXXII = data.copy() data_formXXII['S.no'] = list(range(1, len(data_formXXII) + 1)) data_formXXII['b'] = '---' data_formXXII['c'] = '---' data_formXXII['d'] = '---' data_formXXII['e'] = '---' data_formXXII['f'] = '---' data_formXXII['g'] = '' formXXII_columns = [ 'S.no', 'Employee Name', "Father's Name", 'Designation', 'FIXED MONTHLY GROSS', 'b', 'c', 'd', 'e', 'f', 'g' ] formXXII_data = data_formXXII[formXXII_columns] formXXIIsheet = formXXIIfile['FORM XXII'] formXXIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXII is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXII_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 15): for c_idx, value in enumerate(row, 3): formXXIIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formXXIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formXXIIsheet['C7'].value C7_data = contractline + ' ' + contractor_name + ', ' + contractor_address formXXIIsheet['C7'] = C7_data locationline = formXXIIsheet['C8'].value C8_data = locationline + ' ' + data_formXXII['Unit'][ 0] + ', ' + data_formXXII['Branch'][0] formXXIIsheet['C8'] = C8_data establine = formXXIIsheet['C9'].value C9_data = establine + ' ' + data_formXXII['Unit'][ 0] + ', ' + data_formXXII['Branch'][0] formXXIIsheet['C9'] = C9_data peline = formXXIIsheet['C10'].value C10_data = peline + ' ' + data_formXXII['Unit'][ 0] + ', ' + data_formXXII['Branch'][0] formXXIIsheet['C10'] = C10_data formXXIIfinalfile = os.path.join(filelocation, 'FormXXII.xlsx') formXXIIfile.save(filename=formXXIIfinalfile) def create_form_XXIII(): formXXIIIfilepath = os.path.join(karnatakafilespath, 'FormXXIII.xlsx') formXXIIIfile = load_workbook(filename=formXXIIIfilepath) logging.info('Form XXIII file has sheet: ' + str(formXXIIIfile.sheetnames)) logging.info('create columns which are now available') data_formXXIII = data.copy() data_formXXIII['S.no'] = list(range(1, len(data_formXXIII) + 1)) data_formXXIII['b'] = '---' data_formXXIII['c'] = '---' data_formXXIII['d'] = '---' data_formXXIII['e'] = '---' data_formXXIII['f'] = '---' data_formXXIII['g'] = '' formXXIII_columns = [ 'S.no', 'Employee Name', "Father's Name", 'Designation', 'FIXED MONTHLY GROSS', 'b', 'c', 'd', 'e', 'f', 'g' ] formXXIII_data = data_formXXIII[formXXIII_columns] formXXIIIsheet = formXXIIIfile['FORM XXIII'] formXXIIIsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XXIII is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXXIII_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 12): for c_idx, value in enumerate(row, 3): formXXIIIsheet.cell(row=r_idx, column=c_idx, value=value) formXXIIIsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formXXIIIsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formXXIIIsheet['C5'].value C5_data = contractline + ' ' + contractor_name + ', ' + contractor_address formXXIIIsheet['C5'] = C5_data locationline = formXXIIIsheet['C6'].value C6_data = locationline + ' ' + data_formXXIII['Unit'][ 0] + ', ' + data_formXXIII['Branch'][0] formXXIIIsheet['C6'] = C6_data establine = formXXIIIsheet['C7'].value C7_data = establine + ' ' + data_formXXIII['Unit'][ 0] + ', ' + data_formXXIII['Branch'][0] formXXIIIsheet['C7'] = C7_data peline = formXXIIIsheet['C8'].value C8_data = peline + ' ' + data_formXXIII['Unit'][ 0] + ', ' + data_formXXIII['Branch'][0] formXXIIIsheet['C8'] = C8_data formXXIIIfinalfile = os.path.join(filelocation, 'FormXXIII.xlsx') formXXIIIfile.save(filename=formXXIIIfinalfile) def create_form_XX(): formXXfilepath = os.path.join(karnatakafilespath, 'FormXX.xlsx') formXXfile = load_workbook(filename=formXXfilepath) logging.info('Form XX file has sheet: ' + str(formXXfile.sheetnames)) logging.info('create columns which are now available') data_formXX = data.copy() data_formXX['S.no'] = list(range(1, len(data_formXX) + 1)) data_formXX['a'] = '---' data_formXX['b'] = '---' data_formXX['c'] = '---' data_formXX['d'] = '---' data_formXX['e'] = '---' data_formXX['f'] = '---' data_formXX['g'] = '---' data_formXX['h'] = '---' data_formXX['i'] = '' formXX_columns = [ 'S.no', 'Employee Name', "Father's Name", 'Designation', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i' ] formXX_data = data_formXX[formXX_columns] formXXsheet = formXXfile['FORM XX'] formXXsheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for form XX is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(formXX_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 15): for c_idx, value in enumerate(row, 3): formXXsheet.cell(row=r_idx, column=c_idx, value=value) formXXsheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') formXXsheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = formXXsheet['C6'].value C6_data = contractline + ' ' + contractor_name + ', ' + contractor_address formXXsheet['C6'] = C6_data locationline = formXXsheet['C7'].value C7_data = locationline + ' ' + data_formXX['Unit'][ 0] + ', ' + data_formXX['Branch'][0] formXXsheet['C7'] = C7_data establine = formXXsheet['C8'].value C8_data = establine + ' ' + data_formXX['Unit'][ 0] + ', ' + data_formXX['Branch'][0] formXXsheet['C8'] = C8_data peline = formXXsheet['C9'].value C9_data = peline + ' ' + data_formXX['Unit'][0] + ', ' + data_formXX[ 'Branch'][0] formXXsheet['C9'] = C9_data formXXfinalfile = os.path.join(filelocation, 'FormXX.xlsx') formXXfile.save(filename=formXXfinalfile) def create_wages(): wagesfilepath = os.path.join(karnatakafilespath, 'Wages.xlsx') wagesfile = load_workbook(filename=wagesfilepath) logging.info('wages file has sheet: ' + str(wagesfile.sheetnames)) logging.info('create columns which are now available') data_wages = data.copy() data_wages['S.no'] = list(range(1, len(data_wages) + 1)) data_wages['Site Address'] = '' data_wages['OT hours'] = 0 data_wages['Conv'] = 0 data_wages['Sub Allow'] = 0 data_wages['Fines'] = 0 data_wages['Damages'] = 0 data_wages['Pay mode'] = '' data_wages['Remarks'] = '' wages_columns = [ 'S.no', 'Employee Code', 'Employee Name', 'Gender', 'Designation', 'Department', 'Site Address', 'Date Joined', 'ESIC Number', 'PF Number', 'FIXED MONTHLY GROSS', 'Days Paid', 'OT hours', 'Earned Basic', 'HRA', 'Conv', 'Medical Allowance', 'Tel and Int Reimb', 'Bonus', 'Fuel Reimb', 'Prof Dev Reimb', 'Corp Attire Reimb', 'Special Allowance', 'CCA', 'Other Earning', 'Sub Allow', 'Leave Encashment', 'Total Earning', 'ESIC', 'PF', 'P.Tax', 'TDS', 'CSR', 'Insurance', 'Salary Advance', 'Fines', 'Damages', 'Other Deduction', 'Total Deductions', 'Net Paid', 'Pay mode', 'Bank A/c Number', 'Remarks' ] wages_data = data_wages[wages_columns] wagessheet = wagesfile['Wages'] wagessheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for wages is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(wages_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 21): for c_idx, value in enumerate(row, 2): wagessheet.cell(row=r_idx, column=c_idx, value=value) wagessheet.cell(row=r_idx, column=c_idx).font = Font(name='Verdana', size=8) border_sides = Side(style='thin') wagessheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) contractline = wagessheet['A10'].value A10_data = contractline + ' ' + contractor_name + ', ' + contractor_address wagessheet['A10'] = A10_data locationline = wagessheet['A11'].value A11_data = locationline + ' ' + data_wages['Unit'][ 0] + ', ' + data_wages['Branch'][0] wagessheet['A11'] = A11_data establine = wagessheet['A12'].value A12_data = establine + ' ' + data_wages['Unit'][0] + ', ' + data_wages[ 'Branch'][0] wagessheet['A12'] = A12_data peline = wagessheet['A13'].value A13_data = peline + ' ' + data_wages['Unit'][0] + ', ' + data_wages[ 'Branch'][0] wagessheet['A13'] = A13_data wagessheet[ 'F4'] = 'Combined Muster Roll-cum-Register of Wages in lieu of ' + month + ' ' + str( year) wagesfinalfile = os.path.join(filelocation, 'Wages.xlsx') wagesfile.save(filename=wagesfinalfile) def create_form_H_F(form): if form == 'FORM H': formHfilepath = os.path.join(karnatakafilespath, 'FormH.xlsx') if form == 'FORM F': formHfilepath = os.path.join(karnatakafilespath, 'FormF.xlsx') formHfile = load_workbook(filename=formHfilepath) logging.info('file has sheet: ' + str(formHfile.sheetnames)) sheetformh = formHfile[form] logging.info('create columns which are now available') data_formH = data.copy() def attandance_data(employee_attendance, i): leavelist = list(employee_attendance.columns[( employee_attendance == 'PL').iloc[i]]) empcodeis = employee_attendance.iloc[i]['Employee Code'] logging.info(empcodeis) if 'Leave Type' in leavelist: leavelist.remove('Leave Type') emp1 = pd.DataFrame(leavelist) if len(emp1.index) == 0: defaultemp = { 'emp': (employee_attendance).iloc[i]['Employee Code'], 'startdate': 0, 'enddate': 0, 'days': 0, 'start_date': '-------', 'end_date': '-------' } emp1 = pd.DataFrame(defaultemp, index=[0]) emp1.index = np.arange(1, len(emp1) + 1) emp1['s.no'] = emp1.index emp1.reset_index(drop=True, inplace=True) emp1['from'] = datetime.date(year, month_num, 1) emp1['to'] = datetime.date( year, month_num, calendar.monthrange(year, month_num)[1]) emp1['totaldays'] = calendar.monthrange(year, month_num)[1] emp1['leavesearned'] = ( employee_attendance).iloc[i]['Monthly Increment'] emp1['leavesstart'] = float( employee_attendance.iloc[i]['Opening']) emp1['leavesend'] = emp1.leavesstart - emp1.days emp1 = emp1[[ "s.no", "from", "to", "totaldays", "leavesearned", "leavesstart", "start_date", "end_date", "days", "leavesend" ]] else: logging.info(emp1) emp1.columns = ['Leaves'] emp1['emp'] = (employee_attendance).iloc[i]['Employee Code'] emp1['Leavesdays'] = emp1.Leaves.str[5:7].astype(int) emp1['daysdiff'] = (emp1.Leavesdays.shift(-1) - emp1.Leavesdays).fillna(0).astype(int) emp1['startdate'] = np.where(emp1.daysdiff.shift() != 1, emp1.Leavesdays, 0) emp1['enddate'] = np.where(emp1.daysdiff != 1, emp1.Leavesdays, 0) emp1.drop(emp1[(emp1.startdate == 0) & (emp1.enddate == 0)].index, inplace=True) emp1['startdate'] = np.where(emp1.startdate == 0, emp1.startdate.shift(), emp1.startdate).astype(int) emp1['enddate'] = np.where(emp1.enddate == 0, emp1.enddate.shift(-1), emp1.enddate).astype(int) emp1 = emp1[['emp', 'startdate', 'enddate']] emp1.drop_duplicates(subset='startdate', inplace=True) emp1['days'] = emp1.enddate - emp1.startdate + 1 emp1['start_date'] = [ datetime.date(year, month_num, x) for x in emp1.startdate ] emp1['end_date'] = [ datetime.date(year, month_num, x) for x in emp1.enddate ] emp1.index = np.arange(1, len(emp1) + 1) emp1['s.no'] = emp1.index emp1.reset_index(drop=True, inplace=True) emp1['from'] = datetime.date(year, month_num, 1) emp1['to'] = datetime.date( year, month_num, calendar.monthrange(year, month_num)[1]) emp1['totaldays'] = calendar.monthrange(year, month_num)[1] emp1['leavesearned'] = ( employee_attendance).iloc[i]['Monthly Increment'] emp1['totalleaves'] = float( employee_attendance.iloc[i]['Opening']) emp1['cumdays'] = emp1['days'].cumsum() emp1['leavesend'] = emp1.totalleaves - emp1.cumdays emp1['leavesstart'] = emp1['totalleaves'] emp1 = emp1[[ "s.no", "from", "to", "totaldays", "leavesearned", "leavesstart", "start_date", "end_date", "days", "leavesend" ]] return emp1 def prepare_emp_sheet(emp1, sheet_key, key, name, fathername): sheet1 = formHfile.copy_worksheet(sheetformh) sheet1.title = sheet_key lastline = sheet1['B18'].value sheet1['B18'] = '' if len(emp1) > 3: lastlinerow = 'B' + str(18 + len(emp1)) else: lastlinerow = 'B18' logging.info(lastlinerow) sheet1[lastlinerow] = lastline from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(emp1, index=False, header=False) for r_idx, row in enumerate(rows, 14): for c_idx, value in enumerate(row, 2): sheet1.cell(row=r_idx, column=c_idx, value=value) sheet1['H5'] = key sheet1['F7'] = name sheet1['F8'] = fathername sheet1.sheet_properties.pageSetUpPr.fitToPage = True emp_count = len(data_formH.index) emp_dic = dict() for i in range(0, emp_count): key = (data_formH).iloc[i]['Employee Code'] emp_dic[key] = attandance_data(data_formH, i) sheet_key = form + '_' + str(key) name = data_formH[data_formH['Employee Code'] == key]['Employee Name'].values[0] fathername = data_formH[data_formH['Employee Code'] == key]["Father's Name"].values[0] logging.info(name) logging.info(fathername) prepare_emp_sheet(emp_dic[key], sheet_key, key, name, fathername) logging.info(key) logging.info(sheet_key) if form == 'FORM H': formHfinalfile = os.path.join(filelocation, 'FormH.xlsx') if form == 'FORM F': formHfinalfile = os.path.join(filelocation, 'FormF.xlsx') formHfile.remove(sheetformh) formHfile.save(filename=formHfinalfile) def create_muster(): musterfilepath = os.path.join(karnatakafilespath, 'Muster.xlsx') musterfile = load_workbook(filename=musterfilepath) logging.info('muster file has sheet: ' + str(musterfile.sheetnames)) logging.info('create columns which are now available') data_muster = data.copy() data_muster['S.no'] = list(range(1, len(data_muster) + 1)) first3columns = ["S.no", 'Employee Code', 'Employee Name'] last2columns = ["Date Left", "Days Paid"] columnstotake = [] days = [ '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31' ] for day in days: for col in data_muster.columns: if col[5:7] == day: columnstotake.append(col) if len(columnstotake) == 28: columnstotake.append('29') columnstotake.append('30') columnstotake.append('31') data_muster['29'] = '' data_muster['30'] = '' data_muster['31'] = '' elif len(columnstotake) == 29: columnstotake.append('30') columnstotake.append('31') data_muster['30'] = '' data_muster['31'] = '' elif len(columnstotake) == 30: columnstotake.append('31') data_muster['31'] = '' muster_columns = first3columns + columnstotake + last2columns muster_data = data_muster[muster_columns] mustersheet = musterfile['Muster'] mustersheet.sheet_properties.pageSetUpPr.fitToPage = True logging.info('data for muster is ready') from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(muster_data, index=False, header=False) logging.info('rows taken out from data') for r_idx, row in enumerate(rows, 18): for c_idx, value in enumerate(row, 2): mustersheet.cell(row=r_idx, column=c_idx, value=value) mustersheet.cell(row=r_idx, column=c_idx).font = Font(name='Bell MT', size=10) mustersheet.cell(row=r_idx, column=c_idx).alignment = Alignment( horizontal='center', vertical='center', wrap_text=True) border_sides = Side(style='thin') mustersheet.cell(row=r_idx, column=c_idx).border = Border( outline=True, right=border_sides, bottom=border_sides) logging.info('') contractline = mustersheet['B10'].value B10_data = contractline + ' ' + contractor_name + ', ' + contractor_address mustersheet['B10'] = B10_data locationline = mustersheet['B11'].value B11_data = locationline + ' ' + data_muster['Unit'][ 0] + ', ' + data_muster['Branch'][0] mustersheet['B11'] = B11_data establine = mustersheet['B12'].value B12_data = establine + ' ' + data_muster['Unit'][ 0] + ', ' + data_muster['Branch'][0] mustersheet['B12'] = B12_data peline = mustersheet['B13'].value B13_data = peline + ' ' + data_muster['Unit'][0] + ', ' + data_muster[ 'Branch'][0] mustersheet['B13'] = B13_data mustersheet[ 'B4'] = 'Combined Muster Roll-cum-Register of Wages in lieu of ' + month + ' ' + str( year) musterfinalfile = os.path.join(filelocation, 'Muster.xlsx') musterfile.save(filename=musterfinalfile) def create_formXIX(): formXIXfilepath = os.path.join(karnatakafilespath, 'FormXIX.xlsx') formXIXfile = load_workbook(filename=formXIXfilepath) logging.info('Form XIX file has sheet: ' + str(formXIXfile.sheetnames)) sheetformXIX = formXIXfile['FORM XIX'] logging.info('create columns which are now available') data_formXIX = data.copy() emp_count = len(data_formXIX.index) for i in range(0, emp_count): key = (data_formXIX).iloc[i]['Employee Code'] sheet_key = 'FORM XIX_' + str(key) emp_data = (data_formXIX).iloc[i] emp_data.fillna(value='', inplace=True) sheet1 = formXIXfile.copy_worksheet(sheetformXIX) sheet1.title = sheet_key sheet1['D7'] = contractor_name + ', ' + contractor_address sheet1['D8'] = emp_data['Unit'] + ', ' + emp_data['Branch'] sheet1['D9'] = emp_data['Unit'] + ', ' + emp_data['Branch'] sheet1['D10'] = emp_data['Unit'] + ', ' + emp_data['Branch'] sheet1['D11'] = emp_data['Employee Name'] sheet1['D12'] = emp_data['Gender'] sheet1['D13'] = month + '-' + str(year) sheet1['D14'] = key sheet1['D15'] = emp_data['Days Paid'] sheet1['D16'] = emp_data['Earned Basic'] sheet1['D17'] = emp_data['HRA'] sheet1['D18'] = emp_data['Tel and Int Reimb'] sheet1['D19'] = emp_data['Bonus'] sheet1['D20'] = emp_data['Fuel Reimb'] sheet1['D21'] = emp_data['Corp Attire Reimb'] sheet1['D22'] = emp_data['CCA'] sheet1['D23'] = emp_data['Other Earning'] sheet1['D24'] = emp_data['Total Earning'] sheet1['D25'] = emp_data['Insurance'] sheet1['D26'] = emp_data['P.Tax'] sheet1['D27'] = emp_data['TDS'] sheet1['D28'] = emp_data['Total Deductions'] sheet1['D29'] = emp_data['Net Paid'] formXIXfinalfile = os.path.join(filelocation, 'FormXIX.xlsx') formXIXfile.remove(sheetformXIX) formXIXfile.save(filename=formXIXfinalfile) def create_ecard(): ecardfilepath = os.path.join(karnatakafilespath, 'Employment card.xlsx') ecardfile = load_workbook(filename=ecardfilepath) logging.info('Employment card file has sheet: ' + str(ecardfile.sheetnames)) sheetecard = ecardfile['Employment card'] logging.info('create columns which are now available') data_ecard = data.copy() emp_count = len(data_ecard.index) for i in range(0, emp_count): key = (data_ecard).iloc[i]['Employee Code'] sheet_key = 'Employment card_' + str(key) emp_data = (data_ecard).iloc[i] emp_data.fillna(value='', inplace=True) sheet1 = ecardfile.copy_worksheet(sheetecard) sheet1.title = sheet_key sheet1['B4'] = contractor_name sheet1['B5'] = '' sheet1['B6'] = '' sheet1['B7'] = '' sheet1['B8'] = emp_data['Department'] sheet1['B9'] = contractor_address sheet1['B10'] = emp_data['Unit'] sheet1['B11'] = emp_data['Registration_no'] sheet1['B12'] = '' sheet1['B13'] = '' sheet1['B14'] = emp_data['Employee Name'] sheet1['B15'] = emp_data['Aadhar Number'] sheet1['B16'] = emp_data['Mobile Tel No.'] sheet1['B17'] = key sheet1['B18'] = emp_data['Designation'] sheet1['B19'] = emp_data['Net Paid'] sheet1['B20'] = emp_data['Date Joined'] ecardfinalfile = os.path.join(filelocation, 'Employment card.xlsx') ecardfile.remove(sheetecard) ecardfile.save(filename=ecardfinalfile) create_form_A() create_form_B() create_form_XXI() create_form_XXII() create_form_XXIII() create_form_XX() create_wages() create_form_H_F('FORM H') create_form_H_F('FORM F') create_muster() create_formXIX() create_ecard()