def get_table_range(data_sheet: xw.Sheet, starting_cell: str = 'A1',
columns: TableSpan = 'expand',
rows: TableSpan = 'expand',
header: int = 1)->xw.Range:
'''Returns an excel range for the specified table.
columns='expand' assumes no break in the variable names.
Args:
data_sheet: The excel worksheet containing the table
starting_cell: the top right cell in the excel table.
columns: The number of columns in the table. If 'expand', the table
will include all columns left of starting_cell until the first
empty cell is encountered.
rows: The number of rows in the table. If 'expand', the table
will include all rows below the starting_cell until the first
empty cell is encountered.
header: The number of variable header rows. Default is 1.
To include the top row in the range selection set header to 0.
Returns:
An XLWings Range spanning the table data.
'''
start_range = data_sheet.range(starting_cell).offset(row_offset=header)
if 'expand' in str(rows):
data_bottom = start_range.end('down')
else:
data_bottom = start_range.offset(row_offset=int(rows)-1)
num_rows = data_bottom.row - start_range.row
if 'expand' in str(columns):
end_range = start_range.end('right')
else:
end_range = start_range.offset(column_offset=int(columns)-1)
end_range = end_range.offset(row_offset=num_rows)
selection_range = xw.Range(start_range, end_range)
return selection_range
def space_first_row_of_sheet(sht: xw.Sheet):
total_rng: xw.Range = sht.range('a1').current_region
format_row: xw.Range = total_rng[1, ::]
cell: xw.Range
for cell in format_row:
apply_spaced_format_to_cell(cell)
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 get_two_dimensional_look_up_ranges_to_name(sht: xw.Sheet):
entire_rng: xw.Range = sht.range('A1').current_region
row_rng = entire_rng[1:, 0]
header_rng = entire_rng[0, 1:]
data_rng = entire_rng[1:, 1:]
return row_rng.get_address(include_sheetname=True), \
header_rng.get_address(include_sheetname=True), data_rng.get_address(include_sheetname=True)
def get_metakey_dict_from_sheet(xwsheet: xw.Sheet,
last_col=None,
data_tab=True):
first_col = 1
if last_col is None:
last_col = get_last_col(xwsheet, first_col)
if data_tab:
#Added flag to improve efficiency of config tabs (particularly the columns config tab)
partner_name_row_no = find_row_no_by_row_metakey(
xwsheet, 'PartnerName')
pot_name_row_no = find_row_no_by_row_metakey(xwsheet, 'PotName')
size_number_row_no = find_row_no_by_row_metakey(xwsheet, 'SizeNumber')
mk_dict = {}
for col_no in range(first_col, last_col + 1):
mk_range = xwsheet.range(1, col_no)
primary_metakey = find_metakey_value_by_col(col_no, xwsheet)
if data_tab:
# Added flag to improve efficiency of config tabs (particularly the columns config tab)
partner_metakey = find_partner_metakey_by_col(
col_no,
xwsheet,
partner_name_row_no=partner_name_row_no,
pot_name_row_no=pot_name_row_no)
size_metakey = find_size_metakey_by_col(
col_no, xwsheet, size_number_row_no=size_number_row_no)
else:
partner_metakey = None
size_metakey = None
mk_tuple = (primary_metakey, partner_metakey, size_metakey)
mk_dict[mk_tuple] = col_no
return mk_dict
def add_value(self, value: Any, sheet: xw.Sheet):
'''Enter the value into the spreadsheet.
Arguments:
value {Any} -- The value to be placed in the target cell.
sheet {xw.Sheet} -- The Excel worksheet containing the target cell.
'''
cell_address = self.get('CellAddress')
if cell_address:
cell_format = self.get('CellFormat')
if cell_format:
# Set the cell format
sheet.range(cell_address).number_format = cell_format
if '%' in cell_format:
value = value / 100
# spreadsheet expects percent values as a decimal
# Set the cell Value
sheet.range(cell_address).value = value
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 get_standard_row_ranges_to_name(sht: xw.Sheet):
stop = len(sht.range('A1').current_region.columns) + 1
sht_nm = sht.name
return [
'{sht_nm}!${col_letter}:${col_letter}'.format(
sht_nm=sht_nm, col_letter=xw.utils.col_name(i))
for i in range(1, stop)
]
def get_scalar_ranges_to_name(sht: xw.Sheet):
"""Returns a list containing the address of each filled row in the value (2nd) column
of a scalar sheet.
Arguments:
sht {xw.Sheet} -- scalar input sheet.
"""
stop = len(sht.range('a1').current_region.rows) + 1
sht_nm = sht.name
return ['{}!$B${}'.format(sht_nm, i) for i in range(2, stop)]
def get_metakey_dict_from_sheet(xwsheet: xw.Sheet):
first_col = 4
last_col = xwu.get_last_col(xwsheet, first_col) + 1
mk_dict = {}
for col_no in range(first_col, last_col + 1):
mk_range = xwsheet.range(1, col_no)
primary_metakey = cfs.get_metakey_string(mk_range)
partner_metakey = cfs.get_partner_metakey_string(mk_range)
size_tuple = cfs.get_size_ordinal(mk_range)
mk_tuple = (primary_metakey, partner_metakey, size_tuple)
mk_dict[mk_tuple] = col_no
return mk_dict
def get_scalar_names(sht: xw.Sheet):
"""Returns a list of
Arguments:
sht {xw.Sheet} -- [description]
Returns:
[type] -- [description]
"""
values = sht.range('a1').current_region[1:, 0].options(ndim=1).value
sht_nm = sht.name
return ['{}__{}'.format(sht_nm, value) for value in values]
def gen_csv_from_sheet(xwsheet: xw.Sheet):
first_row = xwu.find_first_data_row(xwsheet)
last_row = xwu.get_insert_data_row_number(xwsheet) + 1
first_col = xwu.find_col_by_metakey('IsFinalised', xwsheet)
last_col = xwu.get_last_col(xwsheet, first_col) + 1
with open(filename, 'w') as file:
for r in range(first_row, last_row):
for c in range(first_col, last_col):
extract_range = xwsheet.range(r, c)
ref_string = get_ref_from_cell(extract_range)
if (ref_string is not None):
file.write(ref_string + '\n')
def apply_info_to_sheet(self, sheet: Sheet, value: str):
"""
Manipulates a worksheet with the specified value.
It removes the value in the worksheet.
:param sheet: a worksheet which will be manipulated
:param value: a value used when manipulating. In this case, missing values. If there are more than one
value, it will separate with white spaces.
"""
missing_values = value.split()
if len(missing_values) == 0: return
data = sheet.range('A1').expand().options(ndim=2).value
for row in range(len(data)):
for col in range(len(data[row])):
for mv in missing_values:
try:
if float(mv) == data[row][col]:
data[row][col] = None
except:
if mv == data[row][col]:
data[row][col] = None
sheet.range((1, 1)).value = data
def excel_header(df: pd.DataFrame, sheet: xw.Sheet) -> xw.Range:
try:
current_app = sheet.book.app
temp_excel_path = temp_path('.xlsx')
df.head(1).to_excel(temp_excel_path)
xl_header = current_app.books.open(temp_excel_path).sheets[0]
xl_header_last_cell_address = xl_header.cells.api.SpecialCells(
C.xlLastCell).Address
xl_header_last_row = xw.Range(xl_header_last_cell_address).row
xl_header.range('A1', xl_header_last_cell_address).api.Copy(
sheet.range('A1').api)
sheet.activate()
sheet.api.Rows(xl_header_last_row).Delete()
return sheet.cells(xl_header_last_row, 1)
except Exception as e:
raise e
finally:
xl_header.book.close()
os.unlink(temp_excel_path)
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 get_first_sht_row(sht: xw.Sheet):
return sht.range('a1').current_region.options(ndim=1)[0, ::]
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 len(sht.range('a1').current_region.rows) < 2:
return []
return func(sht)
def inner_func(sht: xw.Sheet):
if sht.range('A1').value is None:
return []
return func(sht)
