def content_parse_logic_regression(bsobj, target_url, rowNumber):
input_numberofroomtype_value = bsobj.find('input', attrs={'id': 'numberOfRoomType_id'})['value']
train_result_dict_id_value = bsobj.find('input', attrs={'id': 'train_result_dict_id'})['value']
print(train_result_dict_id_value)
global ROWNUMBERGOBAL
if train_result_dict_id_value != '' and input_numberofroomtype_value != '0':
train_result_dict = literal_eval(train_result_dict_id_value)
print("rowNumber:{}".format(rowNumber))
# print(train_result_dict)
rowNumber += 1
xw.Range((rowNumber, 1)).value = target_url
xw.Range((rowNumber, 2)).value = train_result_dict['NumberOfRooms']
xw.Range((rowNumber, 3)).value = train_result_dict['Completeness']
xw.Range((rowNumber, 4)).value = train_result_dict['CompletenessGain']
xw.Range((rowNumber, 5)).value = train_result_dict['Purity']
time.sleep(2)
ROWNUMBERGOBAL = rowNumber
else:
print("content is null {}".format(target_url))
rowNumber += 1
xw.Range((rowNumber, 1)).value = target_url
xw.Range((rowNumber, 2)).value = ''
xw.Range((rowNumber, 3)).value = ''
xw.Range((rowNumber, 4)).value = ''
xw.Range((rowNumber, 5)).value = ''
ROWNUMBERGOBAL = rowNumber
def master_shop():
global dict_df
global wb
dict_df = import_data()
wb = xw.Workbook.caller()
if dict_df == 0:
# wb.macro('ShowMsg')("Data Import Failed")
return 0
l_output = [l for l in xw.Range(
'Macro', 'OutputField').value if l[0] != None]
df_output = DataFrame(
l_output, columns=['FilePath', 'FileName', 'DataType', 'Status'])
dict_ms = {"BOM": ms_BOM,
"COOP": ms_COOP,
"Future Sample Receipts": ms_Sample,
"Gross Cost Margin": ms_Margin,
"Inbound Freight": ms_Freight,
"Receipts": ms_Receipts,
"Sales, Discounts, Points": ms_Sales}
for index, row in df_output.iterrows():
try:
row['Status'] = dict_ms[row['DataType']](row)
except:
row['Status'] = "Fail"
wb.active()
xw.Range('Macro', 'C_Status').options(
transpose=True).value = df_output['Status'].tolist()
wb.macro('ShowMsg')("Done!")
def add_act_in_main_f(empty_line_in_table, all_list_name_act_date_nds):
"""
Добавляем номер акта, сумму без НДС, дату акта, по совпадени с номером счета
Args:
empty_line_in_table:
all_list_name_act_date_nds:
Returns:
"""
#[[дата, номер акта, номер счета, прибор, сумма без НДС]...]
# Можно добавить проверку по названию прибора
i = 0
wb = xw.Book('Уралтест.xlsx')
open_f_act = xlrd.open_workbook('Уралтест.xlsx')
sheet_f_act = open_f_act.sheet_by_index(0)
info_in_act = [
sheet_f_act.row_values(rownum) for rownum in range(sheet_f_act.nrows)
]
for item_all_list_name_act_date_nds in all_list_name_act_date_nds:
for item_info_in_act_f_exel in info_in_act:
i += 1
if not isinstance(item_info_in_act_f_exel[4], str):
if int(item_info_in_act_f_exel[4]) == int(
item_all_list_name_act_date_nds[2]):
xw.Range('K' +
str(i)).value = item_all_list_name_act_date_nds[1]
xw.Range('L' +
str(i)).value = item_all_list_name_act_date_nds[0]
xw.Range('M' +
str(i)).value = item_all_list_name_act_date_nds[4]
wb.save()
wb.close()
def insert_row(settles, full_update):
#row: GE GE GE TU FV ZN ZB
row = [
date.today().strftime("%m/%d/%Y"),
settles['Eurodollar FuturesSettlements ' + EURO_DATES[0]],
settles['Eurodollar FuturesSettlements ' + EURO_DATES[1]],
settles['Eurodollar FuturesSettlements ' + EURO_DATES[2]],
settles['2-Year T-Note FuturesSettlements'],
settles['5-Year T-Note FuturesSettlements'],
settles['10-Year T-Note FuturesSettlements'],
settles['U.S. Treasury Bond FuturesSettlements']
]
last = xw.Range('A1:OO1').end('down')
if str(last.offset(0,
3).value) == "This value has not been updated today. ":
next_empty = last
last = last.offset(-1)
else:
next_empty = last.offset(1)
next_empty_row = xw.Range(next_empty, next_empty.offset(0, 300))
next_empty_row.api.Insert()
next_empty = next_empty.offset(-1)
xw.Range(last, next_empty).formula = last.formula
xw.Range(next_empty.offset(0, 2), next_empty.offset(0, 9)).value = row
if full_update:
for i in range(10, 300):
xw.Range(last.offset(0, i),
next_empty.offset(0, i)).formula = last.offset(0,
i).formula
def run():
num_data = int(xw.Range("B2").value)
for i in range(5, num_data + 5):
xw.Range("B" + str(i)).value = i - 4
time.sleep(1)
def preprocess_excel(self):
self.sheet_work_stock.range("B1").value = "Stock Treader excel_package"
self.sheet_work_stock.range("I1").api.Font.Bold = True
self.sheet_work_stock.range("A2:j2").color = (96, 211, 249
) # blue header
self.sheet_work_stock.range("A2:A2").color = (255, 165, 0
) # orange header
self.sheet_work_stock.range("H2:J2").color = (255, 165, 0
) # orange header
self.sheet_work_stock.range('A3:j32').color = (230, 230, 230
) # gray body
self.sheet_work_stock.range('A2:j32').api.Borders.Weight = 3
self.sheet_work_stock.range('A2:j32').api.Font.Bold = True
self.sheet_work_stock.range("A3:E32").options(
transpose=True).value = list(stocks_translate_dict.values())
self.sheet_work_stock.range("A2").value = [
'stock', "value", "bid", 'ask', "min", "max", "open",
"higher to send", "lower to send"
]
xw.Range('H2:I2').autofit()
# graph chose
self.sheet_work_stock.range("P2:Q2").color = (255, 165, 0
) # orange header
self.sheet_work_stock.range("P2").value = ['start date', "end date"]
self.sheet_work_stock.range("P3").value = ["2019-11-1", "2019-12-31"]
xw.Range('P3:Q3').autofit()
self.sheet_work_stock.range("P3:Q3").color = (230, 230, 230
) # gray body
self.sheet_work_stock.range("P2:Q3").api.Borders.Weight = 3
self.sheet_work_stock.range("P2:Q3").api.Font.Bold = True
def get_results(wb, abbrev):
sheetname = abbrev
result_columns = RESULT_COLUMNS[abbrev]
# Collect response spectrum
d = {}
for key, rc in zip(["periods", "spec_accels", "ln_stds"], result_columns):
d[key] = xw.Range(sheetname, "{col}6:{col}26".format(col=rc),
wkb=wb).value
# Collect the PGA and PGV
for key, row in zip(["pga", "pgv"], [28, 29]):
values = xw.Range(
sheetname,
"{col0:}{row:d}:{col1:}{row:d}".format(col0=result_columns[1],
col1=result_columns[2],
row=row),
wkb=wb,
).value
for subkey, v in zip(["", "_ln_std"], values):
try:
float(v)
except ValueError:
v = None
d[key + subkey] = v
return d
def main():
wb = xw.Workbook.caller()
# get data and remove extra rows showing totals
df1 = get_df(1)
df2 = get_df(2)
df1 = rm_rows(df1, "employee_ssn", "Total")
df2 = rm_rows(df2, "GEMSID", "Total")
# get employee by client count data frames for each
client_count1 = get_client_count(df1)
client_count2 = get_client_count(df2)
# merge data frames for variance analysis
client_var = get_eid_var(client_count1, client_count2)
# filter client variance list with the list of employee's who have loaned
# to OT hours greater than 0
valid_eids = get_valid_eids(
df1
) # must be un-grouped df and we only need to look at employees on sheet 1
final = refine_list(client_var, "EmployeeID", valid_eids)
# print list of employee IDs to file; if none, print 'None'
eids = list(final.EmployeeID)
if not eids:
xw.Range('bldg1_empl', 'F3').value = "None"
else:
xw.Range('bldg1_empl', 'F3').value = eids
def get_table(table_name=None, mandatory_keys=None, default_values=None):
"""
Read the content of a table from the active worksheet in excel.
The table must have a dynamic name assigned to it. This function
will create a dictionary with the row as the primary key, columns
as the sub-keys which are mapped to the cell content.
Args:
table_name(str): The name of the table in excel.
mandatory_keys (list): table mandatory keys, from launcher.json
default_values (dict): table default values, from launcher.json
Returns:
table(dict): content from table in excel that has removed
invalid rows and has default values applied to cells
"""
t = xw.Range(table_name).options(numbers=int)
header = xw.Range(table_name).value[0]
table = {}
# skip the first two rows (headers)
for row, col in enumerate(t.value[2:]):
dictionary = dict(zip(header, col))
table[row] = {}
table[row] = dictionary
table[row]['status_cell'] = t[(row + 2, 0)].address
# remove invalid rows
table = remove_invalid_rows(table, mandatory_keys=mandatory_keys)
# if a non-mandatory cell has no value, then use default values
table = apply_default_values(table, default_values=default_values)
return table
def main():
pyautogui.FAILSAFE = True
while True:
last_row = input(
"What is the last row in the excel sheet occupied by students? ")
try:
int(last_row)
break
except ValueError:
print("Not a valid row number, please try again ")
names, rejects, xl_rejects, xl_rejects_reverse = name_check(last_row)
# begin entering data
while True:
# check for quimestre scores
if 'QUIM' in xw.sheets.active.name:
columns = ['C']
else:
columns = ['X', 'Y', 'Z', 'AA', 'AB']
# start data entry
input(
"...\n...\nMove your mouse to the first cell then press 'Enter'"
" \n ***Leave the computer alone after you press Enter!*** "
" \n ***Move your mouse to the upper left hand\n corner of the screen to stop the process!***"
"...\n...\n")
pyautogui.click(pyautogui.position())
pyautogui.hotkey('ctrl', 'a')
for ind, col in enumerate(columns):
grade_dict = {}
if ind % 2 == 0:
# changed rounding
xl_range = list(
xw.Range(f'{col}11:{col}{last_row}').value).copy()
xl_range_no_null = [
0.00 if x == '' or x is None else x for x in xl_range
]
scores = [
str(format(score, '.2f')) for score in xl_range_no_null
]
for index in xl_rejects:
scores.pop(index)
copy_paste(scores, names, rejects)
else:
xl_range = list(
xw.Range(f'{col}11:{col}{last_row}').value)[::-1].copy()
xl_range_no_null = [
0.00 if x == '' or x is None else x for x in xl_range
]
scores = [
str(format(score, '.2f')) for score in xl_range_no_null
]
for index in xl_rejects_reverse:
scores.pop(index)
copy_paste(scores, names[::-1], rejects, reverse=True)
input(
"\n\nGrade entry complete, press enter and open up the next sheet.\n"
)
def write_in_chunks(wb, sheet_name, c_start, df_data, chunk_size=5000):
n_lim = df_data.shape[0]
n_chunk = int(math.ceil(n_lim * 1.0 / chunk_size))
wb.active()
for i in range(n_chunk):
xw.Range(sheet_name, c_start).value = df_data[
i * chunk_size:min((i + 1) * chunk_size, n_lim), :]
c_start = xw.Range(sheet_name, c_start).vertical.last_cell.offset(
1).get_address()
def ExcelReadtoList():
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
F = filedialog.askopenfilename()
import xlwings as xw
wb = xw.Workbook(F)
Results = {}
Result = {}
endRow = 500
endCol = 100
sheetNo = 1
while True:
try:
carrier = xw.Sheet(sheetNo).name
Results[carrier] = xw.Range(sheetNo, (1, 1),
(endRow, endCol)).value
maxCol = 1
for i in range(0, endRow):
countCol = endCol - 1
for j in range(endCol - 1, -1, -1):
if Results[carrier][i][j] != None:
break
else:
countCol -= 1
if maxCol < countCol:
maxCol = countCol
maxRow = 1
for i in range(0, endCol):
countRow = endRow - 1
for j in range(endRow - 1, -1, -1):
if Results[carrier][j][i] != None:
break
else:
countRow -= 1
if maxRow < countRow:
maxRow = countRow
Result[carrier] = xw.Range(sheetNo, (1, 1),
(maxRow + 1, maxCol + 1)).value
sheetNo += 1
except:
wb.close()
print(
'Completed!\nBe noted maximum of %d rows and %d columns have been tested!!!'
% (endRow, endCol))
return Result
def normalize_mole_fractions_from_excel(cls, begin_cell, end_cell):
workbook = xw.Book.caller()
input_mole_fractions = xw.Range(begin_cell + ':' + end_cell).value
input_composition = []
for mole_fraction in input_mole_fractions:
input_composition.append(("", mole_fraction, 0.0))
normalized_mole_fractions = PropertyPackage.normalize_mole_fractions(input_composition)
xw.Range(begin_cell).options(transpose=True).value = normalized_mole_fractions
return
def get_file_list():
"""get the list of files in the folder"""
wb = xw.Workbook.caller()
path_input = xw.Range('Macro', 'C2').value
l_file_path = glob.glob(path_input + '*.*')
l_file_name = [l.split('/')[-1] for l in l_file_path]
xw.Range('File', 'A2:B100').clear_contents()
xw.Range('File', 'A2').options(transpose=True).value = l_file_path
xw.Range('File', 'B2').options(transpose=True).value = l_file_name
xw.Sheet('File').activate()
def compName_toexcel(startrow, endrow):
#输出公司名
compNameDic = companyName()
if startrow <= endrow:
xx = xw.Range("c%d:c%d" % (startrow, endrow)).value
if isinstance(xx, str):
xx = [xx]
xw.Range("d" + str(startrow)).options(transpose=True).value = [
compNameDic[i] for i in xx
]
def get_results(wb):
d = {}
# Collect PGA and PGV
for key, row in zip(['pga', 'pgv'], [13, 10]):
d[key] = xw.Range('ANN(PGA)', 'O%d' % row, wkb=wb).value
# Collect response spectrum
for key, rc in zip(['periods', 'spec_accels'], 'NO'):
d[key] = xw.Range('ANN(PGA)', '%s17:%s78' % (rc, rc), wkb=wb).value
return d
def get_results(wb):
d = {}
# Collect PGA and PGV
for key, row in zip(["pga", "pgv"], [13, 10]):
d[key] = xw.Range("ANN(PGA)", "O%d" % row, wkb=wb).value
# Collect response spectrum
for key, rc in zip(["periods", "spec_accels"], "NO"):
d[key] = xw.Range("ANN(PGA)", "%s17:%s78" % (rc, rc), wkb=wb).value
return d
def get_file_list():
"""get the list of files in the folder"""
wb = xw.Workbook.caller()
path_input = xw.Range('Macro', 'FilePath').value
l_file_path = glob.glob(path_input + '[!~]*.*')
l_file_name = [l.split('/')[-1] for l in l_file_path]
xw.Range('Macro', 'FileField').clear_contents()
xw.Range('Macro', 'C_FilePath').options(transpose=True).value = l_file_path
xw.Range('Macro', 'C_FileName').options(transpose=True).value = l_file_name
xw.Sheet('Macro').activate()
wb.macro('ShowMsg')("Choose DataType for all the listed files")
def set_row_vals():
''' Note: must have active link to a workbook'''
vals = xw.Range("B:B").value
list_vals = []
xw.Range("B:B").number_format = "@"
for cell in vals:
list_vals.append(
[cell]
) # to insert a column vector the list must be a list of list ([[a],[b],...])
xw.Range("B:B").value = list_vals # set values
def setRangeColorH(
xId_: int,
yId_: int,
count_: int,
isUp_: bool = True,
color_: constants.RgbColor = constants.RgbColor.rgbMediumSeaGreen):
if count_ == 0:
return
if isUp_:
xlwings.Range((yId_, xId_), (yId_, xId_ + count_)).color = color_
else:
xlwings.Range((yId_, xId_ - count_), (yId_, xId_)).color = color_
def test_Equation_1(self):
"""First type of test for Equation_1"""
xw.books[self.workbook_name].sheets['Growing Degree Day'].activate()
goal = xw.books[self.workbook_name].sheets['Growing Degree Day'].range(xw.Range('D2'), xw.Range('D6')).options(array).value
tmin = xw.books[self.workbook_name].sheets['Growing Degree Day'].range(xw.Range('B2'), xw.Range('B6')).options(array).value
tmax = xw.books[self.workbook_name].sheets['Growing Degree Day'].range(xw.Range('C2'), xw.Range('C6')).options(array).value
inputs_for_DegreeDay = DataFrame({'T_min': tmin, 'T_max': tmax})
result = self.my_fk.evaluate_koala_model('Equation_1', inputs_for_DegreeDay).to_numpy()
assert_array_equal(goal, result)
def run_Pi():
N = xw.Range('B1').options(numbers=int).value
t0 = time.time()
temp = 0.
for i in range(N):
x, y = random.random(), random.random()
if x * x + y * y <= 1.:
temp += 1
my_Pi = 4. * temp / N
t1 = time.time() - t0
xw.Range('B2').value = my_Pi
xw.Range('B3').value = t1
def get10Gudong():
driver = webdriver.Chrome()
try:
sz_url = 'http://f10.eastmoney.com/f10_v2/ShareholderResearch.aspx?code=sz'
sh_url = 'http://f10.eastmoney.com/f10_v2/ShareholderResearch.aspx?code=sh'
gudong = []
codes = []
eachGudong = []
for i in xw.Range('A2:A1000'):
if i.value == None:
break
codes.append(i.value)
for code in codes:
if int(code)>600000:
driver.get(sh_url+code)
else:
driver.get(sz_url+code)
soup = bs(driver.page_source, 'lxml')
if str(pandas.read_html(str(soup.findAll('table')[1]))[0][2][0]) == '股东性质':
table = soup.findAll('table')[7]
print(pandas.read_html(str(soup.findAll('table'))))
tab = pandas.read_html(str(table))
#print('if', tab)
gongsi = list(tab[0][1][1:-1])
bizhong = list(tab[0][4][1:-1])
for i, j in enumerate(gongsi):
eachGudong.append(j + '（' + bizhong[i] + '）')
gudong.append('、'.join(eachGudong))
#print(driver.find_element_by_xpath('/html/body/div[1]/div[13]/div[2]'))
else:
table = soup.findAll('table')[1]
tab = pandas.read_html(str(table))
print('else', tab)
gongsi = list(tab[0][1][1:-1])
bizhong = list(tab[0][4][1:-1])
for i, j in enumerate(gongsi):
eachGudong.append(j + '（' + bizhong[i] + '）')
gudong.append('、'.join(eachGudong))
gudong.insert(0, '10大股东')
print(gudong)
xw.Range('P1').options(transpose=True).value = gudong
finally:
#input()
driver.quit()
def run():
ser = serial.Serial('COM13', 9600)
time.sleep(1)
for i in range(1, 11):
line = ser.readline()
line = int(line)
xw.Range("A" + str(i)).value = i
xw.Range("B" + str(i)).value = str(line)
time.sleep(0.1)
sp.close()
def get_info_for_AID(unique_AID, get_AID_table):
for index, value in enumerate(get_AID_table):
if value == unique_AID:
indeces_set.add(int(index) + 2)
# get project names, WS id of projects,
# comments and scoping
for i in indeces_set:
try:
link = xlwings.Range((i, wsproj_pos)).hyperlink
except Exception:
missing_link.append(str(i))
else:
project_name = str(xlwings.Range((i, wsproj_pos)).value)
project_id_search = re.search(project_id_rgx, link)
try:
project_id = project_id_search.group(1)
except AttributeError:
missing_link.append(str(i))
else:
project_name = project_name.replace('\xa0', '')
project_deadline = datetime.datetime.strptime(
str(xlwings.Range((i, projdeadline_pos)).value),
"%Y-%m-%d %H:%M:%S").strftime('%d-%m-%Y %H:%M:%S').split(
" ")[0]
project_name_list.append(project_name)
deadline_dict[project_name] = project_deadline
checkbox_value.append(project_id)
comment_list.append(str(xlwings.Range((i, comment_pos)).value))
freelancer_name = xlwings.Range((i, freelance_pos)).value
try:
freelance_PO_pos = email_list.index(freelancer_name) + 2
except UnboundLocalError:
indeces_set.clear()
else:
projects_list = project_name_list[:]
checkbox_list = checkbox_value[:]
software_value = str(wb.sheets['PO'].range(
(freelance_PO_pos, software_pos)).value).lower()
if "trados" in software_value or ("memoq" in software_value
or "memo q" in software_value):
scheme_value = 'trados_studio'
else:
scheme_value = 'xliff'
AID_data[unique_AID] = [checkbox_list, scheme_value, projects_list]
del project_name_list[:]
del checkbox_value[:]
del comment_list[:]
indeces_set.clear()
'''Check if Excel is active and if the correct
def check():
# get target url list
targets = xw.Range('A3').table.value
# check each url
for target in targets:
if target[0] == "No":
continue
else:
result = uc.url_check(target[1])
target[2] = result[0]
target[3] = result[1]
# set the result
xw.Range('A3').value = targets
def predict(self):
try:
data = self.wb.sheets(1).used_range.options(pd.DataFrame).value
cat_data = pd.get_dummies(data).values # converting to dummies
result = self.model.model.predict(
cat_data) #model.model!!!!! shity code
col = last_col(self.wb) + 1
xw.Range((1, col)).value = 'ALERT PREDICTION'
for v, k in zip(result, range(2, len(result) + 2)):
xw.Range((k, col)).value = v
except:
text = 'Data isn t good looking.'
message = QMessageBox()
message.setText(text)
message.setWindowTitle('Error')
def pre_run(self):
self._xlwing_sheet.range("B1").value = "Stock trader excel_package"
self._xlwing_sheet.range("I1").api.Font.Bold = True
self._xlwing_sheet.range("A2:j2").color = BLUE_HEADER # blue header
self._xlwing_sheet.range("A2:A2").color = ORANGE_HEADER # orange header
self._xlwing_sheet.range("I2:M2").color = ORANGE_HEADER # orange header
self._xlwing_sheet.range('A3:M32').color = (230, 230, 230) # gray body
self._xlwing_sheet.range('A2:M32').api.Borders.Weight = 3
self._xlwing_sheet.range('A2:M32').api.Font.Bold = True
# self._xlwing_sheet.range("A3:E32").options(transpose=True).value = list(stocks_translate_dict.values())
self._xlwing_sheet.range("A2").value = ['stock', "value", "bid", 'ask', "min", "max", "open", "volume", "interval",
"days ago", "Buy/Sell", "count", "condition"]
# graph chose
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[0]}1").value = "Graph dates"
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[0]}2:{GRAPH_DATES_COL[0]}3").color = ORANGE_HEADER # orange header
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[0]}2").value = ['start date', "2019-11-1"]
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[0]}3").value = ["end date", "2019-12-31"]
xw.Range(f'{GRAPH_DATES_COL[1]}2:{GRAPH_DATES_COL[1]}3').autofit()
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[1]}2:{GRAPH_DATES_COL[1]}3").color = (230, 230, 230) # gray body
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[0]}2:{GRAPH_DATES_COL[0]}3").api.Borders.Weight = 3
self._xlwing_sheet.range(f"{GRAPH_DATES_COL[0]}2:{GRAPH_DATES_COL[1]}3").api.Font.Bold = True
# Add hyper link
self._xlwing_sheet.range(f"{LINK_COL}1").value = "Graph Links"
links_val = [HYPER_LINK.format(sheet.name) for sheet in xw.Book(self.file_name).sheets]
for i, link in zip(range(len(links_val)), links_val):
self._xlwing_sheet.range(f"{LINK_COL}{i + 2}").value = link
# Mail
self._xlwing_sheet.range(f"{MAIL_COL[0]}1").value = "Mail for updates"
self._xlwing_sheet.range(f"{MAIL_COL}2").api.Borders.Weight = 3
self._xlwing_sheet.range(f"{MAIL_COL}2").api.Font.Bold = True
def arrange_seat(input_filename, output_filename):
with open(input_filename) as input_file:
students = json.load(input_file)
shuffle(students)
student_num = len(students)
col_num = sum(len(cols) for cols in AVAILABLE_COLS_IN_EACH_ROOM.values())
col_length = ceil(student_num / col_num)
available_rows = range(START_ROW, START_ROW + (col_length - 1) * 2 + 1, 2)
xb = xw.Book(output_filename)
counter = 0
try:
for room, cols in AVAILABLE_COLS_IN_EACH_ROOM.items():
xw.Sheet(room).activate()
print(room)
for col in cols:
for row in available_rows:
print('{}{}'.format(col, row), students[counter])
xw.Range('{}{}'.format(col, row)).value = students[counter]
counter += 1
except IndexError:
pass
xb.save()
xb.close()
def get_users_data():
"""
Returns a data frame of pandas data
"""
df = xw.Range('A1').expand().options(pd.DataFrame).value
df.reset_index(inplace=True)
return df