Exemple #1
0
    def non_zero_row(self, test_row, col_names, q_alias):
        non_zero = 0
        check = False
        check3 = False
        nil = 0
        flag = 0
        q_col = 0
        number = ''
        for i in col_names:
            try:
                if Extraction.capitalised(self, i[0]) in q_alias:
                    q_col = i[1]
                    break
                else:
                    for temp_val in q_alias:
                        if temp_val in Extraction.capitalised(self, i[0]):
                            q_col = i[1]
                            break
            except Exception as e:
                pass

        for i in range(len(test_row)):
            if test_row[i] == '':
                nil += 1

        if test_row[q_col] != '':
            if type(test_row[q_col]) == int or type(test_row[q_col]) == float:
                check3 = True
        if check3:
            if len(test_row) - 2 * nil >= 0:
                return True

            else:
                return True

        else:
            return False
Exemple #2
0
 def formatting_data(self, convert_nb_2_i, size_type, source_wb, dest_wb,
                     data_col, alias_data, material_check, a105, f22, f91,
                     f92, wcb, wcc, wc9, c12a, c12ab):
     sheet = source_wb.sheet_by_name('Standard Sheet')
     sheet_f = dest_wb.get_sheet_by_name('Standard Sheet')
     redFill = PatternFill(start_color='00CED1FF',
                           end_color='00CED1FF',
                           fill_type='solid')
     for data in alias_data:
         print "FORMATTING FOR -" + str(data)
         for row_s in range(sheet.nrows):
             try:
                 cap = sheet.cell(row_s, data_col[data]).value
             except Exception as e:
                 break
             if '-' not in str(cap)[:1]:
                 cap = Extraction.capitalised(self, str(cap))
             for key in alias_data[data]:
                 if cap in alias_data[data][key]:
                     sheet_f.cell(row=row_s + 1,
                                  column=data_col[data] + 1).value = key
                     if data == 'VALVE MATERIAL' and material_check == 'YES' and key not in [
                             'F316', 'CF3', 'CF8'
                     ]:
                         try:
                             check, material = self.verify_material(
                                 convert_nb_2_i, size_type, key,
                                 sheet.cell(row_s,
                                            data_col['TEMPERATURE']).value,
                                 sheet.cell(row_s,
                                            data_col['VALVE SIZE']).value,
                                 a105, f22, f91, f92, wcb, wcc, wc9, c12a,
                                 c12ab)
                             if check:
                                 sheet_f.cell(row=row_s + 1,
                                              column=data_col[data] +
                                              1).value = material
                             else:
                                 sheet_f.cell(row=row_s + 1,
                                              column=data_col[data] +
                                              1).value = material
                                 sheet_f.cell(row=row_s + 1,
                                              column=data_col[data] +
                                              1).fill = redFill
                         except Exception as e:
                             print e
                             pass
                     break
     dest_wb.save(os.getcwd() + '/' + 'Output.xls')
    def format_n_filter(self, length, alias_data, sheet_row, data_column, size,
                        i2n):
        temp_l = []
        temp = ''
        for data in data_column:
            try:
                cap = sheet_row[data[1]]
            except Exception as e:
                print e
                continue

            if ('-' not in str(cap)
                    or '/' not in str(cap)) and data[0] != 'SIZE':
                cap = Extraction.capitalised(self, str(cap))
            for key in alias_data:
                for alias in alias_data[key]:
                    if cap in alias_data[key][alias]:
                        temp_l.append(alias)
            if data[0] == 'SIZE':
                if size == 'IN':
                    cap += '\"'
                    temp_l.append(int(self.i2n[cap]))
                else:
                    if cap != '':
                        try:
                            temp_l.append(int(cap))
                        except Exception as e:
                            print e
                            pass
            rat = ''
            if data[0] == 'RATING':
                for val in str(cap):
                    try:
                        if type(int(val)) == int or type(float(val)) == float:
                            rat += str(val)
                    except:
                        pass

                temp_l.append(int(rat))

        for i in range(length):
            try:
                temp += '-' + str(temp_l[i])
            except:
                temp += '-'
        temp = temp[1:] + '-'
        return temp
Exemple #4
0
    def main_code(self, fname):
        wb = open_workbook(fname, formatting_info=True)
        standard_list = open_workbook(os.getcwd() + '/Standard Values.xlsx')
        standard_table = Extraction.extract_data(self, standard_list)
        ptm_data = Extraction.extract_data(self, standard_list, 'alias_col')
        font = wb.font_list
        sheet_name = []
        cordinates = Extraction.search_titles(self, wb, standard_table)
        cordinates_row = Extraction.extract_rows(self, cordinates)
        #below code finds the max column
        max_col = 1
        max_col_sheet = ''
        row_x = 0
        for sheetname in cordinates.keys():
            for value in cordinates[sheetname]:
                x, y = value
                if y > max_col and y != '':
                    max_col = y
                    max_col_sheet = sheetname
                    row_x = x
        sheet_m = wb.sheet_by_name(max_col_sheet)
        header = {}
        cordinates_dict = Extraction.get_cordinates(self, cordinates_row, wb)
        work = Workbook()
        w_sheet = work.active
        w_sheet.title = 'Standard Sheet'
        counter = 0
        heading_count = 0
        x = row_x
        for col in range(sheet_m.ncols):
            if col < sheet_m.ncols - 1 and sheet_m.cell(
                    x, col).value != '' and sheet_m.cell(
                        x, col + 1).value == '' and sheet_m.cell(
                            x + 1, col).value != '':
                w_sheet.cell(row=counter + 1,
                             column=col + 1).value = sheet_m.cell(x + 1,
                                                                  col).value
                header[sheet_m.cell(x + 1, col).value] = col
            elif col < sheet_m.ncols - 1 and sheet_m.cell(
                    x, col).value == '' and x < sheet_m.nrows - 1:
                if sheet_m.cell(x, col + 1).value != '' and sheet_m.cell(
                        x + 1, col).value != '':
                    w_sheet.cell(row=counter + 1,
                                 column=col + 1).value = sheet_m.cell(
                                     x + 1, col).value
                    header[sheet_m.cell(x + 1, col).value] = col
                elif sheet_m.cell(x, col + 1).value == '' and sheet_m.cell(
                        x + 1, col).value != '':
                    w_sheet.cell(row=counter + 1,
                                 column=col + 1).value = sheet_m.cell(
                                     x + 1, col).value
                    header[sheet_m.cell(x + 1, col).value] = col
                elif sheet_m.cell(x, col + 1).value != '' and sheet_m.cell(
                        x - 1, col).value != '':
                    w_sheet.cell(row=counter + 1,
                                 column=col + 1).value = sheet_m.cell(
                                     x - 1, col).value
                    header[sheet_m.cell(x - 1, col).value] = col
                elif sheet_m.cell(x, col + 1).value == '' and sheet_m.cell(
                        x - 1, col).value != '':
                    w_sheet.cell(row=counter + 1,
                                 column=col + 1).value = sheet_m.cell(
                                     x - 1, col).value
                    header[sheet_m.cell(x - 1, col).value] = col
            else:
                if sheet_m.cell(x, col).value != '':
                    w_sheet.cell(row=counter + 1, column=col +
                                 1).value = sheet_m.cell(x, col).value
                    header[sheet_m.cell(row_x, col).value] = col
        counter += 1
        for sheet in wb.sheets():
            if sheet.visibility == 0:
                col_n = None
                second_max = 0
                print sheet.name
                counting = 1
                order_row = (cordinates_dict[sheet.name].keys())
                order_row.sort()
                for tempp in range(len(order_row) - 1, -1, -1):
                    if order_row[tempp] < sheet.nrows:
                        second_max = order_row[tempp]
                        break
                for row_n in order_row:  #row_n is row in increasing form
                    row_count = row_n
                    if row_count < second_max:  #counting is the number of rows in a sheet
                        while row_count < order_row[counting]:
                            for column_name in cordinates_dict[
                                    sheet.name][row_n]:
                                cell_formatting = wb.xf_list[
                                    sheet.cell_xf_index(
                                        row_count, column_name[1])]
                                # tests for the presence of striked out cells and skips them
                                check2 = font[
                                    cell_formatting.font_index].struck_out != 1
                                if not check2:
                                    if sheet.cell(
                                            row_count, column_name[1]
                                    ).ctype == XL_CELL_BLANK or sheet.cell(
                                            row_count, column_name[1]
                                    ).ctype == XL_CELL_EMPTY:
                                        check2 = True

                                if check2:
                                    check = Filter.non_zero_row(
                                        self, sheet.row_values(row_count),
                                        cordinates_dict[sheet.name][row_n],
                                        ptm_data['QUANTITY'])
                                    if check:
                                        try:
                                            w_sheet.cell(
                                                row=counter + 1,
                                                column=header[column_name[0]] +
                                                1).value = sheet.cell(
                                                    row_count,
                                                    column_name[1]).value
                                        except Exception as e:
                                            cap1 = Extraction.capitalised(
                                                self, column_name[0])
                                            for key in header.keys():
                                                cap2 = Extraction.capitalised(
                                                    self, key)
                                                for tempi in ptm_data:
                                                    for j in ptm_data[tempi]:

                                                        if (cap1 in j
                                                                or j in cap1
                                                            ) and (j in cap2
                                                                   or tempi
                                                                   in cap2):
                                                            col_n = key
                                                            break
                                                    if col_n == key:
                                                        break
                                                if col_n == key:
                                                    break
                                            w_sheet.cell(
                                                row=counter + 1,
                                                column=header[key] +
                                                1).value = sheet.cell(
                                                    row_count,
                                                    column_name[1]).value
                                    else:
                                        break
                                else:
                                    break
                            row_count += 1
                            if check2 and check:
                                counter += 1
                    elif row_count == second_max:
                        while row_count < sheet.nrows:
                            for column_name in cordinates_dict[
                                    sheet.name][row_n]:
                                cell_formatting = wb.xf_list[
                                    sheet.cell_xf_index(
                                        row_count, column_name[1])]
                                check2 = font[
                                    cell_formatting.font_index].struck_out != 1
                                if not check2:
                                    if sheet.cell(
                                            row_count, column_name[1]
                                    ).ctype == XL_CELL_BLANK or sheet.cell(
                                            row_count, column_name[1]
                                    ).ctype == XL_CELL_EMPTY:
                                        check2 = True
                                if check2:

                                    check = Filter.non_zero_row(
                                        self, sheet.row_values(row_count),
                                        cordinates_dict[sheet.name][row_n],
                                        ptm_data['QUANTITY'])
                                    if check:
                                        try:
                                            w_sheet.cell(
                                                row=counter + 1,
                                                column=header[column_name[0]] +
                                                1).value = sheet.cell(
                                                    row_count,
                                                    column_name[1]).value
                                        except Exception as e:
                                            cap1 = Extraction.capitalised(
                                                self, column_name[0])
                                            for key in header.keys():
                                                cap2 = Extraction.capitalised(
                                                    self, key)
                                                if cap1 == cap2:
                                                    break
                                                else:
                                                    for i in ptm_data:
                                                        for j in ptm_data[i]:
                                                            if (cap1 in j or j
                                                                    in cap1
                                                                ) and (
                                                                    j in cap2
                                                                    or i
                                                                    in cap2):
                                                                col_n = key
                                                                break
                                                        if col_n == key:
                                                            break
                                                    if col_n == key:
                                                        break
                                            w_sheet.cell(
                                                row=counter + 1,
                                                column=header[key] +
                                                1).value = sheet.cell(
                                                    row_count,
                                                    column_name[1]).value
                                    else:
                                        break
                                else:
                                    break
                            if check2 and check:
                                counter += 1
                            row_count += 1
                        counting += 1

                    else:
                        continue
                    counting += 1
        print 'writing done'
        print sheet.name
        work.save(os.getcwd() + '/' + 'Output.xls')
        wb2 = open_workbook(os.getcwd() + '/' + 'Output.xls')
        sheet = wb2.sheet_by_index(0)
        data_column = {}
        for vtemp in header:
            stamp = 0
            for key in ptm_data:
                for value in ptm_data[key]:
                    cap = str(Extraction.capitalised(self, vtemp))
                    cap2 = str(Extraction.capitalised(self, value))
                    if cap != '' and cap == cap2 or cap2 in cap or cap in cap2:
                        data_column[key] = header[vtemp]
                        stamp = 1
                        break
                if stamp == 1:
                    break
        '''below code is used to construct a nested dictionary of aliases for material, operation, valve type, etc'''
        alias_s = standard_list.sheet_by_name('alias')
        alias_db = standard_list.sheet_by_name('db alias')
        size_con = standard_list.sheet_by_name('conversion')
        convert_nb_2_i = {}
        convert_i_2_nb = {}
        for row in range(size_con.nrows):
            word = ''
            for val in str(size_con.cell(row, 1).value):
                word += val
            convert_nb_2_i[size_con.cell(row, 0).value] = word
            convert_i_2_nb[word] = size_con.cell(row, 0).value
        if self.size_value == 'IN':
            conversion_data = convert_nb_2_i
            for row in range(1, sheet.nrows):
                d_value = ''
                value_string = str(
                    sheet.cell(row, data_column['VALVE SIZE']).value).lstrip()
                for i in str(value_string):
                    if i in '0123456789':
                        d_value += i
                    else:
                        break
                try:
                    w_sheet.cell(
                        row=row + 1, column=data_column['VALVE SIZE'] +
                        1).value = conversion_data[int(d_value)].strip("\"")
                except:
                    pass
        elif self.size_value == 'NB':
            conversion_data = convert_i_2_nb
            for row in range(1, sheet.nrows):
                d_value = ''
                value_string = str(
                    sheet.cell(
                        row,
                        data_column['VALVE SIZE']).value).strip().strip("\"")
                for i in str(value_string):
                    if i in '0123456789':
                        d_value += i
                    else:
                        break
                try:
                    w_sheet.cell(row=row + 1,
                                 column=data_column['VALVE SIZE'] +
                                 1).value = conversion_data[int(d_value)]
                except:

                    pass
        alias_data = {}
        for col in range(alias_s.ncols):
            alias_d = {}
            temp = []
            for row in range(1, alias_s.nrows):
                if alias_s.cell(row, col).value != '':
                    if alias_s.cell(row, col).value != '-' and alias_s.cell(
                            row, col).value != '--':
                        temp.append(
                            str(
                                Extraction.capitalised(
                                    self,
                                    alias_s.cell(row, col).value)))
                    else:
                        temp.append(str(alias_s.cell(row, col).value))
                elif alias_s.cell(row, col).value == '' and len(temp) >= 1:
                    alias_d[temp[0]] = temp
                    temp = []
            alias_data[alias_s.cell(0, col).value] = alias_d
        alias_data2 = {}
        for col in range(alias_db.ncols):
            alias_d = {}
            temp = []
            for row in range(1, alias_db.nrows):
                if alias_db.cell(row, col).value != '':
                    if alias_db.cell(row, col).value != '-' and alias_db.cell(
                            row, col).value != '--':
                        temp.append(
                            str(
                                Extraction.capitalised(
                                    self,
                                    alias_db.cell(row, col).value)))
                    else:
                        temp.append(str(alias_db.cell(row, col).value))
                elif alias_db.cell(row, col).value == '' and len(temp) >= 1:
                    alias_d[temp[0]] = temp
                    temp = []
            alias_data2[alias_db.cell(0, col).value] = alias_d
        C = 0
        '''Below code is used to standardise the material name given in the original excel sheetusing alias excel sheet'''
        work.save(os.getcwd() + '/' + 'Output.xls')
        wb2 = open_workbook(os.getcwd() + '/' + 'Output.xls')
        Filter.formatting_data(self, convert_nb_2_i, self.size_value, wb2,
                               work, data_column, alias_data,
                               self.material_value, self.a105, self.f22,
                               self.f91, self.f92, self.wcb, self.wcc,
                               self.wc9, self.c12a, self.c12ab)
        work.save(os.getcwd() + '/' + 'Output.xls')
        wb2 = open_workbook(os.getcwd() + '/' + 'Output.xls')
        sheet = wb2.sheet_by_index(0)
        if self.rating_value == 'YES':
            cliet = Client('enter ip address for the soap service')
            r = []
            w_sheet.cell(row=1, column=sheet.ncols + 1).value = "RATING(S)"
            data_column['VALVE RATING'] = sheet.ncols
            print("FETCHING RATING FROM SERVER")
            for rows in range(1, sheet.nrows):
                w_sheet.cell(
                    row=rows + 1, column=sheet.ncols +
                    1).value = cliet.service.GetMinClassRating(
                        sheet.cell(rows, data_column['TEMPERATURE']).value,
                        sheet.cell(rows, data_column['PRESSURE']).value,
                        sheet.cell(rows, data_column['VALVE MATERIAL']).value)

        work.save(os.getcwd() + '/' + 'Output.xls')
        order = []
        ssd = standard_list.sheet_by_name('standard')
        for col in range(ssd.ncols):
            order.append(ssd.cell(0, col).value)

        final_s = work.create_sheet('final')
        regret_s = work.create_sheet('regret')
        wb2 = open_workbook(os.getcwd() + '/' + 'Output.xls')
        sheet = wb2.sheet_by_index(0)
        counter = 1
        counter2 = 1
        head = 1
        redFill = PatternFill(start_color='00CED1FF',
                              end_color='00CED1FF',
                              fill_type='solid')
        for row in range(sheet.nrows):
            count = 0

            while count < len(order):
                if head == 1:
                    for col in range(len(order)):
                        if order[col] in data_column:
                            final_s.cell(row=counter,
                                         column=col + 3).value = order[col]
                            final_s.cell(row=counter,
                                         column=2).value = 'VALVE PROJECT'
                            final_s.cell(row=counter,
                                         column=1).value = 'VALVE SCHEDULE'
                    count += 1
                    counter += 1
                    head = 0
                elif sheet.cell(row, data_column[order[count]]
                                ).value in alias_data['VALVE TYPE'].keys():
                    for col in range(len(order)):
                        if order[col] in data_column:
                            if w_sheet.cell(row=row + 1,
                                            column=data_column[order[col]] +
                                            1).fill == redFill:
                                final_s.cell(row=counter,
                                             column=col + 3).fill = redFill
                                final_s.cell(row=counter, column=col +
                                             3).value = sheet.cell(
                                                 row,
                                                 data_column[order[col]]).value
                                final_s.cell(row=counter,
                                             column=2).value = self.vp
                                final_s.cell(row=counter,
                                             column=1).value = self.vs

                            else:
                                final_s.cell(row=counter, column=col +
                                             3).value = sheet.cell(
                                                 row,
                                                 data_column[order[col]]).value
                                final_s.cell(row=counter,
                                             column=2).value = self.vp
                                final_s.cell(row=counter,
                                             column=1).value = self.vs
                    counter += 1
                    count += 1
                    break
                else:
                    for col in range(len(order)):
                        if order[col] in data_column:
                            if w_sheet.cell(row=row + 1,
                                            column=data_column[order[col]] +
                                            1).fill == redFill:
                                regret_s.cell(row=counter2,
                                              column=col + 3).fill = redFill
                                regret_s.cell(
                                    row=counter2,
                                    column=col + 3).value = sheet.cell(
                                        row, data_column[order[col]]).value
                                regret_s.cell(row=counter2,
                                              column=2).value = self.vp
                                regret_s.cell(row=counter2,
                                              column=1).value = self.vs
                            else:
                                regret_s.cell(
                                    row=counter2,
                                    column=col + 3).value = sheet.cell(
                                        row, data_column[order[col]]).value
                                regret_s.cell(row=counter2,
                                              column=2).value = self.vp
                                regret_s.cell(row=counter2,
                                              column=1).value = self.vs
                    counter2 += 1
                    count += 1
                    break
        work.save(os.getcwd() + '/' + 'Output.xls')
        wb2 = open_workbook(os.getcwd() + '/' + 'Output.xls')
        Part_number_extraction(alias_data2, standard_list, wb2, work,
                               self.size_value, convert_i_2_nb,
                               self.part_value, self.part_value2,
                               convert_nb_2_i)
        work.save(os.getcwd() + '/' + 'Output.xls')