def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
from openpyxl.styles.named_styles import NamedStyleList
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=DEFAULT_FONT, builtinId=0))
def _setup_styles(self):
"""Bootstrap styles"""
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._date_formats = {}
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), builtinId=0))
self._table_styles = TableStyleList()
self._differential_styles = DifferentialStyleList()
def write(self,
filename,
data,
sheet=None,
sheet_index=0,
has_title=False,
is_overwrite=False):
'''Write data to excel file.
Arguments:
file {str} -- The absolute name of excel file
data {list} -- The data list which will write to excel file.
Keyword Arguments:
sheet {str} -- The name of destnation sheet.
sheet_index {int} -- The index of sheet, start with 0.
has_title {bool} -- True means the first row as table header (default: {False})
is_overwrite {bool} -- When the destnation file exist, it will be overwrite
when this flag is true (default: {False}).
'''
if not is_overwrite and os.path.isfile(filename):
logger.error(
'The destination file {} is already exist, abort!'.format(
filename))
sys.exit(1)
if os.path.isfile(filename):
wb = load_workbook(filename)
sheets = wb.get_sheet_names()
if sheet and sheet in sheets:
ws = wb.get_sheet_by_name(sheet)
wb.remove_sheet(ws)
ws = wb.create_sheet(sheet, index=sheet_index)
if not sheet:
ws = wb.active
else:
wb = Workbook()
if sheet:
ws = wb.create_sheet(sheet, index=sheet_index)
else:
ws = wb.active
ws.print_options.horizontalCentered = True
ws.print_options.verticalCentered = True
wb._named_styles = NamedStyleList()
max_width = {}
for row_id, row in enumerate(data, start=1):
for column_id, column in enumerate(row, start=1):
# for column width
last_width = max_width[
column_id] if column_id in max_width else 0
# calcalated the column
unicode_column = column if isinstance(
column, unicode) else str(column).decode('utf-8')
chinese_chars = chinese_counter(unicode_column)
curr_width = len(unicode_column) + chinese_chars + 2
# update the biggest column width
if curr_width > last_width:
chinese_chars = chinese_counter(unicode_column)
# chinese has 2 characters
max_width[column_id] = curr_width
# for table header
if has_title and row_id == 1:
ws.cell(row=1, column=column_id,
value=column).style = self.__header_style
else:
# write data
cell = ws.cell(row=row_id, column=column_id, value=column)
cell.border = self.__border
cell.alignment = self.__alignment
# set column width
for k, v in max_width.items():
ws.column_dimensions[get_column_letter(k)].width = v
# save file
wb.save(filename)
