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