excel.py

def excel_file(table_data):
    import code12
    from openpyxl import Workbook
    from openpyxl.styles import  Border, Side, Alignment,  Font
    import gui
    import datetime
    import os
    
    
    # This file is used to get values from the GUI file and then  add them to an excel file .
    # the excel file generated by the code is saved in a folder titled by the date and customer no.
    # finalt this folder is saved in another folder titled as PROJECTS
    
    wb= Workbook()
    ws=wb.active
    
    def wrap_text(cell_range):
        wrap_alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')
        for row in cell_range:
            for cell in row:
                cell.alignment = wrap_alignment
                
                
    font = Font(name='Calibri',
                size=12,
                bold=True,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False,
                color='FF000000')

    #border style for all tabular data
    left=Side(border_style='thick',color='FF000000')
    right=Side(border_style='thick',color='FF000000')
    top=Side(border_style='thick',color='FF000000')
    bottom=Side(border_style='thick',color='FF000000')
    thick_border1=Border(top=top,right=right,left=left,bottom=bottom)

    #increasing width of cells so data can fit in in
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 10
    ws.column_dimensions['E'].width = 13
    ws.column_dimensions['F'].width = 15
    ws.column_dimensions['I'].width = 16
    ws.column_dimensions['J'].width = 10

    
    value=gui.data_entry()
    ws.title=(str(datetime.date.today()))

    def print_between_keys(dictionary, start_key, end_key):
        keys = list(dictionary.keys())
        start_index = keys.index(start_key)
        end_index = keys.index(end_key)
        list1=[]
        paired=[]
        for key in keys [start_index:end_index+1]:
            value = dictionary[key]
            paired.extend([key,value])
            list1.append(tuple(paired))
            paired=[]
        return list1


        #Abdhishay Murthy Nandula
        #16/05/2023
        
        
    #MAIN HEADING FOR EXCEL FILE
    ws.merge_cells('A1:R1')
    cell = ws.cell(row=1,column=1)
    cell.value = 'PUMP PERFORMANCE TESTING REPORT'
    cell. alignment = Alignment(horizontal='center', vertical='center')
    range=ws['A1':'R1']
    for cell in range:
        for x in cell:
            x.border=thick_border1


     #TABLE 1 HEADER

    cell = ws.cell(row=3,column=1)
    ws.merge_cells('A3:B3')
    cell.value = 'PUMP DETAILS'
    cell. alignment = Alignment(horizontal='center', vertical='center')
    range=ws['A3':'B3']
    for cell in range:
        for x in cell:
            x.border=thick_border1
    wrap_text(ws['A4:A8'])

    #PRINTING DATA FOR TABLE 1
    row=4
    first=print_between_keys(value,'sl_no_','special_')
    for i in first:
        ws.cell(row=row, column=1, value=i[0])
        ws.cell(row=row, column=2, value=i[1])
        row += 1

    #giving borders to data in table 1
    range=ws['A4':'B8']
    for cell in range:
        for x in cell:
            x.border = thick_border1


    #TABLE 2 HEADER
    cell = ws.cell(row=3,column=5)
    ws.merge_cells('E3:F3')
    cell.value = 'PUMP OPERATION CONDITION'
    cell. alignment = Alignment(horizontal='center', vertical='center')
    range=ws['E3':'F3']
    for cell in range:
        for x in cell:
            x.border=thick_border1
    wrap_text(ws['E4:E11'])

    #PRINTING DATA FOR TABLE 2
    second=print_between_keys(value,'guageHeight_','head_')
    row=4
    for i in second:
        ws.cell(row=row, column=5, value=i[0])
        ws.cell(row=row, column=6, value=i[1])
        row+= 1

    #giving borders to data in table 2
    range=ws['E4':'F11']
    for cell in range:
        for x in cell:
            x.border=thick_border1


    #TABLE 3 HEADER
    cell = ws.cell(row=3,column=9)
    ws.merge_cells('I3:J3')
    cell.value = 'TESTING MOTOR DETAILS'
    cell. alignment = Alignment(horizontal='center', vertical='center')
    range=ws['I3':'J3']
    for cell in range:
        for x in cell:
            x.border=thick_border1
    wrap_text(ws['I4:I11'])
    
        
   #PRINTING DATA FOR TABLE 3
    third=print_between_keys(value,'make_','customer_WO_')
    row=4
    for i in third:
        ws.cell(row=row, column=9, value=i[0])
        ws.cell(row=row, column=10, value=i[1])
        row += 1

    #giving borders to data in table 3
    range=ws['I4':'J11']
    for cell in range:
        for x in cell:
            x.border=thick_border1

    # set the headers
    headers = [ "Sl.No.","Speed (RPM)", "Suction Pressure (bar)", "Delivery Gauge Reading (bar)", "Suction Gauge Reading (m)", "Delivery Gauge Reading (m)", "Flow Rate (m/hr)", "Velocity Head (m)", "Total Head (m)", "Voltage (Volts)", "Current (Amps)", "Power (kW)","Temperature (C)","Power Input (kW)",  "Flow Rate (m/hr)","Total head (m)", "Power (kW)", "Efficiency (%)"]
    column_index = 1
    for i, val in enumerate(headers):
        ws.cell(row=13, column=column_index + i, value=val)

    #Abdhishay Murthy Nandula
        #16/05/2023

    
    # populate the data
    data=table_data
  
    for cell in ws[13]:
        for row in data:
            ws.append(row)
        break

    # set the border style
    thick_border = Border(left=Side(style='thick'), 
                            right=Side(style='thick'),
                            top=Side(style='thick'),
                            bottom=Side(style='thick')
                            )

    # set the alignment to wrap text
    
    wrap_text(ws['A13:R13'])
    range=ws['A13':'R18']
    for cell in range:
        for x in cell:
            x.border=thick_border
            
   
    # SETTING THE DATE AS WELL AS A SPACE FOR START AND END TIME
    cell = ws.cell(row=9,column=13)
    cell.value = 'DATE'
    cell2 = ws.cell(row=9,column=14)
    cell2.value = str(datetime.date.today())
    ws.column_dimensions["N"].width = 14
    ws.column_dimensions["M"].width = 12
    

    range=ws['M9':'N11']
    for cell in range:
        for x in cell:
            x.border=thick_border1

    cell3 = ws.cell(row=10,column=13)
    cell3.value = 'Started at : '
    cell4 = ws.cell(row=11,column=13)
    cell4.value = 'Stopped at : '

    #PRINTING DATA FOR VIBRATION
    fifth=print_between_keys(value,'vibx_','vibz_')
    row=4
    for i in fifth:
        ws.cell(row=row, column=13, value=i[0])
        ws.cell(row=row, column=14, value=i[1])
        row+= 1
    ws.merge_cells('M3:N3')
    vibheader=ws.cell(row=3,column=13)
    vibheader.value='Vibration'
    vibheader.alignment = Alignment(horizontal='center', vertical='center')
    

    range=ws['M3':'N6']
    for cell in range:
        for x in cell:
            x.border=thick_border1

 #APPROVAL
    cell8 = ws.cell(row=7,column=17)
    cell8.value = 'TESTED BY : '
    cell9 = ws.cell(row=8,column=17)
    cell9.value = 'WITNESSED BY : '
    wrap_text(ws['Q7':'Q8'])
    ws.column_dimensions["Q"].width = 15
    
    range=ws['Q7':'R8']
    for cell in range:
        for x in cell:
            x.border=thick_border1
    
     # Create a new folder using the current date and time
    now = datetime.datetime.now()
    folder_name = now.strftime(f"%d-%m-%Y{value['Customer_Name_']}_{value['customer_WO_']}")

    primary_folder_path = os.path.join(f'{os.getcwd()}', 'projects')
    folder_path = os.path.join(primary_folder_path, folder_name)
    os.makedirs(folder_path, exist_ok=True)

    # Save the workbook to the new folder
    wb.save(os.path.join(folder_path, f"{value['Customer_Name_']}.xlsx"))
            
   
    wb.close()



   #  Varun Shankar Duddu
   #  18/05/2023
    
    
  
#                                        _                                               _
#                                       / \         \"""\		    /"""/       / \
# 				       /   \         \   \		   /   /       /   \
#                                     /     \         \   \		  /   /       /     \
#                                    /   _   \         \   \ 	         /   /       /   _   \
#                                   /   / \   \         \   \           /   /	    /   / \   \
#                                  /   /   \   \         \   \         /   /       /   /   \   \ 
#                                 /   /     \   \         \   \       /   /   	  /   /     \   \
#                                /    """""""    \         \   \     /   /       /    """""""    \ 
#                               /                 \	    \   \   /   /       /                 \
#                              /    /"""""""""\    \	     \   \ /   /       /    /""""""""""\   \
#                             /    /           \    \ 	      \   V   /	      /    /		\   \
#                            /    /             \    \         \     /       /    /		 \   \
#                            """""               """""          """""        """""                """"