def test_range_offset(self): ws = Worksheet(self.wb) xlrange = ws.range('A1:C4', 1, 3) assert isinstance(xlrange, tuple) eq_(4, len(xlrange)) eq_(3, len(xlrange[0])) eq_('D2', xlrange[0][0].get_coordinate())
def __init__(self, parent_workbook, title, workbook_name, sheet_codename, xml_source): Worksheet.__init__(self, parent_workbook, title) self._workbook_name = workbook_name self._sheet_codename = sheet_codename self._xml_source = xml_source
def test_merge_range_string(self): ws = Worksheet(self.wb) ws['A1'] = 1 ws['D4'] = 16 ws.merge_cells(range_string="A1:D4") assert ws._merged_cells == ["A1:D4"] assert 'D4' not in ws._cells
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_range_offset(self): ws = Worksheet(self.wb) xlrange = ws.range('A1:C4', 1, 3) assert isinstance(xlrange, tuple) assert 4 == len(xlrange) assert 3 == len(xlrange[0]) assert 'D2' == xlrange[0][0].get_coordinate()
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_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_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_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_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 test_get_named_range(self): ws = Worksheet(self.wb) self.wb.create_named_range('test_range', ws, 'C5') xlrange = tuple(ws.get_named_range('test_range')) cell = xlrange[0] assert isinstance(cell, Cell) assert cell.row == 5
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_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 __init__(self, parent_workbook, title): Worksheet.__init__(self, parent_workbook, title) self._max_col = 0 self._max_row = 0 self._parent = parent_workbook self._fileobj_name = create_temporary_file()
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 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 __init__(self, parent_workbook, title, worksheet_path, xml_source, shared_strings, style_table): Worksheet.__init__(self, parent_workbook, title) self.worksheet_path = worksheet_path self.shared_strings = shared_strings self.base_date = parent_workbook.excel_base_date dimensions = read_dimension(self.xml_source) if dimensions is not None: self.min_col, self.min_row, self.max_col, self.max_row = dimensions
def test_append_iterator(self): def itty(): for i in range(30): yield i ws = Worksheet(self.wb) gen = itty() ws.append(gen) assert ws['AD1'].value == 29
def test_printer_settings(self): ws = Worksheet(self.wb) ws.set_printer_settings(Worksheet.PAPER_SIZE_LEGAL, Worksheet.ORIENTATION_LANDSCAPE) xml_string = write_worksheet(ws, None, None) assert '<pageSetup paperSize="5" orientation="landscape"></pageSetup>' in xml_string ws = Worksheet(self.wb) xml_string = write_worksheet(ws, None, None) assert "<pageSetup paperSize" not in xml_string
def test_auto_filter(self): ws = Worksheet(self.wb) ws.auto_filter = ws.range('a1:f1') assert ws.auto_filter == 'A1:F1' ws.auto_filter = '' assert ws.auto_filter is None ws.auto_filter = 'c1:g9' assert ws.auto_filter == 'C1:G9'
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_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_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.range('A1:B2') eq_((('This is A1', 'This is B1'), ('This is A2', 'This is B2'),), flatten(vals))
def test_squared_range(self): ws = Worksheet(self.wb) expected = [ ('A1', 'B1', 'C1' ), ('A2', 'B2', 'C2' ), ('A3', 'B3', 'C3' ), ('A4', 'B4', 'C4' ), ] rows = ws.get_squared_range(1, 1, 3, 4) for row, coord in zip(rows, expected): assert tuple(c.coordinate for c in row) == coord
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_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 __init__(self, parent_workbook, title): Worksheet.__init__(self, parent_workbook, title) self._max_col = 0 self._max_row = 0 self._parent = parent_workbook self._fileobj_header_name = create_temporary_file(suffix=".header") self._fileobj_content_name = create_temporary_file(suffix=".content") self._fileobj_name = create_temporary_file() self._string_builder = self._parent.strings_table_builder
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 __init__(self, parent_workbook, title, workbook_name, sheet_codename, xml_source): Worksheet.__init__(self, parent_workbook, title) self._workbook_name = workbook_name self._sheet_codename = sheet_codename self._xml_source = xml_source min_col, min_row, max_col, max_row = read_dimension(xml_source=xml_source) self._max_row = max_row self._max_column = max_col self._dimensions = '%s%s:%s%s' % (min_col, min_row, max_col, max_row)
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_is_not_date_color_format(): wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = -13.5 cell.style.number_format.format_code = '0.00_);[Red]\(0.00\)' eq_(cell.is_date(), False)
def test_printer_settings(self): ws = Worksheet(self.wb) ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID ws.page_setup.fitToPage = True ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 ws.page_setup.horizontalCentered = True ws.page_setup.verticalCentered = True xml_string = write_worksheet(ws, None, None) assert '<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"></pageSetup>' in xml_string assert '<pageSetUpPr fitToPage="1"></pageSetUpPr>' in xml_string assert '<printOptions horizontalCentered="1" verticalCentered="1">' in xml_string ws = Worksheet(self.wb) xml_string = write_worksheet(ws, None, None) assert "<pageSetup" not in xml_string assert "<pageSetUpPr" not in xml_string assert "<printOptions" not in xml_string
def test_page_margins(self): ws = Worksheet(self.wb) ws.page_margins.left = 2.0 ws.page_margins.right = 2.0 ws.page_margins.top = 2.0 ws.page_margins.bottom = 2.0 ws.page_margins.header = 1.5 ws.page_margins.footer = 1.5 xml_string = write_worksheet(ws, None, None) assert '<pageMargins left="2.00" right="2.00" top="2.00" bottom="2.00" header="1.50" footer="1.50"></pageMargins>' in xml_string
def test_is_not_date_color_format(): wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = -13.5 cell.style = cell.style.copy(number_format='0.00_);[Red]\(0.00\)') assert cell.is_date() is False
def _create_ws(): wb = Workbook() ws = Worksheet(wb) comment1 = Comment("text", "author") comment2 = Comment("text2", "author2") comment3 = Comment("text3", "author3") ws["B2"].comment = comment1 ws["C7"].comment = comment2 ws["D9"].comment = comment3 return ws, comment1, comment2, comment3
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 read_worksheet(xml_source, parent, preset_title, string_table, style_table, workbook_name=None, sheet_codename=None): """Read an xml worksheet""" if workbook_name and sheet_codename: ws = IterableWorksheet(parent, preset_title, workbook_name, sheet_codename, xml_source, string_table) else: ws = Worksheet(parent, preset_title) fast_parse(ws, xml_source, string_table, style_table) return ws
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 read_worksheet(xml_source, parent, preset_title, shared_strings, style_table, color_index=None, worksheet_path=None, keep_vba=False): """Read an xml worksheet""" if worksheet_path: ws = IterableWorksheet(parent, preset_title, worksheet_path, xml_source, shared_strings, style_table) else: ws = Worksheet(parent, preset_title) fast_parse(ws, xml_source, shared_strings, style_table, color_index) return ws
def test_read_comments(datadir, cell, author, text): datadir.chdir() with open("comments2.xml") as src: xml = src.read() wb = Workbook() ws = Worksheet(wb) reader.read_comments(ws, xml) comment = ws[cell].comment assert comment.author == author assert comment.text == text
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_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 add_worksheet_table(worksheet: Worksheet, table_name: str, col: int, row: int, start_col: int = ord('A'), start_row: int = 1) -> None: """Adds table formatting to a worksheet Col and Row are the table limits :param worksheet: :param table_name: :param col: :param row: :param start_col: :param start_row: """ tab = Table(displayName=table_name, ref='{}{}:{}{}'.format(column_format(start_col), start_row, column_format(col), row)) worksheet.add_table(tab)
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_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 __init__(self, optimized_write=False): self.worksheets = [] self._active_sheet_index = 0 self._named_ranges = [] self.properties = DocumentProperties() self.style = Style() self.security = DocumentSecurity() self.__optimized_write = optimized_write self.__optimized_read = False self.strings_table_builder = StringTableBuilder() if not optimized_write: self.worksheets.append(Worksheet(self))
def read_worksheet(xml_source, parent, preset_title, string_table, style_table, color_index=None, sheet_codename=None, keep_vba=False): """Read an xml worksheet""" if sheet_codename: from openpyxl.reader.iter_worksheet import IterableWorksheet ws = IterableWorksheet(parent, preset_title, sheet_codename, xml_source, string_table, style_table) else: ws = Worksheet(parent, preset_title) fast_parse(ws, xml_source, string_table, style_table, color_index) if keep_vba: ws.xml_source = xml_source return ws
def test_time(): def check_time(raw_value, coerced_value): cell.value = raw_value eq_(cell.value, coerced_value) eq_(cell.TYPE_NUMERIC, cell.data_type) wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) values = (('03:40:16', time(3, 40, 16)), ('03:40', time(3, 40)),) for raw_value, coerced_value in values: yield check_time, raw_value, coerced_value
def create_sheet(self, index=None, title=None): """Create a worksheet (at an optional index). :param index: optional position at which the sheet will be inserted :type index: int """ if self.__optimized_read: raise ReadOnlyWorkbookException( 'Cannot create new sheet in a read-only workbook') if self.__optimized_write: new_ws = DumpWorksheet(parent_workbook=self, title=title) else: if title: new_ws = Worksheet(parent_workbook=self, title=title) else: new_ws = Worksheet(parent_workbook=self) self.add_sheet(worksheet=new_ws, index=index) return new_ws
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 write_to_worksheet(self, exported_value: Any, work_sheet: Worksheet, starting_row: int, starting_column: int, include_index: bool, include_column_names: bool): """ Exports a given value to Excel worksheet. If the :exported_value is a series or dataframe, then the :starting_row and :starting_column correspond to the top left corner of the container's values in the worksheet. If the :exported_value isn't a series nor dataframe, then the :include_index and :include_column_names parameters should be False. """ if isinstance(exported_value, Series): self._write_series_to_worksheet( exported_value, work_sheet, starting_row, starting_column, include_index, exported_value.name if include_column_names else None) elif isinstance(exported_value, DataFrame): self._write_dataframe_to_worksheet(exported_value, work_sheet, starting_row, starting_column, include_index, include_column_names) else: assert not include_index and not include_column_names work_sheet.cell(row=starting_row, column=starting_column, value=self._to_supported_type(exported_value))
def __init__(self, optimized_write=False, encoding='utf-8'): self.worksheets = [] self._active_sheet_index = 0 self._named_ranges = [] self.properties = DocumentProperties() self.style = Style() self.security = DocumentSecurity() self.__optimized_write = optimized_write self.__optimized_read = False self.__thread_local_data = threading.local() self.strings_table_builder = StringTableBuilder() self.loaded_theme = None self.encoding = encoding if not optimized_write: self.worksheets.append(Worksheet(self))
def print_EF_list(EF_list): from openpyxl.workbook import Workbook from openpyxl.worksheet import Worksheet result_filename = 'EF_print.xlsx' wb = Workbook() #creating a workbook ws = Worksheet(wb, title='EF_list') #creating a sheet inside the workbook ws.freeze_panes = 'A2' header = ['compartment', 'substance', 'subcompartment'] ws.append(header) for EF in EF_list: ws.append(EF) print 'saving in excel sheet named: ' + result_filename wb.add_sheet(ws) wb.save(result_filename)
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
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 '<v>Cell B1</v>' in xml_string ws.merge_cells('A1:B1') xml_string = write_worksheet(ws, string_table, None) assert '<v>Cell B1</v>' not in xml_string assert '<mergeCells count="1"><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"/>' not in xml_string
def create_sheet(self, title=None, index=None): """Create a worksheet (at an optional index). :param title: optional title of the sheet :type tile: unicode :param index: optional position at which the sheet will be inserted :type index: int """ if self.read_only: raise ReadOnlyWorkbookException('Cannot create new sheet in a read-only workbook') if self.write_only : new_ws = WriteOnlyWorksheet(parent=self, title=title) else: new_ws = Worksheet(parent=self, title=title) self._add_sheet(sheet=new_ws, index=index) return new_ws
def write(self, wb: Workbook, ws: Worksheet, dest_row: int): # print("Dest row: " + str(dest_row)) cell = ws.cell(row=int(dest_row), column=int(self.column) + 1, value=self.data) # test_cell = ws['A1'] # print("TEST " + str(test_cell)) if self.style: style_name = "Normal" if str(self.style).find("Date"): self.style = "dd-mmm-yyyy" if str(self.style).find("%"): self.style = "0.0000%" # cell.style = self.style return ws
def walk_column_until(ws: Worksheet, coln: int, strval: str, limit=30, limitstr=""): strval = strval.lower() itr = next( ws.iter_cols(min_col=coln + 1, max_col=coln + 1, min_row=0, max_row=limit)) for i, c in enumerate(itr): cval = str(c.value).lower() if strval in cval: return i + 1 if i >= limit: return None if limitstr: if limitstr in cval: return None