openpyxl使用

from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill, Alignment, colors, Protection
from openpyxl.worksheet.datavalidation import DataValidation


class Made_homepage():
    def __init__(self, wb, data, excel_name_path):
        """
        初始化参数
        :param wb: 创建的Workbook对象
        :param data: 所有数据字典，表中每个sheet的数据，例如
        {"sheet名1"：[[第一行数据], [第二行数据].....], "sheet名2": [[第一行数据], [第二行数据].....], ......}
        :param excel_name_path: excel表位置
        """

        self._wb = wb
        self._data = data
        self._excel_name_path = excel_name_path
        # 字体样式
        self._font = Font(name="微软雅黑",
                        color=colors.BLACK, # 字体颜色，使用#FFFFFF也可以
                        italic=True,   # 是否斜体
                        size=14,
                        bold=False,  # 是否加粗
                        vertAlign=None,  # 对齐方式 None、subscript（下标）、baseline（基线）、superscript（上标）
                        underline="none", # 下划线，double、single、doubleAccounting、singleAccounting
                        strike=False,  # 删除线
                        )
         self._hyperlink_font = Font(name="微软雅黑",
                          color=colors.BLUE,
                          italic=False,
                          size=12,
                          bold=False,
                          vertAlign=None,
                          underline="single",
                          strike=False,
                          )
        # 填充，颜色等
        self._fill = PatternFill(fill_type=None,  # 填充的样式，fill_type为solid颜色背景颜色；fill_type为None，焦点在单元格中才会显示颜色
                           start_color=colors.BLACK,  # start_color和end_color颜色，值为HEX颜色值(渐变色填充，起始和结束颜色一致，单元格中的颜色就是一致的)
                           end_color=colors.BLACK
                           )
        # 边框
        # self._border = Border( # border_style边线样式，thin、medium、thick实线依次加粗
        #     left=Side(border_style="thin", color=colors.BLACK),
        #     right=Side(border_style="thin", color=colors.BLACK),
        #     top=Side(border_style="thin", color=colors.BLACK),
        #     bottom=Side(border_style="thin", color=colors.BLACK),
        #     diagonal=Side(border_style=None, color=colors.BLACK), # 对角线
        #     diagonal_direction=0,
        #     outline=Side(border_style=None, color=colors.BLACK),  # 删除线
        #     vertical=Side(border_style=None, color=colors.BLACK),  # 水平线
        #     horizontal=Side(border_style=None, color=colors.BLACK),  # 垂直线
        # )
        # 位置
        self._alignment = Alignment(
            # 水平方向right、center（水平居中）、centerContinuous、distributed、general、left、justify、fill
                              horizontal='general',
            # 垂直方向 top、justify、distributed、center（垂直居中）、bottom
                              vertical='bottom',
                              text_rotation=0,
                              wrap_text=False,  # 是否自动换行
                              shrink_to_fit=False,
                              indent=0)
        # 数据格式
        self._number_format = "General"
        # 保护模式
        self._protection = Protection(locked=True, hidden=False)  # 锁定不可编辑，不隐藏

        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_freeze(self, ws, row_col):
        """
        冻结，传入单元格位置，这个单元格之上的所有行和左边的所有列都会被冻结，例如：A2，只冻结首行
        :param ws: sheet对象
        :param row_col: 单元格位置，例如：A2
        :return:
        """
        ws.freeze_panes = row_col
        
    def _set_row_hidden(self, ws, row_list):
        """
        设置行隐藏
        :param ws: sheet对象
        :param row_list: 行号列表，例如：[1, 2, 3]
        :return: 
        """
        for i in row_list:
            ws.row_dimensions[i].hidden = 1
            
    def _set_column_hidden(self, ws, column_list):
        """
        设置列隐藏
        :param ws: sheet对象
        :param column_list: 列号列表，例如：["B", "C", "D"]
        :return: 
        """
        for i in column_list:
            ws.column_dimensions[i].hidden = 1  

    def set_protection(self, ws, value, already_hashed=False):
        """
        对sheet进行保护
        :param ws: sheet对象
        :param value: 密码
        :param ws: 是否对密码进行hash，默认：False
        :return:
        """
        ws.protection.set_password(value=value, already_hashed=already_hashed)

    def set_table_colwidth(self, ws, col_name_len_dict):
        """
        设置列宽
        :param ws: sheet对象
        :param col_name_len_dict: 列名长度字典，例如：{"A": 10, "B": 15}
        :return: None
        """
        for col_name, length in col_name_len_dict.items():
            ws.column_dimensions[col_name].width = length if length <= 40 else 40  # 最长设置40

    def set_title_style(self, cell, value='', font=None, alignment=None):
        """
        设置单元格数据及样式
        :param cell:  单元格位置
        :param value: 单元格值
        :param font:  样式
        :param alignment: 位置样式
        :return:
        """
        cell.value = value
        cell.style = self._title
        cell.font = font if font else self._font
        cell.alignment = alignment if alignment else self._alignment
        
	def set_hyperlink(self, ws, row, column, value, link, font=self.hyperlink_font, 	alignment=self.alignment, border=self.border):
        """
        为单元格设置超链接
        :param cell: 单元格对象
        :param value: 单元格需要设置显示的文本
        :param link: 超链接
        :param font: 字体
        :param alignment: 对齐方式
        :param border: 边框
        """
        cell = ws.cell(row=row, column=column)
        cell.value = value
        cell.hyperlink = link
        cell.font = font
        cell.alignment = alignment
        cell.border = border
        
    def record_max_col(self, col_num_list, text, index):
        """
        记录每列的最大宽度值
        :param col_num_list:
        :param text:
        :param index:
        :return:
        """
        col = len(str(text).encode('gb18030'))  # 列宽度
        if col > col_num_list[index]:
            col_num_list[index] = col

    def get_col_num_dict(self):
        """
        获取位置和列字母的对应关系
        :return: 返回结果: {0: "A", 1: "B" ........25: "Z"}
        """
        A_Z = [chr(a) for a in range(ord("A"), ord("Z"))]
        col_num_dict = {k: v for k, v in enumerate(A_Z)}

        return col_num_dict

    def set_upslide(self,ws, cell_alignment, upslid_list, allow_blank=True):
        """
        单元格设置下拉菜单
        :param ws: sheet对象
        :param cell_alignment: 单元格的范围，例如"G2:G10"
        :param upslid_list: 下拉选项，例如：'"确认漏洞,确认误报,确认报备,待确认"'
        :param allow_blank: 是否可以为空，默认为True，可以为空
        :return:
        """
        dv = DataValidation(type="list", formula1=upslid_list, allow_blank=allow_blank)
        dv.error = "Your entry is not in the list"
        dv.errorTitle = "Invalid Entry"
        dv.prompt = "Please select from the list"
        dv.promptTitle = "List Selection"
        dv.add(cell_alignment)
        ws.add_data_validation(dv)

    def set_table_data(self, ws, data_list):
        """
        给表中添加数据
        :param ws: sheet对象
        :param data_list: 每个sheet中数据列表
        :return: 每列的最大长度列表
        """
        # ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=7)
        font = Font(name="微软雅黑",
                    color=colors.BLACK, # 使用#FFFFFF也可以
                    italic=True,
                    size=14,
                    bold=True,  # 加粗
                    vertAlign=None,
                    underline="none",
                    strike=False,
                    )
        column_num = len(data_list[0])  # 记录列数
        col_num_list = [0 for _ in range(column_num)]  # 设置每列宽度初始值为0
        for row_num, row_list in enumerate(data_list):  # 循环每一行数据
            for col_num, value in enumerate(row_list):  # 循环一行中的数据
                self.set_title_style(
                                     ws.cell(row=row_num + 1, column=col_num + 1),
                                     value=value,
                                     font=font if row_num == 0 else self._font  # 如果是第一行，则单独设置font
                                     )
                self.record_max_col(col_num_list, value, col_num)

        return col_num_list

    def run(self):
        index = 0
        for sheet_name, data_list in self._data.items():
            # 创建sheet
            ws = self._wb.create_sheet(sheet_name, index=index)
            # 写入数据
            col_num_list = self.set_table_data(ws=ws, data_list=data_list)
            col_num_dict = self.get_col_num_dict()
            # 获取列名和长度对应关系字典
            col_name_len_dict = {col_num_dict[i]: value for i, value in enumerate(col_num_list)}
            # 设置列宽
            self.set_table_colwidth(ws=ws, col_name_len_dict=col_name_len_dict)

            index += 1
        # 保存
        self._wb.save(self._excel_name_path)

openpyxl 下拉框

dv = DataValidation(type="list", formula1='"a,b,v"', allow_blank=True)  # 下拉框可选择值为：a、b、c
dv.error = "Your entry is not in the list"  # 输入错误时候的提示信息
dv.errorTitle = "Invalid Entry"   # 输入错误时候的提示框的标题
dv.prompt = "Please select from the list"  # 鼠标点击输入框时候的提示信息
dv.promptTitle = "List Selection"  # 鼠标点击输入框时候的提示信息
dv.add("P2:P6")  # 这里样例是给P列添加下拉框，从2行到6行
sheet.add_data_validation(dv)  # 给sheet添加下拉框

隐藏列

sheet.column_dimensions["M"].hidden = 1   # 单列隐藏，样例为给M列隐藏
如果想隐藏多列，可以写多个上面的代码，也可以下面这样
sheet.column_dimensions.group("K", "M", hidden=True)  # 这里是给K、M列同时隐藏

冻结

sheet.freeze_panes = "A2"  # 冻结首行
sheet.freeze_panes = "B2"  # 冻结首行和首列