def build_addins():
# transform code for addin use
with open(os.path.join(par_dir, "xlwings", "xlwings.bas"), "r") as vba_module, \
open(os.path.join(this_dir, "xlwings_addin.bas"), "w") as vba_addin:
content = vba_module.read().replace("ThisWorkbook", "ActiveWorkbook")
content = content.replace('Attribute VB_Name = "xlwings"', 'Attribute VB_Name = "xlwings_addin"')
vba_addin.write(content)
# create addin workbook
wb = Book()
# remove unneeded sheets
for sh in list(wb.xl_workbook.Sheets)[1:]:
sh.Delete()
# rename vbproject
wb.xl_workbook.VBProject.Name = "xlwings"
# import modules
wb.xl_workbook.VBProject.VBComponents.Import(os.path.join(this_dir, "xlwings_addin.bas"))
# save to xla and xlam
wb.xl_workbook.IsAddin = True
wb.xl_workbook.Application.DisplayAlerts = False
# wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xla"), FileFormat.xlAddIn)
wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xlam"), FileFormat.xlOpenXMLAddIn)
wb.xl_workbook.Application.DisplayAlerts = True
# clean up
wb.close()
os.remove(os.path.join(this_dir, 'xlwings_addin.bas'))
def __init__(self, file_name='Plantilla_Declaraciones'):
try:
# If this is a standalone program the defaul dir is in Temp Folder
path = os.path.abspath(
os.path.join(os.path.dirname(__file__), file_name + '.xlsx'))
#print(path)
wb = Book(path)
except:
print("seleccione el archivo Plantilla.xlsx")
path = easygui.fileopenbox(None,
'Seleccione el archivo de Plantilla')
if path == '.':
quit()
wb = Book(path)
self.current_dir = os.path.split(path)[0]
#print(self.current_dir)
#quit()
working_sheet = Sheet('breakdown')
self.data_dict = working_sheet.range('A2:B100').options(dict).value
""":type : dict"""
print("Data values from Plantilla_Breakdown:")
for key, values in self.data_dict.items():
print(key, ": ", values)
print("#####################################")
wb.app.quit()
def merge_workbook(self, weekly_summary_wb: Book,
files_dir_path: str) -> None:
'''
合并各县区上报的数据
:param weekly_summary_wb: 每周汇总数据表
:param files_dir_path: 各县区上报表格所在的文件夹路径
:return: 无返回值
'''
sub_wb: Book = None # 用于汇总的分表
data_sheets: list = ["举报投诉核查工作", "维权工作", "督察工作情况"] # 工作簿中的三张工作表
excel_paths = self.get_dir_all_file(files_dir_path)
# 循环访问文件夹中的工作簿
print("正在合并")
for excel_path in excel_paths:
try:
sub_wb: Book = xw.Book(excel_path)
print("-->" + excel_path)
# 循环访问工作簿中的三张工作表
for data_sheet in data_sheets:
last_row: int = self.get_sheet_last_row(
weekly_summary_wb, data_sheet)
last_column: int = self.get_sheet_last_column(
weekly_summary_wb, data_sheet)
# 通过复制粘贴来合并工作表
weekly_summary_wb.sheets[data_sheet].range((last_row, 1), (last_row, last_column)).value = \
sub_wb.sheets[data_sheet].range((3, 1), (3, last_column)).value
last_row = last_row + 1
except BaseException as e:
logging.exception(e)
finally:
weekly_summary_wb.save()
sub_wb.close()
print('合并完毕')
def close(self, excel: xw.Book = None, app: xw.App = None):
""" 关闭 Excel 文档 """
if excel is None and app is None:
self.excel.close()
self.app.quit()
if excel is not None:
excel.close()
if app is not None:
app.quit()
def __init__(self, path_and_file_to_book: str = None, visible=True):
'''
:param path_and_file_to_book: Path and file name or just filename in base directory.
:param visible: If excel shall open in windows or run in the background.
'''
super(PulsExcel, self).__init__()
self.app = App(visible=visible)
self.book = Book(path_and_file_to_book)
self.names_sp = {
'Identification': 1,
'Length of panel': 3,
'Stiffener spacing': 4,
'Plate thickness': 5,
'Number of primary stiffeners': 6,
'Stiffener type (L,T,F)': 7,
'Stiffener boundary': 8,
'Stiff. Height': 9,
'Web thick.': 10,
'Flange width': 11,
'Flange thick.': 12,
'Tilt angle': 13,
'Number of sec. stiffeners': 14,
'Modulus of elasticity': 21,
"Poisson's ratio": 22,
'Yield stress plate': 23,
'Yield stress stiffener': 24,
'Axial stress': 25,
'Trans. stress 1': 26,
'Trans. stress 2': 27,
'Shear stress': 28,
'Pressure (fixed)': 29,
'In-plane support': 30
}
self.names_up = {
'Identification': 1,
'Length of plate': 3,
'Width of c': 4,
'Plate thickness': 5,
'Modulus of elasticity': 6,
"Poisson's ratio": 7,
'Yield stress plate': 8,
'Axial stress 1': 9,
'Axial stress 2': 10,
'Trans. stress 1': 11,
'Trans. stress 2': 12,
'Shear stress': 13,
'Pressure (fixed)': 14,
'In-plane support': 15,
'Rot left': 16,
'Rot right': 17,
'Rot upper': 18,
'Rot lower': 19
}
def close(self,
excel_book: xlwings.Book = None,
excel_app: xlwings.App = None):
""" 关闭 Excel 文档 """
""" 无参数时，关闭程序初始化进程 """
if excel_book is None and excel_app is None:
self.excel.close()
self.app.quit()
if excel_book is not None:
excel_book.close()
if excel_app is not None:
excel_app.quit()
def get_named_struct_list_from_existing_nms_only(nms_list: list, bk: xw.Book):
"""Function to convert a list of known named range names to a named struct.
Arguments:
nms_list {list} -- list of known names.
bk {xw.Book} -- book names exist in.
"""
return [NameStruct(nm, bk.names(nm).refers_to) for nm in nms_list]
def main():
print('checkmyxl project generator')
parser = ArgumentParser(add_help=False)
parser.add_argument('-s', '--sample', action='store_true')
args = parser.parse_args()
try:
run(['xlwings', 'quickstart', 'book'], check=True)
except FileNotFoundError:
print('Command not found: `xlwings`.',
'xlwings package is not installed.',
'Online installation guide:',
'https://docs.xlwings.org/en/stable/installation.html.',
sep='\n')
exit(1)
try:
run(['mv', join_path('book', 'book.xlsm'), 'book.xlsm'], check=True)
except CalledProcessError:
print('The project `book` has not been created.')
exit(1)
try:
run(['rm', '-r', 'book'], check=True)
except CalledProcessError:
print('Temporary directory `book` cannot be removed.')
exit(1)
if apps.count == 0:
App()
Book('book.xlsm').set_mock_caller()
book = Book.caller()
sheets = book.sheets
sheets.add('Sheet1.code', after='Sheet1')
sheets.add('checkmyxl.conf', after='Sheet1.code')
book.save()
print('Successfully generated `book.xlsm` file.')
if args.sample:
sheet1, sheet1_code, checkmyxl_conf = \
sheets['Sheet1'], sheets['Sheet1.code'], sheets['checkmyxl.conf']
sheet1['A1'].options(index=False).value = \
read_csv(join_path('sample', 'sample.csv'))
sheet1_code['A1'].options(index=False).value = \
read_csv(join_path('sample', 'sample_code.csv'))
checkmyxl_conf['A1'].options(index=False).value = \
read_csv(join_path('sample', 'sample_conf.csv'))
print('Sample added.')
def summarize_sales():
"""
Retrieve the account number and date ranges from the Excel sheet
"""
# Make a connection to the calling Excel file
wb = Book.caller()
# Retrieve the account number and dates
account = Range('B2').value
start_date = Range('D2').value
end_date = Range('F2').value
# Output the data just to make sure it all works
Range('A5').value = account
Range('A6').value = start_date
Range('A7').value = end_date
def open_ssrs_report_file():
last_modified = 0
last_modified_path = None
# scan through folder looking for most recent SSRS report file
for dir_entry in scandir(expanduser(r"~\Downloads")):
if dir_entry.name.startswith(WORKORDER_SHEET_NAME):
if dir_entry.stat().st_mtime > last_modified:
last_modified = dir_entry.stat().st_mtime
last_modified_path = dir_entry.path
# no SSRS report file found
if last_modified_path is None:
MessageBox(0, "Please download report from SSRS", "Report Not Found")
raise FileNotFoundError
return Book(last_modified_path)
def change_tool_environment(env: str, tool: xw.Book = None):
'''
Changes the db environment that the defined wtso tool is pointing to. Strings are defined in constants.
TODO: Move the connection string cells to constants.
:param env: The target environment as a three character caps string
'''
if (tool is None):
tool = xw.books.active
tool.app.api.EnableEvents = False
info_sheet = tool.sheets('Tool Info')
info_sheet.api.Visible = True
unprotect_wtso_sheet(info_sheet)
conn_list = c.XL_CONNECTIONS[env]
info_sheet.range("d3").value = conn_list[0]
info_sheet.range("e3").value = conn_list[1]
info_sheet.api.Visible = False
tool.app.api.EnableEvents = True
def main():
book = Book.caller()
active_sheet = book.sheets.active
active_range = active_sheet.used_range
code_sheet = book.sheets[f'{active_sheet.name}.code']
code_range = code_sheet.used_range
conf_sheet = book.sheets['checkmyxl.conf']
imports = conf_sheet['B1'].value
exec(imports)
for cell in code_range:
if cell.value is not None:
this_sheet = active_sheet
this_table = active_range
this_row = active_sheet.range(
(cell.row, active_range.column),
(cell.row, active_range.last_cell.column))
this_column = active_sheet.range(
(active_range.row + 1, cell.column),
(active_range.last_cell.row, cell.column))
this_cell = active_sheet[cell.get_address(False, False)]
exec(cell.value)
from checkmyxl import main
from xlwings import App, apps, Book
if __name__ == '__main__':
if apps.count == 0:
App()
Book('book.xlsm').set_mock_caller()
main()
def projekt():
sheet_name = "Zakupy"
wb = Book(r'Dane.xlsm')
table = wb.sheets[sheet_name].range('A1').current_region
[ids, dates, size, cost] = table.options(transpose=True).value
ids = [int(x) for x in ids[1:]
] # ids będą liczbami całkowitymi (bez tej pętli byłyby typu float)
dates_zakupy = [
datetime.strptime(data, '%d.%m.%Y').date() for data in dates[1:]
]
size = size[1:]
cost = cost[1:]
sheet_name = "Klient"
table = wb.sheets[sheet_name].range('A1').current_region
[names, birth, ids_klient] = table.options(transpose=True).value
names = names[1:]
birth = [datetime.strptime(data, '%d-%m-%Y') for data in birth[1:]]
ids_klient = [int(x) for x in ids_klient[1:]]
firstnames, surnames, ages = [], [], []
names[0] = ' nancy\xa0 davolio\xa0'
for i in range(0, len(birth)):
ages.append(date.today().year - birth[i].year -
((date.today().month, date.today().day) <
(birth[i].month, birth[i].day)))
for data in names:
# dzielimy napisy na spacjach, wyrzucamy puste, powiększamy 1. literę
parts = [part.capitalize() for part in data.split(" ") if part]
# zapamiętanie danych
firstnames.append(" ".join(parts[:-1]))
surnames.append(parts[-1])
# 1. zadanie: -------------------------
with open(get_path('zad1.htm'), 'wb') as f:
def pisz(tekst):
tekst += "\r\n"
f.write(tekst.encode("utf-8"))
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Posumowanie</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="tabelka.htm">Tabelka</a></li>')
pisz('<li><a href="wykres.htm">Wykres</a></li>')
pisz('<li><a href="autor.htm">Autorzy</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<div class="card-container">')
pisz('<div class="card">')
pisz('<div class="front">')
pisz('<h2>Touch me!</h2>')
pisz('</div>')
pisz('<div class="back">')
pisz('<h1>Liczba klientów: ' + str(len(set(ids))) + '</h1>')
pisz('<h1>Całkowity obrót: ' + str(round(sum(cost), 2)) + ' zł</h1>')
pisz('<h1>Ostatnia sprzedaż: ' + str(max(dates_zakupy)) + '</h1>')
pisz('</div>')
pisz('</div>')
pisz('</div>')
pisz('</body>')
pisz('</html>')
#--------------------------------------
# 2. zadanie: -------------------------
l = []
for i in set(ids):
l.extend(
[i, 0, 0, 0]
) # stworzenie listy na zadanie 2. przechowuje dane w sposób: [id_klienta , obecnosc_w_sklepie , ilosc_produktow , suma_pieniędy , id_klienta , obecnosc_w_sklepie , ilosc_produktow , suma_pieniędy itd.]
for row in range(len(ids)):
j = l.index(ids[row])
l[j + 1] += 1
l[j + 2] += size[row]
l[j + 3] += cost[row] # uzupełnia listę o dane z excela
#print(l)
#for k in range(int(len(l)/4)):
#print('ID: ',l[4*k],'Imię: ',firstnames[ids_klient.index(l[4*k])],'Nazwisko: ',surnames[ids_klient.index(l[4*k])],'Ile razy był w sklepie: ',l[4*k+1],'Ile produktów kupił: ',int(l[4*k+2]),'Ile pieniędzy zostawił: ',round(l[4*k+3],2),'zł')
#--------------------------------------
with open(get_path('Tabelka.htm'), 'wb') as f:
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Tabelka</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="zad1.htm">Podsumowanie</a></li>')
pisz('<li><a href="wykres.htm">Wykres</a></li>')
pisz('<li><a href="autor.htm">Autorzy</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<table>')
# Nagłówki
pisz('<tr>')
pisz('<td>ID</td>')
pisz('<td>Imię</td>')
pisz('<td>Nazwisko</td>')
pisz('<td>Wiek</td>')
pisz('<td>Ile razy w sklepie</td>')
pisz('<td>Suma produktów</td>')
pisz('<td>Ilość pieniędzy</td>')
pisz('</tr>')
# Zawartość tabelki
for k in range(int(len(l) / 4)):
pisz('<tr>')
pisz('<th>' + str(l[4 * k]) + '</th>')
pisz('<th>' + firstnames[ids_klient.index(l[4 * k])] + '</th>')
pisz('<th>' + surnames[ids_klient.index(l[4 * k])] + '</th>')
pisz('<th>' + str(ages[ids_klient.index(l[4 * k])]) + '</th>')
pisz('<th>' + str(l[4 * k + 1]) + '</th>')
pisz('<th>' + str(int(l[4 * k + 2])) + '</th>')
pisz('<th>' + str(round(l[4 * k + 3], 2)) + 'zł</th>')
pisz('</tr>')
pisz('</table>')
pisz('</body>')
pisz('</html>')
# Zadanie dodatkowe: ---------------------------------------
d = {}
for key in set(ages):
d[key] = 0 # stworzenie słownika łączącego wiek i wydane pieniądze
for i in range(int(len(l) / 4)):
age = ages[ids_klient.index(
l[4 * i])] # wyszukanie wieku korzystając z id klienta z listy l
d[age] += l[4 * i + 3] # dodanie pieniędzy do słownika
for i in d:
d[i] = d[i] / ages.count(i)
x = list(d.keys()) # zbiór X: wiek klienta
y = list(d.values()
) # zbiór Y: suma wydanych pieniędzy dla danego wieku klienta
plt.plot(x, y, 'ro')
plt.xlabel('wiek')
plt.ylabel('Średnia kwota')
plt.savefig(get_path('wykres.jpg'))
print(get_path('wykres.jpg'))
with open(get_path('wykres.htm'), 'wb') as f:
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Wykres</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="zad1.htm">Podsumowanie</a></li>')
pisz('<li><a href="Tabelka.htm">Tabelka</a></li>')
pisz('<li><a href="autor.htm">Autorzy</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<img src="wykres.jpg" alt="Wykres" height="500" width="700">')
pisz('</body>')
pisz('</html>')
with open(get_path('autor.htm'), 'wb') as f:
def pisz(tekst):
tekst += "\r\n"
f.write(tekst.encode("utf-8"))
pisz('<!DOCTYPE html>')
pisz('<html lang="pl">')
pisz('<head>')
pisz('<meta charset="utf-8">')
pisz(
'<link href="https://fonts.googleapis.com/css?family=Do+Hyeon|Gugi" rel="stylesheet">'
)
pisz('<link rel="stylesheet" href="styl1.css">')
pisz('<title>Autorzy</title>')
pisz('</head>')
pisz('<body>')
pisz('<nav>')
pisz('<ul>')
pisz('<li><a href="tabelka.htm">Tabelka</a></li>')
pisz('<li><a href="wykres.htm">Wykres</a></li>')
pisz('<li><a href="zad1.htm">Podsumowanie</a></li>')
pisz('</ul>')
pisz('</nav>')
pisz('<h3>Damian Matusiak</h3>')
pisz('<h4>Numer indeksu: 243027</h4>')
pisz(
'<a href="mailto:[email protected]" target="_top">Napisz email</a>'
)
pisz('<h3>Jakub Jasiński</h4>')
pisz('<h4>Numer indeksu: 243019</h4>')
pisz(
'<a href="mailto:[email protected]" target="_top">Napisz email</a>'
)
pisz('</body>')
pisz('</html>')
class PulsExcel():
''' This class open a PulsExcel work.
Input and output structure data and results.
Running macros.
'''
def __init__(self, path_and_file_to_book: str = None, visible=True):
'''
:param path_and_file_to_book: Path and file name or just filename in base directory.
:param visible: If excel shall open in windows or run in the background.
'''
super(PulsExcel, self).__init__()
self.app = App(visible=visible)
self.book = Book(path_and_file_to_book)
self.names_sp = {
'Identification': 1,
'Length of panel': 3,
'Stiffener spacing': 4,
'Plate thickness': 5,
'Number of primary stiffeners': 6,
'Stiffener type (L,T,F)': 7,
'Stiffener boundary': 8,
'Stiff. Height': 9,
'Web thick.': 10,
'Flange width': 11,
'Flange thick.': 12,
'Tilt angle': 13,
'Number of sec. stiffeners': 14,
'Modulus of elasticity': 21,
"Poisson's ratio": 22,
'Yield stress plate': 23,
'Yield stress stiffener': 24,
'Axial stress': 25,
'Trans. stress 1': 26,
'Trans. stress 2': 27,
'Shear stress': 28,
'Pressure (fixed)': 29,
'In-plane support': 30
}
self.names_up = {
'Identification': 1,
'Length of plate': 3,
'Width of c': 4,
'Plate thickness': 5,
'Modulus of elasticity': 6,
"Poisson's ratio": 7,
'Yield stress plate': 8,
'Axial stress 1': 9,
'Axial stress 2': 10,
'Trans. stress 1': 11,
'Trans. stress 2': 12,
'Shear stress': 13,
'Pressure (fixed)': 14,
'In-plane support': 15,
'Rot left': 16,
'Rot right': 17,
'Rot upper': 18,
'Rot lower': 19
}
def close_book(self, save=False):
''' Closing ass and book. '''
if save:
self.book.save()
self.book.close()
self.app.kill()
def read_data(self, row_number: int = 1, column_number: int = 1):
''' Read one cell in the sheet
Row and columns starts at 1 (not 0 as in python general)
'''
return self.book.sheets[1].range((row_number, column_number)).value
def set_cell_value(self,
row_number: int = 1,
column_number: int = 1,
cell_value=None,
sheet_num=1):
'''
Set values of a cell.
:param row_number:
:param column_number:
:param cell_value:
:return:
'''
#print('Row', row_number, 'Col', column_number, 'Cell', cell_value)
self.book.sheets[sheet_num].range(
(row_number, column_number)).value = cell_value
def set_one_row(self, row_number: int = 20, data_dict: dict = None):
'''
Set one row of values
:param row_number: The row to set.
:param data_dict: Data for one panel.
:return:
'''
for name, col_num in self.names_sp.items():
self.set_cell_value(row_number, col_num, data_dict[name])
def set_multiple_rows(self, start_row: int = 20, data_dict: dict = None):
'''
:param start_row: First row to input.
:param list_of_dicts: The data to be set.
:return:
'''
row_number = start_row
for id, data in data_dict.items():
self.set_one_row(row_number, data)
row_number += 1
def set_multiple_rows_batch(self, data_dict: dict = None):
'''
:param start_row: First row to input.
:param list_of_dicts: The data to be set.
:return:
'''
sp_dict, up_dict = dict(), dict()
for key, val in data_dict.items():
if val['sp or up'] == 'SP':
sp_dict[key] = val
else:
up_dict[key] = val
if len(sp_dict) > 0:
for name, col_num in self.names_sp.items():
start_row = 20
self.book.sheets[1].range((start_row, col_num)).options(expand='table', transpose=True).value = \
[val[name] for val in sp_dict.values()]
if len(up_dict) > 0:
for name, col_num in self.names_up.items():
start_row = 21
self.book.sheets[4].range((start_row, col_num)).options(expand='table', transpose=True).value = \
[val[name] for val in up_dict.values()]
return len(sp_dict) > 0, len(up_dict) > 0
def calculate_panels(self, sp=True, up=False):
''' Calculate the panels in the sheet. '''
if sp:
run_macro_sp = self.app.macro('Sheet1.cmdCalculatePanels_Click')
run_macro_sp()
if up:
run_macro_up = self.app.macro('Sheet3.CalculatePanelsU3')
run_macro_up()
def get_results_one_cell(self,
row_number: int = 1,
column_number: int = 1):
''' Return the results in one cell of a calculated panel. '''
return self.book.sheets[2].range((row_number, column_number)).value
def get_results_one_row(self, row_number: int = 15):
'''
Return one row.
:param row_number:
:return: dict : [value, unit] e.g. [3000, 'mm']
'''
return_dict = dict()
return_dict['Identification'] = self.get_results_one_cell(
row_number, column_number=1)
# print('start')
# for idx in range(3,74):
# if self.get_results_one_cell(11, column_number=idx) != None:
# return_dict[self.get_results_one_cell(11, column_number=idx)] = {}
# print('stop')
current_top_row = ''
for idx in range(3, 74):
if self.get_results_one_cell(11, column_number=idx) != None:
current_top_row = self.get_results_one_cell(11,
column_number=idx)
return_dict[current_top_row] = {}
return_dict[current_top_row][self.get_results_one_cell(12, column_number=idx)] = \
[self.get_results_one_cell(row_number, column_number=idx),
self.get_results_one_cell(14, column_number=idx)]
return return_dict
def get_all_results_batch(self, sp=True, up=False):
spup = {
(True, True): ['SP', 'UP'],
(True, False): ['SP'],
(False, True): ['UP']
}
return_dict = {}
for run in spup[(sp, up)]:
all_ids = self.book.sheets[2 if run == 'SP' else 5].range(
'A15').expand().value
if type(all_ids) != list:
all_ids = [all_ids]
all_data = np.array(
self.book.sheets[2 if run == 'SP' else 5].range(
'C12').expand().value)
all_top_names = np.array(
self.book.sheets[2 if run == 'SP' else 5].range(
'C11:BU11' if run == 'SP' else 'C11:AS11').value)
all_names = all_data[0]
all_data = all_data[3:]
all_units = np.array(
self.book.sheets[2 if run == 'SP' else 5].range(
'C14:BU14' if run == 'SP' else 'C14:AS14').value)
current_top_row = ''
for data_idx, id in enumerate(all_ids):
return_dict[id] = {}
return_dict[id]['Identification'] = id
same, same_idx = False, 2
for top_name, name, data, unit in zip(all_top_names, all_names,
all_data[data_idx],
all_units):
if top_name != None:
current_top_row = top_name
return_dict[id][current_top_row] = {}
if name in return_dict[id][current_top_row].keys():
same = True
name = name + ' ' + str(same_idx)
same_idx += 1
elif same == True and name not in return_dict[id][
current_top_row].keys():
same = False
same_idx = 2
return_dict[id][current_top_row][name] = [data, unit]
if run == 'UP':
return_dict[id]['Ultimate capacity']['Actual usage Factor'] = \
return_dict[id]['Ultimate capacity']['Usage factor']
return_dict[id]['Ultimate capacity']['Allowable usage factor'] = \
return_dict[id]['Ultimate capacity']['Alowable usage']
return_dict[id]['Buckling strength']['Actual usage Factor'] = \
return_dict[id]['Buckling strength']['Usage factor']
return_dict[id]['Buckling strength']['Allowable usage factor'] = \
return_dict[id]['Buckling strength']['Alowable usage']
return_dict[id]['Ultimate capacity'].pop('Usage factor')
return_dict[id]['Ultimate capacity'].pop('Alowable usage')
return_dict[id]['Buckling strength'].pop('Usage factor')
return_dict[id]['Buckling strength'].pop('Alowable usage')
return return_dict
def get_all_results(self):
'''
Return all results in run.
:return:
'''
return_dict, found_last, row_number = dict(), False, 15
while found_last is False:
this_row = self.get_results_one_row(row_number)
if this_row['Identification'] is not None:
return_dict[row_number] = this_row
row_number += 1
else:
found_last = True
return return_dict
import os
FileName = os.path.basename(__file__)[:-3]
from xlwings import Book
wb = Book(
FileName) # connect to an existing file in the current working directory
AT_sheet = wb.sheets['Actual Test Data']
TP_sheet = wb.sheets['Test Procedure Data']
FD_sheet = wb.sheets['DataSheet']
CF_sheet = wb.sheets['CalcFlow']
AT_sheet['F36'].value = 'Carregando bibliotecas...'
TP_sheet['J23'].value = 'Carregando bibliotecas...'
CF_sheet['A1'].value = 'Carregando bibliotecas...'
import xlwings as xw
from scipy.optimize import newton
import os
from time import sleep
try:
aux = os.environ['RPprefix']
except:
os.environ['RPprefix'] = 'C:\\Users\\Public\\REFPROP'
import ccp
from ccp import State, Q_
import numpy as np
global P_FD_eff
AT_sheet['H35'].value = None
from xlwings import Book, Range
from Autocall import *
from Underlying import *
from DataManager import *
from Curve import *
import os
wb = Book('Exam Data.xlsm') # Creates a reference to the calling Excel file
sht = wb.sheets['Excel Connect']
os.chdir(wb.fullname[:-15])
def convert_to_bool(txt):
if txt == "True":
return True
elif txt == "False":
return False
dict_names = {
"Eurostoxx 50 Price Index":
"STOXX",
"Eurostoxx 50 Equal Weight decrement 5% Price Index ( 5% dividend)":
"STOXX_DEC"
}
Nb_Sim = int(sht.range("C13").value)
DM = DataManager()
for lang in self.lang_select_list:
sht_raw = self.excel_raw.loc[:, ['类型', self.base_lang.get()]]
sht_trans = self.excel_trans.loc[:,
[self.base_lang.get(), lang]]
pool.apply_async(translate, (
sht_raw,
sht_trans,
self.roles,
self.return_dict,
))
pool.close()
pool.join()
# self.destroy()
self.quit()
except BaseLangError:
messagebox.showerror('Error', '翻译文档的第一列不是基本语言。')
if __name__ == "__main__":
freeze_support()
root = Root()
root.mainloop()
wb_raw = Book(root.raw_file.get())
sht_raw = wb_raw.sheets('Sheet1')
for lang, result in root.return_dict.items():
index = root.column_index_raw[lang]
sht_raw.range(3, index).options(transpose=True).value = result
# 翻译结束后弹窗告知
messagebox.showinfo('Info', "已经成功完成翻译，并写入原始文档中，请查看。未匹配单元格已标注为\'$ERROR$\'")
from xlwings import Book, Range
from deposit import *
from fra import *
from swap import *
from swaption import *
from CMS import *
from convention import *
#from workalendar.usa import NewYork
import os
wb = Book('Input-Group7.xlsm') # Creates a reference to the calling Excel file
sht = wb.sheets['Excel Connect']
os.chdir(wb.fullname[:-18])
def convert_to_bool(txt):
if txt == "True":
return True
elif txt == "False":
return False
type_of_product = sht.range('B2').value
notional = sht.range('B3').value
pricing_date = sht.range('B5').value
start_date = sht.range('B6').value
end_date = sht.range('B7').value
return_value = col_number
if modify_index is True:
col_number = col_number - 1 - offset
for row in refdata:
for value in range(size):
row.insert(col_number, "")
return return_value
# print("New:", refdata)
if __name__ == '__main__':
path_file = fileopenbox()
wb = Book(path_file)
from read_xl import ExcelReadtoList
references = ExcelReadtoList(path_file, close=False)
print(references.get("Sheet1"))
ref_data = references.get("Sheet1")
print(ref_data)
insert_col_after("SubTotal", wb, 4, ref_data, 0, True)
print(ref_data)
# col_remove_name("A:A", wb)
from xlwings import Book, Sheet, Range, Chart
import time
import matplotlib.pyplot as plt
wb = Book()
def getQuote(ticker, type):
Range('B1').value = '=RTD("tos.rtd", ,"%s", "%s")' % (type, ticker)
time.sleep(2.5)
return Range('B1').value
def quoteOption(type, ticker, cp, exp, strike):
Range('B1').value = '=RTD("tos.rtd", ,"%s", ".%s%s%s%s")' % (
type, ticker, exp, cp, strike)
time.sleep(
2.5
) #wait for excel to update the cell, usually a couple of seconds. return Range('b1')
return (Range('B1').value)
#getQuote("AMD", "LAST")
count = 0
# while (count < 10):
# quoteOption('LAST','AMD','C',170804, 14)
# quoteOption('LAST','AMD','C',170804, 15)
# quoteOption('LAST','AMD','C',170804, 16)
# quoteOption('LAST','AMD','C',170804, 17)