def fill_ratio(ws, raw_sheet, ref_sheet, dest_row, dest_col, keys, metrics):
cell_range = CellRange(
min_row=dest_row, max_row=dest_row, min_col=dest_col, max_col=dest_col, title=ws.title
)
link_sheet_header(ws, ref_sheet, row=dest_row, values=keys + metrics)
dest_row += 1
col_offset = 0
for ref_col in range(1, ref_sheet.max_column + 1):
ref_header = ref_sheet.cell(row=1, column=ref_col)
raw_header = raw_sheet.cell(row=1, column=ref_col)
ref_header_name = ref_header.value.strip()
raw_header_name = ref_header.value.strip()
assert ref_header_name == raw_header_name, "Header values must match"
if ref_header_name not in keys + metrics:
continue
row_offset = 0
for ref_row in range(2, ref_sheet.max_row + 1):
ref_cell = ref_sheet.cell(row=ref_row, column=ref_col)
raw_cell = raw_sheet.cell(row=ref_row, column=ref_col)
dest_cell = ws.cell(row=dest_row + row_offset, column=dest_col + col_offset)
if ref_header_name in keys:
assert ref_cell.value == raw_cell.value, "Key value must match"
dest_cell.value = "={}".format(value_ref(ref_cell))
elif ref_header.value in metrics and ref_cell.data_type == TYPE_NUMERIC:
dest_cell.value = "={}".format(safe_ratio_ref(raw_cell, ref_cell))
else:
pass
# assert False
row_offset += 1
# Last row: Add GEOMEAN
dest_cell = ws.cell(row=dest_row + row_offset, column=dest_col + col_offset)
if ref_header.value in keys:
if keys.index(ref_header.value) == len(keys) - 1:
# Last key make geomean title
dest_cell.value = "GEOMEAN"
else:
start_cell = ws.cell(
row=dest_row + row_offset - ref_sheet.max_row + 1, column=dest_col + col_offset
)
end_cell = ws.cell(row=dest_row + row_offset - 1, column=dest_col + col_offset)
dest_cell.value = "=GEOMEAN({}:{})".format(start_cell.coordinate, end_cell.coordinate)
row_offset += 1
col_offset += 1
cell_range.expand(down=row_offset, right=col_offset - 1)
return cell_range
def paste_merged_cell_range(self, source_start_row: int,
source_block_area: CellRange,
merged_cell_ranges: list[MergedCellRange]):
for mcr in merged_cell_ranges:
if mcr.coord in source_block_area:
cr = CellRange(mcr.coord)
cr.shift(row_shift=self.start_rows[self.sheet_no] -
source_start_row)
self.worksheet.merge_cells(cr.coord)
def _get_footer(self):
time_total_col = self._dep[TimeTotalColumn]
total_range = CellRange(min_col=time_total_col,
max_col=time_total_col,
min_row=2,
max_row=NUM_BODY_ITEMS + 1)
my_range = CellRange(min_col=self.column,
max_col=self.column,
min_row=2,
max_row=NUM_BODY_ITEMS + 1)
template = '=SUMPRODUCT({0},{1}) / SUM({0})'
return template.format(total_range, my_range)
def test_merged_cell_class():
test_dict = {}
merged_cell = MergedCell(CellRange(range_string="A7:J8"))
merged_cell.register_cells(test_dict)
keys = sorted(list(test_dict.keys()))
expected = [
"7-1",
"7-10",
"7-2",
"7-3",
"7-4",
"7-5",
"7-6",
"7-7",
"7-8",
"7-9",
"8-1",
"8-10",
"8-2",
"8-3",
"8-4",
"8-5",
"8-6",
"8-7",
"8-8",
"8-9",
]
eq_(keys, expected)
eq_(merged_cell, test_dict["7-1"])
eq_(merged_cell.bottom_row(), 8)
eq_(merged_cell.right_column(), 10)
def open():
xfile = load_workbook(template_xlsx)
sheet = xfile.get_sheet_by_name('Sheet1')
column_map = {}
match_words = set()
for data in xfile.defined_names.definedName:
for title, coord in data.destinations:
if title == 'Sheet1':
if data.name.startswith('__match_'):
cell_range = CellRange(coord)
assert cell_range.min_row == cell_range.max_row
for column_num in range(cell_range.min_col,
cell_range.max_col + 1):
(match_value, match_type, *_) = sheet.cell(
row=cell_range.min_row,
column=column_num).value.lower().split(' ')
column_name = '__match_' + match_type + '_' + match_value
column_map[column_name] = column_num
match_words.add(
MatchWord(column_name=column_name,
match_type=match_type,
match_value=match_value))
else:
column_map[data.name] = sheet[coord][0].column
return Exporter(xfile=xfile,
column_map=column_map,
match_words=match_words)
def copy_range(_self, cell_range, rows=0, cols=0):
""" 同表格内的 range 复制操作
Copy a cell range by the number of rows and/or columns:
down if rows > 0 and up if rows < 0
right if cols > 0 and left if cols < 0
Existing cells will be overwritten.
Formulae and references will not be updated.
"""
from openpyxl.worksheet.cell_range import CellRange
from itertools import product
# 1 预处理
if isinstance(cell_range, str):
cell_range = CellRange(cell_range)
if not isinstance(cell_range, CellRange):
raise ValueError("Only CellRange objects can be copied")
if not rows and not cols:
return
min_col, min_row, max_col, max_row = cell_range.bounds
# 2 注意拷贝顺序跟移动方向是有关系的，要防止被误覆盖，复制了新的值，而非原始值
r = sorted(range(min_row, max_row + 1), reverse=rows > 0)
c = sorted(range(min_col, max_col + 1), reverse=cols > 0)
for row, column in product(r, c):
_self.cell(row,
column).copy_cell(_self.cell(row + rows, column + cols))
def cell(self, rdrowx, rdcolx, wtrowx, wtcolx, value, data_type=None):
self.copy_col_dimension(rdcolx, wtcolx)
source_cell = self.rdsheet._cells[(rdrowx, rdcolx)]
target_cell = self.wtsheet.cell(column=wtcolx, row=wtrowx)
if data_type:
target_cell._value = value
target_cell.data_type = data_type
else:
target_cell.value = value
if source_cell.has_style:
target_cell._style = copy.copy(source_cell._style)
if source_cell.hyperlink:
target_cell._hyperlink = copy.copy(source_cell.hyperlink)
#if source_cell.comment:
# target_cell.comment = copy.copy(source_cell.comment)
rdcoords2d = (rdrowx, rdcolx)
if rdcoords2d in self.rdsheet.mc_top_left_map:
if self.wtsheet.mc_ranges.get(rdcoords2d):
rlo, rhi, clo, chi = self.wtsheet.mc_ranges.get(rdcoords2d)
cr = CellRange(min_row=rlo,
max_row=rhi,
min_col=clo,
max_col=chi)
self.wtsheet.merged_cells.add(cr)
self.wtsheet.mc_ranges[rdcoords2d] = (wtrowx, wtrowx, wtcolx,
wtcolx)
else:
mc_top_left = self.rdsheet.mc_already_set.get(rdcoords2d)
if mc_top_left:
rlo, rhi, clo, chi = self.wtsheet.mc_ranges.get(mc_top_left)
self.wtsheet.mc_ranges[mc_top_left] = (rlo, max(rhi,
wtrowx), clo,
max(chi, wtcolx))
def add_cell_range(self, rdcoords2d, crange):
rlo, rhi, clo, chi = crange
cr = CellRange(min_row=rlo, max_row=rhi, min_col=clo, max_col=chi)
dv = self.dv_copies.get(rdcoords2d)
if not dv:
dv = copy(self._orig_map.get(rdcoords2d))
dv.ranges = MultiCellRange()
self.dv_copies[rdcoords2d] = dv
dv.ranges.add(cr)
def _get_body_item(self, item_num):
data_start_idx = self._dep[CategoryTimeColumn]
data_end_idx = len(Column.col_order)
row = item_num + 1
template = '=IF(SUM({0})=0,"N/A",SUM({0})/60)'
cell_range = CellRange(min_col=data_start_idx,
max_col=data_end_idx,
min_row=row,
max_row=row)
return template.format(cell_range)
def merge_cells(self,
range_string=None,
start_row=None,
start_column=None,
end_row=None,
end_column=None):
cr = CellRange(range_string=range_string,
min_col=start_column,
min_row=start_row,
max_col=end_column,
max_row=end_row)
self.merged_cells.add(cr.coord)
def get_xlrange(start_row, start_col, end_row=None, end_col=None):
if end_row is None: end_row = start_row
if end_col is None: end_col = start_col
if end_row < start_row:
start_row, end_row = end_row, start_row
if end_col < start_col:
start_col, end_col = end_col, start_col
return CellRange(min_row=start_row,
min_col=start_col,
max_row=end_row,
max_col=end_col)
def merge_cell(self, rdrowx, rdcolx, wtrowx, wtcolx):
rdcoords2d = (rdrowx, rdcolx)
if rdcoords2d in self.rdsheet.mc_top_left_map:
if self.wtsheet.mc_ranges.get(rdcoords2d):
rlo, rhi, clo, chi = self.wtsheet.mc_ranges.get(rdcoords2d)
cr = CellRange(min_row=rlo, max_row=rhi, min_col=clo, max_col=chi)
self.wtsheet.merged_cells.add(cr)
self.wtsheet.mc_ranges[rdcoords2d] = (wtrowx, wtrowx, wtcolx, wtcolx)
else:
mc_top_left = self.rdsheet.mc_already_set.get(rdcoords2d)
if mc_top_left:
rlo, rhi, clo, chi = self.wtsheet.mc_ranges.get(mc_top_left)
self.wtsheet.mc_ranges[mc_top_left] = (rlo, max(rhi, wtrowx), clo, max(chi, wtcolx))
def __init__(self, rdsheet):
if not rdsheet.auto_filter.ref:
self.to_merge = False
return
self.to_merge = True
self.auto_filter = rdsheet.auto_filter
self.set_range()
cell_range = CellRange(rdsheet.auto_filter.ref)
self._first_row = cell_range.min_row
self._last_row = cell_range.max_row
self._first_col = cell_range.min_col
self._last_col = cell_range.max_col
self.first_af = None
def test_merged_cell_class():
test_dict = {}
merged_cell = MergedCell(CellRange(range_string="A7:J8"))
merged_cell.register_cells(test_dict)
keys = sorted(list(test_dict.keys()))
expected = [
'7-1', '7-10', '7-2', '7-3', '7-4', '7-5', '7-6', '7-7', '7-8', '7-9',
'8-1', '8-10', '8-2', '8-3', '8-4', '8-5', '8-6', '8-7', '8-8', '8-9'
]
eq_(keys, expected)
eq_(merged_cell, test_dict['7-1'])
eq_(merged_cell.bottom_row(), 8)
eq_(merged_cell.right_column(), 10)
def set_ranges(self):
"""
Iter over named ranges and collect ALL ranges into:
- self.ranges
- self.params
- self.text_ranges
here we dont know if ranges are used, but we cannot distinguish cell_alias, and other stuff.
:return:
None
"""
self.ranges = {}
self.text_ranges = {}
self.params = {}
self.raw_cells_coordinates = {}
for x in self.named_ranges:
for sheet, rng in x.destinations:
if x.type == 'TEXT':
self.text_ranges[x.name] = x.value
elif x.type == 'NUMBER':
self.params[x.name] = x.value
else:
if useless_range(rng):
log.debug("{} range [{}]in sheet {} discarded ".format(x.name,rng,sheet))
continue
with timeit(f"set ranges for {sheet} {rng}"):
try:
cells = self.wb_data[sheet][rng]
except KeyError as err:
log.error(err)
continue
if isinstance(cells, (Cell, ReadOnlyCell, EmptyCell)):
# Single named cell
self.ranges[x.name] = (cells,)
else:
# Range named
self.ranges[x.name] = [x for x in chain(*cells)]
self.raw_cells_coordinates[x.name] = tuple(CellRange(rng).cells)
log.debug("{} named ranges collected".format(len(self.ranges)))
log.debug("{} parameters collected".format(len(self.params)))
log.debug("{} text range collected".format(len(self.text_ranges)))
def new_range(self):
if self.start_wtrowx==-1:
return
if not self.first_af:
self.first_af = CellRange(None, self.start_wtcolx, self.start_wtrowx,
self.end_wtcolx, self.end_wtrowx)
def add_cell_range(self, rdcoords2d, crange):
rlo, rhi, clo, chi = crange
cr = CellRange(min_row=rlo, max_row=rhi, min_col=clo, max_col=chi)
self.wtsheet.merged_cells.add(cr)
def set_mc_ranges(self):
for key, crange in self.wtsheet.mc_ranges.items():
rlo, rhi, clo, chi = crange
cr = CellRange(min_row=rlo, max_row=rhi, min_col=clo, max_col=chi)
self.wtsheet.merged_cells.add(cr)
def getmerge(cell):
for r in sheet_ranges.merged_cells.ranges:
if r.issuperset(CellRange(cell.coordinate)): return r
# return None
return CellRange(cell.coordinate)
def ismerged(cell):
for r in sheet_ranges.merged_cells.ranges:
if r.issuperset(CellRange(cell.coordinate)): return True
return False
wb = load_workbook(file)
print(wb.sheetnames)
for sheetname in wb.sheetnames:
print(sheetname)
if sheetname == "Sheet1": continue
sheet_ranges = wb[sheetname]
suites = []
i = 11
casename0 = None
preconditions0 = ""
m0 = None
pm0 = CellRange("A1:A1")
cm0 = CellRange("A1:A1")
suitename0 = ""
suite = None
j = 2
cases = []
while sheet_ranges['B' + str(i)].value:
# print(sheet_ranges['B'+str(i)].value)
#确定模块
if sheet_ranges['C' + str(i)].value:
if suitename0 != "":
print(suitename0 + ":" + str(len(cases)))
suite = SUITE % dict(suitename=suitename0,
testcases="".join(cases))
cases = []
suites.append(suite)
def get_merged_cell_range(self):
block_area = CellRange(min_col=self.start_column,
max_col=self.end_column,
min_row=self.start_row,
max_row=self.end_row)
return self.start_row, block_area, self.worksheet.merged_cell_ranges
def _parseData(model, data, rowmapper, user, database, ping):
selfReferencing = []
def formfieldCallback(f):
# global selfReferencing
if isinstance(f, RelatedField):
tmp = BulkForeignKeyFormField(field=f, using=database)
if f.remote_field.model == model:
selfReferencing.append(tmp)
return tmp
else:
return f.formfield(localize=True)
# Initialize
headers = []
rownumber = 0
changed = 0
added = 0
content_type_id = ContentType.objects.get_for_model(
model, for_concrete_model=False).pk
# Call the beforeUpload method if it is defined
if hasattr(model, "beforeUpload"):
model.beforeUpload(database)
errors = 0
warnings = 0
has_pk_field = False
processed_header = False
rowWrapper = rowmapper()
# Detect excel autofilter data tables
if isinstance(data, Worksheet) and data.auto_filter.ref:
try:
bounds = CellRange(data.auto_filter.ref).bounds
except Exception:
bounds = None
else:
bounds = None
for row in data:
rownumber += 1
if bounds:
# Only process data in the excel auto-filter range
if rownumber < bounds[1]:
continue
elif rownumber > bounds[3]:
break
else:
rowWrapper.setData(row)
else:
rowWrapper.setData(row)
# Case 1: Skip empty rows
if rowWrapper.empty():
continue
# Case 2: The first line is read as a header line
elif not processed_header:
processed_header = True
# Collect required fields
required_fields = set()
for i in model._meta.fields:
if (not i.blank and i.default == NOT_PROVIDED
and not isinstance(i, AutoField)):
required_fields.add(i.name)
# Validate all columns
for col in rowWrapper.values():
col = str(col).strip().strip("#").lower() if col else ""
if col == "":
headers.append(None)
continue
ok = False
for i in model._meta.fields:
# Try with translated field names
if (col == i.name.lower() or col == i.verbose_name.lower()
or col
== ("%s - %s" %
(model.__name__, i.verbose_name)).lower()):
if i.editable is True:
headers.append(i)
else:
headers.append(None)
required_fields.discard(i.name)
ok = True
break
if translation.get_language() != "en":
# Try with English field names
with translation.override("en"):
if (col == i.name.lower()
or col == i.verbose_name.lower() or col ==
("%s - %s" %
(model.__name__, i.verbose_name)).lower()):
if i.editable is True:
headers.append(i)
else:
headers.append(None)
required_fields.discard(i.name)
ok = True
break
if not ok:
headers.append(None)
warnings += 1
yield (
WARNING,
None,
None,
None,
force_str(
_("Skipping unknown field %(column)s" %
{"column": col})),
)
if (col == model._meta.pk.name.lower()
or col == model._meta.pk.verbose_name.lower()):
has_pk_field = True
if required_fields:
# We are missing some required fields
errors += 1
yield (
ERROR,
None,
None,
None,
force_str(
_("Some keys were missing: %(keys)s" %
{"keys": ", ".join(required_fields)})),
)
# Abort when there are errors
if errors:
if isinstance(data,
Worksheet) and len(data.parent.sheetnames) > 1:
# Skip this sheet an continue with the next one
return
else:
raise NameError("Can't proceed")
# Create a form class that will be used to validate the data
fields = [i.name for i in headers if i]
if hasattr(model, "getModelForm"):
UploadForm = model.getModelForm(tuple(fields),
database=database)
else:
UploadForm = modelform_factory(
model,
fields=tuple(fields),
formfield_callback=formfieldCallback)
rowWrapper = rowmapper(headers)
# Get natural keys for the class
natural_key = None
if hasattr(model.objects, "get_by_natural_key"):
if model._meta.unique_together:
natural_key = model._meta.unique_together[0]
elif hasattr(model, "natural_key") and isinstance(
model.natural_key, tuple):
natural_key = model.natural_key
# Case 3: Process a data row
else:
try:
# Step 1: Send a ping-alive message to make the upload interruptable
if ping:
if rownumber % 50 == 0:
yield (DEBUG, rownumber, None, None, None)
# Step 2: Fill the form with data, either updating an existing
# instance or creating a new one.
if has_pk_field:
# A primary key is part of the input fields
try:
# Try to find an existing record with the same primary key
it = (model.objects.using(database).only(*fields).get(
pk=rowWrapper[model._meta.pk.name]))
form = UploadForm(rowWrapper, instance=it)
except model.DoesNotExist:
form = UploadForm(rowWrapper)
it = None
elif natural_key:
# A natural key exists for this model
try:
# Build the natural key
key = []
for x in natural_key:
key.append(rowWrapper.get(x, None))
# Try to find an existing record using the natural key
it = model.objects.get_by_natural_key(*key)
form = UploadForm(rowWrapper, instance=it)
except model.DoesNotExist:
form = UploadForm(rowWrapper)
it = None
except model.MultipleObjectsReturned:
yield (
ERROR,
rownumber,
None,
None,
force_str(_("Key fields not unique")),
)
continue
else:
# No primary key required for this model
form = UploadForm(rowWrapper)
it = None
# Step 3: Validate the form and model, and save to the database
if form.has_changed():
if form.is_valid():
# Save the form
obj = form.save(commit=False)
if it:
changed += 1
obj.save(using=database, force_update=True)
else:
added += 1
obj.save(using=database, force_insert=True)
# Add the new object in the cache of available keys
for x in selfReferencing:
if x.cache is not None and obj.pk not in x.cache:
x.cache[obj.pk] = obj
if user:
if it:
Comment(
user_id=user.id,
content_type_id=content_type_id,
object_pk=obj.pk,
object_repr=force_str(obj)[:200],
type="change",
comment="Changed %s." %
get_text_list(form.changed_data, "and"),
).save(using=database)
else:
Comment(
user_id=user.id,
content_type_id=content_type_id,
object_pk=obj.pk,
object_repr=force_str(obj)[:200],
type="add",
comment="Added",
).save(using=database)
else:
# Validation fails
for error in form.non_field_errors():
errors += 1
yield (ERROR, rownumber, None, None, error)
for field in form:
for error in field.errors:
errors += 1
yield (
ERROR,
rownumber,
field.name,
rowWrapper[field.name],
error,
)
except Exception as e:
errors += 1
yield (ERROR, None, None, None,
"Exception during upload: %s" % e)
yield (
INFO,
None,
None,
None,
_("%(rows)d data rows, changed %(changed)d and added %(added)d records, %(errors)d errors, %(warnings)d warnings"
) % {
"rows": rownumber - 1,
"changed": changed,
"added": added,
"errors": errors,
"warnings": warnings,
},
)
def new_range(self):
if self.start_wtrowx==-1:
return
range = CellRange(None, self.start_wtcolx, self.start_wtrowx, self.end_wtcolx, self.end_wtrowx)
self.merger.add_new_range(self.dv_key, range)
def new_range(self):
if self.start_wtrowx==self.end_wtrowx and self.start_wtcolx==self.end_wtcolx:
return
range = CellRange(None, self.start_wtcolx, self.start_wtrowx, self.end_wtcolx, self.end_wtrowx)
self.merger.add_new_range(range)
def main():
start_date = date(2016, 1, 1)
end_date = date(2018, 12, 31)
date_list = daterange(start_date, end_date)
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws = wb.active
ws.title = 'ah ha'
holydays_fill = PatternFill('solid', fgColor='CCCCCC')
column_width = 3
colored_rows = 10
col = 2
row = 1
for this_date in date_list:
ws.column_dimensions[get_column_letter(col)].width = column_width
year_cell = ws.cell(column=col, row=row, value=int(this_date.strftime('%Y')))
year_cell.font = styles.Font(bold=True)
year_cell.alignment = Alignment(horizontal='center')
month_cell = ws.cell(column=col, row=row+1, value=this_date.strftime('%B'))
month_cell.font = styles.Font(bold=True)
month_cell.alignment = Alignment(horizontal='center')
day_cell = ws.cell(column=col, row=row+2, value=int(this_date.strftime('%d')))
day_cell.font = styles.Font(bold=True)
day_cell.alignment = Alignment(horizontal='center')
if this_date.weekday() >= 5:
for i in range(day_cell.row, day_cell.row+colored_rows):
ws.cell(column=col, row=i).fill = holydays_fill
col = col + 1
ranges = []
# calculate year ranges
this_year = ws.cell(row=1, column=2)
for i in range(2, col+1):
if ws.cell(row=1, column=i).value != this_year.value:
ranges.append({'type': 'year', 'range': [1, 1, column_index_from_string(this_year.column), i-1]})
this_year = ws.cell(row=1, column=i)
if this_year.value is None:
break
# calculate month ranges
this_year = ws.cell(row=2, column=2)
for i in range(2, col + 1):
if ws.cell(row=2, column=i).value != this_year.value:
ranges.append({'type': 'month', 'range': [2, 2, column_index_from_string(this_year.column), i - 1]})
this_year = ws.cell(row=2, column=i)
if this_year.value is None:
break
# merge ranges and add borders
for this_range_dict in ranges:
this_range = this_range_dict['range']
ws.merge_cells(start_row=this_range[0], end_row=this_range[1], start_column=this_range[2], end_column=this_range[3])
if this_range_dict['type'] == 'year':
set_border(ws, CellRange(min_row=this_range[0], max_row=this_range[1],
min_col=this_range[2], max_col=this_range[3]).coord, 'thick')
if this_range_dict['type'] == 'month':
# month thin line
set_border(ws, CellRange(min_row=this_range[0], max_row=this_range[1],
min_col=this_range[2], max_col=this_range[3]).coord, 'thick')
# day thin line
set_border(ws, CellRange(min_row=this_range[0]+1, max_row=this_range[1]+1,
min_col=this_range[2], max_col=this_range[3]).coord, 'thin')
# month thick line
set_border(ws, CellRange(min_row=this_range[0], max_row=this_range[1]+colored_rows,
min_col=this_range[2], max_col=this_range[3]).coord, 'thick')
wb.save(filename=dest_filename)
def _get_footer(self):
range = CellRange(min_col=self.column,
max_col=self.column,
min_row=2,
max_row=1 + NUM_BODY_ITEMS)
return '=AVERAGE({})'.format(str(range))
def addTableRows(wb, tableName, rowsCount):
ws = wb.active
# Определение диапазона таблицы
table = getTableObject(ws, tableName)
initRange = CellRange(table.ref)
# Определение диапазона, который включает в себя всё, что находится ниже данной таблицы
rangeToMove = CellRange(min_col=initRange.min_col,
max_col=initRange.max_col,
min_row=initRange.max_row + 1,
max_row=ws.max_row)
# Определение именованных диапазонов, находящихся на активном листе ниже данной таблицы
definedNames = {}
for name in wb.defined_names.definedName:
for dest in name.destinations:
if (dest[0] == ws.title) and (not CellRange(
dest[1]).isdisjoint(rangeToMove)):
if name.name in definedNames.keys():
definedNames[name.name].append(dest[1])
else:
definedNames[name.name] = [dest[1]]
# Переопределение всех объединённых ячеек, находящихся ниже данной таблицы
mergedCells = [a for a in ws.merged_cells.ranges]
for item in mergedCells:
if not item.isdisjoint(rangeToMove):
ws.unmerge_cells(str(item))
item.shift(row_shift=rowsCount)
ws.merge_cells(str(item))
# Дополнительное перемещение именованных диапазонов, находящихся ниже данной таблицы
for name, rangeList in definedNames.items():
newRangeList = []
for range in rangeList:
rangeObject = CellRange(range)
rangeObject.shift(row_shift=rowsCount)
newRangeList.append(
re.sub(r'([A-Z]+|[0-9]+)', r'$\1', str(rangeObject)))
newNamedRange = DefinedName(name=name,
attr_text='%s!%s' %
(ws.title, ';'.join(newRangeList)))
del wb.defined_names[name]
wb.defined_names.append(newNamedRange)
# Дополнительное перемещение таблиц, находящихся ниже данной таблицы
for tbl in ws._tables:
if not CellRange(tbl.ref).isdisjoint(rangeToMove):
newTblRange = CellRange(tbl.ref)
newTblRange.shift(row_shift=rowsCount)
tbl.ref = str(newTblRange)
# Сдвиг rangeToMove на количество добавляемых в таблицу строк
ws.move_range(rangeToMove, rows=rowsCount)
newTableRange = initRange
newTableRange.expand(down=rowsCount)
table.ref = str(newTableRange)