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