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 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 clearcontents(range_name, ws=None): """Clear a table started in a range cell, ex: clearcontents('a1'). clearcontents(range_name, ws=None) """ ws = Sheet.active() if ws is None else ws Range(ws.name, range_name).table.clear_contents()
def clearcontents_ws(ws=None): """Clear entire worksheet, ex: clearcontents_ws(ws=worksheetobj). clearcontents_ws(ws=None) """ ws = Sheet.active() if ws is None else ws ws.clear_contents()
def df_write(df, range_name, ws=None): """Write a dataframe to a cell. df_write(df, range_name, ws=None) """ ws = Sheet.active() if ws is None else ws Range(ws.name, range_name, index=False).value = df # without pd indices
def clear_ws(ws=None): """Clear a table started in a range cell, ex: clearcontents('a1'). clear_ws(ws=None) """ ws = Sheet.active() if ws is None else ws ws.clear()
def __init__(self, sheet="log"): """ create sheet and setup format """ logging.Handler.__init__(self) try: self.caller = Workbook.caller() except: self.caller = None return # create sheet if it does not exist self.sheet = sheet if sheet not in [s.name for s in Sheet.all()]: Sheet.add(sheet) Sheet(sheet).clear() self.row = 0
def list_write(range_name, value_list, ws=None): """Write a list vertially. list_write(range_name, value_list, ws=None) """ ws = Sheet.active() if ws is None else ws # Turn list into a column value_column = [[e] for e in value_list] Range(ws.name, range_name).value = value_column
def list_read(range_name, ws=None): """Read a list vertially. list_read(range_name, value_list, ws=None) """ ws = Sheet.active() if ws is None else ws # Range(range_name).options(transpose=True).value = value_list datalist = Range(ws.name, range_name).vertical.value return datalist
def df_read(range_name, ws=None): """Return dataframe from range name and Sheet.; ex: df = df_read('a1'). df_read(range_name, ws=None) """ ws = Sheet.active() if ws is None else ws data = Range(ws.name, range_name).table.value df = pd.DataFrame(data[1:], columns=data[0]) return df
def test_add_wkb(self): # test use of add with wkb argument # Connect to an alternative test file and make Sheet1 the active sheet xl_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_range_1.xlsx') wb_2nd = Workbook(xl_file, app_visible=False, app_target=APP_TARGET) n_before = [sh.name for sh in Sheet.all(wkb=wb_2nd)] Sheet.add(name="default", wkb=wb_2nd) Sheet.add(name="after1", after=1, wkb=wb_2nd) Sheet.add(name="before1", before=1, wkb=wb_2nd) n_after = [sh.name for sh in Sheet.all(wkb=wb_2nd)] n_before.append("default") n_before.insert(1, "after1") n_before.insert(0, "before1") assert_equal(n_before, n_after) wb_2nd.close()
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 get_sheet_arrange(): """ To find sheet name "TX or RX" and "2.4G or 5G" and make a dict (name:sheet_pos) """ sheet_names = [i.name.lower() for i in Sheet.all()] sheet_ref = {} for idx in range(len(sheet_names)): if "2.4ghz" in sheet_names[idx]: if "tx" in sheet_names[idx]: sheet_ref["TX2G"] = idx + 1 elif "sensitivity" in sheet_names[idx]: sheet_ref["RX2G"] = idx + 1 elif "5ghz" in sheet_names[idx]: if "tx" in sheet_names[idx]: sheet_ref["TX5G"] = idx + 1 elif "sensitivity" in sheet_names[idx]: sheet_ref["RX5G"] = idx + 1 return sheet_ref
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 sheet_ref(self): Range(Sheet(1), 'A20').value = 123 assert_equal(Range(1, 'A20').value, 123) Range(Sheet(1), (2, 2), (4, 4)).value = 321 assert_equal(Range(1, (2, 2)).value, 321)
def test_all(self): all_names = [i.name for i in Sheet.all()] assert_equal(all_names, ['Sheet1', 'Sheet2', 'Sheet3'])
def test_activate(self): Sheet('Sheet2').activate() assert_equal(Sheet.active().name, 'Sheet2') Sheet(3).activate() assert_equal(Sheet.active().index, 3)
def test_index(self): assert_equal(Sheet('Sheet1').index, 1)
def get_standard_row_names(sht: xw.Sheet): values = sht.range('A1').current_region[0, ::].options(ndim=1).value sht_nm = sht.name return ['{}__{}'.format(sht_nm, value) for value in values]
def inner_func(sht: xw.Sheet): if sht.range('A1').value is None: return [] return func(sht)
def copy_and_paste(sheet_copy: Sheet, sheet_paste: Sheet, ignore_row_num: int = 0, copy_region_used: bool = False, paste_region_used: bool = False, close_after_copy: bool = False) -> None: """copy a range from sheet_copy and paste it to sheet_paste Args: ---- sheet_copy: Sheet the sheet instance for copying sheet_paste: Sheet the sheet instance for pasting ignore_row_num: int, default 0 the row num to ignore for copying copy_region_used: bool, default False paste_region_used: bool, default False whether to use the used region, if false, use range("A1").current_region instead close_after_copy: bool, default False whether close workbook after pasted """ # define the copy range of copy region copy_region = sheet_copy.used_range if copy_region_used else sheet_copy.range( "A1").current_region log.debug("copy_region: {}".format(copy_region)) range_copy = copy_region.get_address(False, False) # change the start row if ignore_row_num if ignore_row_num: h_tuple = get_height(range_copy, return_details=True) copy_region_height, s_row = h_tuple[0], h_tuple[1] if copy_region_height > ignore_row_num: cells_list = range_copy.split(":") cells_list[0] = cells_list[0].replace(str(s_row), str(s_row + ignore_row_num)) range_copy = ":".join(cells_list) else: log.error( 'ignore_row_num[{}] is equal or bigger than copy_region_height[{}]' .format(ignore_row_num, copy_region_height)) log.debug("height_copy: {}".format(copy_region_height - ignore_row_num)) log.debug("range_copy: {}".format(range_copy)) # define the paste range of target sheet paste_region = sheet_paste.used_range if paste_region_used else sheet_paste.range( "A1").current_region log.debug("paste_region.address: {}".format(paste_region.address)) if paste_region.address != '$A$1': cell_s_paste = paste_region.address.split(":")[0] height_paste = get_height(paste_region.address) else: cell_s_paste = 'A1' height_paste = 0 # paste after the last row in sheet_paste range_paste = sheet_paste.range(cell_s_paste).offset( row_offset=height_paste) log.debug("range_paste: %s", range_paste.address) # copy value from range_copy in sheet_copy to range_paste in sheet_paste sheet_copy.range(range_copy).copy(destination=range_paste) if close_after_copy: sheet_copy.book.close()
def add_sheet(self, name): Sheet.add(name) self._i = 1
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 setUp(self): # Connect to test file and make Sheet1 the active sheet xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_chart_1.xlsx') self.wb = Workbook(xl_file1, visible=False) Sheet('Sheet1').activate()
def test_add_name_already_taken(self): Sheet.add('Sheet1')
def autofit(ws=None): """Autofit columns of worksheet """ ws = Sheet.active() if ws is None else ws ws.autofit(axis='columns')
def get_first_sht_row(sht: xw.Sheet): return sht.range('a1').current_region.options(ndim=1)[0, ::]
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 test_delete(self): assert_true('Sheet1' in [i.name for i in Sheet.all()]) Sheet('Sheet1').delete() assert_false('Sheet1' in [i.name for i in Sheet.all()])
def test_clear_active_sheet(self): Range('G10').value = 22 Sheet.active().clear() cell = Range('G10').value assert_equal(cell, None)
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 test_add_after(self): Sheet.add(after=Sheet.count()) assert_equal(Sheet(Sheet.count()).name, Sheet.active().name) Sheet.add(after=1) assert_equal(Sheet(2).name, Sheet.active().name)
def sheetCount(): return Sheet.count()
def test_add_named(self): Sheet.add('test', before=1) assert_equal(Sheet(1).name, 'test')
def inner_func(sht: xw.Sheet): if len(sht.range('a1').current_region.rows) < 2: return [] return func(sht)
def test_count(self): count = Sheet.count() assert_equal(count, 3)
def setUp(self): # Connect to test file and make Sheet1 the active sheet xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx') self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET) Sheet('Sheet1').activate()
try: wbTarget = Workbook(TargetPath) Excel_Path = os.path.join(CWPath, 'Result-Output.xlsx') wb = Workbook(Excel_Path) #rename worksheet Sheet('Result',wkb=wb).name=NewSheetName wb.set_current() #Copy All Range AllRRow = len(Range('A1').vertical.value) AllRCol = len(Range('A1').horizontal.value) print AllRCol,AllRRow RangeLimit = ('A1:B%d' % AllRRow) TempData = Range(RangeLimit).value wbTarget.set_current() lastSheetVal = Sheet.count() OriginSArr=[] for SheetName in xrange(1,lastSheetVal+1): OriginSArr.append(Sheet(SheetName).name) if NewSheetName not in OriginSArr: print "I should Add new array" 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
def test_name(self): Sheet(1).name = 'NewName' assert_equal(Sheet(1).name, 'NewName')
# Any way to create a new worksheet using xlwings? from xlwings import Sheet Sheet.add()
def clear_sheet_contents_without_changing_formatting(xls_filepath, sheet_name): if os.path.exist(xls_filepath): # else do nothing with Workbook(fullname=xls_filepath, app_visible=False) as wkb: Sheet(sheet=sheet_name, wkb=wkb).clear_contents()
def test_clear(self): Range('Sheet2', 'G10').value = 22 Sheet('Sheet2').clear() cell = Range('Sheet2', 'G10').value assert_equal(cell, None)
def test_add_before(self): new_sheet = Sheet.add(before='Sheet1') assert_equal(Sheet(1).name, new_sheet.name)
def test_add_default(self): # TODO: test call without args properly Sheet.add()
## CVS File check exist or not & Size if os.path.isfile(file_itogo or file_ir or file_iv): if os.stat(file_itogo or file_ir or file_iv).st_size > 0: ## Open new Workbook wb = Workbook(app_visible=False ) # Prepare first sheet sheet1 = pd.read_csv(file_itogo, delim_whitespace=True) Sheet.add('csv_itogo',before='Sheet1') Range('csv_itogo','A1',index=False).value =sheet1 Range('csv_itogo','A:ZZ').autofit('c') # Prepare Second sheet sheet2 = pd.read_csv( file_ir, delim_whitespace=True) Sheet.add('csv_ir',after='csv_itogo') Range('csv_ir','A1',index=False).value = sheet2 Range('csv_ir','A:ZZ').autofit('c')