python openpyxl 简单实例（包含超链接、设置列宽、解除、合并单元格）

柔光的暖阳◎ 2023-06-07 15:27 19阅读 0赞

#coding:utf-8
    import xlrd
    import time
    import os
    import re
    from openpyxl import Workbook, load_workbook
    from openpyxl.styles import Font
    from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill, Alignment
    import collections
    import sys
    import copy
    
    class Made_homepage():
    
        def __init__(self, wb, data, excel_name):
            self._wb = wb
            self._data = data
            self.excel_name = excel_name
            self._ws_name = u'应收汇总表'
    
            self._title = NamedStyle(name='title') #注册样式
            self._border = Side(style='thin', color='000000')
            self._title.border = Border(
                left=self._border,
                top=self._border,
                right=self._border,
                bottom=self._border)
    
        def set_table_colwidth(self, ws): #设置列宽
            ws.column_dimensions['A'].width = 7
            ws.column_dimensions['B'].width = 50
            ws.column_dimensions['C'].width = 25
            ws.column_dimensions['D'].width = 25
            ws.column_dimensions['E'].width = 25
            ws.column_dimensions['F'].width = 25
            ws.column_dimensions['G'].width = 25
    
        def set_table_title(self, ws):
            ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=7)
    
            self.set_title_style(ws.cell(row = 1, column = 1), value = '应收汇总表', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 1), value = '序号', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 2), value = '客户名', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 3), value = '本期期初', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 4), value = '本期新增', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 5), value = '本期回款', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 6), value = '本期退货', font_bold = True, font_size=15)
            self.set_title_style(ws.cell(row = 2, column = 7), value = '期末余额', font_bold = True, font_size=15)
    
        def set_table_body(self, ws):
            index, start_row = 0, 3
            res = [0] * 5
            for i in self._data:
                acc = i
                ws_name = self._data[i]['index']
                value = self._data[i]['data']
                self.set_title_style(ws.cell(row = start_row, column = 1), value = index)
                self.set_title_style(ws.cell(row = start_row, column = 2), value = acc, font_color = '0000FF')
                ws.cell(row = start_row, column = 2).hyperlink = "%s#'%s'!A1"(self.excel_name.decode('gbk'), ws_name) #超链接
                start_col = 3
                for n, j in enumerate(value):
                    self.set_title_style(ws.cell(row = start_row, column = start_col), value = j)
                    res[n] += j
                    start_col += 1
                start_row += 1
                index += 1
            self.set_title_style(ws.cell(row = start_row, column = 1), value = index) #赋值 并设置样式
            self.set_title_style(ws.cell(row = start_row, column = 2), value = '合计', font_bold = True)
            start_col = 3
            for j in res:
                self.set_title_style(ws.cell(row = start_row, column = start_col), value = j, font_bold = True)
            start_col += 1
    
        def made_homepage(self):
            ws = self._wb.create_sheet(self._ws_name, 0)
            self.set_table_colwidth(ws)
            self.set_table_title(ws)
            self.set_table_body(ws)
    
        def set_title_style(self, cell, value='', font_bold=False, font_size=12, font_color='000000', alignment='center'):
            cell.value = value
            cell.style = self._title
            cell.font = Font(bold=font_bold, size=font_size, name='宋体', color=font_color)
            cell.alignment = Alignment(horizontal=alignment,vertical='center')