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 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 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 __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