def _cells_by_col(self, min_col, min_row, max_col, max_row):
"""
Get cells by column
"""
for column in range(min_col, max_col + 1):
yield tuple(
self.cell(row=row, column=column)
for row in range(min_row, max_row + 1))
def _clean_merge_range(self, cr):
"""
Remove all but the top left-cell from a range of merged cells
"""
min_col, min_row, max_col, max_row = cr.bounds
rows = range(min_row, max_row + 1)
cols = range(min_col, max_col + 1)
cells = product(rows, cols)
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
def _get_row(self, element, min_col=1, max_col=None, row_counter=None):
"""Return cells from a particular row"""
col_counter = min_col
data_only = getattr(self.parent, 'data_only', False)
for cell in safe_iterator(element, CELL_TAG):
coordinate = cell.get('r')
if coordinate:
row, column = coordinate_to_tuple(coordinate)
else:
row, column = row_counter, col_counter
if max_col is not None and column > max_col:
break
if min_col <= column:
if col_counter < column:
for col_counter in range(max(col_counter, min_col),
column):
# pad row with missing cells
yield EMPTY_CELL
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
value = None
formula = cell.findtext(FORMULA_TAG)
if formula is not None and not data_only:
data_type = 'f'
value = "=%s" % formula
elif data_type == 'inlineStr':
child = cell.find(INLINE_TAG)
if child is not None:
richtext = Text.from_tree(child)
value = richtext.content
else:
value = cell.findtext(VALUE_TAG) or None
yield ReadOnlyCell(self, row, column, value, data_type,
style_id)
col_counter = column + 1
if max_col is not None:
for _ in range(max(min_col, col_counter), max_col + 1):
yield EMPTY_CELL
def test_read_fast_integrated_numbers_2(self, sample_workbook):
wb = sample_workbook
query_range = 'K1:K30'
expected = expected = [[(x + 1) / 100.0] for x in range(30)]
ws = wb['Sheet2 - Numbers']
for row, expected_row in zip(ws.iter_rows(query_range), expected):
row_values = [x.value for x in row]
assert row_values == expected_row
def test_read_fast_integrated_numbers(self, sample_workbook):
wb = sample_workbook
expected = [[x + 1] for x in range(30)]
query_range = 'D1:D30'
ws = wb['Sheet2 - Numbers']
for row, expected_row in zip(ws.iter_rows(query_range), expected):
row_values = [x.value for x in row]
assert row_values == expected_row
def _gutter(idx, offset, max_val):
"""
When deleting rows and columns are deleted we rely on overwriting.
This may not be the case for a large offset on small set of cells:
range(cells_to_delete) > range(cell_to_be_moved)
"""
gutter = range(max(max_val + 1 - offset, idx),
min(idx + offset, max_val) + 1)
return gutter
def test_large_append(mode):
print("Using write only mode {0}".format(mode))
wb = openpyxl2.Workbook(optimized_write=mode)
ws = wb.create_sheet()
row = ('this is some text', 3.14)
total_rows = int(2e4)
for idx in range(total_rows):
if not idx % 10000:
print("%.2f%%" % (100 * (float(idx) / float(total_rows))))
ws.append(row)
wb.save(tempfile.TemporaryFile(mode='wb'))
def test_illegal_characters(dummy_cell):
from openpyxl2.utils.exceptions import IllegalCharacterError
from openpyxl2.compat import range
from itertools import chain
cell = dummy_cell
# The bytes 0x00 through 0x1F inclusive must be manually escaped in values.
illegal_chrs = chain(range(9), range(11, 13), range(14, 32))
for i in illegal_chrs:
with pytest.raises(IllegalCharacterError):
cell.value = chr(i)
with pytest.raises(IllegalCharacterError):
cell.value = "A {0} B".format(chr(i))
cell.value = chr(33)
cell.value = chr(9) # Tab
cell.value = chr(10) # Newline
cell.value = chr(13) # Carriage return
cell.value = " Leading and trailing spaces are legal "
def delete_cols(self, idx, amount=1):
"""
Delete column or columns from col==idx
"""
remainder = _gutter(idx, amount, self.max_column)
self._move_cells(min_col=idx + amount,
offset=-amount,
row_or_col="col_idx")
for col in remainder:
for row in range(self.min_row, self.max_row + 1):
if (row, col) in self._cells:
del self._cells[row, col]
def _cells_by_row(self, min_col, min_row, max_col, max_row):
"""
The source worksheet file may have columns or rows missing.
Missing cells will be created.
"""
if max_col is not None:
empty_row = tuple(EMPTY_CELL
for column in range(min_col, max_col + 1))
else:
empty_row = []
row_counter = min_row
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row_id = int(element.get("r", row_counter))
# got all the rows we need
if max_row is not None and row_id > max_row:
break
# some rows are missing
for row_counter in range(row_counter, row_id):
row_counter += 1
yield empty_row
# return cells from a row
if min_row <= row_id:
yield tuple(
self._get_row(element,
min_col,
max_col,
row_counter=row_counter))
row_counter += 1
element.clear()
def count_open_fds():
"""Return the number of open file descriptors for this process
The implementation assumes that all FDs are smaller than 10,000 and that
nobody (other threads, garbage collection) modifies the file descriptors
while we are counting.
"""
count = 0
for i in range(10000):
try:
os.fstat(i)
except Exception:
pass
else:
count += 1
return count
def delete_rows(self, idx, amount=1):
"""
Delete row or rows from row==idx
"""
remainder = _gutter(idx, amount, self.max_row)
self._move_cells(min_row=idx + amount,
offset=-amount,
row_or_col="row")
for row in remainder:
for col in range(self.min_column, self.max_column + 1):
if (row, col) in self._cells:
del self._cells[row, col]
self._current_row = self.max_row
if not self._cells:
self._current_row = 0
def _cells_by_row(self, min_col, min_row, max_col, max_row):
for row in range(min_row, max_row + 1):
yield tuple(
self.cell(row=row, column=column)
for column in range(min_col, max_col + 1))