def _parse_style_names(self):
"""
Extract style names. There can be duplicates in which case last wins
"""
node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS)
names = {}
for _name in node:
name = _name.get("name")
style = NamedStyle(name=name,
builtinId=_name.get("builtinId"),
hidden=_name.get("hidden"))
style.xfId = int(_name.get("xfId"))
names[name] = style
return names
def _parse_style_names(self):
"""
Extract style names. There can be duplicates in which case last wins
"""
node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS)
names = {}
for _name in safe_iterator(node, '{%s}cellStyle' % SHEET_MAIN_NS):
name = _name.get("name")
style = NamedStyle(name=name,
builtinId=_name.get("builtinId"),
hidden=_name.get("hidden")
)
style.xfId = int(_name.get("xfId"))
names[name] = style
return names
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 _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 parse_named_styles(self):
"""
Extract named styles
"""
ns = []
styles_node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS)
self._parse_xfs(styles_node)
_ids = self.cell_styles
for _name, idx in self._parse_style_names():
_id = _ids[idx]
style = NamedStyle(name=_name)
style.border = self.border_list[_id.border]
style.fill = self.fill_list[_id.fill]
style.font = self.font_list[_id.font]
if _id.alignment:
style.alignment = self.alignments[_id.alignment]
if _id.protection:
style.protection = self.protections[_id.protection]
ns.append(style)
self.named_styles = IndexedList(ns)
def parse_named_styles(self):
"""
Extract named styles
"""
ns = []
styles_node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS)
self._parse_xfs(styles_node)
_ids = self.cell_styles
for _name, idx in self._parse_style_names():
_id = _ids[idx]
style = NamedStyle(name=_name)
style.border = self.border_list[_id.border]
style.fill = self.fill_list[_id.fill]
style.font = self.font_list[_id.font]
if _id.alignment:
style.alignment = self.alignments[_id.alignment]
if _id.protection:
style.protection = self.protections[_id.protection]
ns.append(style)
self.named_styles = IndexedList(ns)
def start(self, tag, attrib):
if tag == 'sheet':
if not self._current_ws:
self._current_ws = self.wb.active
if 'title' in attrib:
self._current_ws.title = attrib['title']
else:
index = int(attrib.get('index')) if 'index' in attrib else None
self._current_ws = self.wb.create_sheet(title=attrib.get(
'title', None),
index=index)
self._row = 0
elif tag == 'columns':
start = column_index_from_string(attrib['start'])
end = column_index_from_string(attrib.get('end', attrib['start']))
for i in range(start, end + 1):
self._current_ws.column_dimensions[get_column_letter(
i)].width = int(attrib.get('width')) / 7.0
elif tag == 'row':
self._row_buf = []
self._col = 0
elif tag == 'cell':
self._cell = WriteOnlyCell(self._current_ws)
for attr, value in iteritems(attrib):
if attr == 'font':
self._cell.font = self._get_font(value)
elif attr == 'fill':
self._cell.fill = self._get_fill(value)
elif attr == 'alignment':
self._cell.alignment = self._get_alignment(value)
elif attr == 'ref-id':
self._refs[value] = CellRef(self, self._row, self._col)
elif attr == 'ref-append':
self._refs[value] = self._refs.get(value, [])
self._refs[value].append(
CellRef(self, self._row, self._col))
elif attr == 'fmt':
self._cell.number_format = value
elif attr == 'rowspan':
self._current_ws.merge_cells(start_row=self._row + 1,
start_column=self._col + 1,
end_row=self._row +
int(value),
end_column=self._col + 1)
elif attr == 'colspan':
self._current_ws.merge_cells(start_row=self._row + 1,
start_column=self._col + 1,
end_row=self._row + 1,
end_column=self._col +
int(value))
ctype = attrib.get('type', 'unicode')
if ctype not in ['unicode', 'number', 'date']:
raise ValueError(u'Unknown cell type {ctype}.'.format(
ctype=ctype, ))
self._cell_type = ctype
try:
self._cell_date_format = attrib.get('date-fmt')
except KeyError:
raise ValueError(u"Specify 'date-fmt' attribute for 'date'"
u" type")
elif tag == 'style':
style = NamedStyle(name=attrib['name'])
if 'font' in attrib:
style.font = self._get_font(attrib['font'])
if 'fill' in attrib:
style.fill = self._get_fill(attrib['fill'])
self.wb.add_named_style(style)
import time
from numpy import random
from pandas import DataFrame
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment
from openpyxl.styles.cell_style import StyleArray
from openpyxl.styles.named_styles import NamedStyle
from openpyxl.utils.dataframe import dataframe_to_rows
ft = Font(bold=True)
al = Alignment(horizontal="center")
side = Side(style="thin", color="000000")
border = Border(left=side, right=side, top=side, bottom=side)
highlight = NamedStyle(name="Pandas Title", font=ft, alignment=al, border=border)
def openpyxl_in_memory(df):
"""
Import a dataframe into openpyxl
"""
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=True, header=True):
ws.append(r)
for c in ws['A'] + ws['1']:
c.style = 'Pandas'
