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_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 test_clear_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear()
cell = Range('G10').value
assert_equal(cell, None)
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_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 3)
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_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()
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 test_add_name_already_taken(self):
Sheet.add('Sheet1')
def test_add_default(self):
# TODO: test call without args properly
Sheet.add()
def test_all(self):
all_names = [i.name for i in Sheet.all()]
assert_equal(all_names, ['Sheet1', 'Sheet2', 'Sheet3'])
def test_add_named(self):
Sheet.add('test', before=1)
assert_equal(Sheet(1).name, 'test')
## 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')
def test_count(self):
count = Sheet.count()
assert_equal(count, 3)
