Provi
У меня с пунктом 2.2. проблема диапазоны я достаю через merged_cell_ranges.
А вот как обработать этот массив не могу найти. С ним не получается провести ни каких операций.
Преобразовать ни в список ни в словарь тоже не могу. Могу только вывести через print)
Это уже список диапазонов. Его не надо приводить к списку.
>>> import openpyxl
>>>
>>> ifname = 'in.xlsx'
>>>
>>> workbook = openpyxl.load_workbook(filename=ifname)
>>> sheet = workbook['TDSheet']
>>>
>>> ranges = sheet.merged_cells.ranges
>>>
>>> ranges[:3]
[<MergedCellRange AA14:AE14>, <MergedCellRange AF14:AJ14>, <MergedCellRange AK14:AO14>]
>>>
>>> type(ranges)
<class 'list'>
>>>
>>> r0 = ranges[0]
>>> r0
<MergedCellRange AA14:AE14>
>>>
>>> r0.cells
<itertools.product object at 0x7f746ae2a0d8>
>>> list(r0.cells)
[(14, 27), (14, 28), (14, 29), (14, 30), (14, 31)]
>>>
>>> r0.rows
<generator object rows at 0x7f7445bbfaf0>
>>> list(r0.rows)
[[(14, 27), (14, 28), (14, 29), (14, 30), (14, 31)]]
>>>
>>> r0.cols
<generator object cols at 0x7f7445bbfa98>
>>> list(r0.cols)
[[(14, 27)], [(14, 28)], [(14, 29)], [(14, 30)], [(14, 31)]]
>>>
>>>
>>> r0.left
[(14, 27)]
>>> r0.right
[(14, 31)]
>>> r0.top
[(14, 27), (14, 28), (14, 29), (14, 30), (14, 31)]
>>> r0.bottom
[(14, 27), (14, 28), (14, 29), (14, 30), (14, 31)]
>>>
>>>
>>> r0.bounds
(27, 14, 31, 14)
>>>
>>> r0.expand(down=2)
>>>
>>> r0.bounds
(27, 14, 31, 16)
>>>
>>> r0
<MergedCellRange AA14:AE16>
>>>
>>> r0.shrink(bottom=2)
>>>
>>> r0
<MergedCellRange AA14:AE14>
>>>
Вот у тебя есть, например, r0 в консоли питона, ты берёшь help(r0) набираешь и выполняешь. Так питон тебе выведет помощь для класса openpyxl.worksheet.merge.MergedCellRange , там будет информация, какие методы у него есть, как их вызывать, с какими аргументами. Также можешь использовать dir(r0), это просто даёт возможность просмотреть атрибуты в объекте.
Это dir(r0)
>>> dir(r0)
['_CellRange__superset', '__add__', '__and__', '__attrs__', '__class__', '__contains__', '__copy__', '__delattr__', '__dict__', '__dir__', '__doc__', '__elements__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__namespaced__', '__ne__', '__nested__', '__new__', '__or__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_check_title', '_get_borders', 'bottom', 'bounds', 'cells', 'cols', 'coord', 'expand', 'format', 'from_tree', 'idx_base', 'intersection', 'isdisjoint', 'issubset', 'issuperset', 'left', 'max_col', 'max_row', 'min_col', 'min_row', 'namespace', 'right', 'rows', 'shift', 'shrink', 'size', 'start_cell', 'tagname', 'title', 'to_tree', 'top', 'union', 'ws']
>>>
Это help(r0)
Help on MergedCellRange in module openpyxl.worksheet.merge object:
class MergedCellRange(openpyxl.worksheet.cell_range.CellRange)
| MergedCellRange stores the border information of a merged cell in the top
| left cell of the merged cell.
| The remaining cells in the merged cell are stored as MergedCell objects and
| get their border information from the upper left cell.
|
| Method resolution order:
| MergedCellRange
| openpyxl.worksheet.cell_range.CellRange
| openpyxl.descriptors.serialisable.Serialisable
| openpyxl.descriptors._Serialisable
| builtins.object
|
| Methods defined here:
|
| __contains__(self, coord)
| Check whether the range contains a particular cell coordinate
|
| __copy__(self)
|
| __init__(self, worksheet, coord)
| Initialize self. See help(type(self)) for accurate signature.
|
| format(self)
| Each cell of the merged cell is created as MergedCell if it does not
| already exist.
|
| The MergedCells at the edge of the merged cell gets its borders from
| the upper left cell.
|
| - The top MergedCells get the top border from the top left cell.
| - The bottom MergedCells get the bottom border from the top left cell.
| - The left MergedCells get the left border from the top left cell.
| - The right MergedCells get the right border from the top left cell.
|
| ----------------------------------------------------------------------
| Data and other attributes defined here:
|
| __attrs__ = ()
|
| __elements__ = ()
|
| __namespaced__ = ()
|
| __nested__ = ()
|
| ----------------------------------------------------------------------
| Methods inherited from openpyxl.worksheet.cell_range.CellRange:
|
| __and__ = intersection(self, other)
| Return a new range with cells common to this range and *other*
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range.
| :return: the intersecting sheet range.
| :raise: :class:`ValueError` if the *other* range doesn't intersect
| with this range.
|
| __eq__(self, other)
| Test whether the ranges are equal.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* == *other*.
|
| __ge__ = issuperset(self, other)
| Test whether every cell in *other* is in this range.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* >= *other* (or *other* in *range*).
|
| __gt__(self, other)
| Test whether this range contains every cell in *other*, and more.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* > *other*.
|
| __iter__(self)
| For use as a dictionary elsewhere in the library.
|
| __le__ = issubset(self, other)
| Test whether every cell in this range is also in *other*.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* <= *other*.
|
| __lt__(self, other)
| Test whether *other* contains every cell of this range, and more.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* < *other*.
|
| __ne__(self, other)
| Test whether the ranges are not equal.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* != *other*.
|
| __or__ = union(self, other)
| Return the minimal superset of this range and *other*. This new range
| will contain all cells from this range, *other*, and any additional
| cells required to form a rectangular ``CellRange``.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range.
| :return: a ``CellRange`` that is a superset of this and *other*.
|
| __repr__(self)
| Return repr(self).
|
| __str__(self)
| Return str(self).
|
| expand(self, right=0, down=0, left=0, up=0)
| Expand the range by the dimensions provided.
|
| :type right: int
| :param right: expand range to the right by this number of cells
| :type down: int
| :param down: expand range down by this number of cells
| :type left: int
| :param left: expand range to the left by this number of cells
| :type up: int
| :param up: expand range up by this number of cells
|
| intersection(self, other)
| Return a new range with cells common to this range and *other*
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range.
| :return: the intersecting sheet range.
| :raise: :class:`ValueError` if the *other* range doesn't intersect
| with this range.
|
| isdisjoint(self, other)
| Return ``True`` if this range has no cell in common with *other*.
| Ranges are disjoint if and only if their intersection is the empty range.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range.
| :return: ``True`` if the range has no cells in common with other.
|
| issubset(self, other)
| Test whether every cell in this range is also in *other*.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* <= *other*.
|
| issuperset(self, other)
| Test whether every cell in *other* is in this range.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range
| :return: ``True`` if *range* >= *other* (or *other* in *range*).
|
| shift(self, col_shift=0, row_shift=0)
| Shift the focus of the range according to the shift values (*col_shift*, *row_shift*).
|
| :type col_shift: int
| :param col_shift: number of columns to be moved by, can be negative
| :type row_shift: int
| :param row_shift: number of rows to be moved by, can be negative
| :raise: :class:`ValueError` if any row or column index < 1
|
| shrink(self, right=0, bottom=0, left=0, top=0)
| Shrink the range by the dimensions provided.
|
| :type right: int
| :param right: shrink range from the right by this number of cells
| :type down: int
| :param down: shrink range from the top by this number of cells
| :type left: int
| :param left: shrink range from the left by this number of cells
| :type up: int
| :param up: shrink range from the bottown by this number of cells
|
| union(self, other)
| Return the minimal superset of this range and *other*. This new range
| will contain all cells from this range, *other*, and any additional
| cells required to form a rectangular ``CellRange``.
|
| :type other: openpyxl.worksheet.cell_range.CellRange
| :param other: Other sheet range.
| :return: a ``CellRange`` that is a superset of this and *other*.
|
| ----------------------------------------------------------------------
| Data descriptors inherited from openpyxl.worksheet.cell_range.CellRange:
|
| bottom
| A list of cell coordinates that comprise the bottom of the range
|
| bounds
| Vertices of the range as a tuple
|
| cells
|
| cols
| Return cell coordinates as columns
|
| coord
| Excel-style representation of the range
|
| left
| A list of cell coordinates that comprise the left-side of the range
|
| right
| A list of cell coordinates that comprise the right-side of the range
|
| rows
| Return cell coordinates as rows
|
| size
| Return the size of the range as a dictionary of rows and columns.
|
| top
| A list of cell coordinates that comprise the top of the range
|
| ----------------------------------------------------------------------
| Data and other attributes inherited from openpyxl.worksheet.cell_range.CellRange:
|
| __hash__ = None
|
| max_col = Values must be of type <class 'int'>
|
| max_row = Values must be of type <class 'int'>
|
| min_col = Values must be of type <class 'int'>
|
| min_row = Values must be of type <class 'int'>
|
| ----------------------------------------------------------------------
| Methods inherited from openpyxl.descriptors.serialisable.Serialisable:
|
| __add__(self, other)
|
| to_tree(self, tagname=None, idx=None, namespace=None)
|
| ----------------------------------------------------------------------
| Class methods inherited from openpyxl.descriptors.serialisable.Serialisable:
|
| from_tree(node) from openpyxl.descriptors.MetaSerialisable
| Create object from XML
|
| ----------------------------------------------------------------------
| Data descriptors inherited from openpyxl.descriptors.serialisable.Serialisable:
|
| tagname
|
| ----------------------------------------------------------------------
| Data and other attributes inherited from openpyxl.descriptors.serialisable.Serialisable:
|
| idx_base = 0
|
| namespace = None
|
| ----------------------------------------------------------------------
| Data descriptors inherited from openpyxl.descriptors._Serialisable:
|
| __dict__
| dictionary for instance variables (if defined)
|
| __weakref__
| list of weak references to the object (if defined)
Чтобы отыскать подсписок диапазонов текущего счёта в общем списке диапазонов, ты просто перебираешь элементы списка диапазонов и заглядываешь у каждого диапазона в его кортеж bounds. В кортеже bounds есть левый верхний угол, в котором можно взять ряд этого диапазона. Найдя этот первый диапазон, ты начинаешь добавлять этот диапазон и последующие диапазоны в список диапазонов для текущего счёта; и так ты добавляешь, пока тебе не встретится диапазон после конца счёта, который ты так же определяешь через кортеж bounds. Там по номеру ряда будет видно, что ты ниже конца окна. Таким образом ты выберешь из общего списка диапазонов только те диапазоны, которые относятся к текущему счёту. Когда ты их выберешь, будешь дальше с ними работать уже, как указано в
алгоритме. То есть ты эти выбранные диапазоны для текущего счёта будешь так же перебирать и смещать каждый из них через метод shift() наверх, чтобы самый первый диапазон с такого-то ряда сместился на столько-то рядов выше и писался уже в начало листа в новом файле, а второй диапазон с такого-то ряда сместился на столько-то рядов выше и писался уже после первого записанного диапазона от начала листа в новом файле и так далее.