def set_cat(): url_money = 'C://Users//ww//Desktop//excel//eshp//3//full_products.xlsx' wb_money = excel.open(url_money) ws_money = wb_money[wb_money.sheetnames[0]] url_categ = 'C://Users//ww//Desktop//excel//eshp//3//cz_categories.xlsx' wb_categ = excel.open(url_categ) ws_categ = wb_categ[wb_categ.sheetnames[0]] for index, eshop_row in enumerate(ws_money.rows): def_cat = eshop_row[3].value cat_1 = eshop_row[4].value cat_2 = eshop_row[5].value cat_3 = eshop_row[6].value for cat_row in ws_categ.rows: eng_cat = cat_row[0].value cz_cat = cat_row[1].value if def_cat is not None and eng_cat in def_cat: eshop_row[3].value = eshop_row[3].value.replace( eng_cat, cz_cat) if cat_1 is not None and eng_cat in cat_1: eshop_row[4].value = eshop_row[4].value.replace( eng_cat, cz_cat) if cat_2 is not None and eng_cat in cat_2: eshop_row[5].value = eshop_row[5].value.replace( eng_cat, cz_cat) if cat_3 is not None and eng_cat in cat_3: eshop_row[6].value = eshop_row[6].value.replace( eng_cat, cz_cat) wb_money.save( 'C://Users//ww//Desktop//excel//eshp//3//en_prod_vis_23_4_translated.xlsx' )
def set_img(): url_money = 'C://Users//ww//Desktop//excel//eshp//24_04_17_00//cz_products_images.xlsx' wb_money = excel.open(url_money) ws_money = wb_money[wb_money.sheetnames[0]] url_eshop = 'C://Users//ww//Desktop//excel//eshp//24_04_17_00//en_products_images.xlsx' wb_eshop = excel.open(url_eshop) ws_eshop = wb_eshop[wb_eshop.sheetnames[0]] for e_shop_row in ws_eshop.rows: for money_row in ws_money.rows: mone_code = money_row[0].value e_sh_code = e_shop_row[0].value if mone_code == e_sh_code: e_shop_row[4].value = money_row[4].value e_shop_row[5].value = money_row[5].value e_shop_row[6].value = money_row[6].value e_shop_row[7].value = money_row[7].value e_shop_row[8].value = money_row[8].value e_shop_row[9].value = money_row[9].value e_shop_row[10].value = money_row[10].value e_shop_row[11].value = money_row[11].value e_shop_row[12].value = money_row[12].value e_shop_row[13].value = money_row[13].value e_shop_row[14].value = money_row[14].value e_shop_row[15].value = money_row[15].value wb_eshop.save( 'C://Users//ww//Desktop//excel//eshp//24_04_17_00//en_products_images_updated.xlsx' )
def prices(): url_money = 'C://Users//ww//Desktop//excel//eshp//28_04//PolozkaCeniku_20_43.xlsx' wb_money = excel.open(url_money) ws_money = wb_money[wb_money.sheetnames[0]] url_eshop = 'C://Users//ww//Desktop//excel//eshp//28_04//en_products_prices_21_00.xlsx' wb_eshop = excel.open(url_eshop) ws_eshop = wb_eshop[wb_eshop.sheetnames[0]] for row_money in ws_money.rows: for index, row_eshop in enumerate(ws_eshop.rows): money_code = row_money[4].value eshop_code = row_eshop[0].value if eshop_code == money_code: ws_eshop.cell(row=index + 1, column=11, value=row_money[2].value) ws_eshop.cell(row=index + 1, column=12, value=row_money[10].value) if row_eshop[3].value != row_money[2].value: for cell in row_eshop: cell.fill = PatternFill('solid', fgColor=YELLOW) # row_eshop[9].value = row_money[9].value # row_eshop[10].value = row_money[10].value # 9 column index, code 3, price 9, 10 wb_eshop.save( 'C://Users//ww//Desktop//excel//eshp//28_04//en_products_compare_21_00.xlsx' )
def change_names(): url_money = 'C://Users//ww//Desktop//excel//eshp//28_04//Artikl_28_04.xlsx' wb_money = excel.open(url_money) ws_money = wb_money[wb_money.sheetnames[0]] url_eshop = 'C://Users//ww//Desktop//excel//eshp//28_04//cz_products_vis_pr.xlsx' wb_eshop = excel.open(url_eshop) ws_eshop = wb_eshop[wb_eshop.sheetnames[0]] for e_shop_row in ws_eshop.rows: for money_row in ws_money.rows: money_code = money_row[4].value e_eshop_code = e_shop_row[0].value if money_code == e_eshop_code: brand_name = money_row[6].value money_name = money_row[5].value if brand_name is not None and brand_name != '': money_name = money_name.replace(brand_name, '') money_name = brand_name + ' ' + money_name # index of name from money e_shop_row[2].value = money_name e_shop_row[3].value = brand_name wb_eshop.save( 'C://Users//ww//Desktop//excel//eshp//28_04//cz_products_names.xlsx')
def set_stock(): url_money = 'C://Users//ww//Desktop//excel//eshp//24_04//Artikl_24_04_stock.xlsx' wb_money = excel.open(url_money) ws_money = wb_money[wb_money.sheetnames[0]] url_eshop = 'C://Users//ww//Desktop//excel//eshp//24_04_17_00//cz_stock.xlsx' wb_eshop = excel.open(url_eshop) ws_eshop = wb_eshop[wb_eshop.sheetnames[0]] for e_shop_row in ws_eshop.rows: for money_row in ws_money.rows: money_code = money_row[0].value e_eshop_code = e_shop_row[0].value if money_code == e_eshop_code: money_stock = money_row[1].value if money_stock > 0: e_shop_row[6].value = 'blockUnregistered' e_shop_row[3].value = in_stock e_shop_row[4].value = in_stock else: e_shop_row[6].value = 'hidden' e_shop_row[3].value = out_stock e_shop_row[4].value = out_stock # money_code = money_row[0].value # e_eshop_code = e_shop_row[0].value # # if money_code == e_eshop_code: # money_stock = money_row[1].value # money_unit = money_row[2].value # # e_shop_unit = e_shop_row[unit].value # # if e_shop_unit == money_unit: # e_shop_row[stock_id].value = money_stock # if money_stock == 0: # e_shop_row[av_in_stock].value = out_stock # e_shop_row[av_out_stock].value = out_stock # else: # e_shop_row[av_in_stock].value = in_stock # e_shop_row[av_out_stock].value = in_stock # # elif e_shop_unit == '100g' and money_unit == 'kg': # e_shop_row[stock_id].value = money_stock * 10 # # if money_stock == 0: # e_shop_row[av_in_stock].value = out_stock # e_shop_row[av_out_stock].value = out_stock # else: # e_shop_row[av_in_stock].value = in_stock # e_shop_row[av_out_stock].value = in_stock wb_eshop.save( 'C://Users//ww//Desktop//excel//eshp//24_04_17_00//cz_products_stock_updated.xlsx' )
def __init__(self, money_url, eshop_url): self.__money_url = money_url self.__eshop_url = eshop_url self.money_workbook = excel.open(self.__money_url) money_sheet_names = self.money_workbook.sheetnames self.money_sheet = self.money_workbook[money_sheet_names[0]] # Eshop sheet self.eshop_workbook = excel.open(self.__eshop_url) eshop_sheet_names = self.eshop_workbook.sheetnames self.eshop_sheet = self.eshop_workbook[eshop_sheet_names[0]] self.money_class = Money(self.money_sheet) self.eshop_class = Eshop(self.eshop_sheet)
def get_exchange_rate(xlsx_file): """ Повертає список трійок: коду 1-ої валюти, коду 2-ої валюти, курс обміну з xlsx-файлу""" wb = openpyxl.open(xlsx_file) ws = wb["rate"] return [[cell.value for cell in row] for row in ws.rows][1:]
def __init__(self, path_tkp_file, number_tkp, main_folder): self.main_folder = main_folder self.path_tkp_file = path_tkp_file self.number_tkp = number_tkp book = openpyxl.open(path_tkp_file, read_only=True) # выбор первого листа докумета sheet = book.active self.dict_value_file = { "Номер ТКП": str(sheet['B1'].value).zfill(4), "ГИП": sheet['B2'].value, "Краткое наименование ТКП": sheet['D1'].value, "Объект": sheet['D2'].value, "Бренд 1": sheet['A8'].value, "Бренд 2": sheet['B8'].value, "Бренд 3": sheet['C8'].value, "Бренд 4": sheet['D8'].value, "Бренд 5": sheet['E8'].value, "Бренд 6": sheet['F8'].value, "Бренд 7": sheet['G8'].value, "Бренд 8": sheet['H8'].value, "Бренд 9": sheet['I8'].value, "Бренд 10": sheet['J8'].value, "Статус ТКП 1С": sheet['A11'].value, "Статус ТКП в отделе": sheet['B11'].value, "Название направления 1": sheet['H1'].value, "Название направления 2": sheet['H2'].value, "Название направления 3": sheet['J1'].value, "Название направления 4": sheet['J1'].value, } pass
def delete(msg, userid): #1~5の域を出た数値だったら怒る if not (0 < int(msg[1]) < 6): r_msg = '予約対象のボスは数値1~5で指定してください' return r_msg wb = openpyxl.open('./datasheet.xlsx') sh = wb['メンバーリスト'] for i in range(30): if sh.cell(row=2 + i, column=2).value == str(userid): #一致する予約場所を消す if str(sh.cell(row=2 + i, column=3).value).split(',')[0] == msg[1]: sh.cell(row=2 + i, column=3).value = None elif str(sh.cell(row=2 + i, column=4).value).split(',')[0] == msg[1]: sh.cell(row=2 + i, column=4).value = None elif str(sh.cell(row=2 + i, column=5).value).split(',')[0] == msg[1]: sh.cell(row=2 + i, column=5).value = None else: r_msg = str(msg[1]) + 'ボスへの予約が見つかりませんでした' return r_msg #予約の完了 wb.save('./datasheet.xlsx') r_msg = str(msg[1]) + 'ボスへの予約を取り消しました' return r_msg #メンバーが見つからない場合 r_msg = '登録されていないメンバーです.リーダーに連絡してください.' return r_msg
def boss_change(): wb = openpyxl.open('./datasheet.xlsx') sh = wb['メンバーリスト'] #ボスの変更処理 sh['J1'].value += 1 if sh['J1'].value == 6: sh['J1'].value = 1 wb.save('./datasheet.xlsx') msg = str(sh['J1'].value) + "ボスが来ました!\n予約者\n" #予約者の確認 for i in range(30): #予約をリスト化 for reserve_list_cell in sh['C' + str(i + 2) + ':E' + str(i + 2)]: reserve_list = [] for j in range(len(reserve_list_cell)): reserve_list.append( str(reserve_list_cell[j].value).split(',')[0]) #予約リストの中に含まれていたら… if str(sh['J1'].value) in reserve_list: msg += '<@' + str(sh.cell(row=2 + i, column=2).value) + '> ' #持越し登録者の確認 msg += '\n持越し登録者\n' for i in range(30): if sh.cell(row=i + 2, column=7).value == sh['J1'].value: msg += '<@' + str(sh.cell(row=2 + i, column=2).value) + '> ' return msg
def openfile(self): _fname = QFileDialog.getOpenFileName(self, 'Open file', '/home')[0] if _fname.find('xlsx') != -1: book = openpyxl.open(_fname, read_only=True) s = book.sheetnames[0] sheet = book[s] rows = sheet.max_row for row in range(4, rows + 1): _teacher = sheet[row][0].value _quantity = sheet[row][1].value _grade = sheet[row][2].value _connection = sqlite3.connect("MyDb.db") _cursor = _connection.cursor() _cursor.execute("SELECT * FROM person WHERE Teacher=?", (_teacher, )) if _cursor.fetchone(): continue _cursor.execute( "INSERT INTO person (Teacher, Quantity, Grade) VALUES (?, ?, ?)", (_teacher, _quantity, _grade)) _connection.commit() _cursor.close() _connection.close() elif _fname.find('xlsx') == -1: QMessageBox.warning( QMessageBox(), "Error", "There was an error while opening file. \n" "Did you chose none excel file?")
def measure_1(): # sa.write(f'DISP:TRAC1:Y:RLEV -5dbm') result = [] fs = [0.45, 1.0, 5.0, 10.0, 15.0] us = [4.7, 5.0, 5.3] gen.write(f':POW:POW {p_in}dbm') gen.write('OUTP ON') for f_gen in fs: print('set freq', f_gen) gen.write(f'SOUR:FREQ:CW {f_gen}ghz') for u_src in us: src.write(f'APPLY {u_src}V,{i_source}ma,1') src.write('OUTP:CHAN1 ON') src.write('OUTP:MAST ON') time.sleep(0.6) curr = src.query('MEAS:CURR?') print('read curr', curr) result.append([f_gen, u_src, float(curr) * 1_000]) res = sorted(result, key=lambda el: el[1]) freqs = sorted({el[0] for el in result}) res = {el[0]: list(el[1]) for el in groupby(res, key=lambda el: el[1])} res = {k: [el[2] for el in v] for k, v in res.items()} cols = ['Fin, GHz'] + [f'I@Uin={u}V, mA' for u in res] df = pd.DataFrame( [[f] + currs for f, *currs in zip(freqs, *res.values())], columns=cols, ) print(df) df.to_excel(file_name) wb = openpyxl.open(file_name) ws = wb.active rows = len(df) data = Reference( ws, range_string=f'{ws.title}!C1:{ascii_uppercase[len(cols)]}{rows + 1}') xs = Reference(ws, range_string=f'{ws.title}!B1:B{rows + 1}') chart = LineChart() chart.add_data(data, titles_from_data=True) chart.set_categories(xs) ws.add_chart(chart, f'G4') wb.save(file_name) wb.close()
def measure_1(): raw = sa.query('TRAC1:DATA? TRACE1') result = [float(v) for v in raw.split(',')] fs = [ round(x, 2) for x in np.arange(start=f_start, stop=f_end + 100, step=f_step) ] result = [[f, r] for f, r in zip(fs, result)] cols = ['F, MHz', 'Noise, dB'] df = pd.DataFrame(result, columns=cols) print(df) df.to_excel(file_name) wb = openpyxl.open(file_name) ws = wb.active rows = len(df) data = Reference( ws, range_string=f'{ws.title}!C1:{ascii_uppercase[len(cols)]}{rows + 1}') xs = Reference(ws, range_string=f'{ws.title}!B1:B{rows + 1}') chart = LineChart() chart.add_data(data, titles_from_data=True) chart.set_categories(xs) ws.add_chart(chart, f'E4') wb.save(file_name) wb.close()
def measure_1(): src.write(f'APPLY {u_source}V,{i_source}ma,1') src.write('OUTP:CHAN1 ON') src.write('OUTP:MAST ON') # sa.write(f'DISP:TRAC1:Y:RLEV -5dbm') sa.write(f'BAMD 200khz') sa.write('frequency:start 1mhz') sa.write('frequency:stop 30ghz') result = [] fs = [ round(x, 1) for x in np.linspace( f_start, f_end, int((f_end - f_start) / f_step) + 1, endpoint=True) ] for f_gen in fs: print('set freq', f_gen) gen.write(f'SOUR:FREQ:CW {f_gen}ghz') gen.write(f':POW:POW {p_in}dbm') gen.write('OUTP ON') time.sleep(0.5) f_sa = f_gen / coeff sa.write('CALC1:MARK1 ON') sa.write(f'CALC1:MARK1:X {f_sa}ghz') time.sleep(0.5) pw1 = sa.query(f'CALC1:MARK1:Y?') print('read power', pw1) result.append([f_gen, float(pw1)]) cols = ['F, GHz', f'Pout@F/{coeff}, dB'] df = pd.DataFrame(result, columns=cols) print(df) df.to_excel(file_name) wb = openpyxl.open(file_name) ws = wb.active rows = len(df) data = Reference(ws, range_string=f'{ws.title}!C1:C{rows + 1}') xs = Reference(ws, range_string=f'{ws.title}!B1:B{rows + 1}') chart = LineChart() chart.add_data(data, titles_from_data=True) chart.set_categories(xs) ws.add_chart(chart, f'E4') wb.save(file_name) wb.close()
def read(self, sheet_name): """ 读取excel数据并返回 :param sheet_name: 表单名称 :return: 存在传入的表单, 返回表单数据,不存在则返回空 """ # 创建一个工作簿工作对象 workbook = openpyxl.open(self.file) # 跟上面那句一个意思 workbook = openpyxl.load_workbook(self.file) # 获取excel当中所有的sheet,返回的是一个列表 sheets = workbook.sheetnames # 获取sheet对象 if sheet_name in sheets: sheet = workbook[sheet_name] all_values = list(sheet.values) header = all_values[0] data = [] for i in all_values[1:]: data.append(dict(zip(header, i))) # 关闭excel workbook.close() return data else: # 关闭excel workbook.close() return None
def multiexcelwrite(file, sheetdatadic, dir=os.getcwd()): dic = sheetdatadic cwd = os.getcwd() revert = False if dir != cwd: os.chdir(dir) revert = True wb = pyxl.open(file) for sheet in list(dic.keys()): ws = wb[sheet] data = dic[sheet] if ws['A1'] != None: for row in ws: for cell in row: cell.value = None r = 1 for row in data: c = 1 for field in row: if field == '#REF!' or field == '#VALUE!': field = None ws.cell(r, c).value = field c += 1 r += 1 wb.save(file) if revert == True: os.chdir(cwd)
def sync_douban(request): """ Sync douban data from .xlsx file generated by doufen """ if request.method == 'POST': # validate sunmitted data try: uploaded_file = request.FILES['file'] wb = openpyxl.open(uploaded_file, read_only=True, data_only=True, keep_links=False) wb.close() except (MultiValueDictKeyError, InvalidFileException, BadZipFile) as e: # raise e return HttpResponseBadRequest(content="invalid excel file") # file_data = {'file': request.FILES['xlsx']} form = SyncTaskForm(request.POST, request.FILES) if form.is_valid(): # stop all preivous task SyncTask.objects.filter(user=request.user, is_finished=False).update(is_finished=True) form.save() sync_task_manager.add_task(form.instance) return HttpResponse(status=204) else: return HttpResponseBadRequest() else: return HttpResponseBadRequest()
def excelcreate(data, file, sheet, dir=os.getcwd()): cwd = os.getcwd() revert = False if dir != cwd: os.chdir(dir) revert = True wb = pyxl.open(file) ws = wb[sheet] if ws['A1'] != None: for row in ws: for cell in row: cell.value = None r = 1 for row in data: c = 1 for field in row: if field == '#REF!' or field == '#VALUE!': field = None ws.cell(r, c).value = field c += 1 r += 1 wb.save(file) if revert == True: os.chdir(cwd)
def manipularArqXl(arquivo): """ Editar e alterar uma planilha excel brancht01 :param arquivo: str com o nome do arquivo excel :return: str de "sucesso" ou "cancelado" """ arquivo = f'G:\AmbientePython\PycharmProjects\gestaocontratos\{arquivo}' ws = Workbook() print(type(ws)) ws1 = open(arquivo) print(type(ws1)) #workbook = load_workbook('----------/dataset.xlsx') #sheet = workbook.active #row_count = sheet.max_row #for i in range(row_count): # print(sheet.cell(row=i, column=2).value) sheet = ws1.active row_count = sheet.max_row print(row_count) for i in range(row_count): print(i) print(sheet.cell(row=i + 1, column=1).value) return 'Sucesso'
def write(self, sheetname, data, row, column): '''写入表格数据''' workbook = openpyxl.open(self.xpath) worksheet: Worksheet = workbook[sheetname] worksheet.cell(row=row, column=column).value = data workbook.save(self.xpath) workbook.close()
def write(self, sheet_name, data, row, column): """写入数据""" wb = openpyxl.open(self.fpath) ws = wb[sheet_name] ws.cell(row=row, column=column).value = data wb.save(self.fpath) wb.close()
def _read_file(self, file: str): """ Create an Excel file reader @param file: @return: """ wb = openpyxl.open( file, read_only=True, ) sheet = wb.active header = None col_map = {} data_rows = [] for row in sheet.rows: if not col_map: col_map = map_header(row, MAP_BY_VALUE) else: record = PossibleRow.from_row(row, col_map) if record.amount: data_rows.append(record) context = self.pull_info(wb) context['header'] = col_map self.header = context return data_rows, context
def dialog_start(update, context): book = openpyxl.open(new_elem_replace, read_only=True) my_keyboard = ReplyKeyboardMarkup([book.sheetnames], resize_keyboard=True) update.message.reply_text( "Изменения к расписанию занятий Корпус 1 (ул. Мурманская, д. 30)\nвыберите дату:", reply_markup=my_keyboard) return "step_one"
def import_from_excel(self, path: str) -> any: cache_standard = [] cache_map = {} try: wb = openpyxl.open(path) sh = wb["Mapping"] row = 2 while not sh.cell(row, 1).value == None: code = sh.cell(row, 2).value mapcode = sh.cell(row, 1).value if code in cache_map: return "Code assiged to non-standard list cannot become standard code" if mapcode in cache_standard: return "Code assiged to stanard list cannot become non-standard code" if mapcode == None: return "Error: empty cell is found" if not code in cache_standard: cache_standard.append(code) cache_map[mapcode] = code row += 1 sh = wb["Standard_Codes"] row = 2 while not sh.cell(row, 1).value == None: code = sh.cell(row, 1).value if not code in cache_standard: cache_standard.append(code) row += 1 except: return False self.standard = cache_standard self.map = cache_map self.save_map_json() return True
def allEmployee(self, employeeFile: str) -> Dict: wb = openpyxl.open(employeeFile) ws = wb['employee'] accountList = ws['A'] accountList = [ account for account in accountList if account.value is not None ] nameList = ws['B'] nameList = [name for name in nameList if name.value is not None] isCodingList = ws['J'] isCodingList = [ isCoding for isCoding in isCodingList if isCoding.value is not None ] workingStateList = ws['F'] workingStateList = [ workingState for workingState in workingStateList if workingState.value is not None ] result = {} for i in range(1, len(accountList)): if isCodingList[i].value == '是' and workingStateList[ i].value == '正常': result[accountList[i].value] = nameList[i].value wb.close() return result
def measure_1(): # sa.write(f'DISP:TRAC1:Y:RLEV -5dbm') sa.write(f'BAMD 200khz') sa.write(f'frequency:start {band_start}mhz') sa.write(f'frequency:stop {band_end}mhz') result = [] ucs = [round(x, 2) for x in np.arange(start=uc_start, stop=uc_end + 0.2, step=uc_step)] # ucs = [round(x, 1) for x in np.linspace(uc_start, uc_end, int((uc_end - uc_start) / uc_step) + 1, endpoint=True)] src.write('OUTP:CHAN1 ON') src.write('OUTP:MAST ON') for u_src in u_sources: print('set source', u_src) src.write(f'APPLY {u_src}V,{i_source}ma,1') for uc in ucs: src.write(f'APPLY {uc}V,{i_source}ma,2') sa.write('CALC1:MARK1 ON') sa.write(f'CALC1:MARK1:MAX:AUTO ON') time.sleep(0.5) freq = float(sa.query(f'CALC1:MARK1:x?')) print('read freq', freq) result.append([u_src, uc, freq / 1_000_000]) u_srcs = sorted({el[0] for el in result}) res = {el[0]: list(el[1]) for el in groupby(result, key=lambda el: el[0])} res = {k: [el[2] for el in v] for k, v in res.items()} cols = ['Uc, V'] + [f'F@Usrc={u}, MHz' for u in res.keys()] df = pd.DataFrame( [[u] + freqs for u, *freqs in zip(u_srcs, *res.values())], columns=cols ) print(df) df.to_excel(file_name) wb = openpyxl.open(file_name) ws = wb.active rows = len(df) data_work_freq_xs = Reference(ws, range_string=f'{ws.title}!B1:B{rows + 1}') data_work_freq_ys = Reference(ws, range_string=f'{ws.title}!C1:E{rows + 1}') chart = LineChart() chart.add_data(data_work_freq_ys, titles_from_data=True) chart.set_categories(data_work_freq_xs) chart.title = 'Диапазон рабочих частот в зависимости от напряжения питания' ws.add_chart(chart, f'G2') wb.save(file_name) wb.close()
def write_excel(self, sheet_name, row, colnum, data): '''将测试的响应结果写入到Excel中''' work_book = openpyxl.open(self.test_file) work_sheet = work_book[sheet_name] # 写入文件到Excel中 write_data = work_sheet.cell(row, colnum).value = data work_book.save(self.test_file) work_book.close()
def assert_excel_content(file_path_or_actual_content: Union[str, bytes], expected_file_path: str): if isinstance(file_path_or_actual_content, str): actual_workbook = openpyxl.open(file_path_or_actual_content) else: actual_workbook = openpyxl.load_workbook( BytesIO(file_path_or_actual_content)) expected_workbook = openpyxl.open(expected_file_path) assert sorted(actual_workbook.sheetnames) == sorted( expected_workbook.sheetnames), "Different sheet names" for sheet_name in actual_workbook.sheetnames: _assert_sheet_content( sheet_name, actual_workbook.get_sheet_by_name(sheet_name), expected_workbook.get_sheet_by_name(sheet_name), )
def check_if_existed(self, title): """check if Excel file already exists""" try: file = o.open(title) file.close() return True except FileNotFoundError: return False
def build_sheet(): temp_code = '' intersection = 65 url_money = 'C://Users//ww//Desktop//excel//eshp//full_migration//filtered_v3.xlsx' url_eshop = 'C://Users//ww//Desktop//excel//eshp//full_migration//products_17_4.xlsx' wb_money = excel.open(url_money) ws_money = wb_money[wb_money.sheetnames[0]] wb_eshop = excel.open(url_eshop) ws_eshop = wb_eshop[wb_eshop.sheetnames[0]] new_book = excel.Workbook() new_sheet = new_book.active # exited = 'C://Users//ww//Desktop//excel//eshp//full_migration//New codes (hope so)_v1.xlsx' # exited_book = excel.open(exited) # exited_sheet = exited_book[exited_book.sheetnames[0]] # # existed_code = [row[0].value for row in exited_sheet.rows] # while intersection > 85: # print('intersection', intersection) # new_sheet.append(['intersection', intersection]) for index, row_eshop in enumerate(ws_eshop.rows): for row_money in ws_money.rows: name_eshop = remove_char(row_eshop[2].value) name_money = remove_char(row_money[2].value).split(';')[0] intersection_value = SequenceMatcher(a=name_eshop, b=name_money).ratio() * 100 intersection_value = math.ceil(intersection_value) if intersection_value > intersection: # print('intersection_value', intersection_value) # print(row_eshop[2].value, '/------\\', row_money[2].value.split(';')[0]) # print('=' * 30) whole_row = [cell.value for cell in row_eshop] whole_row[0] = row_money[0].value whole_row[3] = row_money[2].value new_sheet.append(whole_row) # intersection -= 1 new_book.save( 'C://Users//ww//Desktop//excel//eshp//full_migration//New codes (hope so)_v3.xlsx' )