def test_auto_filter(self):
ws = Worksheet(self.wb)
ws.auto_filter.ref = ws.iter_rows('a1:f1')
assert ws.auto_filter.ref == 'A1:F1'
ws.auto_filter.ref = ''
assert ws.auto_filter.ref is None
ws.auto_filter.ref = 'c1:g9'
assert ws.auto_filter.ref == 'C1:G9'
def test_cell_range_name(self):
ws = Worksheet(self.wb)
self.wb.create_named_range('test_range_single', ws, 'B12')
c_range_name = ws.get_named_range('test_range_single')
c_range_coord = tuple(tuple(ws.iter_rows('B12'))[0])
c_cell = ws.cell('B12')
assert c_range_coord == (c_cell, )
assert c_range_name == (c_cell, )
def test_iter_rows(self, row, column, coordinate):
from itertools import islice
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
assert ws.calculate_dimension() == 'A1:C9'
rows = ws.iter_rows()
first_row = tuple(next(islice(rows, row - 1, row)))
assert first_row[column].coordinate == coordinate
def colorNext3Rows(ws: Worksheet, startRow):
color = openpyxl.styles.Color(rgb='f0f0f0')
for index, row in enumerate(
ws.iter_rows(min_row=startRow, max_row=startRow + 2)):
for cell in row:
cell.fill = openpyxl.styles.PatternFill(patternType='solid',
fgColor=color)
def test_cell_range_name(self):
ws = Worksheet(self.wb)
self.wb.create_named_range('test_range_single', ws, 'B12')
c_range_name = ws.get_named_range('test_range_single')
c_range_coord = tuple(tuple(ws.iter_rows('B12'))[0])
c_cell = ws.cell('B12')
assert c_range_coord == (c_cell,)
assert c_range_name == (c_cell,)
def applyBorders(ws: Worksheet):
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
for index, row in enumerate(ws.iter_rows()):
for cell in row:
cell.border = thin_border
def test_iter_rows(self, row, column, coordinate):
from itertools import islice
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
assert ws.calculate_dimension() == 'A1:C9'
rows = ws.iter_rows()
first_row = tuple(next(islice(rows, row-1, row)))
assert first_row[column].coordinate == coordinate
def test_auto_filter(self):
ws = Worksheet(self.wb)
ws.auto_filter.ref = ws.iter_rows('a1:f1')
assert ws.auto_filter.ref == 'A1:F1'
ws.auto_filter.ref = ''
assert ws.auto_filter.ref is None
ws.auto_filter.ref = 'c1:g9'
assert ws.auto_filter.ref == 'C1:G9'
def test_iter_rows_offset(self):
ws = Worksheet(self.wb)
rows = ws.iter_rows('A1:C4', 1, 3)
expected = [
('D2', 'E2', 'F2'),
('D3', 'E3', 'F3'),
('D4', 'E4', 'F4'),
('D5', 'E5', 'F5'),
]
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def test_iter_rows(self, ):
ws = Worksheet(self.wb)
expected = [
('A1', 'B1', 'C1'),
('A2', 'B2', 'C2'),
('A3', 'B3', 'C3'),
('A4', 'B4', 'C4'),
]
rows = ws.iter_rows('A1:C4')
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def test_iter_rows_offset(self):
ws = Worksheet(self.wb)
rows = ws.iter_rows('A1:C4', 1, 3)
expected = [
('D2', 'E2', 'F2' ),
('D3', 'E3', 'F3' ),
('D4', 'E4', 'F4' ),
('D5', 'E5', 'F5' ),
]
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def test_iter_rows(self, ):
ws = Worksheet(self.wb)
expected = [
('A1', 'B1', 'C1' ),
('A2', 'B2', 'C2' ),
('A3', 'B3', 'C3' ),
('A4', 'B4', 'C4' ),
]
rows = ws.iter_rows('A1:C4')
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def test_append_2d_list(self):
ws = Worksheet(self.wb)
ws.append(['This is A1', 'This is B1'])
ws.append(['This is A2', 'This is B2'])
vals = ws.iter_rows('A1:B2')
expected = (
('This is A1', 'This is B1'),
('This is A2', 'This is B2'),
)
for e, v in zip(expected, flatten(vals)):
assert e == tuple(v)
def test_append_2d_list(self):
ws = Worksheet(self.wb)
ws.append(['This is A1', 'This is B1'])
ws.append(['This is A2', 'This is B2'])
vals = ws.iter_rows('A1:B2')
expected = (
('This is A1', 'This is B1'),
('This is A2', 'This is B2'),
)
for e, v in zip(expected, flatten(vals)):
assert e == tuple(v)
def parse_sheet(sheet: Worksheet) -> (list, list):
logging.info('parsing sheet {}'.format(sheet.title))
i = 0
headings = []
rows = []
for row in sheet.iter_rows():
row_data = []
for cell in row:
print(cell)
if i == 0:
headings.append(cell.value)
else:
row_data.append(cell.value)
rows.append(row_data)
return headings, rows
def applyStrategyBorders(ws: Worksheet):
top_thick_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thick'),
bottom=Side(style='thin'))
bottom_thick_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thick'))
for index, row in enumerate(ws.iter_rows()):
if ws['A' + str(index + 1)].value is not None and (
index + 1) >= firstRowOutput:
for cell in row:
cell.border = top_thick_border
rowBottom = ws[index + 2 + 1]
for cell in rowBottom:
cell.border = bottom_thick_border
