def on_clicked_pushButton_3(self): if self.senderList: e = Excel() if self.senderList: colnum = len(self.senderList) for i in range(len(self.senderList)): e.setRangeFormat('sheet1', 1, i + 1, colnum, i + 1, '@') if self.senderList: for i, row in enumerate(self.senderList): for j, cell in enumerate(row): e.setCell('sheet1', i + 1, j + 1, str(cell)) desktop = os.path.join(os.path.expanduser("~"), 'Desktop') savename = '发件人信息' + time.strftime('%m%d', time.localtime()) savepath = desktop + '\\' + savename fname = QFileDialog.getSaveFileName(self, '保存', savepath, '.xlxs') fpath = os.path.abspath(fname[0]) try: e.save(fpath) except Exception as e: self.showMsg('错误!', '保存时发生错误:\n {}'.format(e.args)) finally: e.close() else: self.showMsg('错误!', '请先上传表格并完成转换。')
def on_clicked_pushButton_3(self): desktop = os.path.join(os.path.expanduser("~"), 'Desktop') fname = QFileDialog.getOpenFileName(self.centralwidget, '打开', desktop) if fname[0]: fpath = os.path.abspath(fname[0]) if (not fpath.endswith('.xls')) and (not fpath.endswith('.xlsx')): self.showMsg('错误!', '请上传xls或者xlsx文件') return e = Excel(fpath) try: data = dict() order_sheet = e.getContiguousRange('订单表', 1, 1) data['order_data'] = e.fixStringsAndDates(order_sheet) detail_sheet = e.getContiguousRange('明细表', 1, 1) data['detail_data'] = e.fixStringsAndDates(detail_sheet) self.data = data except Exception as e: self.showMsg('错误!', '发生错误:\n {}'.format(e.args)) finally: e.close()
def on_clicked_pushButton(self): if self.output: e = Excel() if self.outformat: colnum = len(self.output) for i in range(len(self.outformat)): e.setRangeFormat('sheet1', 2, i + 1, colnum, i + 1, self.outformat[i]) if self.output: for i, row in enumerate(self.output): for j, cell in enumerate(row): if isinstance(cell, dict): e.setCell('sheet1', i + 1, j + 1, cell.get('value', '')) elif isinstance(cell, str): e.setCell('sheet1', i + 1, j + 1, cell) desktop = os.path.join(os.path.expanduser("~"), 'Desktop') savename = time.strftime('%m%d', time.localtime()) savepath = desktop + '\\' + savename save_select = QFileDialog.getSaveFileName(self, '保存', savepath, '.xlsx') if save_select[0] == '': return fpath = os.path.abspath(save_select[0]) if os.path.exists(savepath + '.xlsx'): answer = QMessageBox.warning( self.centralwidget, '警告!', '{}.xlsx 已经存在于当前目录下,是否覆盖?'.format(savepath), QMessageBox.Yes | QMessageBox.No, QMessageBox.No) if answer == QMessageBox.No: return try: e.save(fpath) except Exception as e: self.showMsg('错误!', '保存时发生错误:\n {}'.format(e.args)) finally: e.close() else: self.showMsg('错误!', '请先上传表格并完成转换。')
def nodal_displ_from_excel(self, xls_file): """Create nodal displacement constraints based on an Excel file. This function reads the xls file with nodal displacement constraints and returns a dictionary with the format:: nd = {load_case_id1 : {grid_1 : {output : [minvalue, maxvalue], grid_2 : output : [minvalue, maxvalue], grid_3 : output : [minvalue, maxvalue]}} load_case_id2 : {grid_1 : {output : [minvalue, maxvalue], grid_3 : outout : [minvalue, maxvalue]}}} where `minvalue` and `maxvalue` are the minimum and maximum displacement values. Parameters ---------- xls_file : str The full path to the Excel file. Returns ------- output : str A string with one of the values: - `'Translation X'` - `'Translation Y'` - `'Translation Z'` - `'Rotation X'` - `'Rotation Y'` - `'Rotation Z'` - `'Translation Total'` - `'Absolute X'` - `'Absolute Y'` - `'Absolute Z'` """ from excel import Excel if not xls_file: return None ex = Excel(xls_file) found = False for row in range(1, 100): for col in range(1, 23): refvalue = str(ex.get_cell(1, row, col)) if refvalue.find('DISPLACEMENT CONSTRAINTS') > -1: found = True break if found: break nd = {} irow = row + 2 #TODO this try/except block is mainly to avoid Excel from being # a defunct try: for row in range(irow, 65536): load_id = ex.get_cell(1, row, col) if load_id.__class__.__name__ == 'NoneType': break # load_id = int(load_id) node_id = ex.get_cell(1, row, col + 1) node_id = int(node_id) output = ex.get_cell(1, row, col + 2) output = str(output) minvalue = ex.get_cell(1, row, col + 3) minvalue = float(minvalue) maxvalue = ex.get_cell(1, row, col + 4) maxvalue = float(maxvalue) if not load_id in nd.keys(): nd[load_id] = {} if not node_id in nd[load_id].keys(): nd[load_id][node_id] = {} nd[load_id][node_id][output] = [minvalue, maxvalue] ex.close() self.nodal_displ = nd except: ex.close() print('nodal_displ_from_excel() failed!') return None for load_id in nd.keys(): for node_id in nd[load_id].keys(): for con_name in nd[load_id][node_id].keys(): con_minvalue = nd[load_id][node_id][con_name][0] con_maxvalue = nd[load_id][node_id][con_name][1] for k, v in OUTC['DISP'].iteritems(): if v.find(con_name) > -1: code = k break if code == 7: #DRESP1 x labelx = 'x' + str(node_id) dresp1 = DRESP1(labelx, 'DISP', '', '', 1, [node_id]) self.dresps[dresp1.id] = dresp1 dresp1xid = dresp1.id #DRESP1 y labely = 'y' + str(node_id) dresp1 = DRESP1(labely, 'DISP', '', '', 2, [node_id]) self.dresps[dresp1.id] = dresp1 dresp1yid = dresp1.id #DRESP1 z labelz = 'z' + str(node_id) dresp1 = DRESP1(labelz, 'DISP', '', '', 3, [node_id]) self.dresps[dresp1.id] = dresp1 dresp1zid = dresp1.id #DEQATN eq = ('T(%s,%s,%s)=SQRT(%s**2+%s**2+%s**2)' % (labelx, labely, labelz, labelx, labely, labelz)) deqatn = DEQATN(eq) self.deqatns[deqatn.id] = deqatn #DRESP23 label = 'r' + str(node_id) dresp23 = DRESP23(label, deqatn.id) dresp23.add_dresp1(dresp1xid) dresp23.add_dresp1(dresp1yid) dresp23.add_dresp1(dresp1zid) self.dresps[dresp23.id] = dresp23 #DCONS stress_type = 'positive' lid_lb_ub = [str(load_id), con_minvalue, con_maxvalue] self.dcons[dresp23.id] = DCONS(dresp23.id, lid_lb_ub, stress_type) elif (code == 8 or code == 9 or code == 10): #DRESP1 label = 'a' + str(node_id) dresp1 = DRESP1(label, 'DISP', '', '', code, [node_id]) self.dresps[dresp1.id] = dresp1 #DEQATN eq = 'D(%s)=ABS(%s)' % (label, label) deqatn = DEQATN(eq) self.deqatns[deqatn.id] = deqatn #DRESP23 label = 'r' + str(node_id) dresp23 = DRESP23(label, deqatn.id) dresp23.add_dresp1(dresp23.id) self.dresps[dresp23.id] = dresp23 #DCONS stress_type = 'positive' lid_lb_ub = [str(load_id), con_minvalue, con_maxvalue] self.dcons[dresp23.id] = DCONS(dresp23.id, lid_lb_ub, stress_type) else: #DRESP1 label = 'r' + str(node_id) dresp1 = DRESP1(label, 'DISP', '', '', code, [node_id]) self.dresps[dresp1.id] = dresp1 #DCONS stress_type = 'both' lid_lb_ub = [str(load_id), con_minvalue, con_maxvalue] self.dcons[dresp1.id] = DCONS(dresp1.id, lid_lb_ub, stress_type)
def dlinks_from_excel(self, xls_file): """Read links between variables from an Excel file. The Excel file should have the following format: ======= ====== ======= ======= ======= ======= ======= ======= ======= . col j col j+1 col j+2 col j+3 col j+4 col j+5 col j+6 col j+7 ======= ====== ======= ======= ======= ======= ======= ======= ======= row i DLINK row i+1 row i+2 dvar c0 c ivar1 c1 ivar2 c2 ... row i+3 LP.1.1 0. 1. LS.1.1 0.4 LS.1.2 0.6 ... ======= ====== ======= ======= ======= ======= ======= ======= ======= where the relative position between the cell with `DLINK` and the others must be held. Parameters ---------- xls_file : str The full path of the Excel file. """ from excel import Excel ex = Excel(xls_file) found = False print('Reading Excel File %s...' % xls_file) for row in range(1, 101): for col in range(1, 256): rvalue = ex.get_cell(1, row, col) if 'DLINK' == rvalue: found = True break if found: break dlinks = {} count = -1 irow = row + 3 for row in range(irow, 65536): dvar_code = ex.get_cell(1, row, col) if dvar_code is None: break print(' creating DLINK for variable: %s' % dvar_code) c0 = ex.get_cell(1, row, col + 1) c = ex.get_cell(1, row, col + 2) ivar_code_1 = ex.get_cell(1, row, col + 3) c_1 = ex.get_cell(1, row, col + 4) ivar_code_2 = ex.get_cell(1, row, col + 5) c_2 = ex.get_cell(1, row, col + 6) ivar_code_3 = ex.get_cell(1, row, col + 7) c_3 = ex.get_cell(1, row, col + 8) dvar = self.dvar_codes[dvar_code] ivar_1 = self.dvar_codes[ivar_code_1] try: dvi_ci = [ivar_1.id, float(c_1)] except: dvi_ci = [ivar_1.id, str(c_1)] if ivar_code_2.__class__.__name__ <> 'NoneType': ivar_2 = self.dvar_codes[ivar_code_2] dvi_ci = dvi_ci + [ivar_2.id, float(c_2)] if ivar_code_3.__class__.__name__ <> 'NoneType': ivar_3 = self.dvar_codes[ivar_code_3] dvi_ci = dvi_ci + [ivar_3.id, float(c_3)] count += 1 self.dlinks[count] = DLINK(dvar.id, dvi_ci, c0=c0, cmult=c) ex.close()