def DFtoExcel(df, FolderName, FileName): write_df = df.loc[:, ["FileName", "hyperlink", "Sheet Name"]] # Path Cell_Search_By_Key MainFolder = "C:\\Cell_Search_By_Key" FolderPath = os.path.join(MainFolder, FolderName) if not os.path.exists(FolderPath): os.makedirs(FolderPath) os.chdir(FolderPath) ExcelName = "%s.xlsx" % FileName writer = ExcelWriter(ExcelName) write_df.to_excel(writer, "Result", index=False) writer.save() # turn path into hyperlink Excel_Path = os.path.join(FolderPath, ExcelName) wb = Workbook(Excel_Path) # wb = Workbook.caller() checkArr = Range("B2").vertical.value i = 2 for check in checkArr: RangeName = "B%d" % (i) displayRange = "A%d" % (i) address = Range(RangeName).value display_name = Range(displayRange).value i += 1 try: Range(RangeName).add_hyperlink(address, text_to_display=address) except: pass wb.save() wb.close() return "FINISH"
def tableau_data(): Workbook.caller() save_path = str(dr_pivot_path()) save_path = save_path[:save_path.rindex('\\')] ddr_data = dr.raw_pivot() d = dr.tableau_campaign_data(ddr_data) s = search.merge_data() tableau = d.append(s) tableau['Quarter'] = qquarter() if Range('merged', 'A1').value is None: chunk_df(tableau, 'merged', 'A1') # If data is already present in the tab, the two data sets are merged together and then copied into the data tab. else: past_data = pd.read_excel(dr_pacing_path(), 'merged', index_col=None) past_data = past_data[past_data['Campaign'] != 'Search'] appended_data = past_data.append(tableau) Sheet('merged').clear() chunk_df(appended_data, 'merged', 'A1') #Range('Sheet3', 'AT1').value = pd.to_datetime(ddr_data['Date'].max()) + datetime.timedelta(days= 1) wb = Workbook() Sheet('Sheet1').name = 'DDR Data' chunk_df(ddr_data, 'DDR Data', 'A1') wb.save(save_path + '\\' + 'DR_Raw_Data.xlsx') wb.close()
def write_array_to_xl_using_xlwings(ar, file, sheet): # Note: if file is opened In Excel, it must be first saved before writing # new output to it, but it may be left open in Excel application. wb = Workbook(file) Sheet(sheet).activate() Range(sheet, 'A1').value = ar.astype(str) wb.save()
def months_stat(self, q_months, year): q_df = self.period_calc(q_months, year) sum_q = self.period_stat(q_months, year) wb = Workbook() sh = Sheet.add("Summary", wkb = wb) row_flag = write_to_excel(q_df, sh = sh) row_flag = write_to_excel(sum_q, sh = sh, row_flag = row_flag) sh = Sheet.add("Master", wkb = wb) row_flag = write_to_excel(self.active_on_the_day(t_month_end(q_months[-1], year)) .data.pipe(ready_excel), sh = sh) sh1 = Sheet.add("Aggregate", wkb = wb) row_flag = write_to_excel('New Leases During the Period', sh = sh1) new_leases_list = self.new_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel) row_flag = write_to_excel(new_leases_list, sh = sh1, row_flag = row_flag) row_flag = write_to_excel('Expired During the Period', sh = sh1, row_flag = row_flag) expired_leases_list = self.old_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel) row_flag = write_to_excel(expired_leases_list, sh = sh1, row_flag = row_flag) r_expired_leases_list, r_new_leases_list, period_rate = self.renewal_a(q_months, year) sh1 = Sheet.add("Renewal", wkb = wb) row_flag = write_to_excel('Renewed Leases During the Period', sh = sh1) row_flag = write_to_excel('Original Leases', sh = sh1, row_flag = row_flag) row_flag = write_to_excel(r_expired_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag) row_flag = write_to_excel('Renewed Leases', sh = sh1, row_flag = row_flag) row_flag = write_to_excel(r_new_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag) row_flag = write_to_excel('Weighted Average Reversion Rate', sh = sh1, row_flag = row_flag) row_flag = write_to_excel(period_rate, sh = sh1, row_flag = row_flag) quarter = q_months[-1]//3 for tower in range(1,3): sh_new = Sheet.add("Tower {tower} {year} Q{quarter}".format(tower = tower, year = year, quarter = quarter), wkb = wb) row_flag = write_to_excel('Tower {tower} New Leases During the Period'.format(tower = tower), sh = sh_new) new_leases_list_T = new_leases_list.loc[new_leases_list['BLDG'] == tower].copy() row_flag = write_to_excel(new_leases_list_T, sh = sh_new, row_flag = row_flag) row_flag = write_to_excel('Tower {tower} Expired Leases During the Period'.format(tower = tower), sh = sh_new, row_flag = row_flag) expired_leases_list_T = expired_leases_list.loc[expired_leases_list['BLDG'] == tower].copy() row_flag = write_to_excel(expired_leases_list_T, sh = sh_new, row_flag = row_flag) Sheet('Sheet1').delete() wb.save("Operating Statistics Q{quarter} {year}".format(quarter = quarter, year = year)) #wb.close() return "OK"
def write_array_to_sheet(filepath, sheet, arr): path = _fullpath(filepath) # Workbook(path) seems to fail unless full path is provided if os.path.exists(path): wb = Workbook(path) Sheet(sheet).activate() Range("A1").value = arr wb.save() else: raise FileNotFound(path)
def savexlsMethod(self): print('Saving excel File') self.saveNameExcel = os.path.splitext(str(self.filepath).split("/")[-1])[0] wbOut = Workbook() Range('A1').value = ['Tempature [C]','Relative Humidity [%]','Dew Point [C]'] Range('A2').value = self.data wbOut.save()
def write_array_to_xl_using_xlwings(ar, file, sheet): # Note: if file is opened In Excel, it must be first saved before writing # new output to it, but it may be left open in Excel application. wb = Workbook(file) Sheet(sheet).activate() def nan_to_empty_str(x): return '' if type(x) == float and np.isnan(x) else x Range(sheet, 'A1').value = [[nan_to_empty_str(x) for x in row] for row in ar] wb.save()
def saveFile(self, accountManageFileName): """ Save the destination file. """ from xlwings import Workbook, Range wb = Workbook(accountManageFileName) for placeToWrite in self.infoToSave: sheet = placeToWrite[0] cell = placeToWrite[1] data = self.infoToSave[placeToWrite] Range(sheet, cell).value = data wb.save(accountManageFileName)
def output_forecasts(pacing_data): pacing_data['Week'] = pacing_data['Date'].apply(lambda x: main.monday_week_start(x)) pacing_data = pd.pivot_table(pacing_data, index= ['Site', 'Tactic', 'Metric'], columns= ['Week'], values= 'value', aggfunc= np.sum).reset_index() wb = Workbook(main.dr_pacing_path()) Sheet('forecast_data').clear_contents() Range('forecast_data', 'A1', index= False).value = pacing_data wb.save() wb.close()
def xlo(df, filename=None): """ show pandas dataframe or series in excel sheet uses xlwings which allows writing to open file """ if not filename: filename = "_temp.xlsx" if not os.path.isfile(filename): wb = Workbook() Sheet("Sheet2").delete() Sheet("Sheet3").delete() else: wb = Workbook(filename) Sheet.add() Range("A1").value = df wb.save(filename)
def test_save_path(self): cwd = os.getcwd() wb1 = Workbook(app_visible=False, app_target=APP_TARGET) target_file_path = os.path.join(cwd, 'TestFile.xlsx') if os.path.isfile(target_file_path): os.remove(target_file_path) wb1.save(target_file_path) assert_equal(os.path.isfile(target_file_path), True) wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET) wb2.close() if os.path.isfile(target_file_path): os.remove(target_file_path)
def test_save_path(self): cwd = os.getcwd() wb1 = Workbook() target_file_path = os.path.join(cwd, 'TestFile.xlsx') if os.path.isfile(target_file_path): os.remove(target_file_path) wb1.save(target_file_path) assert_equal(os.path.isfile(target_file_path), True) wb2 = Workbook(target_file_path) wb2.close() if os.path.isfile(target_file_path): os.remove(target_file_path)
def test_save_naked(self): cwd = os.getcwd() wb1 = Workbook(app_visible=False) target_file_path = os.path.join(cwd, wb1.name + '.xlsx') if os.path.isfile(target_file_path): os.remove(target_file_path) wb1.save() assert_equal(os.path.isfile(target_file_path), True) wb2 = Workbook(target_file_path, app_visible=False) wb2.close() if os.path.isfile(target_file_path): os.remove(target_file_path)
def test_save_naked(self): if sys.platform.startswith('darwin'): os.chdir(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/') cwd = os.getcwd() wb1 = Workbook(app_visible=False, app_target=APP_TARGET) target_file_path = os.path.join(cwd, wb1.name + '.xlsx') if os.path.isfile(target_file_path): os.remove(target_file_path) wb1.save() assert_equal(os.path.isfile(target_file_path), True) wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET) wb2.close() if os.path.isfile(target_file_path): os.remove(target_file_path)
def test_save_path(self): if sys.platform.startswith('darwin'): folder = os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/' if os.path.isdir(folder): os.chdir(folder) cwd = os.getcwd() wb1 = Workbook(app_visible=False, app_target=APP_TARGET) target_file_path = os.path.join(cwd, 'TestFile.xlsx') if os.path.isfile(target_file_path): os.remove(target_file_path) wb1.save(target_file_path) assert_equal(os.path.isfile(target_file_path), True) wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET) wb2.close() if os.path.isfile(target_file_path): os.remove(target_file_path)
def run_concatenation(directory=None): import re, os from xlwings import Workbook, Range, Application wb = Workbook(app_visible=False) lastRow = 3 newlastRow = lastRow lastColumn = 6 if directory is None: directory = os.getcwd() for file in os.listdir(directory): findling = re.search(r'.*Data.*[0-9]{2,3}.*.xls$', file) if findling: wb2 = Workbook(os.path.abspath(findling.group()), \ app_visible=False) addedRows = Range((4, 1), wkb = wb2).table.last_cell.row - 3 newlastRow = lastRow + addedRows Range((lastRow+1, lastColumn+1), wkb = wb).value = \ findling.group() Range((lastRow+1, 1), wkb = wb).table.value = \ Range((4, 1), (newlastRow, lastColumn), wkb = wb2).value lastRow = newlastRow wb2.close() wb.save(os.path.join(os.getcwd(), 'Output.xlsx')) wb.close()
def publishers(dr): cd, t2t, fbx, search, pros, aal = data_transform.dr_placement_types() week = week_of(dr) # Publisher Performance pub_dr = dr[(dr['Campaign'] == 'DR') & (dr['Date'] >= main.quarter_start())] pub_dr = pub_dr.groupby(['Site', 'Placement Messaging Type', 'Week', 'Date']) pub_dr = pd.DataFrame(pub_dr.sum()).reset_index() #pub_dr = pub_dr[(pub_dr['NTC Media Cost'] != 0)] pub_dr['Tactic'] = np.where((pub_dr['Placement Messaging Type'].str.contains(cd) == True) & (pub_dr['Placement Messaging Type'].str.contains(fbx) != True), 'C/D Remessaging', np.where(pub_dr['Placement Messaging Type'].str.contains(fbx) == True, 'FBX Remessaging', pub_dr['Placement Messaging Type'])) # Quarter q_dr = pub_dr[pub_dr['Date'] >= main.quarter_start()] q_dr = q_dr.groupby(['Site', 'Tactic']) q_dr = pd.DataFrame(q_dr.sum()).reset_index() q_dr = goals(q_dr) # Last Week last_week = pub_dr[pub_dr['Week'] == pub_dr['Week'].max()] last_week = last_week.groupby(['Site', 'Tactic']) last_week = pd.DataFrame(last_week.sum()).reset_index() last_week.rename(columns={'NET Media Cost': week}, inplace= True) # Publishers Overall sites = q_dr.groupby('Site') sites = pd.DataFrame(sites.sum()).reset_index() sites['CPGA'] = sites['NET Media Cost'] / sites['Total GAs'] # Brand Remessaging br = dr[dr['Campaign'] == 'Brand Remessaging'] br_quarter = br[br['Date'] >= main.quarter_start()] br_lw = br[br['Week'] == br['Week'].max()] br_quarter = br_quarter.groupby('Site') br_quarter = pd.DataFrame(br_quarter.sum().reset_index()) br_quarter['Traffic Yield'] = br_quarter['Total Traffic Actions'].astype(float) / \ br_quarter['Impressions'].astype(float) br_lw = br_lw.groupby('Site') br_lw = pd.DataFrame(br_lw.sum().reset_index()) pacing_wb = Workbook(main.dr_pacing_path()) pacing_wb.set_current() Range('Publisher Performance', 'A13', index=False, header=False).value = q_dr[ ['Site', 'Tactic', 'Total GAs']] Range('Publisher Performance', 'E13', index= False, header= False).value = q_dr['NET Media Cost'] Range('Publisher Performance', 'D13', index= False, header= False).value = last_week[week] Range('Publisher Performance', 'D12').value = week Range('Publisher Performance', 'A32', index= False, header= False).value = sites[['Site', 'Total GAs', 'CPGA']] Range('Publisher Performance', 'C7').value = pub_dr['Week'].max().strftime('%m/%d/%Y') Range('Publisher Performance', 'B45', index= False, header= False).value = \ br_quarter[['Traffic Yield', 'Impressions', 'Total Traffic Actions']] Range('Publisher Performance', 'G45', index= False, header= False).value = br_quarter['NET Media Cost'] Range('Publisher Performance', 'F45', index= False, header= False).value = br_lw['NET Media Cost'] Range('Publisher Performance', 'F44').value = week pacing_wb.save() pacing_wb.close()
Sheet.add('abc',after=lastSheetVal) Sheet('abc').name=NewSheetName #Paste Data Range(RangeLimit).value=TempData #hypelink for hyperRow in xrange(2,AllRRow): RangeName=('B%d' % (hyperRow)) address=Range(RangeName).value try: Range(RangeName).add_hyperlink(address, text_to_display=address) except: pass wbTarget.save() wb.close() wbTarget.close() except: #No Main Excel Exist, Create New One Excel_Path = os.path.join(CWPath, 'Result-Output.xlsx') wb = Workbook(Excel_Path) #rename worksheet Sheet('Result',wkb=wb).name=NewSheetName wb.save(TargetPath) wb.close() pass
import re import time import shutil import codecs import subprocess import pexpect import sys from xlwings import Workbook, Sheet, Range, Chart # from PDFWriter import PDFWriter graded_folder_path = "/Users/evermal/Downloads/COMP248-Q-2" student_id_regex = '.*([0-9]{8}).*' # Create a connection with a new workbook. template_workbook = Workbook('/Users/evermal/Documents/COMP248-Assigment2/examples/COMP248_Q_F2015_GradeSheet for A2.xlsx') template_workbook.save('/Users/evermal/Documents/COMP248-Assigment2/examples/newCOMP248_Q_F2015_GradeSheet for A2.xlsx') template_workbook.close() # open nem workbook to work with professors_workbook = Workbook('/Users/evermal/Documents/COMP248-Assigment2/examples/newCOMP248_Q_F2015_GradeSheet for A2.xlsx') # go through each line of the table for x in xrange(7,101): # set the profesors spreedsheet as current workbook professors_workbook.set_current() student_id_cell = str(Range('B'+str(x)).value) if student_id_cell is not None: student_id_matcher = re.match(student_id_regex, student_id_cell) if student_id_matcher is not None: student_id = student_id_matcher.group(1) print student_id
# create feedback workbook student_id_matcher = re.match(student_id_regex, absolute_file_name) student_id = student_id_matcher.group(1) template_workbook = Workbook(template_feedback_workbook_path) Range('B4').value = student_id Range('B15').value = personalized_message Range('B16').value = height_validation + width_validation Range('B17').value = drawing_of_the_house_roof Range('B18').value = drawing_of_the_house_body Range('B19').value = repetition_process Range('B20').value = extra_point Range('D15').value = personalized_message_comment Range('D16').value = height_and_width_comment Range('D17').value = roof_comment Range('D18').value = body_comment Range('D19').value = comment_5 Range('D20').value = extra_point_comment student_feedback_workbook_path = root+'/'+student_id+'.xlsx' template_workbook.save(student_feedback_workbook_path) student_workbook = Workbook(student_feedback_workbook_path) template_workbook.close() subprocess.call(["subl", absolute_file_name]) success = raw_input('success?') if success == 'y': student_workbook.close()
class ExcelTemplateRenderer(object): """ A class to render an Excel template using data stored in a database and given a specification file. Parameters ---------- db_engine : object the :class:`sqlalchemy.engine.Engine` object used to connect and query the database. template_name : str path to the `xlsx` template file. spec_filename : str path to the specification file (yaml format). output_dirname : str path to the directory where to store the generated excel file(s) (i.e., the rendered template(s)). jinja_env : object or None allow to provide a :class:`jinja2.Environment` object, helpful when using custom filters in the specification file (optional). Notes ----- The specification file (yaml format) consists of a list of render blocks. TODO: documentation on the format. """ def __init__(self, db_engine, template_name, spec_filename, output_dirname, jinja_env=None): self.db_engine = db_engine self.template_name = template_name self.spec_filename = spec_filename self.output_dirname = os.path.abspath(output_dirname) if jinja_env is None: self.jinja_env = jinja2.Environment() else: self.jinja_env = jinja_env with open(self.spec_filename, 'r', encoding='utf-8') as f: self.render_blocks = yaml.load(f) os.makedirs(self.output_dirname, exist_ok=True) logger.info("output directory is %s", self.output_dirname) def open_template_as_current_wkb(self): self.wkb = Workbook( os.path.abspath(self.template_name), app_visible=False ) self.wkb.set_current() def save_current_wkb(self, filename): filepath = os.path.join(self.output_dirname, filename) self.wkb.save(filepath) logger.info("created %s", filepath) def save_current_wkb_as_pdf(self, filename, worksheet_name): filepath = os.path.join(self.output_dirname, filename) try: ws = Sheet(worksheet_name, wkb=self.wkb) ws.xl_sheet.ExportAsFixedFormat(0, filepath) logger.info("created %s", filepath) except Exception as e: logger.error("failed to export pdf") logger.error("detailled error: %s - %s", e.__class__.__name__, str(e)) def close_current_wkb(self): self.wkb.close() def insert_one_series(self, series, cell_specification): """ Populate the current workbook given a single :class=:`pandas.Series` object. """ if not len(series): return # contiguous cells #TODO: (use vertical and horizontal properties of xlwings) # non-contiguous user-defined cells for cs in cell_specification.get('cells', []): ws = cs.get('worksheet') or Sheet.active(self.wkb).name content = self.jinja_env.from_string(cs['content']).render(**series) logger.debug("insert content '%s' at cell '%s' in sheet '%s'", content, cs['cell'], ws) Range(ws, cs['cell']).value = content def insert_one_dataframe(self, df, cell_specification): """ Populate the current workbook given a single :class=:`pandas.DataFrame` object. """ if not len(df): return index = cell_specification.get('index', False) header = cell_specification.get('header', False) top_left_cell = cell_specification.get('top_left_cell', 'A0') logger.debug("insert %d by %d rows/cols dataframe " "at cell '%s' in sheet '%s'", len(df), len(df.columns), str(top_left_cell), Sheet.active(self.wkb).name) Range(top_left_cell, index=index, header=header).value = df def apply_render_block(self, render_block, query_context=None, **kwargs): """ Apply a single render block in the specification file. - `query_context` (mappable or None) is a context used when rendering the database query with jinja2 (optional). - **kwargs is used to overwrite any key/value pair in the render block. """ # override render_block key/val with kwargs render_block.update(kwargs) logger.info("processing render block '%s'", render_block.get('name', '<unamed>')) # query the DB into a pandas DataFrame if query_context is None: query_context = dict() query_template = self.jinja_env.from_string( render_block['query'].strip() ) query = query_template.render(**query_context) logger.debug("rendered query: \n'''\n%s\n'''", query) df = pd.read_sql(query, self.db_engine) logger.debug("query returned %d record(s)", len(df)) # TODO: calculate extra columns and add it to the DataFrame # activate worksheet if provided ws_name = render_block['cell_specification'].get('worksheet') or None if ws_name is not None: ws2reactivate_name = Sheet.active(self.wkb).name Sheet(ws_name, wkb=self.wkb).activate() # apply the render_block, apply recusively included blocks, # and save the rendered workbook(s) if needed apply_by_row = render_block.get('apply_by_row', False) save_as = render_block.get('save_as', None) if apply_by_row and save_as is not None: logger.info("%d file(s) to generate", len(df)) if apply_by_row: for row, pseries in df.iterrows(): self.insert_one_series( pseries, render_block['cell_specification'] ) for item in render_block.get('include', []): if isinstance(item, dict): block_name = item.pop('render_block') override_vars = item else: block_name = item override_vars = {} block = [b for b in self.render_blocks if b['name'] == block_name][0] self.apply_render_block(block, query_context=pseries, **override_vars) if save_as is not None: tpl = save_as['filename'] filename = self.jinja_env.from_string(tpl).render(**pseries) self.save_current_wkb(filename) # save to pdf if save_as.get('export_pdf', False): filename_pdf = os.path.splitext(filename)[0] + '.pdf' if ws_name is None: logger.error( "(export to pdf) no worksheet specified" ) else: self.save_current_wkb_as_pdf(filename_pdf, ws_name) # re-open the template, re-activate the worksheet self.close_current_wkb() self.open_template_as_current_wkb() if ws_name is not None: Sheet(ws_name, wkb=self.wkb).activate() else: self.insert_one_dataframe( df, render_block['cell_specification'] ) # TODO: include and save_as in this case # re-activate former worksheet if needed if ws_name is not None: Sheet(ws2reactivate_name, wkb=self.wkb).activate() def render(self): """Main render method.""" self.open_template_as_current_wkb() save_render_blocks = [block for block in self.render_blocks if 'save_as' in block.keys()] for block in save_render_blocks: self.apply_render_block(block) self.wkb.close()
def write_xlsx(self, xlsx_filename, is_mag_phase=False, delete_objects=True): """ Writes an OP2 file based on the data we have stored in the object :param xlsx_filename: the name of the XLSX file to write :param is_mag_phase: should complex data be written using Magnitude/Phase instead of Real/Imaginary (default=False; Real/Imag) Real objects don't use this parameter. :param delete_objects: should objects be deleted after they're written to reduce memory (default=True) """ from xlwings import Workbook, Sheet #, Range, Chart from pywintypes import com_error print('writing %s' % xlsx_filename) if isinstance(xlsx_filename, str): workbook = Workbook() # Creates a connection with a new workbook try: workbook.save(xlsx_filename) except com_error: raise RuntimeError('Close %s' % xlsx_filename) else: raise NotImplementedError(type(xlsx_filename)) # assert isinstance(xlsx_filename, file), 'type(xlsx_filename)= %s' % xlsx_filename # op2 = xlsx_filename # xlsx_filename = op2.name # print('xlsx_filename =', xlsx_filename) #op2_ascii.write('writing [3, 7, 0] header\n') #if markers == [3,]: # PARAM, POST, -1 #self.read_markers([3]) #data = self.read_block() #self.read_markers([7]) #data = self.read_block() #data = self._read_record() #self.read_markers([-1, 0]) #elif markers == [2,]: # PARAM, POST, -2 isheet = 1 if 0: #_write_markers(op2, op2_ascii, [3, 0, 7]) #tape_code = b'NASTRAN FORT TAPE ID CODE - ' Sheet(isheet).name = sheet_name sheet = Sheet(isheet) sheet['A1'].value = 'NASTRAN FORT TAPE ID CODE' sheet['B1'].value = tape_code nastran_version = b'NX8.5 ' if self.is_nx else b'XXXXXXXX' sheet['A2'].value = 'nastran_version' sheet['B2'].value = nastran_version isheet = +1 if self.grid_point_weight.reference_point is not None and 0: if has_attr(result, 'write_xlsx'): self.grid_point_weight.write_xlsx(workbook, page_stamp, self.page_num) else: print("*op2 - grid_point_weight not written") #is_mag_phase = False # eigenvalues are written first for ikey, result in sorted(iteritems(self.eigenvalues)): # header #print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase)) if has_attr(result, 'write_xlsx'): result.write_xlsx(xlsx) if delete_objects: del result else: print("*xlsx - %s not written" % result.__class__.__name__) # asdf # then eigenvectors # has a special header for isubcase, result in sorted(iteritems(self.eigenvectors)): (subtitle, label) = self.iSubcaseNameMap[isubcase] if hasattr(result, 'write_xlsx'): print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase)) result.write_xlsx(workbook, is_mag_phase=is_mag_phase) if delete_objects: del result else: print("*xlsx - %s not written" % result.__class__.__name__) # asdf # finally, we writte all the other tables # nastran puts the tables in order of the Case Control deck, # but we're lazy so we just hardcode the order #if markers == [3,]: # PARAM, POST, -2 #self.read_markers([3]) #data = self.read_block() #self.read_markers([7]) #data = self.read_block() #data = self._read_record() oef = [ # OEF - forces # alphabetical order... self.cbar_force, # beam #self.cbeam_forces, #self.cbar100_forces, #self.cbend_forces, self.cbeam_force, self.cbush_force, self.celas1_force, self.celas2_force, self.celas3_force, self.celas4_force, self.cdamp1_force, self.cdamp2_force, self.cdamp3_force, self.cdamp4_force, #self.plateForces, # centroidal elements #self.plateForces2, # bilinear elements self.conrod_force, self.cquad4_force, self.cquad8_force, self.crod_force, self.cshear_force, self.ctria3_force, self.ctria6_force, self.ctube_force, # other #self.cgap_force, #self.solidPressureForces, ] oes1x1 = [ # cbars/cbeams self.cbar_stress, self.cbeam_stress, # bush self.cbush_stress, self.cbush1d_stress_strain, # rods #self.nonlinearRodStress, self.celas1_stress, self.celas2_stress, self.celas3_stress, self.celas4_stress, self.chexa_stress, self.conrod_stress, self.cpenta_stress, self.cquad4_stress, self.cquad8_stress, self.cquadr_stress, self.crod_stress, self.cshear_stress, self.ctetra_stress, self.ctria3_stress, self.ctria6_stress, self.ctriar_stress, self.ctube_stress, ] oes1c = [ self.cquad4_composite_stress, self.cquad8_composite_stress, self.cquadr_composite_stress, self.ctria3_composite_stress, self.ctria6_composite_stress, self.ctriar_composite_stress, #self.nonlinearPlateStress, self.ctriax_stress, #self.hyperelasticPlateStrain, ] #stress = oes1x1 + oes1c strain = [ # bars/beams self.cbar_strain, self.cbeam_strain, # springs, self.celas1_strain, self.celas2_strain, self.celas3_strain, self.celas4_strain, # plates self.ctria3_strain, self.cquad4_strain, self.cshear_strain, self.cquad4_composite_strain, self.cquad8_composite_strain, self.cquadr_composite_strain, self.ctria3_composite_strain, self.ctria6_composite_strain, self.ctriar_composite_strain, #self.nonlinearPlateStrain, #self.ctriax_strain, self.hyperelasticPlateStress, # solids self.ctetra_strain, self.cpenta_strain, self.chexa_strain, # rods #self.nonlinearRodStrain, # non-vectorized self.chexa_strain, self.conrod_strain, self.cpenta_strain, self.cquad4_strain, self.cquad8_strain, self.cquadr_strain, self.crod_strain, self.cshear_strain, self.ctetra_strain, self.ctria3_strain, self.ctria6_strain, self.ctriar_strain, self.ctube_strain, # bush self.cbush_strain, ] oug = [ self.accelerations, self.displacements, self.displacementsPSD, self.displacementsATO, self.displacementsRMS, #self.scaled_displacements, # ??? self.temperatures, self.velocities, self.eigenvectors, ] oqg_mpc = [ self.mpc_forces, ] oqg_spc = [ self.spc_forces, ] ogs = [ self.grid_point_stresses, self.grid_point_volume_stresses, ] ogp = [ self.grid_point_forces, ] other = [ #self.forceVectors, #self.loadVectors, self.thermal_load_vectors, ] isubcases = sorted(self.iSubcaseNameMap.keys()) #title = self.title res_categories = [('OUG', oug), ('OQG_SPC', oqg_spc), ('OQG_MPC', oqg_mpc), ('OEF', oef), ('OES1X', oes1x1), ('OES1C', oes1c), ('OSTR', strain), ('OGS', ogs), ('OGP', ogp), ('other', other)] res_outs = {} # TODO: add a summary sheet # TODO: this may need to be reworked such that all of subcase 1 #is printed before subcase 2 for res_category_name, res_category in res_categories: #print("res_category_name = %s" % res_category_name) for res_type in res_category: res_keys = isubcases for res_key in res_keys: isubcase = res_key if isubcase in res_type: #(subtitle, label) = self.iSubcaseNameMap[isubcase] result = res_type[isubcase] element_name = '' if hasattr(result, 'element_name'): element_name = ' - ' + result.element_name if hasattr(result, '_write_xlsx'): class_name = result.__class__.__name__ sheet_name = '%s_%s' % (class_name, isubcase) assert len(sheet_name) < 31, sheet_name Sheet(isheet).name = sheet_name sheet = Sheet(isheet) print(' %s - isubcase=%i%s' % (result.__class__.__name__, isubcase, element_name)) result._write_xlsx(sheet, is_mag_phase=False) isheet += 1 else: print(" *xlsx - %s not written" % result.__class__.__name__) #footer = [4, 0, 4] workbook.save()
def update_excel_dashboard(project): print('Started function.') ### filekey requires at least first two characters filename file_key = return_file_key(project.name) dash_filename = return_filename(file_key, dash_list) ### dispatch project class object, importing data into Project Class if dash_filename != None: filename = LOCAL_DASH_PATH + dash_filename save_filename = LOCAL_DASH_PATH + file_key + '-dashboard-' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx' else: filename = LOCAL_DASH_PATH + PP_DASH_TEMPLATE save_filename = LOCAL_DASH_PATH + file_key + '-dashboard-' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx' wb = Workbook(filename) excel = wb.xl_app excel.visible = True pending_tasks = Sheet('pending_tasks') project_plan = Sheet('project_plan') sheet_dict = {'project_plan': (project_plan, project.Task_list), 'pending_tasks': (pending_tasks, get_pending_tasks(project))} cat_task_dict = get_cat_task_dict(project) for sheet_name in list(sheet_dict.keys())[:2]: row =2 next_row = Range(sheet_dict[sheet_name][0].name, (row,1), (row,11)) clear_range = Range(sheet_dict[sheet_name][0].name, (2,1), (500,11)) clear_range.value = '' for item in sheet_dict[sheet_name][1]: task = item.task task_fields = [task.WBS, item.action, project.get_phase(item), task.Name, task.Start, task.Finish, task.Deadline, task.PercentComplete, \ item.status(), item.status_override, item.recovery_plan] next_row.value = task_fields row = row +1 next_row = Range(sheet_dict[sheet_name][0].name, (row,1), (row,9)) print('Updated project plan and pending tasks.') ### milestone dashboard row = 5 next_row = Range('milestones', (row,2), (row,4)) for item in get_milestones(project): due_date = "Not assigned" task = item.task if task.Deadline != 'NA': if task.Deadline.date() < task.Finish.date() and task.PercentComplete != 100: due_date = task.Deadline mitigation = 'New target date: ' + task.Finish.strftime("%d-%b-%Y") + '. ' + item.recovery_plan else: due_date = task.Finish mitigation = item.recovery_plan else: due_date = task.Finish mitigation = item.recovery_plan task_fields = [task.Name, due_date, item.status(), mitigation] next_row.value = task_fields row = row +1 next_row = Range('milestones', (row,2), (row,4)) print('Updated milestones.') row = 2 clear_range = Range('CAPA status', (2,1), (500,11)) clear_range.value = '' ### project project_status_row = Range('CAPA status', (row,1), (row,10)) task_fields = get_project_status(project) project_status_row.value = task_fields project_status = Range('milestones', (1,4), (1,4)) project_status.value = task_fields[6] capa_status_row = Range('CAPA status', (row+1,1), (row+1,10)) task_fields = get_capa_status(project) capa_status_row.value = task_fields capa_status = Range('milestones', (2,4), (2,4)) capa_status.value = task_fields[6] row = row + 2 ### interim controls next_row = Range('CAPA status', (row,1), (row,11)) IC_cat_dash = get_cat_dash(project, 'IC') for task_fields in IC_cat_dash: next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) ### CA / PA dashboard for task_fields in get_cat_dash(project, 'CA'): next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) for task_fields in get_cat_dash(project, 'PA'): if task_fields != None: next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) ### remediation for task_fields in get_cat_dash(project, 'RM'): next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) print('Updated CAPA status.') #### implementation row = 2 clear_range = Range('implementation', (2,1), (500,11)) clear_range.value = '' next_row = Range('implementation', (row,1), (row,11)) for procedure in get_implementation(project): next_row.value = procedure row = row +1 next_row = Range('implementation', (row,1), (row,11)) print('Updated implementation.') wb.save(save_filename) #print(save_filename) wb.close()
class ExcelTemplateRenderer(object): """ A class to render an Excel template using data stored in a database and given a specification file. Parameters ---------- db_engine : object the :class:`sqlalchemy.engine.Engine` object used to connect and query the database. template_name : str path to the `xlsx` template file. spec_filename : str path to the specification file (yaml format). output_dirname : str path to the directory where to store the generated excel file(s) (i.e., the rendered template(s)). jinja_env : object or None allow to provide a :class:`jinja2.Environment` object, helpful when using custom filters in the specification file (optional). Notes ----- The specification file (yaml format) consists of a list of render blocks. TODO: documentation on the format. """ def __init__(self, db_engine, template_name, spec_filename, output_dirname, jinja_env=None): self.db_engine = db_engine self.template_name = template_name self.spec_filename = spec_filename self.output_dirname = os.path.abspath(output_dirname) if jinja_env is None: self.jinja_env = jinja2.Environment() else: self.jinja_env = jinja_env with open(self.spec_filename, 'r', encoding='utf-8') as f: self.render_blocks = yaml.load(f) os.makedirs(self.output_dirname, exist_ok=True) logger.info("output directory is %s", self.output_dirname) def open_template_as_current_wkb(self): self.wkb = Workbook(os.path.abspath(self.template_name), app_visible=False) self.wkb.set_current() def save_current_wkb(self, filename): filepath = os.path.join(self.output_dirname, filename) self.wkb.save(filepath) logger.info("created %s", filepath) def save_current_wkb_as_pdf(self, filename, worksheet_name): filepath = os.path.join(self.output_dirname, filename) try: ws = Sheet(worksheet_name, wkb=self.wkb) ws.xl_sheet.ExportAsFixedFormat(0, filepath) logger.info("created %s", filepath) except Exception as e: logger.error("failed to export pdf") logger.error("detailled error: %s - %s", e.__class__.__name__, str(e)) def close_current_wkb(self): self.wkb.close() def insert_one_series(self, series, cell_specification): """ Populate the current workbook given a single :class=:`pandas.Series` object. """ if not len(series): return # contiguous cells #TODO: (use vertical and horizontal properties of xlwings) # non-contiguous user-defined cells for cs in cell_specification.get('cells', []): ws = cs.get('worksheet') or Sheet.active(self.wkb).name content = self.jinja_env.from_string( cs['content']).render(**series) logger.debug("insert content '%s' at cell '%s' in sheet '%s'", content, cs['cell'], ws) Range(ws, cs['cell']).value = content def insert_one_dataframe(self, df, cell_specification): """ Populate the current workbook given a single :class=:`pandas.DataFrame` object. """ if not len(df): return index = cell_specification.get('index', False) header = cell_specification.get('header', False) top_left_cell = cell_specification.get('top_left_cell', 'A0') logger.debug( "insert %d by %d rows/cols dataframe " "at cell '%s' in sheet '%s'", len(df), len(df.columns), str(top_left_cell), Sheet.active(self.wkb).name) Range(top_left_cell, index=index, header=header).value = df def apply_render_block(self, render_block, query_context=None, **kwargs): """ Apply a single render block in the specification file. - `query_context` (mappable or None) is a context used when rendering the database query with jinja2 (optional). - **kwargs is used to overwrite any key/value pair in the render block. """ # override render_block key/val with kwargs render_block.update(kwargs) logger.info("processing render block '%s'", render_block.get('name', '<unamed>')) # query the DB into a pandas DataFrame if query_context is None: query_context = dict() query_template = self.jinja_env.from_string( render_block['query'].strip()) query = query_template.render(**query_context) logger.debug("rendered query: \n'''\n%s\n'''", query) df = pd.read_sql(query, self.db_engine) logger.debug("query returned %d record(s)", len(df)) # TODO: calculate extra columns and add it to the DataFrame # activate worksheet if provided ws_name = render_block['cell_specification'].get('worksheet') or None if ws_name is not None: ws2reactivate_name = Sheet.active(self.wkb).name Sheet(ws_name, wkb=self.wkb).activate() # apply the render_block, apply recusively included blocks, # and save the rendered workbook(s) if needed apply_by_row = render_block.get('apply_by_row', False) save_as = render_block.get('save_as', None) if apply_by_row and save_as is not None: logger.info("%d file(s) to generate", len(df)) if apply_by_row: for row, pseries in df.iterrows(): self.insert_one_series(pseries, render_block['cell_specification']) for item in render_block.get('include', []): if isinstance(item, dict): block_name = item.pop('render_block') override_vars = item else: block_name = item override_vars = {} block = [ b for b in self.render_blocks if b['name'] == block_name ][0] self.apply_render_block(block, query_context=pseries, **override_vars) if save_as is not None: tpl = save_as['filename'] filename = self.jinja_env.from_string(tpl).render( **pseries) self.save_current_wkb(filename) # save to pdf if save_as.get('export_pdf', False): filename_pdf = os.path.splitext(filename)[0] + '.pdf' if ws_name is None: logger.error( "(export to pdf) no worksheet specified") else: self.save_current_wkb_as_pdf(filename_pdf, ws_name) # re-open the template, re-activate the worksheet self.close_current_wkb() self.open_template_as_current_wkb() if ws_name is not None: Sheet(ws_name, wkb=self.wkb).activate() else: self.insert_one_dataframe(df, render_block['cell_specification']) # TODO: include and save_as in this case # re-activate former worksheet if needed if ws_name is not None: Sheet(ws2reactivate_name, wkb=self.wkb).activate() def render(self): """Main render method.""" self.open_template_as_current_wkb() save_render_blocks = [ block for block in self.render_blocks if 'save_as' in block.keys() ] for block in save_render_blocks: self.apply_render_block(block) self.wkb.close()
# Prepare Third Sheet sheet3 = pd.read_csv(file_iv, delim_whitespace=True) Sheet.add('csv_iv',after='csv_ir') Range('csv_iv','A1',index=False).value = sheet3 Range('csv_iv','A:ZZ').autofit('c') # Save WorkSheet xlspath = xls_dir + "reference_view_balance_" + datetime + ".xlsx" wb.save(xlspath) wb.close() # Empty Files open( file_itogo,"w").close() open( file_ir,"w").close() open( file_iv,"w").close() else: print ( "Check File Size, It shouldn't be empty") else: print ("Check All files exists ")
def write_xlsx(self, xlsx_filename, is_mag_phase=False, delete_objects=True): """ Writes an OP2 file based on the data we have stored in the object :param xlsx_filename: the name of the XLSX file to write :param is_mag_phase: should complex data be written using Magnitude/Phase instead of Real/Imaginary (default=False; Real/Imag) Real objects don't use this parameter. :param delete_objects: should objects be deleted after they're written to reduce memory (default=True) """ from xlwings import Workbook, Sheet#, Range, Chart from pywintypes import com_error print('writing %s' % xlsx_filename) if isinstance(xlsx_filename, str): workbook = Workbook() # Creates a connection with a new workbook try: workbook.save(xlsx_filename) except com_error: raise RuntimeError('Close %s' % xlsx_filename) else: raise NotImplementedError(type(xlsx_filename)) # assert isinstance(xlsx_filename, file), 'type(xlsx_filename)= %s' % xlsx_filename # op2 = xlsx_filename # xlsx_filename = op2.name # print('xlsx_filename =', xlsx_filename) #op2_ascii.write('writing [3, 7, 0] header\n') #if markers == [3,]: # PARAM, POST, -1 #self.read_markers([3]) #data = self.read_block() #self.read_markers([7]) #data = self.read_block() #data = self._read_record() #self.read_markers([-1, 0]) #elif markers == [2,]: # PARAM, POST, -2 isheet = 1 if 0: #_write_markers(op2, op2_ascii, [3, 0, 7]) #tape_code = b'NASTRAN FORT TAPE ID CODE - ' Sheet(isheet).name = sheet_name sheet = Sheet(isheet) sheet['A1'].value = 'NASTRAN FORT TAPE ID CODE' sheet['B1'].value = tape_code nastran_version = b'NX8.5 ' if self.is_nx else b'XXXXXXXX' sheet['A2'].value = 'nastran_version' sheet['B2'].value = nastran_version isheet =+ 1 if self.grid_point_weight.reference_point is not None and 0: if has_attr(result, 'write_xlsx'): self.grid_point_weight.write_xlsx(workbook, page_stamp, self.page_num) else: print("*op2 - grid_point_weight not written") #is_mag_phase = False # eigenvalues are written first for ikey, result in sorted(iteritems(self.eigenvalues)): # header #print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase)) if has_attr(result, 'write_xlsx'): result.write_xlsx(xlsx) if delete_objects: del result else: print("*xlsx - %s not written" % result.__class__.__name__) # asdf # then eigenvectors # has a special header for isubcase, result in sorted(iteritems(self.eigenvectors)): (subtitle, label) = self.iSubcaseNameMap[isubcase] if hasattr(result, 'write_xlsx'): print('%-18s SUBCASE=%i' % (result.__class__.__name__, isubcase)) result.write_xlsx(workbook, is_mag_phase=is_mag_phase) if delete_objects: del result else: print("*xlsx - %s not written" % result.__class__.__name__) # asdf # finally, we writte all the other tables # nastran puts the tables in order of the Case Control deck, # but we're lazy so we just hardcode the order #if markers == [3,]: # PARAM, POST, -2 #self.read_markers([3]) #data = self.read_block() #self.read_markers([7]) #data = self.read_block() #data = self._read_record() oef = [ # OEF - forces # alphabetical order... self.cbar_force, # beam #self.cbeam_forces, #self.cbar100_forces, #self.cbend_forces, self.cbeam_force, self.cbush_force, self.celas1_force, self.celas2_force, self.celas3_force, self.celas4_force, self.cdamp1_force, self.cdamp2_force, self.cdamp3_force, self.cdamp4_force, #self.plateForces, # centroidal elements #self.plateForces2, # bilinear elements self.conrod_force, self.cquad4_force, self.cquad8_force, self.crod_force, self.cshear_force, self.ctria3_force, self.ctria6_force, self.ctube_force, # other #self.cgap_force, #self.solidPressureForces, ] oes1x1 = [ # cbars/cbeams self.cbar_stress, self.cbeam_stress, # bush self.cbush_stress, self.cbush1d_stress_strain, # rods #self.nonlinearRodStress, self.celas1_stress, self.celas2_stress, self.celas3_stress, self.celas4_stress, self.chexa_stress, self.conrod_stress, self.cpenta_stress, self.cquad4_stress, self.cquad8_stress, self.cquadr_stress, self.crod_stress, self.cshear_stress, self.ctetra_stress, self.ctria3_stress, self.ctria6_stress, self.ctriar_stress, self.ctube_stress, ] oes1c = [ self.cquad4_composite_stress, self.cquad8_composite_stress, self.cquadr_composite_stress, self.ctria3_composite_stress, self.ctria6_composite_stress, self.ctriar_composite_stress, #self.nonlinearPlateStress, self.ctriax_stress, #self.hyperelasticPlateStrain, ] #stress = oes1x1 + oes1c strain = [ # bars/beams self.cbar_strain, self.cbeam_strain, # springs, self.celas1_strain, self.celas2_strain, self.celas3_strain, self.celas4_strain, # plates self.ctria3_strain, self.cquad4_strain, self.cshear_strain, self.cquad4_composite_strain, self.cquad8_composite_strain, self.cquadr_composite_strain, self.ctria3_composite_strain, self.ctria6_composite_strain, self.ctriar_composite_strain, #self.nonlinearPlateStrain, #self.ctriax_strain, self.hyperelasticPlateStress, # solids self.ctetra_strain, self.cpenta_strain, self.chexa_strain, # rods #self.nonlinearRodStrain, # non-vectorized self.chexa_strain, self.conrod_strain, self.cpenta_strain, self.cquad4_strain, self.cquad8_strain, self.cquadr_strain, self.crod_strain, self.cshear_strain, self.ctetra_strain, self.ctria3_strain, self.ctria6_strain, self.ctriar_strain, self.ctube_strain, # bush self.cbush_strain, ] oug = [ self.accelerations, self.displacements, self.displacementsPSD, self.displacementsATO, self.displacementsRMS, #self.scaled_displacements, # ??? self.temperatures, self.velocities, self.eigenvectors, ] oqg_mpc = [self.mpc_forces,] oqg_spc = [self.spc_forces,] ogs = [self.grid_point_stresses, self.grid_point_volume_stresses, ] ogp = [self.grid_point_forces,] other = [ #self.forceVectors, #self.loadVectors, self.thermal_load_vectors, ] isubcases = sorted(self.iSubcaseNameMap.keys()) #title = self.title res_categories = [ ('OUG', oug), ('OQG_SPC', oqg_spc), ('OQG_MPC', oqg_mpc), ('OEF', oef), ('OES1X', oes1x1), ('OES1C', oes1c), ('OSTR', strain), ('OGS', ogs), ('OGP', ogp), ('other', other) ] res_outs = {} # TODO: add a summary sheet # TODO: this may need to be reworked such that all of subcase 1 #is printed before subcase 2 for res_category_name, res_category in res_categories: #print("res_category_name = %s" % res_category_name) for res_type in res_category: res_keys = isubcases for res_key in res_keys: isubcase = res_key if isubcase in res_type: #(subtitle, label) = self.iSubcaseNameMap[isubcase] result = res_type[isubcase] element_name = '' if hasattr(result, 'element_name'): element_name = ' - ' + result.element_name if hasattr(result, '_write_xlsx'): class_name = result.__class__.__name__ sheet_name = '%s_%s' % (class_name, isubcase) assert len(sheet_name) < 31, sheet_name Sheet(isheet).name = sheet_name sheet = Sheet(isheet) print(' %s - isubcase=%i%s' % (result.__class__.__name__, isubcase, element_name)) result._write_xlsx(sheet, is_mag_phase=False) isheet += 1 else: print(" *xlsx - %s not written" % result.__class__.__name__) #footer = [4, 0, 4] workbook.save()
df[(df['region'] == 'NW') & (df['product'] == 'ROJ')]['price'])] * 12 Range('pricing', 'D30:O30').value = [float( df[(df['region'] == 'SW') & (df['product'] == 'ROJ')]['price'])] * 12 # FCOJ Range('pricing', 'D33:O33').value = [float( df[(df['region'] == 'NE') & (df['product'] == 'FCOJ')]['price'])] * 12 Range('pricing', 'D34:O34').value = [float( df[(df['region'] == 'MA') & (df['product'] == 'FCOJ')]['price'])] * 12 Range('pricing', 'D35:O35').value = [float( df[(df['region'] == 'SE') & (df['product'] == 'FCOJ')]['price'])] * 12 Range('pricing', 'D36:O36').value = [float( df[(df['region'] == 'MW') & (df['product'] == 'FCOJ')]['price'])] * 12 Range('pricing', 'D37:O37').value = [float( df[(df['region'] == 'DS') & (df['product'] == 'FCOJ')]['price'])] * 12 Range('pricing', 'D38:O38').value = [float( df[(df['region'] == 'NW') & (df['product'] == 'FCOJ')]['price'])] * 12 Range('pricing', 'D39:O39').value = [float( df[(df['region'] == 'SW') & (df['product'] == 'FCOJ')]['price'])] * 12 wb.save()
def save_xl_using_xlwings(file): wb = Workbook(file) wb.save()
MainFolder = 'C:\\Search_Result' FolderPath = os.path.join(MainFolder, row[1]) if not os.path.exists(FolderPath): os.makedirs(FolderPath) #Search and generate excel path='//Ecs01//ddb00/final_price' GetResult = Search_History.SearchFile(path,row[2],'None') if GetResult != 'No Results': CWPath = '\\\\ecsbks01\\swap\\DDD00\\virtualenv\\WinPython-32bit-2.7.10.2\\python-2.7.10\\Project_Evaluate_Excel\\Search_History' Excel_Path = os.path.join(CWPath, 'Result-Output.xlsx') wb = Workbook(Excel_Path) #wb = Workbook.caller() ExcelName = ('%s.xlsx' % row[2]) ExcelPath = os.path.join(FolderPath,ExcelName) wb.save(ExcelPath) wb.close() else: print "No result" NoFoundArr.append([row[1],row[2]]) #Write No result tag in to NotFound.xlsx print NoFoundArr NotFoundPath = 'C:\\Search_Result\\Not_Found.xlsx' wb = Workbook(NotFoundPath) wb = Workbook.caller() i=0 for line in NoFoundArr:
from xlwings import Workbook, FileFormat this_dir = os.path.dirname(os.path.abspath(__file__)) # Template template_path = os.path.abspath(os.path.join(this_dir, os.pardir, 'xlwings', 'xlwings_template.xltm')) workbook_paths = [template_path] # Examples root = os.path.abspath(os.path.join(this_dir, os.pardir, 'examples')) for root, dirs, files in os.walk(root): for f in files: if f.endswith(".xlsm"): workbook_paths.append((os.path.join(root, f))) for path in workbook_paths: wb = Workbook(path) wb.xl_workbook.VBProject.VBComponents.Remove(wb.xl_workbook.VBProject.VBComponents("xlwings")) wb.xl_workbook.VBProject.VBComponents.Import(os.path.abspath(os.path.join(this_dir, os.pardir, 'xlwings', 'xlwings.bas'))) if 'xlwings_template' in wb.fullname: # TODO: implement FileFormat in xlwings wb.xl_workbook.Application.DisplayAlerts = False wb.xl_workbook.SaveAs(template_path, FileFormat=FileFormat.xlOpenXMLTemplateMacroEnabled) wb.xl_workbook.Application.DisplayAlerts = True else: wb.save() wb.close()
def update_program_dashboard(program): filename = LOCAL_DASH_PATH + PROGRAM_DASH_TEMPLATE print('Excel program template filename: ', filename) save_filename = LOCAL_DASH_PATH + 'program_dashboard_' + datetime.today().strftime("%d-%b-%Y_T%H_%M") + '.xlsx' wb = Workbook(filename) excel = wb.xl_app excel.visible = True ### milestone dashboard for file_key in enumerate(FILE_KEYS): project = program[file_key[1]] row = 4 + 2 * file_key[0] due_date_row = Range('team milestones', (row,3), (row,19)) status_row= Range('team milestones', (row+1,3), (row+1,19)) milestones = get_milestones(project) due_date_row.value = [task.get_due_date() for task in milestones] status_row.value = [task.status() for task in milestones] ### CAPA Status row=2 clear_range = Range('CAPA status', (2,1), (500,11)) clear_range.value = '' for file_key in enumerate(FILE_KEYS): project = program[file_key[1]] project_status_row = Range('CAPA status', (row,1), (row,10)) project_status_row.value = get_project_status(project) capa_status_row = Range('CAPA status', (row+1,1), (row+1,10)) capa_status_row.value = get_capa_status(project) row = row + 2 next_row = Range('CAPA status', (row,1), (row,10)) IC_cat_dash = get_cat_dash(project, 'IC') for task_fields in IC_cat_dash: next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) ### CA / PA dashboard for task_fields in get_cat_dash(project, 'CA'): next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) for task_fields in get_cat_dash(project, 'PA'): if task_fields != None: next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) ### remediation for task_fields in get_cat_dash(project, 'RM'): next_row.value = task_fields row = row +1 next_row = Range('CAPA status', (row,1), (row,11)) row = row +1 ### Implementation row=2 clear_range = Range('implementation', (2,1), (500,11)) clear_range.value = '' for file_key in enumerate(FILE_KEYS): project = program[file_key[1]] for procedure in get_implementation(project): next_row = Range('implementation', (row,1), (row,10)) next_row.value = procedure row = row + 1 wb.save(save_filename) wb.close() excel.quit()
from xlwings import Workbook, Sheet, Range, Chart wb = Workbook(r'C:\Users\mih\Desktop\Stock_Screener\Proj1.xlsx') Range('A1').value = 'Two 2' print Range('A1').value Range('A1').value = [['Too 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 40.0]] Range('A1').table.value # or: Range('A1:C2').value Sheet(1).name chart = Chart.add(source_data=Range('A1').table) wb.save("C:\Users\mih\Desktop\Stock_Screener\Proj1.xlsx")
#generate load proportionality factor data xy_result = session.XYDataFromHistory( name='LPF' + model_num, odb=odb, outputVariableName='Load proportionality factor: LPF for Whole Model', steps=('NonLinear_Buckling', ), ) length_xy = len(xy_result) #extract collapse load proportionality factor for L in range(0, length_xy - 1): if xy_result[L + 1][1] - xy_result[L][1] > 0: if xy_result[L + 1][1] > 0: if L + 1 == length_xy - 1: Range("Sheet1", "EM" + model_num).value = xy_result[L + 1][1] else: continue else: Range("Sheet1", "EM" + model_num).value = xy_result[L][1] else: Range("Sheet1", "EM" + model_num).value = xy_result[L][1] break #save excel sheet wb.save("C:\\Temp\\Abaqus\\OR\\Wharf\\New\\Randomization.xlsm")