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')
