def test_autofit(self):
Range('Sheet1', 'A1:D4').value = 'test_string'
Sheet('Sheet1').autofit()
Sheet('Sheet1').autofit('r')
Sheet('Sheet1').autofit('c')
Sheet('Sheet1').autofit('rows')
Sheet('Sheet1').autofit('columns')
def __init__(self, file_name='Plantilla_Declaraciones'):
try:
# If this is a standalone program the defaul dir is in Temp Folder
path = os.path.abspath(
os.path.join(os.path.dirname(__file__), file_name + '.xlsx'))
#print(path)
wb = Book(path)
except:
print("seleccione el archivo Plantilla.xlsx")
path = easygui.fileopenbox(None,
'Seleccione el archivo de Plantilla')
if path == '.':
quit()
wb = Book(path)
self.current_dir = os.path.split(path)[0]
#print(self.current_dir)
#quit()
working_sheet = Sheet('breakdown')
self.data_dict = working_sheet.range('A2:B100').options(dict).value
""":type : dict"""
print("Data values from Plantilla_Breakdown:")
for key, values in self.data_dict.items():
print(key, ": ", values)
print("#####################################")
wb.app.quit()
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 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 main():
wb = Workbook() # Creates a connection with a new workbook
Range('A1').value = 'Foo 1'
print Range('A1').value
# 'Foo 1'
Range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Range('A1').table.value # or: Range('A1:C2').value
# [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Sheet(1).name
# 'Sheet1'
chart = Chart.add(source_data=Range('A1').table)
def save_by_stock_name_and_date(lines: List[List[str]], name: str, day: str):
try:
xlsx = xlwings.Book(excel_path + name + '交割单.xlsx')
sheet = Sheet(xlsx.sheets[0])
except FileNotFoundError as e:
print(e)
return
for line in lines:
if line[0] <= day and line[2] == name:
sheet.api.Rows(start_row).Insert()
for i in range(1, len(line) + 1):
sheet.range(start_row, i).value = line[i - 1]
if line[3] == '卖':
sheet.range(start_row, 10).value = '-' + line[4]
else:
sheet.range(start_row, 10).value = line[4]
def solver(workfile,
sheet=FIRST_SHEET,
savefile=None,
close_after=False,
is_contiguous=False):
"""
Processes a range on Excel sheet - applies formulas defined as strings to corresponding Excel formulas in cells.
Saves the workbook with formulas applied (overwrites existing file by default)
input
-----
workfile: input excel filepath to be processed
sheet: sheet number or name to be processed (default 1)
savefile: output excel filepath to be saved.
"""
wb = Workbook(workfile)
# wb = Workbook(get_source_file_path(arguments))
# work on given sheet
set_sheet(sheet)
start_cell, end_cell = find_start_end_indices(wb, is_contiguous)
# Parse the column under START label
variables, formulas, comments = parse_variable_values(
wb, start_cell, end_cell)
# checks if 'is_forecast' variable name is present on sheet
require_variable(variables, 'is_forecast')
# parse formulas and obtain Sympy expressions
parsed_formulas = parse_formulas(formulas, variables)
apply_formulas_on_sheet(wb, variables, parsed_formulas, start_cell)
if savefile is None:
savefile = workfile
save_workbook(wb, savefile)
# close file
Sheet(
FIRST_SHEET
).activate # are this missing a funcion call? Should always activate the first sheet?
if close_after is True:
close_workbook(wb)
def test_clear(self):
Range('Sheet2', 'G10').value = 22
Sheet('Sheet2').clear()
cell = Range('Sheet2', 'G10').value
assert_equal(cell, None)
def test_index(self):
assert_equal(Sheet('Sheet1').index, 1)
def test_name(self):
Sheet(1).name = 'NewName'
assert_equal(Sheet(1).name, 'NewName')
def test_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 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_chart_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
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_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 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 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_add_before(self):
new_sheet = Sheet.add(before='Sheet1')
assert_equal(Sheet(1).name, new_sheet.name)
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_add_named(self):
Sheet.add('test', before=1)
assert_equal(Sheet(1).name, 'test')
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()
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")
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)
Sheet('Sheet1').activate()
