class ChoiceColumn(TableColumn):
list_validation = Typed(name="list_validation",
value=True,
expected_type=bool)
choices = Typed(name="choices", expected_type=Iterable)
to_excel_map = None
from_excel_map = None
def __init__(self,
header=None,
choices=None,
list_validation=None,
**kwargs):
self.choices = tuple(choices) if choices else None
self.list_validation = list_validation
self.to_excel_map = {
internal: excel
for internal, excel in self.choices
}
self.from_excel_map = {
excel: internal
for internal, excel in self.choices
}
# Setup maps before super().__init__() to validation of default value.
super(ChoiceColumn, self).__init__(header=header, **kwargs)
if self.list_validation and not self.data_validation:
self.data_validation = DataValidation(
type="list",
formula1="\"%s\"" %
",".join('%s' % str(excel)
for internal, excel in self.choices))
def to_excel(self, value, row_type=None):
if value not in self.to_excel_map:
if self.default is not None:
value = self.default
if value not in self.to_excel_map:
raise IllegalChoice(FakeCell(value),
tuple(self.to_excel_map.keys()))
return self.to_excel_map[value]
def from_excel(self, cell, value):
if value not in self.from_excel_map:
if self.default is not None:
return self.default
if value not in self.from_excel_map:
raise IllegalChoice(cell, tuple(self.from_excel_map.keys()))
return self.from_excel_map[value]
class RowStyle:
cell_style = Typed("cell_style", expected_types=[str, NamedStyle, ExtendedStyle], allow_none=True)
data_validation = Typed("data_validation", expected_type=DataValidation, allow_none=True)
conditional_formatting = Typed("conditional_formatting", expected_type=Rule, allow_none=True)
def __init__(self, row_type, getter=None, cell_style=None, conditional_formatting=None, data_validation=None):
self.row_type=row_type
self.getter = getter
self.cell_style = cell_style
self.conditional_formatting = conditional_formatting
self.data_validation = data_validation
class BoolColumn(TableColumn):
excel_true = Typed(name="excel_true",
value=True,
expected_types=(str, int, float, bool))
excel_false = Typed(name="excel_false",
value=False,
expected_types=(str, int, float, bool))
list_validation = Typed("list_validation", expected_type=bool, value=True)
strict = Typed("strict", expected_type=bool, value=False)
def __init__(self,
header=None,
excel_true=None,
excel_false=None,
list_validation=None,
strict=None,
**kwargs):
self.excel_true = excel_true
self.excel_false = excel_false
self.list_validation = list_validation
self.strict = strict
super(BoolColumn, self).__init__(header=header, **kwargs)
if self.list_validation and not self.data_validation:
self.data_validation = DataValidation(
type="list",
formula1="\"%s\"" % ",".join(
(str(self.excel_true if self.
excel_true is not True else "TRUE"),
str(self.excel_false if self.
excel_false is not False else "FALSE"))))
def to_excel(self, value, row_type=None):
return self.excel_true if value else self.excel_false
def from_excel(self, cell, value):
if isinstance(value, bool):
return value
if value == self.excel_true:
return True
if value == self.excel_false:
return False
if self.strict:
raise UnableToParseBool(cell)
return bool(value)
class IntColumn(FloatColumn):
round_value = Typed("round_value", expected_type=bool, value=True)
def __init__(self, header=None, round_value=None, **kwargs):
kwargs.setdefault("cell_style", "Row, integer")
kwargs.setdefault("default", 0)
super(IntColumn, self).__init__(header=header, **kwargs)
self.round_value = round_value
def to_excel(self, value, row_type=None):
try:
f = float(value)
i = round(f, 0)
if i != f and not self.round_value:
raise RoundingRequired(FakeCell(value=value))
return int(i)
except (ValueError, TypeError):
raise UnableToParseInt(value=value)
def from_excel(self, cell, value):
try:
f = float(value)
i = round(f, 0)
if i != f and not self.round_value:
raise RoundingRequired(cell=cell)
return int(i)
except (ValueError, TypeError):
raise UnableToParseInt(cell)
class FormulaColumn(TableColumn):
formula = Typed(name="formula", expected_type=str, allow_none=True)
def __init__(self, formula=None, **kwargs):
self.formula = formula
if not self.formula:
raise NoFormula()
super(FormulaColumn, self).__init__(**kwargs)
def get_value_from_object(self, obj, row_type=None):
return self.formula
class CharColumn(TableColumn):
max_length = Typed("max_length", expected_type=int, allow_none=True)
def __init__(self, header=None, max_length=None, **kwargs):
super(CharColumn, self).__init__(header=header, **kwargs)
self.max_length = max_length
def from_excel(self, cell, value):
if value is None:
return None
value = str(value)
if self.max_length is not None and len(value) > self.max_length:
raise StringToLong(cell)
return value
def to_excel(self, value, row_type=None):
if value is None:
return ""
return str(value)
class TableColumn(object):
_column_index = None
# Reading/writing properties
_object_attribute = Typed("_object_attribute", expected_type=str, allow_none=True)
getter = None
default = None # internal value not excel
allow_blank = Typed("allow_blank", expected_type=bool, value=True)
ignore_forced_text = Typed("ignore_forced_text", expected_type=bool, value=True)
# Column rendering properties
_header = Typed("header", expected_type=str, allow_none=True)
width = Typed("width", expected_types=(int, float), value=DEFAULT_COLUMN_WIDTH * 2)
hidden = Typed("hidden", expected_type=bool, value=False)
group = Typed("group", expected_type=bool, value=False)
header_style = Typed("header_style", expected_types=[str, ExtendedStyle], value="Header")
freeze = Typed("freeze", expected_type=bool, value=False)
# Cell rendering properties
cell_style = Typed("cell_style", expected_types=[str, ExtendedStyle], value="Row")
data_validation = Typed("data_validation", expected_type=DataValidation, allow_none=True)
conditional_formatting = Typed("conditional_formatting", expected_type=Rule, allow_none=True)
row_styles = None
BLANK_VALUES = (None, "")
def __init__(self, header=None, object_attribute=None, source=None, width=None, hidden=None, group=None,
data_validation=None, conditional_formatting=None, default=None, allow_blank=None,
ignore_forced_text=None, header_style=None, cell_style=None, freeze=False, getter=None,
row_styles=None):
self._header = header
self.width = width
self.hidden = hidden
self.group = group
self.default = default
# Make sure the default value is valid
if self.default is not None:
self._to_excel(default)
self.allow_blank = allow_blank
self.ignore_forced_text = ignore_forced_text
self._object_attribute = object_attribute
self.source = source
if type(header_style) == ExtendedStyle and header_style.base is None:
header_style.base = self.header_style
self.header_style = header_style
self.getter = getter or self.getter
self.getters = defaultdict(lambda: self.getter)
self.cell_style = cell_style or self.cell_style
self.cell_styles = defaultdict(lambda: self.cell_style)
self.data_validation = data_validation
self.data_validations = defaultdict(lambda: self.data_validation)
self.conditional_formatting = conditional_formatting
self.conditional_formattings = defaultdict(lambda: self.conditional_formatting)
self.add_row_style(*(row_styles or self.row_styles or []))
self.freeze = freeze
def add_row_style(self, *row_styles):
for row_style in row_styles:
row_type = row_style.row_type
if row_style.getter is not None:
self.getters[row_type] = row_style.getter
cell_style = row_style.cell_style
if cell_style is not None:
cell_style = copy(cell_style)
if type(cell_style) == ExtendedStyle and not cell_style.base:
cell_style.base = self.cell_style if type(self.cell_style) == str else self.cell_style.name
self.cell_styles[row_type] = cell_style
if row_style.data_validation is not None:
self.data_validations[row_type] = row_style.data_validation
if row_style.conditional_formatting is not None:
self.conditional_formattings[row_type] = row_style.conditional_formatting
def get_value_from_object(self, obj, row_type=None):
getter = self.getters[row_type]
if getter:
return getter(self, obj)
if isinstance(obj, (list, tuple)):
return obj[self.column_index - 1]
if isinstance(obj, dict):
return obj[self.object_attribute]
return getattr(obj, self.object_attribute, None)
def _to_excel(self, value, row_type=None):
if value in self.BLANK_VALUES:
if self.default is not None:
return self.to_excel(self.default, row_type=row_type)
if self.allow_blank:
return None
raise BlankNotAllowed(WriteOnlyCell())
return self.to_excel(value)
def to_excel(self, value, row_type=None):
return value
def _from_excel(self, cell):
value = cell.value
if self.ignore_forced_text and isinstance(value, str) and value.startswith("'"):
value = value[1:]
if value in self.BLANK_VALUES:
if not self.allow_blank:
raise BlankNotAllowed(cell=cell)
return self.default
return self.from_excel(cell, value)
def from_excel(self, cell, value):
return value
def prepare_worksheet(self, worksheet):
for data_validation in set(self.data_validations.values()):
if data_validation:
worksheet.add_data_validation(data_validation)
def create_header(self, worksheet, style_set):
header = WriteOnlyCell(ws=worksheet, value=self.header)
if self.header_style:
style_set.style_cell(header, self.header_style)
return header
def create_cell(self, worksheet, style_set, value=None, row_type=None):
cell = WriteOnlyCell(
worksheet,
value=self._to_excel(value if value is not None else self.default, row_type=row_type)
)
cell_style = self.cell_styles[row_type]
if cell_style:
style_set.style_cell(cell, cell_style)
return cell
def post_process_cell(self, worksheet, style_set, cell, row_type=None):
data_validation = self.data_validations[row_type]
if data_validation:
data_validation.add(cell)
conditional_formatting = self.conditional_formattings[row_type]
if conditional_formatting:
worksheet.conditional_formatting.add(cell, conditional_formatting)
def post_process_worksheet(self, worksheet, style_set, first_row, last_row, data_range):
column_dimension = worksheet.column_dimensions[self.column_letter]
# Hiding of grouped columns is handled on worksheet level.
if not self.group:
column_dimension.hidden = self.hidden
column_dimension.width = self.width
@property
def header(self):
return self._header or self._object_attribute or "Column%d" % self.column_index
@property
def column_index(self):
if self._column_index is None:
raise ColumnIndexNotSet(self)
return self._column_index
@column_index.setter
def column_index(self, value):
self._column_index = value
@property
def column_letter(self):
return get_column_letter(self.column_index)
@property
def object_attribute(self):
if self._object_attribute is None:
raise ObjectAttributeNotSet(self)
return self._object_attribute
@property
def styles(self):
return tuple({self.header_style, self.cell_style, *tuple(self.cell_styles.values())} - {None})
def __str__(self):
return "%s(%s)" % (self.__class__.__name__, self._header or self._object_attribute or "")
def __repr__(self):
return str(self)
class TemplatedWorkbook(with_metaclass(OrderedType)):
item_class = TemplatedWorksheet
templated_sheets = None
template_styles = Typed("template_styles", expected_type=StyleSet)
timestamp = Typed("timestamp", expected_types=(str, bool), value=False)
_default_timestamp = "%Y%m%d_%H%M%S"
_file_extension = "xlsx"
workbook = Typed("workbook", expected_type=Workbook)
# def __new__(cls, *args, file=None, **kwargs):
# if file:
# return load_workbook(file)
# return super().__new__(cls)
def __init__(self,
file=None,
template_styles=None,
timestamp=None,
templated_sheets=None,
data_only=False):
super(TemplatedWorkbook, self).__init__()
self.workbook = load_workbook(
filename=file, data_only=data_only) if file else Workbook()
self.template_styles = template_styles or DefaultStyleSet()
self.timestamp = timestamp
self.templated_sheets = []
for sheetname, templated_sheet in self._items.items():
self.add_templated_sheet(templated_sheet,
sheetname=sheetname,
add_to_self=False)
for templated_sheet in templated_sheets or []:
self.add_templated_sheet(sheet=templated_sheet,
sheetname=templated_sheet.sheetname,
add_to_self=True)
self._validate()
def _validate(self):
self._check_unique_sheetnames()
self._check_only_one_active()
def _check_unique_sheetnames(self):
if len(
set(templated_sheet.sheetname
for templated_sheet in self.templated_sheets)) < len(
self.templated_sheets):
raise SheetnamesNotUnique(self)
def _check_only_one_active(self):
if len(tuple(sheet
for sheet in self.templated_sheets if sheet.active)) > 1:
raise MultipleActiveSheets(self)
def add_templated_sheet(self, sheet, sheetname=None, add_to_self=True):
if sheetname and not sheet._sheetname:
sheet._sheetname = sheetname
sheet.workbook = self.workbook
sheet.template_styles = self.template_styles
self.templated_sheets.append(sheet)
return sheet
# TODO: Parse sheetname to an attribute? Or removing add to self all together?
# if add_to_self:
# setattr(self, sheet.sheetname, sheet)
def remove_all_sheets(self):
for sheetname in self.workbook.sheetnames:
del self.workbook[sheetname]
def save(self, filename):
if self.timestamp:
filename = self.timestamp_filename(filename)
self.sort_worksheets()
self.workbook.save(filename)
return filename
def save_virtual_workbook(self):
self.sort_worksheets()
return save_virtual_workbook(self.workbook)
def sort_worksheets(self):
order = {}
index = 0
active_index = 0
for templated_sheet in self.templated_sheets:
order[templated_sheet.sheetname] = index
if templated_sheet.active:
active_index = index
index += 1
for sheetname in self.workbook.sheetnames:
if sheetname not in order:
order[sheetname] = index
index += 1
self.workbook._sheets = sorted(self.workbook._sheets,
key=lambda s: order[s.title])
self.workbook.active = active_index
def timestamp_filename(self, filename):
return "%s_%s.%s" % (filename.strip(
".%s" % self._file_extension), datetime.now().strftime(
self.timestamp if isinstance(self.timestamp, str) else self.
_default_timestamp), self._file_extension)
@property
def sheetnames(self):
return self.workbook.sheetnames
def create_sheet(self, title=None, index=None):
return self.workbook.create_sheet(title, index)
class TableColumn(object):
_object_attribute = Typed("_object_attribute",
expected_type=str,
allow_none=True)
source = Typed("source",
expected_types=(str, FunctionType),
allow_none=True)
_column_index = None
# Rendering properties
_header = Typed("header", expected_type=str, allow_none=True)
width = Typed("width",
expected_types=(int, float),
value=DEFAULT_COLUMN_WIDTH * 2)
hidden = Typed("hidden", expected_type=bool, value=False)
group = Typed("group", expected_type=bool, value=False)
data_validation = Typed("data_validation",
expected_type=DataValidation,
allow_none=True)
conditional_formatting = Typed("conditional_formatting",
expected_type=Rule,
allow_none=True)
# Reading/writing properties
default = None # internal value not excel
allow_blank = Typed("allow_blank", expected_type=bool, value=True)
ignore_forced_text = Typed("ignore_forced_text",
expected_type=bool,
value=True)
header_style = Typed("header_style", expected_type=str, value="Header")
row_style = Typed("row_style", expected_type=str, value="Row")
freeze = Typed("freeze", expected_type=bool, value=False)
BLANK_VALUES = (None, "")
def __init__(self,
header=None,
object_attribute=None,
source=None,
width=None,
hidden=None,
group=None,
data_validation=None,
conditional_formatting=None,
default=None,
allow_blank=None,
ignore_forced_text=None,
header_style=None,
row_style=None,
freeze=False):
self._header = header
self.width = width
self.hidden = hidden
self.group = group
self.data_validation = data_validation
self.conditional_formatting = conditional_formatting
self.default = default
# Make sure the default value is valid
if self.default is not None:
self._to_excel(default)
self.allow_blank = allow_blank
self.ignore_forced_text = ignore_forced_text
self._object_attribute = object_attribute
self.source = source
self.header_style = header_style
self.row_style = row_style
self.freeze = freeze
def get_value_from_object(self, obj):
if isinstance(obj, (list, tuple)):
return obj[self.column_index - 1]
if isinstance(obj, dict):
return obj[self.object_attribute]
return getattr(obj, self.object_attribute, None)
def _to_excel(self, value):
if value in self.BLANK_VALUES:
if self.default is not None:
return self.to_excel(self.default)
if self.allow_blank:
return None
raise BlankNotAllowed(WriteOnlyCell())
return self.to_excel(value)
def to_excel(self, value):
return value
def _from_excel(self, cell):
value = cell.value
if self.ignore_forced_text and isinstance(
value, str) and value.startswith("'"):
value = value[1:]
if value in self.BLANK_VALUES:
if not self.allow_blank:
raise BlankNotAllowed(cell=cell)
return self.default
return self.from_excel(cell, value)
def from_excel(self, cell, value):
return value
def prepare_worksheet(self, worksheet):
if self.data_validation:
worksheet.add_data_validation(self.data_validation)
def create_header(self, worksheet):
header = WriteOnlyCell(ws=worksheet, value=self.header)
if self.header_style:
header.style = self.header_style
return header
def create_cell(self, worksheet, value=None):
cell = WriteOnlyCell(
worksheet,
value=self._to_excel(value if value is not None else self.default))
if self.row_style:
cell.style = self.row_style
return cell
def post_process_cell(self, worksheet, cell):
pass
def post_process_worksheet(self, worksheet, first_row, last_row,
data_range):
column_dimension = worksheet.column_dimensions[self.column_letter]
# Hiding of grouped columns is handled on worksheet level.
if not self.group:
column_dimension.hidden = self.hidden
column_dimension.width = self.width
if self.data_validation:
self.data_validation.ranges.append(data_range)
if self.conditional_formatting:
worksheet.conditional_formatting.add(data_range,
self.conditional_formatting)
@property
def header(self):
return self._header or self._object_attribute or "Column%d" % self.column_index
@property
def column_index(self):
if self._column_index is None:
raise ColumnIndexNotSet(self)
return self._column_index
@column_index.setter
def column_index(self, value):
self._column_index = value
@property
def column_letter(self):
return get_column_letter(self.column_index)
@property
def object_attribute(self):
if self._object_attribute is None:
raise ObjectAttributeNotSet(self)
return self._object_attribute
def __str__(self):
return "%s(%s)" % (self.__class__.__name__, self._header
or self._object_attribute or "")
def __repr__(self):
return str(self)
class TableSheet(TemplatedWorksheet):
item_class = TableColumn
_table_name = Typed("table_name", expected_type=str, allow_none=True)
title_style = Typed("title_style", expected_type=str, value="Title")
description_style = Typed("description_style",
expected_type=str,
value="Description")
format_as_table = Typed("format_as_header", expected_type=bool, value=True)
freeze_header = Typed("freeze_header", expected_type=bool, value=True)
hide_excess_columns = Typed("hide_excess_columns",
expected_type=bool,
value=True)
look_for_headers = Typed("look_for_headers",
expected_type=bool,
value=True)
exception_policy = Typed(
"exception_policy",
expected_type=TableSheetExceptionPolicy,
value=TableSheetExceptionPolicy.RaiseCellException)
_first_data_cell = None
_last_data_cell = None
_first_header_cell = None
_last_header_cell = None
_row_class = None
_column_index = 1
def __init__(self,
sheetname=None,
active=None,
table_name=None,
title_style=None,
description_style=None,
format_as_table=None,
freeze_header=None,
hide_excess_columns=None,
look_for_headers=None,
exception_policy=None,
columns=None):
super(TableSheet, self).__init__(sheetname=sheetname, active=active)
self._table_name = table_name
self.title_style = title_style
self.description_style = description_style
self.format_as_table = format_as_table
self.freeze_header = freeze_header
self.hide_excess_columns = hide_excess_columns
self.look_for_headers = look_for_headers
self.exception_policy = exception_policy
self.columns = []
for object_attribute, column in self._items.items():
self.add_column(column, object_attribute=object_attribute)
for column in columns or []:
self.add_column(column)
self._validate()
def _validate(self):
self._check_atleast_one_column()
self._check_unique_column_headers()
self._check_max_one_frozen_column()
self._check_last_column_not_hidden_or_grouped_if_hide_excess_columns()
def _check_atleast_one_column(self):
if not self.columns:
raise NoTableColumns(self)
def _check_unique_column_headers(self):
if len(set(column.header
for column in self.columns)) < len(self.columns):
raise ColumnHeadersNotUnique(self.columns)
def _check_max_one_frozen_column(self):
frozen_columns = tuple(column for column in self.columns
if column.freeze)
if len(frozen_columns) > 1:
raise MultipleFrozenColumns(self, frozen_columns)
def _check_last_column_not_hidden_or_grouped_if_hide_excess_columns(self):
if self.hide_excess_columns:
last_column = self.columns[-1]
if last_column.hidden or last_column.group:
raise CannotHideOrGroupLastColumn()
def add_column(
self,
column,
object_attribute=None,
):
column.column_index = self._column_index
self._column_index += 1
if object_attribute and not column._object_attribute:
column._object_attribute = object_attribute
self.columns.append(column)
self._row_class = None
return column
def write(self,
objects=None,
title=None,
description=None,
preserve=False):
if not self.empty:
if preserve:
objects = chain(list(self.read()), objects)
self.remove()
worksheet = self.worksheet
self.prepare_worksheet(worksheet)
self.write_title(worksheet, title)
self.write_description(worksheet, description)
self.write_headers(worksheet)
self.write_rows(worksheet, objects)
self.post_process_worksheet(worksheet)
def prepare_worksheet(self, worksheet):
for column in self.columns:
column.prepare_worksheet(worksheet)
# Register styles
style_names = set(
chain((self.title_style, self.description_style),
*((column.row_style, column.header_style)
for column in self.columns)))
existing_names = set(self.workbook.named_styles)
for name in style_names:
if name in existing_names:
continue
if name not in self.template_styles:
raise TempleteStyleNotFound(name, self.template_styles)
self.workbook.add_named_style(self.template_styles[name])
def write_title(self, worksheet, title=None):
if not title:
return
title = WriteOnlyCell(ws=worksheet, value=title)
title.style = self.title_style
worksheet.append((title, ))
worksheet.merge_cells(start_row=title.row,
start_column=title.col_idx,
end_row=title.row,
end_column=title.col_idx + len(self.columns) - 1)
def write_description(self, worksheet, description=None):
if not description:
return
description = WriteOnlyCell(ws=worksheet, value=description)
description.style = self.description_style
worksheet.append((description, ))
worksheet.merge_cells(start_row=description.row,
start_column=description.col_idx,
end_row=description.row,
end_column=description.col_idx +
len(self.columns) - 1)
def write_headers(self, worksheet):
headers = tuple(
column.create_header(worksheet) for column in self.columns)
self.worksheet.append(headers)
self._first_header_cell = headers[0]
self._last_header_cell = headers[-1]
def write_rows(self, worksheet, objects=None):
self._first_data_cell = None
cells = None
for obj in objects:
cells = tuple(
column.create_cell(worksheet, column.get_value_from_object(
obj)) for column in self.columns)
worksheet.append(cells)
if not self._first_data_cell:
self._first_data_cell = cells[0]
for cell, column in zip(cells, self.columns):
column.post_process_cell(worksheet, cell)
if cells:
self._last_data_cell = cells[-1]
def post_process_worksheet(self, worksheet):
first_row = (self._first_data_cell or self._first_header_cell).row
last_row = (self._last_data_cell or self._first_header_cell).row
for column in self.columns:
column_letter = column.column_letter
column.post_process_worksheet(
worksheet,
first_row=first_row,
last_row=last_row,
data_range="%s%s:%s%s" %
(column_letter, first_row, column_letter, last_row))
if self.format_as_table:
worksheet.add_table(
Table(
ref="%s:%s" %
(self._first_header_cell.coordinate,
self._last_data_cell.coordinate if self._last_data_cell
else self._last_header_cell.coordinate),
displayName=self.table_name,
))
# Freeze pane
if self.freeze_header:
row = (self._first_data_cell or self._first_header_cell).row
else:
row = 1
try:
column = next(column.column_index for column in self.columns
if column.freeze)
except StopIteration:
column = 0
if row + column > 1:
worksheet.freeze_panes = worksheet["%s%s" %
(get_column_letter(column + 1),
row)]
# Grouping
groups = groupby(self.columns, lambda col: col.group)
for columns in (list(columns) for group, columns in groups if group):
worksheet.column_dimensions.group(start=columns[0].column_letter,
end=columns[-1].column_letter,
outline_level=1,
hidden=columns[0].hidden)
if self.hide_excess_columns:
worksheet.column_dimensions.group(
start=get_column_letter(len(self.columns) + 1),
end=get_column_letter(MAX_COLUMN_INDEX + 1),
outline_level=0,
hidden=True)
def read(self, exception_policy=None, look_for_headers=None):
header_found = not (look_for_headers if look_for_headers is not None
else self.look_for_headers)
_exception_policy = exception_policy if exception_policy is not None else self.exception_policy
rows = self.worksheet.__iter__()
row_number = 0
try:
while not header_found:
row_number += 1
header_found = self._is_row_header(next(rows))
row_exceptions = []
while True:
row_number += 1
try:
yield self.object_from_row(
next(rows),
row_number,
exception_policy=_exception_policy)
except CellExceptions as e:
if _exception_policy.value <= TableSheetExceptionPolicy.RaiseRowException.value:
raise e
else:
row_exceptions.append(e)
except IgnoreRow:
continue
if row_exceptions and _exception_policy.value <= TableSheetExceptionPolicy.RaiseSheetException.value:
raise RowExceptions(row_exceptions)
except StopIteration:
pass
if not header_found:
raise HeadersNotFound(self)
def _is_row_header(self, row):
for cell, header in zip(chain(row, repeat(None)), self.headers):
if str(cell.value) != header:
return False
return True
def object_from_row(
self,
row,
row_number,
exception_policy=TableSheetExceptionPolicy.RaiseCellException):
data = OrderedDict()
cell_exceptions = []
for cell, column in zip(chain(row, repeat(None)), self.columns):
try:
data[column.object_attribute] = column._from_excel(cell)
except CellException as e:
if exception_policy.value <= TableSheetExceptionPolicy.RaiseCellException.value:
raise e
else:
cell_exceptions.append(e)
if cell_exceptions:
raise CellExceptions(cell_exceptions)
# return self.row_class(**data)
return self.create_object(row_number, **data)
def create_object(self, row_number, **data):
return self.row_class(**data)
@property
def table_name(self):
if not self._table_name:
table_name = self.sheetname
# Remove invalid characters
table_name = re.sub('[^0-9a-zA-Z_]', '', table_name)
# Remove leading characters until we find a letter or underscore
self._table_name = re.sub('^[^a-zA-Z_]+', '', table_name)
return self._table_name
@property
def headers(self):
return (column.header for column in self.columns)
@property
def row_class(self):
if not self._row_class:
self._row_class = namedtuple("%sRow" % self.__class__.__name__,
(column.object_attribute
for column in self.columns))
return self._row_class
def __iter__(self):
return self.read()
class TemplatedWorksheet(metaclass=OrderedType):
_sheetname = Typed("_sheetname", expected_type=str, allow_none=True)
active = Typed("active", expected_type=bool, value=False)
_workbook = None
template_styles = None
# order = ... # TODO: Add ordering to sheets either through declaration on workbook or here
def __init__(self, sheetname=None, active=None):
self._sheetname = sheetname or self._sheetname
self.active = active if active is not None else self.active
@property
def exists(self):
return self.sheetname in self.workbook
@property
def empty(self):
if not self.exists:
return True
return not bool(len(self.worksheet._cells))
@property
def worksheet(self):
if not self.exists:
self.workbook.create_sheet(self.sheetname)
return self.workbook[self.sheetname]
@property
def workbook(self):
if not self._workbook:
raise TemplatedWorkbookNotSet(self)
return self._workbook
@workbook.setter
def workbook(self, workbook):
self._workbook = workbook
@property
def sheet_index(self):
try:
return self.workbook.sheetnames.index(self.sheetname)
except ValueError:
raise WorksheetDoesNotExist(self)
def write(self, data):
raise NotImplemented()
# 'self.sheet_template.write(self.worksheet, self.templated_workbook.styles, data)
def read(self):
raise NotImplemented()
def remove(self):
if self.exists:
del self.workbook[self.sheetname]
# def activate(self):
# self.workbook.active = self.sheet_index
@property
def sheetname(self):
if not self._sheetname:
raise SheetnameNotSet()
return self._sheetname
@sheetname.setter
def sheetname(self, value):
self._sheetname = value
def __str__(self):
return self._sheetname or self.__class__.__name__
def __repr__(self):
return str(self)