import abc
import copy
import warnings
from typing import IO, TYPE_CHECKING, Any, ClassVar, Dict, Optional, Union, cast
import dataproperty
import typepy
from dataproperty import DataProperty
from tabledata import TableData
from typepy import Integer
from .._common import import_error_msg_template
from ._excel_workbook import ExcelWorkbookInterface, ExcelWorkbookXls, ExcelWorkbookXlsx
from ._interface import AbstractBinaryTableWriter
if TYPE_CHECKING:
from xlwt import XFStyle
class ExcelTableWriter(AbstractBinaryTableWriter, metaclass=abc.ABCMeta):
"""
An abstract class of a table writer for Excel file format.
"""
FORMAT_NAME = "excel"
@property
def format_name(self) -> str:
return self.FORMAT_NAME
@property
def workbook(self) -> Optional[ExcelWorkbookInterface]:
return self._workbook
def __init__(self, **kwargs: Any) -> None:
super().__init__(**kwargs)
self._workbook: Optional[ExcelWorkbookInterface] = None
self._dp_extractor.type_value_map = {
typepy.Typecode.INFINITY: "Inf",
typepy.Typecode.NAN: "NaN",
}
self._first_header_row = 0
self._last_header_row = self.first_header_row
self._first_data_row = self.last_header_row + 1
self._first_data_col = 0
self._last_data_row: Optional[int] = None
self._last_data_col: Optional[int] = None
self._current_data_row = self._first_data_row
self._quoting_flags = copy.deepcopy(dataproperty.NOT_QUOTING_FLAGS)
self._quoting_flags[typepy.Typecode.DATETIME] = True
@property
def first_header_row(self) -> int:
"""int: Index of the first row of the header.
.. note:: |excel_attr|
"""
return self._first_header_row
@property
def last_header_row(self) -> int:
"""int: Index of the last row of the header.
.. note:: |excel_attr|
"""
return self._last_header_row
@property
def first_data_row(self) -> int:
"""int: Index of the first row of the data (table body).
.. note:: |excel_attr|
"""
return self._first_data_row
@property
def last_data_row(self) -> Optional[int]:
"""int: Index of the last row of the data (table body).
.. note:: |excel_attr|
"""
return self._last_data_row
@property
def first_data_col(self) -> int:
"""int: Index of the first column of the table.
.. note:: |excel_attr|
"""
return self._first_data_col
@property
def last_data_col(self) -> Optional[int]:
"""int: Index of the last column of the table.
.. note:: |excel_attr|
"""
return self._last_data_col
def is_opened(self) -> bool:
return self.workbook is not None
def open(self, file_path: str) -> None:
"""
Open an Excel workbook file.
:param str file_path: Excel workbook file path to open.
"""
if self.workbook and self.workbook.file_path == file_path:
self._logger.logger.debug(f"workbook already opened: {self.workbook.file_path}")
return
self.close()
self._open(file_path)
@abc.abstractmethod
def _open(self, workbook_path: str) -> None: # pragma: no cover
pass
def close(self) -> None:
"""
Close the current workbook.
"""
if self.is_opened():
self.workbook.close() # type: ignore
self._workbook = None
def from_tabledata(self, value: TableData, is_overwrite_table_name: bool = True) -> None:
"""
Set following attributes from |TableData|
- :py:attr:`~.table_name`.
- :py:attr:`~.headers`.
- :py:attr:`~.value_matrix`.
And create worksheet named from :py:attr:`~.table_name` ABC
if not existed yet.
:param tabledata.TableData value: Input table data.
"""
super().from_tabledata(value)
if self.is_opened():
self.make_worksheet(self.table_name)
def make_worksheet(self, sheet_name: Optional[str] = None) -> None:
"""Make a worksheet to the current workbook.
Args:
sheet_name (str):
Name of the worksheet to create. The name will be automatically generated
(like ``"Sheet1"``) if the ``sheet_name`` is empty.
"""
if sheet_name is None:
sheet_name = self.table_name
if not sheet_name:
sheet_name = ""
self._stream = self.workbook.add_worksheet(sheet_name) # type: ignore
self._current_data_row = self._first_data_row
def dump(self, output: Union[str, IO], close_after_write: bool = True, **kwargs: Any) -> None:
"""Write a worksheet to the current workbook.
Args:
output (str):
Path to the workbook file to write.
close_after_write (bool, optional):
Close the workbook after write.
Defaults to |True|.
"""
if not isinstance(output, str):
raise TypeError(f"output must be a str: actual={type(output)}")
self.open(output)
try:
self.make_worksheet(self.table_name)
self.write_table(**kwargs)
finally:
if close_after_write:
self.close()
@abc.abstractmethod
def _write_header(self) -> None:
pass
@abc.abstractmethod
def _write_cell(self, row: int, col: int, value_dp: DataProperty) -> None:
pass
def _write_table(self, **kwargs: Any) -> None:
self._preprocess_table_dp()
self._preprocess_table_property()
self._write_header()
self._write_value_matrix()
self._postprocess()
def _write_value_matrix(self) -> None:
for value_dp_list in self._table_value_dp_matrix:
for col_idx, value_dp in enumerate(value_dp_list):
self._write_cell(self._current_data_row, col_idx, value_dp)
self._current_data_row += 1
def _get_last_column(self) -> int:
if typepy.is_not_empty_sequence(self.headers):
return len(self.headers) - 1
if typepy.is_not_empty_sequence(self.value_matrix):
return len(self.value_matrix[0]) - 1
raise ValueError("data not found")
def _postprocess(self) -> None:
self._last_data_row = self._current_data_row
self._last_data_col = self._get_last_column()
[docs]
class ExcelXlsTableWriter(ExcelTableWriter):
"""
A table writer class for Excel file format: ``.xls`` (older or equal to Office 2003).
``xlwt`` package required to use this class.
.. py:method:: write_table()
Write a table to the current opened worksheet.
:raises IOError: If failed to write data to the worksheet.
.. note::
Specific values in the tabular data are converted when writing:
- |None|: written as an empty string
- |inf|: written as ``Inf``
- |nan|: written as ``NaN``
"""
def __init__(self, **kwargs: Any) -> None:
super().__init__(**kwargs)
self.__col_style_table: Dict[int, Any] = {}
def _open(self, workbook_path: str) -> None:
self._workbook = ExcelWorkbookXls(workbook_path)
def _write_header(self) -> None:
if not self.is_write_header or typepy.is_empty_sequence(self.headers):
return
for col, value in enumerate(self.headers):
self.stream.write(self.first_header_row, col, value)
def _write_cell(self, row: int, col: int, value_dp: DataProperty) -> None:
if value_dp.typecode in [typepy.Typecode.REAL_NUMBER]:
try:
cell_style = self.__get_cell_style(col)
except ValueError:
pass
else:
self.stream.write(row, col, value_dp.data, cell_style)
return
self.stream.write(row, col, value_dp.data)
def _postprocess(self) -> None:
super()._postprocess()
self.__col_style_table = {}
def __get_cell_style(self, col: int) -> "XFStyle":
try:
import xlwt
except ImportError:
warnings.warn(import_error_msg_template.format("excel"))
raise
if col in self.__col_style_table:
return self.__col_style_table.get(col)
try:
col_dp = self._column_dp_list[col]
except KeyError:
return {}
if col_dp.typecode not in [typepy.Typecode.REAL_NUMBER]:
raise ValueError()
if not Integer(col_dp.minmax_decimal_places.max_value).is_type():
raise ValueError()
float_digit = col_dp.minmax_decimal_places.max_value
if float_digit <= 0:
raise ValueError()
num_format_str = "#,{:s}0.{:s}".format("#" * int(float_digit), "0" * int(float_digit))
cell_style = xlwt.easyxf(num_format_str=num_format_str)
self.__col_style_table[col] = cell_style
return cell_style
[docs]
class ExcelXlsxTableWriter(ExcelTableWriter):
"""
A table writer class for Excel file format: ``.xlsx`` (newer or equal to Office 2007).
.. py:method:: write_table()
Write a table to the current opened worksheet.
:raises IOError: If failed to write data to the worksheet.
Examples:
:ref:`example-excel-table-writer`
.. note::
Specific values in the tabular data are converted when writing:
- |None|: written as an empty string
- |inf|: written as ``Inf``
- |nan|: written as ``NaN``
"""
MAX_CELL_WIDTH: ClassVar[int] = 60
class TableFormat:
HEADER = "header"
CELL = "cell"
NAN = "nan"
class Default:
FONT_NAME: ClassVar[str] = "MS Gothic"
FONT_SIZE: ClassVar[int] = 9
CELL_FORMAT: Dict[str, Union[int, str, bool]] = {
"font_name": FONT_NAME,
"font_size": FONT_SIZE,
"align": "top",
"text_wrap": True,
"top": 1,
"left": 1,
"bottom": 1,
"right": 1,
}
HEADER_FORMAT: Dict[str, Union[int, str, bool]] = {
"font_name": FONT_NAME,
"font_size": FONT_SIZE,
"bg_color": "#DFDFFF",
"bold": True,
"left": 1,
"right": 1,
}
NAN_FORMAT: Dict[str, Union[int, str, bool]] = {
"font_name": FONT_NAME,
"font_size": FONT_SIZE,
"font_color": "silver",
"top": 1,
"left": 1,
"bottom": 1,
"right": 1,
}
@property
def __nan_format_property(self) -> Dict[str, Union[int, str, bool]]:
return self.format_table.get(self.TableFormat.NAN, self.default_format)
@property
def __cell_format_property(self) -> Dict[str, Union[int, str, bool]]:
return self.format_table.get(self.TableFormat.CELL, self.default_format)
def __init__(self, **kwargs: Any) -> None:
super().__init__(**kwargs)
self.default_format = self.Default.CELL_FORMAT
self.format_table = {
self.TableFormat.CELL: self.Default.CELL_FORMAT,
self.TableFormat.HEADER: self.Default.HEADER_FORMAT,
self.TableFormat.NAN: self.Default.NAN_FORMAT,
}
self.__col_cell_format_cache: Dict[int, Any] = {}
self.__col_numprops_table: Dict[int, Dict[str, str]] = {}
def _open(self, workbook_path: str) -> None:
self._workbook = ExcelWorkbookXlsx(workbook_path)
def _write_header(self) -> None:
if not self.is_write_header or typepy.is_empty_sequence(self.headers):
return
header_format_props = self.format_table.get(self.TableFormat.HEADER, self.default_format)
header_format = self.__add_format(header_format_props)
self.stream.write_row(
row=self.first_header_row, col=0, data=self.headers, cell_format=header_format
)
for row in range(self.first_header_row, self.last_header_row):
self.stream.write_row(
row=row, col=0, data=[""] * len(self.headers), cell_format=header_format
)
def _write_cell(self, row: int, col: int, value_dp: DataProperty) -> None:
base_props = dict(self.__cell_format_property)
format_key = f"{col:d}_{value_dp.typecode.name:s}"
if value_dp.typecode in [typepy.Typecode.INTEGER, typepy.Typecode.REAL_NUMBER]:
num_props = self.__get_number_property(col)
base_props.update(num_props)
cell_format = self.__get_cell_format(format_key, base_props)
try:
self.stream.write_number(row, col, float(value_dp.data), cell_format)
return
except TypeError:
pass
if value_dp.typecode is typepy.Typecode.NAN:
base_props = dict(self.__nan_format_property)
cell_format = self.__get_cell_format(format_key, base_props)
self.stream.write(row, col, value_dp.data, cell_format)
def __get_number_property(self, col: int) -> Dict[str, str]:
if col in self.__col_numprops_table:
return self.__col_numprops_table[col]
try:
col_dp = self._column_dp_list[col]
except KeyError:
return {}
if col_dp.typecode not in [typepy.Typecode.INTEGER, typepy.Typecode.REAL_NUMBER]:
return {}
num_props = {}
if Integer(col_dp.minmax_decimal_places.max_value).is_type():
float_digit = col_dp.minmax_decimal_places.max_value
if float_digit > 0:
num_props = {"num_format": "0.{:s}".format("0" * int(float_digit))}
self.__col_numprops_table[col] = num_props
return num_props
def __get_cell_format(self, format_key, cell_props) -> Dict: # type: ignore
cell_format = self.__col_cell_format_cache.get(format_key)
if cell_format is not None:
return cell_format
# cache miss
cell_format = self.__add_format(cell_props)
self.__col_cell_format_cache[format_key] = cell_format
return cell_format
def __add_format(self, dict_property): # type: ignore
assert self.workbook
return self.workbook.workbook.add_format(dict_property)
def __set_cell_width(self) -> None:
font_size = cast(int, self.__cell_format_property.get("font_size"))
if not Integer(font_size).is_type():
return
for col_idx, col_dp in enumerate(self._column_dp_list):
width = min(col_dp.ascii_char_width, self.MAX_CELL_WIDTH) * (font_size / 10.0) + 2
self.stream.set_column(col_idx, col_idx, width=width)
def _preprocess_table_property(self) -> None:
super()._preprocess_table_property()
self.__set_cell_width()
def _postprocess(self) -> None:
super()._postprocess()
self.stream.autofilter(
self.last_header_row, self.first_data_col, self.last_data_row, self.last_data_col
)
self.stream.freeze_panes(self.first_data_row, self.first_data_col)
self.__col_cell_format_cache = {}
self.__col_numprops_table = {}