def styled_sheet():
from openpyxl import Workbook
from openpyxl.styles import Font, Style, Fill, Color, colors
wb = Workbook()
ws = wb.active
ws.title = 'Test 1'
red_fill = Fill()
red_fill.fill_type = 'solid'
red_fill.start_color = Color(Color.RED),
red_fill.end_color = Color(Color.RED)
empty_fill = Fill()
styles = []
# pregenerate relevant styles
for row in range(ROWS):
_row = []
for col in range(COLUMNS):
cell = ws.cell(row=row + 1, column=col + 1)
cell.value = 1
font = Font()
fill = Fill()
if formatData[row][col] & BOLD:
font.bold = True
if formatData[row][col] & ITALIC:
font.italic = True
if formatData[row][col] & UNDERLINE:
font.underline = 'single'
if formatData[row][col] & RED_BG:
fill = red_fill
style = Style()
style.font = font
style.fill = fill
ws._styles[cell.address] = style
def __init__(self,
optimized_write=False,
encoding='utf-8',
worksheet_class=Worksheet,
guess_types=False,
data_only=False,
read_only=False,
write_only=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self._external_links = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.__thread_local_data = threading.local()
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.style_properties = None
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.code_name = u'ThisWorkbook'
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
def create_xls_attributions(user, found_learning_units, filters):
titles = learning_unit_titles_part1() + learning_unit_titles_part2() + [str(_('Tutor')),
"{} ({})".format(str(_('Tutor')),
str(_('email'))),
str(_('Function')),
str(_('Substitute')),
str(_('Beg. of attribution')),
str(_('Attribution duration')),
str(_('Attrib. vol1')),
str(_('Attrib. vol2')),
]
xls_data = prepare_xls_content_with_attributions(found_learning_units, len(titles))
working_sheets_data = xls_data.get('data')
cells_with_top_border = xls_data.get('cells_with_top_border')
cells_with_white_font = xls_data.get('cells_with_white_font')
parameters = {xls_build.DESCRIPTION: _('Learning units list with attributions'),
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: WORKSHEET_TITLE,
xls_build.STYLED_CELLS: {xls_build.STYLE_BORDER_TOP: cells_with_top_border,
Style(font=Font(color=Color('00FFFFFF')),): cells_with_white_font},
xls_build.COLORED_ROWS: {Style(font=BOLD_FONT): [0]}
}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def make_excel_headers(self):
cell_bold = {'font': Font(bold=True)}
# Cell Properties
cell_existing = {
'alignment': Alignment(horizontal='center'),
'font': Font(bold=True),
'fill': PatternFill(patternType='solid', fgColor=Color('90909000'))
}
self.ws['E1'] = "Existing Motor"
self.ws['E1'].style = Style(**cell_existing)
# Mergeing Cells
self.ws.merge_cells('E1:K1')
# Cell Properties
cell_proposed = {
'alignment': Alignment(horizontal='center'),
'font': Font(bold=True),
'fill': PatternFill(patternType='solid', fgColor=Color('90909099'))
}
self.ws['L1'] = "Proposed Premium Efficiency Motor"
self.ws['L1'].style = Style(**cell_proposed)
# Mergeing Cells
self.ws.merge_cells('L1:R1')
for each_cell in self.headers_dict:
self.ws[each_cell] = self.headers_dict[each_cell]
self.ws[each_cell].style = Style(**cell_bold)
column = each_cell.replace('2', '')
self.ws.column_dimensions[column].width = len(
self.headers_dict[each_cell])
def Speed_InitFirstSheet(wb,FirstSheetName,ListOfMethods):
sheet = wb.get_sheet_by_name(FirstSheetName)
#Main Table
sheet['A1'].style = Style(font=Font(bold=True))
sheet['A1'] = 'N'
for i in range(len(ListOfMethods)):
i4PlusBchar = GetString('B',i)
sheet['{}1'.format(i4PlusBchar)].style = Style(font=Font(bold=True))
sheet['{}1'.format(i4PlusBchar)] = ListOfMethods[i]
def setCategorySheet(tab):
tab['A1'] = 'Keyword'
tab['A1'].style = Style(font=Font(name='Georgia', bold=True))
tab['B1'] = 'Category'
tab['B1'].style = Style(font=Font(name='Georgia', bold=True))
if tab != cword:
for i in range(2, cword.max_row + 1):
tab['A' + str(i)] = cword['A' + str(i)].value
tab['B' + str(i)] = cword['B' + str(i)].value
return
def test_nb_style(self):
for i in range(1, 6):
self.worksheet.cell(row=1,
column=i).style = Style(font=Font(size=i))
w = StyleWriter(self.workbook)
assert len(w.styles) == 6 # 5 + the default
self.worksheet.cell('A10').style = Style(border=Border(top=Side(
border_style=borders.BORDER_THIN)))
w = StyleWriter(self.workbook)
assert len(w.styles) == 7
def parse_cell_xfs(self):
"""Read styles from the shared style table"""
cell_xfs = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
if cell_xfs is None: # can happen on bad OOXML writers (e.g. Gnumeric)
return
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs_nodes = safe_iterator(cell_xfs, '{%s}xf' % SHEET_MAIN_NS)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
new_style = Style(static=True)
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
new_style.number_format.format_code = \
builtin_formats.get(number_format_id, 'General')
else:
fmt_code = self.custom_num_formats.get(number_format_id)
if fmt_code is not None:
new_style.number_format.format_code = fmt_code
else:
raise MissingNumberFormat('%s' % number_format_id)
if bool(cell_xfs_node.get('applyAlignment')):
alignment = cell_xfs_node.find('{%s}alignment' % SHEET_MAIN_NS)
if alignment is not None:
for key in ('horizontal', 'vertical', 'indent'):
_value = alignment.get(key)
if _value is not None:
setattr(new_style.alignment, key, _value)
new_style.alignment.wrap_text = bool(alignment.get('wrapText'))
new_style.alignment.shrink_to_fit = bool(alignment.get('shrinkToFit'))
text_rotation = alignment.get('textRotation')
if text_rotation is not None:
new_style.alignment.text_rotation = int(text_rotation)
# ignore justifyLastLine option when horizontal = distributed
if bool(cell_xfs_node.get('applyFont')):
new_style.font = deepcopy(self.font_list[int(cell_xfs_node.get('fontId'))])
if bool(cell_xfs_node.get('applyFill')):
new_style.fill = deepcopy(self.fill_list[int(cell_xfs_node.get('fillId'))])
if bool(cell_xfs_node.get('applyBorder')):
new_style.borders = deepcopy(self.border_list[int(cell_xfs_node.get('borderId'))])
if bool(cell_xfs_node.get('applyProtection')):
protection = cell_xfs_node.find('{%s}protection' % SHEET_MAIN_NS)
# Ignore if there are no protection sub-nodes
if protection is not None:
new_style.protection.locked = bool(protection.get('locked'))
new_style.protection.hidden = bool(protection.get('hidden'))
self.style_prop['table'][index] = new_style
def test_alignment_indent(self):
self.worksheet.cell('A1').style = Style(alignment=Alignment(indent=1))
self.worksheet.cell('A2').style = Style(alignment=Alignment(indent=4))
self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=0))
self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=-1))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'indent="1"' in xml
assert 'indent="4"' in xml
#Indents not greater than zero are ignored when writing
assert 'indent="0"' not in xml
assert 'indent="-1"' not in xml
def test_alignment_rotation(self):
self.worksheet.cell('A1').style = Style(
alignment=Alignment(vertical='center', text_rotation=90))
self.worksheet.cell('A2').style = Style(
alignment=Alignment(vertical='center', text_rotation=135))
self.worksheet.cell('A3').style = Style(alignment=Alignment(
text_rotation=-34))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'textRotation="90"' in xml
assert 'textRotation="135"' in xml
assert 'textRotation="124"' in xml
def print_worksheet_header(ws):
# Cemetery Name
ws.merge_cells('D2:H2')
ws['D2'].style = Style(font=Font(size=14, bold=True),
alignment=Alignment(horizontal="center"))
ws['D2'] = CEMETERY_NAME
# Cemetery Location
ws.merge_cells('D3:H3')
ws['D3'].style = Style(font=Font(bold=True),
alignment=Alignment(horizontal="center"))
ws['D3'] = CEMETERY_LOCATION
print_row_spacer(2, ws)
def test_write_number_formats():
wb = DummyWorkbook()
from openpyxl.xml.functions import Element
from openpyxl.styles import NumberFormat, Style
wb.shared_styles = [Style(), Style(number_format=NumberFormat('YYYY'))]
writer = StyleWriter(wb)
writer._write_number_format(writer._root, 0, "YYYY")
xml = get_xml(writer._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmt formatCode="YYYY" numFmtId="0"></numFmt>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def setup_class(cls):
now = datetime.datetime.now()
cls.workbook = Workbook(guess_types=True)
cls.worksheet = cls.workbook.create_sheet()
cls.worksheet.cell(coordinate='A1').value = '12.34%' # 2
cls.worksheet.cell(coordinate='B4').value = now # 3
cls.worksheet.cell(coordinate='B5').value = now
cls.worksheet.cell(coordinate='C14').value = 'This is a test' # 1
cls.worksheet.cell(coordinate='D9').value = '31.31415' # 3
st = Style(number_format=NumberFormat(NumberFormat.FORMAT_NUMBER_00),
protection=Protection(locked=True)) # 4
cls.worksheet.cell(coordinate='D9').style = st
st2 = Style(protection=Protection(hidden=True)) # 5
cls.worksheet.cell(coordinate='E1').style = st2
cls.writer = StyleWriter(cls.workbook)
def test_fonts_with_underline(self):
st = Style(
font=Font(size=12, bold=True, underline=Font.UNDERLINE_SINGLE))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_fonts()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font>
<sz val="11" />
<color theme="1" />
<name val="Calibri" />
<family val="2" />
<scheme val="minor" />
</font>
<font>
<sz val="12.0" />
<color rgb="00000000" />
<name val="Calibri" />
<family val="2" />
<b />
<u />
</font>
</fonts>
</styleSheet>
""")
assert diff is None, diff
def test_xfs_fonts(self):
st = Style(font=Font(size=12, bold=True))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fills = DummyElement()
fonts = Element("fonts")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
assert """applyFont="1" """ in xml
assert """fontId="1" """ in xml
expected = """
<fonts count="2">
<font>
<sz val="12.0" />
<color rgb="00000000"></color>
<name val="Calibri" />
<family val="2" />
<b></b>
</font>
</fonts>
"""
xml = get_xml(fonts)
diff = compare_xml(xml, expected)
assert diff is None, diff
def generate_xls_teaching_material(user, learning_units):
""" Generate a XLS file with all filtered learning_units where the course material is required """
titles = [
str(_('code')).title(),
str(_('Title')),
str(_('Req. Entity')).title(),
str(_('bibliography')).title(),
str(_('teaching materials')).title(),
"{} - {}".format(_('online resources'), settings.LANGUAGE_CODE_FR).title(),
"{} - {}".format(_('online resources'), settings.LANGUAGE_CODE_EN).title(),
]
rows = [lu for lu in learning_units if lu.teachingmaterial_set.filter(mandatory=True)]
file_parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.FILENAME: XLS_FILENAME,
xls_build.USER: get_name_or_username(user),
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: _("Teaching material"),
xls_build.STYLED_CELLS: {
Style(alignment=Alignment(wrap_text=True)): _get_text_wrapped_cells(len(rows)),
}
}
learning_units = _annotate_with_pedagogy_info(learning_units)
working_sheets_data = _filter_required_teaching_material(learning_units)
return xls_build.generate_xls(prepare_xls_parameters_list(working_sheets_data, file_parameters))
def style(self):
return Style(font=self.font,
alignment=self.alignment,
fill=self.fill,
number_format=self.number_format,
border=self.border,
protection=self.protection)
def test_xfs_fills(self):
st = Style(fill=PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW)))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fonts = DummyElement()
fills = Element("fills")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyFill="1" borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
expected = """<fills count="3">
<fill>
<patternFill patternType="solid">
<fgColor rgb="00808000"></fgColor>
</patternFill>
</fill>
</fills>
"""
xml = get_xml(fills)
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_only():
wb = Workbook()
ws = wb.create_sheet()
ws.title = "首页列表"
c = ws['A1']
c.style = Style(
font=Font(name='Courrier', size=36),
fill=PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000'),
protection=Protection(locked='inherit', hidden='inherit'),
alignment=Alignment(horizontal='general',
vertical='bottom',
shrink_to_fit=True),
border=Border(left=Side(border_style=None, color='FF000000')))
c.value = '姓名'
# cell = WriteOnlyCell(ws, value="hello world")
# cell.style = Style(font=Font(name='Courrier', size=36))
# cell.comment = Comment(text="A comment", author="Author's Name")
# ws.header_footer.center_header.text = 'My Excel Page'
# ws.header_footer.center_header.font_size = 14
# ws.header_footer.center_header.font_name = "Tahoma,Bold"
# ws.header_footer.center_header.font_color = "CC3366"
wb.save(filename='empty_book.xlsx')
def saveAs(self, outFp):
wb = openpyxl.workbook.Workbook()
ws = wb.get_active_sheet()
rowIndex = 0
# write header
colIndex = 0
for head in HEADERS_XL:
cell = ws.cell(row=rowIndex + 1, column=colIndex + 1)
cell.value = head
cell.style = Style(font=Font(bold=True))
colIndex += 1
# write content
for row in self.db.execute(QSQL_CMD):
rowIndex += 1
for head, index in HEADERS_ROW:
colIndex = HEADERS_XL.index(head)
if "href" == head:
cell = ws.cell(row=rowIndex + 1, column=colIndex + 1)
cell.value = row[index]
cell.hyperlink = row[index]
else:
ws.cell(row=rowIndex + 1,
column=colIndex + 1).value = row[index]
if rowIndex % 100 == 0:
sys.stdout.write('.')
ws.auto_filter.ref = "A1:S1"
ws.freeze_panes = ws.cell('A2')
print("writing...")
wb.save(outFp)
def _build_excel_lines_ues(custom_xls_form: CustomXlsForm, qs: QuerySet):
content = _get_headers(custom_xls_form)
optional_data_needed = _optional_data(custom_xls_form)
colored_cells = defaultdict(list)
idx = 1
for gey in qs:
luy = gey.child_leaf
content.append(
_get_optional_data(_fix_data(gey, luy), luy, optional_data_needed,
gey))
if getattr(luy, "proposallearningunit", None):
colored_cells[PROPOSAL_LINE_STYLES.get(
luy.proposallearningunit.type)].append(idx)
idx += 1
colored_cells[Style(font=BOLD_FONT)].append(0)
return {
'content': content,
'colored_cells': colored_cells,
'row_height': {
'height': 30,
'start': 2,
'stop': (len(content)) + 1
} if optional_data_needed['has_description_fiche']
or optional_data_needed['has_specifications'] else {}
}
def test_fills(self):
st = Style(fill=PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW)))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_fills()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fills count="3">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="gray125" />
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="0000FF00" />
</patternFill>
</fill>
</fills>
</styleSheet>
""")
assert diff is None, diff
def test_protection(self):
prot = Protection(locked=True, hidden=True)
self.worksheet.cell('A1').style = Style(protection=prot)
w = StyleWriter(self.workbook)
w._write_protection(w._root, prot)
xml = get_xml(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<protection hidden="1" locked="1"/>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
nft = fonts = borders = fills = Element('empty')
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<protection hidden="1" locked="1"/>
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyProtection="1" borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0">
<protection hidden="1" locked="1"/>
</xf>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_style():
from openpyxl.styles import Font, Style
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
new_style = Style(font=Font(bold=True))
ws.append([{'value': 'hello', 'style': new_style}])
assert new_style in wb.shared_styles
def test_borders(self):
st = Style(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW))))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_borders()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<borders count="2">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
<border>
<left />
<right />
<top style="thin">
<color rgb="0000FF00" />
</top>
<bottom />
<diagonal />
</border>
</borders>
</styleSheet>
""")
assert diff is None, diff
def __init__(self):
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self._local_data = DummyLocalData()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
def styled_sheet():
from openpyxl import Workbook
from openpyxl.styles import Font, Style, PatternFill, Color, colors
wb = Workbook()
ws = wb.active
ws.title = 'Test 1'
red_fill = PatternFill(fill_type='solid', fgColor=Color(colors.RED), bgColor=Color(colors.RED))
empty_fill = PatternFill()
styles = []
# pregenerate relevant styles
for row in range(ROWS):
_row = []
for col in range(COLUMNS):
cell = ws.cell(row=row+1, column=col+1)
cell.value = 1
font = {}
fill = PatternFill()
if formatData[row][col] & BOLD:
font['bold'] = True
if formatData[row][col] & ITALIC:
font['italic'] = True
if formatData[row][col] & UNDERLINE:
font['underline'] = 'single'
if formatData[row][col] & RED_BG:
fill = red_fill
cell.style = Style(font=Font(**font), fill=fill)
def get_style(self, coordinate, read_only=False):
"""Return the style object for the specified cell."""
if not coordinate in self._styles:
self._styles[coordinate] = Style()
elif self._styles[coordinate].static and not read_only:
self._styles[coordinate] = self._styles[coordinate].copy()
return self._styles[coordinate]
def __init__(self,
optimized_write=False,
encoding='utf-8',
worksheet_class=Worksheet,
optimized_worksheet_class=DumpWorksheet,
guess_types=False,
data_only=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.__thread_local_data = threading.local()
self.strings_table_builder = StringTableBuilder()
self.loaded_theme = None
self._worksheet_class = worksheet_class
self._optimized_worksheet_class = optimized_worksheet_class
self.vba_archive = None
self.style_properties = None
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.encoding = encoding
if not optimized_write:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
def test_read_standalone_worksheet(datadir):
class DummyWb(object):
encoding = 'utf-8'
excel_base_date = CALENDAR_WINDOWS_1900
_guess_types = True
data_only = False
vba_archive = None
def __init__(self):
self.shared_styles = [Style()]
self._cell_styles = IndexedList()
def get_sheet_by_name(self, value):
return None
def get_sheet_names(self):
return []
datadir.join("reader").chdir()
ws = None
shared_strings = IndexedList(['hello'])
with open('sheet2.xml') as src:
ws = read_worksheet(src.read(), DummyWb(), 'Sheet 2', shared_strings,
{1: Style()})
assert isinstance(ws, Worksheet)
assert ws.cell('G5').value == 'hello'
assert ws.cell('D30').value == 30
assert ws.cell('K9').value == 0.09
def test_read_style_iter(tmpdir):
'''
Test if cell styles are read properly in iter mode.
'''
tmpdir.chdir()
from openpyxl import Workbook
from openpyxl.styles import Style, Font
FONT_NAME = "Times New Roman"
FONT_SIZE = 15
ft = Font(name=FONT_NAME, size=FONT_SIZE)
wb = Workbook()
ws = wb.worksheets[0]
cell = ws.cell('A1')
cell.style = Style(font=ft)
xlsx_file = "read_only_styles.xlsx"
wb.save(xlsx_file)
wb_iter = load_workbook(xlsx_file, read_only=True)
ws_iter = wb_iter.worksheets[0]
cell = ws_iter['A1']
assert cell.style.font == ft
def export_to_excel(db_data, xlsx_name):
"""导出到excel文件中"""
_log.info('开始导出到excel文件中')
border = Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000'),
right=Side(border_style=borders.BORDER_THIN, color='FF000000'),
top=Side(border_style=borders.BORDER_THIN, color='FF000000'),
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')
)
alignment = Alignment(horizontal='justify',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=True,
indent=0)
fill = PatternFill(fill_type=None, start_color='FFFFFFFF')
# 基本的样式
basic_style = Style(font=Font(name='Microsoft YaHei')
, border=border, alignment=alignment
, fill=fill)
# header_style = basic_style.copy(
# font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'),
# fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F'))
header_style = basic_style.copy()
common_style = basic_style.copy()
wb = Workbook()
ws = wb.create_sheet(index=0, title='enterprises-{}'.format(len(db_data)))
ws['A1'] = '日期'
ws['A1'].style = header_style
ws['B1'] = '新增数量'
ws['B1'].style = header_style
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 20
for i, row in enumerate(db_data):
ws['A{}'.format(i + 2)] = row[0]
ws['A{}'.format(i + 2)].style = common_style
ws['B{}'.format(i + 2)] = row[1]
ws['B{}'.format(i + 2)].style = common_style
wb.save(filename=xlsx_name)
_log.info('导出excel文件完成')
def read_style_table(xml_source):
"""Read styles from the shared style table"""
style_prop = {'table': {}}
root = fromstring(xml_source)
custom_num_formats = parse_custom_num_formats(root)
style_prop['color_index'] = parse_color_index(root)
font_list = parse_fonts(root, style_prop['color_index'])
fill_list = parse_fills(root, style_prop['color_index'])
border_list = parse_borders(root, style_prop['color_index'])
style_prop['dxf_list'] = parse_dxfs(root, style_prop['color_index'])
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs = root.find('{%s}cellXfs' % SHEET_MAIN_NS)
if cell_xfs is not None: # can happen on bad OOXML writers (e.g. Gnumeric)
cell_xfs_nodes = cell_xfs.findall('{%s}xf' % SHEET_MAIN_NS)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
new_style = Style(static=True)
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
new_style.number_format.format_code = \
builtin_formats.get(number_format_id, 'General')
else:
if number_format_id in custom_num_formats:
new_style.number_format.format_code = \
custom_num_formats[number_format_id]
else:
raise MissingNumberFormat('%s' % number_format_id)
if cell_xfs_node.get('applyAlignment') == '1':
alignment = cell_xfs_node.find('{%s}alignment' % SHEET_MAIN_NS)
if alignment is not None:
if alignment.get('horizontal') is not None:
new_style.alignment.horizontal = alignment.get('horizontal')
if alignment.get('vertical') is not None:
new_style.alignment.vertical = alignment.get('vertical')
if alignment.get('wrapText'):
new_style.alignment.wrap_text = True
if alignment.get('shrinkToFit'):
new_style.alignment.shrink_to_fit = True
if alignment.get('indent') is not None:
new_style.alignment.ident = int(alignment.get('indent'))
if alignment.get('textRotation') is not None:
new_style.alignment.text_rotation = int(alignment.get('textRotation'))
# ignore justifyLastLine option when horizontal = distributed
if cell_xfs_node.get('applyFont') == '1':
new_style.font = deepcopy(font_list[int(cell_xfs_node.get('fontId'))])
new_style.font.color = deepcopy(font_list[int(cell_xfs_node.get('fontId'))].color)
if cell_xfs_node.get('applyFill') == '1':
new_style.fill = deepcopy(fill_list[int(cell_xfs_node.get('fillId'))])
new_style.fill.start_color = deepcopy(fill_list[int(cell_xfs_node.get('fillId'))].start_color)
new_style.fill.end_color = deepcopy(fill_list[int(cell_xfs_node.get('fillId'))].end_color)
if cell_xfs_node.get('applyBorder') == '1':
new_style.borders = deepcopy(border_list[int(cell_xfs_node.get('borderId'))])
new_style.borders.left = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].left)
new_style.borders.left.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].left.color)
new_style.borders.right = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].right)
new_style.borders.right.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].right.color)
new_style.borders.top = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].top)
new_style.borders.top.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].top.color)
new_style.borders.bottom = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].bottom)
new_style.borders.bottom.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].bottom.color)
new_style.borders.diagonal = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].diagonal)
new_style.borders.diagonal.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].diagonal.color)
if cell_xfs_node.get('applyProtection') == '1':
protection = cell_xfs_node.find('{%s}protection' % SHEET_MAIN_NS)
# Ignore if there are no protection sub-nodes
if protection is not None:
if protection.get('locked') is not None:
if protection.get('locked') == '1':
new_style.protection.locked = Protection.PROTECTION_PROTECTED
else:
new_style.protection.locked = Protection.PROTECTION_UNPROTECTED
if protection.get('hidden') is not None:
if protection.get('hidden') == '1':
new_style.protection.hidden = Protection.PROTECTION_PROTECTED
else:
new_style.protection.hidden = Protection.PROTECTION_UNPROTECTED
style_prop['table'][index] = new_style
return style_prop
def write_dest(xlsx_name, schema_name):
border = Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000'),
right=Side(border_style=borders.BORDER_THIN, color='FF000000'),
top=Side(border_style=borders.BORDER_THIN, color='FF000000'),
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')
)
alignment = Alignment(horizontal='justify', vertical='bottom',
text_rotation=0, wrap_text=False,
shrink_to_fit=True, indent=0)
fill = PatternFill(fill_type=None, start_color='FFFFFFFF')
# 基本的样式
basic_style = Style(font=Font(name='Microsoft YaHei')
, border=border, alignment=alignment
, fill=fill)
title_style = basic_style.copy(
font=Font(name='Microsoft YaHei', b=True, size=20, color='00215757'),
alignment=Alignment(horizontal='center', vertical='bottom',
text_rotation=0, wrap_text=False,
shrink_to_fit=True, indent=0),
fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00B2CBED'))
header_style = basic_style.copy(
font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'),
fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F'))
common_style = basic_style.copy()
link_style = basic_style.copy(font=Font(
name='Microsoft YaHei', color=colors.BLUE, underline='single'))
table_data = load_schema(schema_name)
wb = Workbook()
wb.active.title = "首页列表"
for table in table_data:
ws = wb.create_sheet(title=table[0])
ws.merge_cells('E3:H3') # 合并单元格
ws['E3'].style = title_style
ws['F2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['G2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['H2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['I3'].style = Style(border=Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['E3'] = table[0]
ws['E4'].style = header_style
ws['E4'] = '列名'
ws['F4'].style = header_style
ws['F4'] = '类型'
ws['G4'].style = header_style
ws['G4'] = '空值约束'
ws['H4'].style = header_style
ws['H4'] = '备注'
ws.column_dimensions['E'].width = 20
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 16
ws.column_dimensions['H'].width = 45
for idx, each_column in enumerate(table[2:]):
ws['E{}'.format(idx + 5)].style = common_style
ws['E{}'.format(idx + 5)] = each_column[0]
ws['F{}'.format(idx + 5)].style = common_style
ws['F{}'.format(idx + 5)] = each_column[1]
ws['G{}'.format(idx + 5)].style = common_style
ws['G{}'.format(idx + 5)] = each_column[2]
ws['H{}'.format(idx + 5)].style = common_style
ws['H{}'.format(idx + 5)] = each_column[3]
ws = wb['首页列表']
ws.merge_cells('D3:F3')
ws['D3'].style = title_style
ws['E2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['F2'].style = Style(border=Border(
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['G3'].style = Style(border=Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000')))
ws['D3'] = '贷快发数据库系统表'
ws['D4'].style = header_style
ws['D4'] = '编号'
ws['E4'].style = header_style
ws['E4'] = '表名'
ws['F4'].style = header_style
ws['F4'] = '详情链接'
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 25
ws.column_dimensions['F'].width = 35
for inx, val in enumerate(table_data):
ws['D{}'.format(inx + 5)].style = common_style
ws['D{}'.format(inx + 5)] = inx + 1
ws['E{}'.format(inx + 5)].style = common_style
ws['E{}'.format(inx + 5)] = val[1]
linkcell = ws['F{}'.format(inx + 5)]
linkcell.style = link_style
linkcell.value = val[0]
linkcell.hyperlink = '#{0}!{1}'.format(val[0], 'E3')
wb.save(filename=xlsx_name)
def export_to_excel(db_data, xlsx_name):
"""导出到excel文件中"""
_log.info('开始导出到excel文件中')
border = Border(
left=Side(border_style=borders.BORDER_THIN, color='FF000000'),
right=Side(border_style=borders.BORDER_THIN, color='FF000000'),
top=Side(border_style=borders.BORDER_THIN, color='FF000000'),
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')
)
alignment = Alignment(horizontal='justify',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=True,
indent=0)
fill = PatternFill(fill_type=None, start_color='FFFFFFFF')
# 基本的样式
basic_style = Style(font=Font(name='Microsoft YaHei')
, border=border, alignment=alignment
, fill=fill)
header_style = basic_style.copy(
font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'),
fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F'))
common_style = basic_style.copy()
wb = Workbook()
ws = wb.create_sheet(index=0, title='enterprises-{}'.format(len(db_data)))
ws['A1'] = 'id'
ws['A1'].style = common_style
ws['B1'] = 'name'
ws['B1'].style = common_style
ws['C1'] = 'tax_code'
ws['C1'].style = common_style
ws['D1'] = 'region_id'
ws['D1'].style = common_style
ws['E1'] = 'customer_type'
ws['E1'].style = common_style
ws['F1'] = 'enterprise_type'
ws['F1'].style = common_style
ws['G1'] = 'address'
ws['G1'].style = common_style
ws['H1'] = 'postcode'
ws['H1'].style = common_style
ws['I1'] = 'tel'
ws['I1'].style = common_style
ws['J1'] = 'contact'
ws['J1'].style = common_style
ws['K1'] = 'fax'
ws['K1'].style = common_style
ws['L1'] = 'mobile'
ws['L1'].style = common_style
ws['M1'] = 'region_code'
ws['M1'].style = common_style
ws['N1'] = 'regian_name'
ws['N1'].style = common_style
ws['O1'] = 'note'
ws['O1'].style = common_style
ws['P1'] = 'parent_id'
ws['P1'].style = common_style
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 40
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 10
ws.column_dimensions['E'].width = 20
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 80
ws.column_dimensions['H'].width = 18
ws.column_dimensions['I'].width = 40
ws.column_dimensions['J'].width = 20
ws.column_dimensions['K'].width = 20
ws.column_dimensions['L'].width = 40
ws.column_dimensions['M'].width = 20
ws.column_dimensions['N'].width = 20
for i, row in enumerate(db_data):
ws['A{}'.format(i + 2)] = row[0]
ws['A{}'.format(i + 2)].style = common_style
ws['B{}'.format(i + 2)] = row[1]
ws['B{}'.format(i + 2)].style = common_style
ws['C{}'.format(i + 2)] = row[2]
ws['C{}'.format(i + 2)].style = common_style
ws['D{}'.format(i + 2)] = row[3]
ws['D{}'.format(i + 2)].style = common_style
ws['E{}'.format(i + 2)] = row[4]
ws['E{}'.format(i + 2)].style = common_style
ws['F{}'.format(i + 2)] = row[5]
ws['F{}'.format(i + 2)].style = common_style
ws['G{}'.format(i + 2)] = row[6]
ws['G{}'.format(i + 2)].style = common_style
ws['H{}'.format(i + 2)] = row[7]
ws['H{}'.format(i + 2)].style = common_style
ws['I{}'.format(i + 2)] = row[8]
ws['I{}'.format(i + 2)].style = common_style
ws['J{}'.format(i + 2)] = row[9]
ws['J{}'.format(i + 2)].style = common_style
ws['K{}'.format(i + 2)] = row[10]
ws['K{}'.format(i + 2)].style = common_style
ws['L{}'.format(i + 2)] = row[11]
ws['L{}'.format(i + 2)].style = common_style
ws['M{}'.format(i + 2)] = row[12]
ws['M{}'.format(i + 2)].style = common_style
ws['N{}'.format(i + 2)] = row[13]
ws['N{}'.format(i + 2)].style = common_style
ws['O{}'.format(i + 2)] = row[14]
ws['O{}'.format(i + 2)].style = common_style
ws['P{}'.format(i + 2)] = row[15]
ws['P{}'.format(i + 2)].style = common_style
wb.save(filename=xlsx_name)
_log.info('导出excel文件完成')
