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 test_read_comments(): xml = """<?xml version="1.0" standalone="yes"?> <comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><authors> <author>Cuke</author><author>Not Cuke</author></authors><commentList><comment ref="A1" authorId="0" shapeId="0"><text><r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t>Cuke:\n</t></r><r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr> <t xml:space="preserve">First Comment</t></r></text></comment><comment ref="D1" authorId="0" shapeId="0"> <text><r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/> </rPr><t>Cuke:\n</t></r><r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/> <charset val="1"/></rPr><t xml:space="preserve">Second Comment</t></r></text></comment> <comment ref="A2" authorId="1" shapeId="0"><text><r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t>Not Cuke:\n</t></r><r><rPr> <sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr> <t xml:space="preserve">Third Comment</t></r></text></comment></commentList></comments>""" wb = Workbook() ws = Worksheet(wb) comments.read_comments(ws, xml) comments_expected = [['A1', 'Cuke', 'Cuke:\nFirst Comment'], ['D1', 'Cuke', 'Cuke:\nSecond Comment'], ['A2', 'Not Cuke', 'Not Cuke:\nThird Comment'] ] for cell, author, text in comments_expected: assert ws.cell(coordinate=cell).comment.author == author assert ws.cell(coordinate=cell).comment.text == text assert ws.cell(coordinate=cell).comment._parent == ws.cell(coordinate=cell)
def test_garbage_collect(self): ws = Worksheet(self.wb) ws.cell('A1').value = '' ws.cell('B2').value = '0' ws.cell('C4').value = 0 ws.garbage_collect() eq_(list(ws.get_cell_collection()), [ws.cell('B2'), ws.cell('C4')])
def convert_intents_to_lex(sheet: Worksheet, intent_name_prefix: str, zip_file): intent_object = {} for i in range(1, sheet.max_row): intent_name_cell = sheet.cell(i + 1, 1) utterance_cell = sheet.cell(i + 1, 2) response_cell = sheet.cell(i + 1, 3) if intent_name_cell.value: if 'metadata' not in intent_object: intent_object = create_new_lex_intent_object( intent_name_cell.value, intent_name_prefix) if utterance_cell.value: intent_object['sampleUtterances'].append(utterance_cell.value) if response_cell.value: message = { "contentType": "PlainText", "content": response_cell.value } intent_object['conclusionStatement']['messages'].append( message) else: if 'metadata' in intent_object: save_intent_to_file(intent_object, zip_file) intent_object = {}
def test_garbage_collect(self): ws = Worksheet(self.wb) ws.cell('A1').value = '' ws.cell('B2').value = '0' ws.cell('C4').value = 0 ws.garbage_collect() eq_(set(ws.get_cell_collection()), set([ws.cell('B2'), ws.cell('C4')]))
def test_garbage_collect(self): ws = Worksheet(self.wb) ws.cell('A1').value = '' ws.cell('B2').value = '0' ws.cell('C4').value = 0 ws.cell('D1').comment = Comment('Comment', 'Comment') ws._garbage_collect() assert set(ws.get_cell_collection()), set([ws.cell('B2'), ws.cell('C4') == ws.cell('D1')])
def test_init(): wb = Workbook() ws = Worksheet(wb) c = Comment("text", "author") ws.cell(coordinate="A1").comment = c assert c._parent == ws.cell(coordinate="A1") assert c.text == "text" assert c.author == "author"
def _create_ws(): wb = Workbook() ws = Worksheet(wb) comment1 = Comment("text", "author") comment2 = Comment("text2", "author2") comment3 = Comment("text3", "author3") ws.cell(coordinate="B2").comment = comment1 ws.cell(coordinate="C7").comment = comment2 ws.cell(coordinate="D9").comment = comment3 return ws, comment1, comment2, comment3
def test_cell_range_name(self): ws = Worksheet(self.wb) self.wb.create_named_range('test_range_single', ws, 'B12') with pytest.raises(CellCoordinatesException): ws.cell('test_range_single') c_range_name = ws.range('test_range_single') c_range_coord = ws.range('B12') c_cell = ws.cell('B12') assert c_range_coord == c_range_name assert c_range_coord == c_cell
def test_freeze(self): ws = Worksheet(self.wb) ws.freeze_panes = ws.cell('b2') assert ws.freeze_panes == 'B2' ws.freeze_panes = '' assert ws.freeze_panes is None ws.freeze_panes = 'c5' assert ws.freeze_panes == 'C5' ws.freeze_panes = ws.cell('A1') assert ws.freeze_panes is None
def test_append_dict_index(self): ws = Worksheet(self.wb) ws.append({0 : 'This is A1', 2 : 'This is C1'}) eq_('This is A1', ws.cell('A1').value) eq_('This is C1', ws.cell('C1').value)
def test_append_list(self): ws = Worksheet(self.wb) ws.append(['This is A1', 'This is B1']) assert 'This is A1' == ws.cell('A1').value assert 'This is B1' == ws.cell('B1').value
def test_append_dict_index(self): ws = Worksheet(self.wb) ws.append({1: 'This is A1', 3: 'This is C1'}) assert 'This is A1' == ws.cell('A1').value assert 'This is C1' == ws.cell('C1').value
def test_append_dict_index(self): ws = Worksheet(self.wb) ws.append({1 : 'This is A1', 3 : 'This is C1'}) assert 'This is A1' == ws.cell('A1').value assert 'This is C1' == ws.cell('C1').value
def test_append_dict_letter(self): ws = Worksheet(self.wb) ws.append({'A': 'This is A1', 'C': 'This is C1'}) eq_('This is A1', ws.cell('A1').value) eq_('This is C1', ws.cell('C1').value)
def extract_matrix(ws: Worksheet, row, col, flnm): rown = row data = [] ran = 0 limit = 50 first_empty_row = 0 while ran < limit: if ran >= limit: if row: limit += 1 col0 = str(ws.cell(row=rown, column=1).value) row = extract_row(ws, rown, col) if "ráosztott költség" in col0: break if not row: if not first_empty_row and ran > 1: first_empty_row = len(data) ran += 1 rown += 1 continue data.append(row) ran += 1 rown += 1 if len(data) == 0: data = [[""] * 2] return data
def test_append_dict_letter(self): ws = Worksheet(self.wb) ws.append({'A' : 'This is A1', 'C' : 'This is C1'}) eq_('This is A1', ws.cell('A1').value) eq_('This is C1', ws.cell('C1').value)
def test_append_dict_index(self): ws = Worksheet(self.wb) ws.append({0: 'This is A1', 2: 'This is C1'}) eq_('This is A1', ws.cell('A1').value) eq_('This is C1', ws.cell('C1').value)
def test_append_list(self): ws = Worksheet(self.wb) ws.append(['This is A1', 'This is B1']) eq_('This is A1', ws.cell('A1').value) eq_('This is B1', ws.cell('B1').value)
def parse_ws(ws: Worksheet, flnm): nilz = 0 data = [] for rown in range(6, 50): row = [ws.cell(row=rown, column=coln).value for coln in range(1, 5)] assert len(row) == 4 if all(c is None for c in row): nilz += 1 if nilz >= 10: # print("Empty sheet @", flnm) break continue usage = str(row[-1]).split(" ")[0].split("/")[0].strip() if not usage.isdigit(): usage = 0 row[-1] = usage row[0] = row[0] if row[0] is None else str(row[0]).strip() if not valid_iid(row[0]) and row[0] is not None: if valid_iid(row[1]): # print("Swapped @", flnm) iid = row[1] row[1] = row[0] row[0] = iid else: print("Uncorrectabe: {} @ {}".format(row[0], flnm)) data.append(list(map(str, row))) return data
def extract_matrix(ws: Worksheet, row, col, flnm): rown = row data = [] ran = 0 first_empty_row = 0 lines_found = 0 while 1: if ran > 50: print("Overrun in {}.\nClipping to {}!".format( flnm, first_empty_row)) return data[:first_empty_row] col0 = str(ws.cell(row=rown, column=1).value) row = extract_row(ws, rown, col) if "szükséges műszer" in col0: break if not row: if rown > 1 and not first_empty_row: first_empty_row = lines_found ran += 1 rown += 1 continue data.append(row) lines_found += 1 ran += 1 rown += 1 if len(data) == 0: data = [[""] * 2] return data
def test_hyperlink_relationships(self): ws = Worksheet(self.wb) assert len(ws.relationships) == 0 ws.cell('A1').hyperlink = "http://test.com" assert len(ws.relationships) == 1 assert "rId1" == ws.cell('A1').hyperlink_rel_id assert "rId1" == ws.relationships[0].id assert "http://test.com" == ws.relationships[0].target assert "External" == ws.relationships[0].target_mode ws.cell('A2').hyperlink = "http://test2.com" assert len(ws.relationships) == 2 assert "rId2" == ws.cell('A2').hyperlink_rel_id assert "rId2" == ws.relationships[1].id assert "http://test2.com" == ws.relationships[1].target assert "External" == ws.relationships[1].target_mode
def test_hyperlink_relationships(self): ws = Worksheet(self.wb) eq_(len(ws.relationships), 0) ws.cell('A1').hyperlink = "http://test.com" eq_(len(ws.relationships), 1) eq_("rId1", ws.cell('A1').hyperlink_rel_id) eq_("rId1", ws.relationships[0].id) eq_("http://test.com", ws.relationships[0].target) eq_("External", ws.relationships[0].target_mode) ws.cell('A2').hyperlink = "http://test2.com" eq_(len(ws.relationships), 2) eq_("rId2", ws.cell('A2').hyperlink_rel_id) eq_("rId2", ws.relationships[1].id) eq_("http://test2.com", ws.relationships[1].target) eq_("External", ws.relationships[1].target_mode)
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 test_cell_range_name(self): ws = Worksheet(self.wb) self.wb.create_named_range('test_range_single', ws, 'B12') assert_raises(CellCoordinatesException, ws.cell, 'test_range_single') c_range_name = ws.range('test_range_single') c_range_coord = ws.range('B12') c_cell = ws.cell('B12') eq_(c_range_coord, c_range_name) eq_(c_range_coord, c_cell)
def _write_data_rows(ws: Worksheet, amendements: Iterable[Amendement]) -> int: nb_rows = 0 for amend in amendements: values = tuple(export_amendement(amend).values()) for column, value in enumerate(values, 1): cell = ws.cell(row=nb_rows + 2, column=column) cell.value = value cell.font = Font(sz=8) nb_rows += 1 return nb_rows
def load_data(worksheet: Worksheet, data: Iterable) -> None: """Loads data into worksheet from iterable of iterables Args: worksheet: a Worksheet object within a Workbook object data: an iterable of iterables """ for i, row in enumerate(data): for j, item in enumerate(row): worksheet.cell(row=i + 1, column=j + 1).value = item
def test_rows(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' rows = ws.rows eq_(len(rows), 9) eq_(rows[0][0].value, 'first') eq_(rows[-1][-1].value, 'last')
def test_comment_count(): wb = Workbook() ws = Worksheet(wb) cell = ws.cell(coordinate="A1") assert ws._comment_count == 0 cell.comment = Comment("text", "author") assert ws._comment_count == 1 cell.comment = Comment("text", "author") assert ws._comment_count == 1 cell.comment = None assert ws._comment_count == 0 cell.comment = None assert ws._comment_count == 0
def test_rows(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' rows = ws.rows assert len(rows) == 9 assert rows[0][0].value == 'first' assert rows[-1][-1].value == 'last'
def test_cols(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' cols = ws.columns assert len(cols) == 3 assert cols[0][0].value == 'first' assert cols[-1][-1].value == 'last'
def test_cols(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' cols = ws.columns eq_(len(cols), 3) eq_(cols[0][0].value, 'first') eq_(cols[-1][-1].value, 'last')
def _write_xlsx_data_rows(ws: Worksheet, amendements: Iterable[Amendement]) -> Counter: counter = Counter({"amendements": 0}) for amend in amendements: amend_dict = { FIELDS[k]: v for k, v in export_amendement_for_spreadsheet(amend).items() } for column, value in enumerate(HEADERS, 1): cell = ws.cell(row=counter["amendements"] + 2, column=column) cell.value = amend_dict[value] cell.font = Font(sz=8) counter["amendements"] += 1 return counter
def test_rows(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' rows = ws.rows assert len(rows) == 9 first_row = rows[0] last_row = rows[-1] assert first_row[0].value == 'first' and first_row[0].coordinate == 'A1' assert last_row[-1].value == 'last'
def test_merge(self): ws = Worksheet(self.wb) string_table = {'':'', 'Cell A1':'Cell A1','Cell B1':'Cell B1'} ws.cell('A1').value = 'Cell A1' ws.cell('B1').value = 'Cell B1' xml_string = write_worksheet(ws, string_table, None) assert '<c r="B1" t="s"><v>Cell B1</v></c>' in xml_string ws.merge_cells('A1:B1') xml_string = write_worksheet(ws, string_table, None) assert '<c r="B1" t="s"><v>Cell B1</v></c>' not in xml_string assert '<mergeCells><mergeCell ref="A1:B1"></mergeCell></mergeCells>' in xml_string ws.unmerge_cells('A1:B1') xml_string = write_worksheet(ws, string_table, None) assert '<mergeCell ref="A1:B1"></mergeCell>' not in xml_string
def test_comment_assignment(): wb = Workbook() ws = Worksheet(wb) c = Comment("text", "author") ws.cell(coordinate="A1").comment = c with pytest.raises(AttributeError): ws.cell(coordinate="A2").commment = c ws.cell(coordinate="A2").comment = Comment("text2", "author2") with pytest.raises(AttributeError): ws.cell(coordinate="A1").comment = ws.cell(coordinate="A2").comment # this should orphan c, so that assigning it to A2 does not raise AttributeError ws.cell(coordinate="A1").comment = None ws.cell(coordinate="A2").comment = c
def test_cols(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' expected = [ ('A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9'), ('B1', 'B2', 'B3', 'B4', 'B5', 'B6', 'B7', 'B8', 'B9'), ('C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7', 'C8', 'C9'), ] cols = ws.columns for col, coord in zip(cols, expected): assert tuple(c.coordinate for c in col) == coord assert len(cols) == 3 assert cols[0][0].value == 'first' assert cols[-1][-1].value == 'last'
def test_get_cell(self): ws = Worksheet(self.wb) cell = ws.cell('A1') eq_(cell.get_coordinate(), 'A1')
def test_cell_alternate_coordinates(self): ws = Worksheet(self.wb) cell = ws.cell(row=8, column=4) eq_('E9', cell.get_coordinate())
def test_cell_insufficient_coordinates(self): ws = Worksheet(self.wb) cell = ws.cell(row=8)
def test_worksheet_dimension(self): ws = Worksheet(self.wb) eq_('A1:A1', ws.calculate_dimension()) ws.cell('B12').value = 'AAA' eq_('A1:B12', ws.calculate_dimension())