# -*- coding: utf-8 -*- import xlwt import os import xlsxwriter class XLSX(object): """ 文档地址：https://xlsxwriter.readthedocs.io/working_with_data.html 优点：功能强大，支持大文件写入 缺点：不支持读取和修改、不支持XLS文件 """ def __init__(self, file_payh, sheet_name="sheet1"): self.workbook = xlsxwriter.Workbook(file_payh) self.sheet = self.workbook.add_worksheet(sheet_name) # 保存excel中表头列的唯一标识和列的索引 self.keys = {} # 表头开始的行号 self.start_row = 0 # 当前待插入行的行号 self.insert_row = 0 # 表头开始的列号 self.start_col = 0 def get_style(self, font_name=u"微软雅黑", font_size=10, font_bold=False, font_color="black", border=1, background_color="#FFFFFF", align="left", valign="vcenter"): """ 获取单元格样式 设置字体：字体样式（默认微软雅黑），字体大小（默认10），字体默认不加粗，字体颜色（默认黑色） 设置单元格：单元格背景颜色（默认白色） 设置边框：边框宽度上下左右（默认宽度为1） """ # 设置字体 style = self.workbook.add_format({ # 字体样式 "font_name": font_name, # 字体大小 "font_size": font_size, # 是否加粗 "bold": font_bold, # 边框宽度 "border": border, # 水平对齐方式 "align": align, # 是否自动换行 "text_wrap": True, # 背景颜色 "fg_color": background_color, # 字体颜色 "color": font_color, # 垂直居中 "valign": valign }) return style def set_head(self, head, start_row=0, start_col=0): """ 设置表头 head：表头信息[{"name": "表头文本", "width": 10, "background-color": "green", "font-siz": 10}] start_row: 表头插入开始行,行号从0开始计算 start_col：表头插入开始列，列号从0开始计算 """ # 设置当前行号，和表头所在行号 self.start_row = start_row self.insert_row = start_row self.start_col = start_col # 添加表头 for i in range(len(head)): col_index = start_col + i width = head[i].get("width", 10) background_color = head[i].get("background-color", "green") size = head[i].get("font-siz", 10) self.sheet.set_column(col_index, col_index, width) style3 = self.get_style(font_size=size, background_color=background_color) name = head[i].get("name", "").decode("utf-8") self.sheet.write(self.start_row, col_index, name, style3) key = head[i].get("key", "") self.keys[key] = {"col": col_index} self.insert_row += 1 def write(self, data): """ 写入数据 数据中各个对象的key需要和head保持一致 数据格式 data = [{"module": "地图", "function": "地图功能项1", "unique": "map", "info": "这是地图说明", "type": "int", "value_info": "111111", "value": 1}] """ for index, item in enumerate(data): row_index = self.insert_row + index for key in item.keys(): col_index = self.keys[key]["col"] val = str(data[index][key]) self.sheet.write(row_index, col_index, val.decode("utf-8")) def group_write(self, data): """ 数据分组显示 数据中各个对象的key需要和head保持一致 数据格式 data = [ { "module": "地图", "data":[ {"function": "地图功能项1", "unique": "map", "info": "这是地图说明","type": "int", "value_info": "111111", "value": 1}, {"function": "地图功能项1", "unique": "map", "info": "这是地图说明", "type": "int", "value_info": "111111", "value": 1}, {"function": "地图功能项1", "unique": "map", "info": "这是地图说明", "type": "int", "value_info": "111111", "value": 1} ] }, ] """ for item in data: length = len(item["data"]) module_name = item["module"] style = self.get_style(align="center") self.merge_range(self.insert_row, 0, self.insert_row + length - 1, 0, module_name.decode("utf-8"), style) functions = item["data"] self.write(functions) self.insert_row += length self.add_filter() def merge_range(self, first_row, first_col, last_row, last_col, data, style): """ 合并单元格 """ self.sheet.merge_range(first_row, first_col, last_row, last_col, data, style) def add_filter(self): """ 为每个列添加筛选功能 """ # 列添加筛筛选 col_count = len(self.keys.keys()) self.sheet.autofilter(self.start_row, self.start_col, self.insert_row-1, col_count - 1) def save(self): # 导出excel self.workbook.close() class XLS(object): def __init__(self, file_payh, sheet_name="sheet1"): self.workbook = xlwt.Workbook(encoding='utf-8') self.sheet = self.workbook.add_sheet(sheet_name, cell_overwrite_ok=True) self.keys = {} self.file_payh = file_payh def get_style(self, font_name=u"微软雅黑", font_size=10, font_bold=False, font_color="black", background_color="white", border_left=1, border_right=1, border_top=1, border_bottom=1, center=False): """ 获取单元格样式 设置字体：字体样式（默认微软雅黑），字体大小（默认10），字体默认不加粗，字体颜色（默认黑色） 设置单元格：单元格背景颜色（默认白色） 设置边框：边框宽度上下左右（默认宽度为1） """ # 设置字体 style = xlwt.XFStyle() font = xlwt.Font() # 字体样式 font.name = font_name # 字体大小，字体大小的基本单位是20. font.height = 20 * font_size # 字体加粗 font.bold = font_bold # 字体颜色 font.colour_index = xlwt.Style.colour_map[font_color] style.font = font # 单元格对齐方式默认左对齐 al = xlwt.Alignment() al.horz = 0x02 if center else 0x00 # 设置自动换行 al.wrap = True style.alignment = al # 设置单元格 pat = xlwt.Pattern() # 设置单元格背景颜色 pat.pattern = xlwt.Pattern.SOLID_PATTERN # 设置单元格背景颜色 pat.pattern_fore_colour = xlwt.Style.colour_map[background_color] style.pattern = pat # 设置边框 border = xlwt.Borders() border.left = border_left border.right = border_right border.top = border_top border.bottom = border_bottom style.borders = border return style def set_head(self, head, start_row=0, start_col=0): """ 设置表头 head：表头信息[{"name": "表头文本", "width": 10, "background-color": "green", "font-siz": 10}] start_row: 表头插入开始行,行号从0开始计算 start_col：表头插入开始列，列号从0开始计算 """ # 添加表头 for i in range(len(head)): col_index = start_col + i width = head[i].get("width", 10) background_color = head[i].get("background-color", "green") size = head[i].get("font-siz", 10) self.sheet.col(col_index).width = 256 * width # 设置列宽,256为基准数， style3 = self.get_style(font_size=size, background_color=background_color) name = head[i].get("name", "") self.sheet.write(start_row, col_index, name, style3) key = head[i].get("key", "") self.keys[key] = {"col": col_index} def write(self, data, start_row): """ 写入数据 """ for index, item in enumerate(data): row_index = start_row + index for key in item.keys(): col_index = self.keys[key]["col"] val = data[index][key] self.sheet.write(row_index, col_index, val) def save(self): # 导出excel self.workbook.save(self.file_payh) class CppConfigXml(XLSX): def set_head(self, head, start_row=0, start_col=0): # 添加说明行 # 合并单元格第0行第0列到第0行第7列 style1 = self.get_style(font_size=20, font_bold=True, align="center") self.sheet.merge_range(0, 0, 0, 6, u"AUTO CPP 配置参数表", style1) style2 = self.get_style(font_size=10) explain = u"说明：HMI在与适配AL层交互时，由于系统能力或项目上与客户的约定不同，希望可以相应展现不同的UI样式和功能 由项目/产品填写导入研发" self.sheet.merge_range(1, 0, 1, 6, explain, style2) self.sheet.set_row(1, 40) super(CppConfigXml, self).set_head(head, start_row, start_col) if __name__=="__main__": try: data = [{"module": "地图", "function": "地图功能项1", "unique": "map", "info": "这是地图说明", "type": "int", "value_info": "111111", "value": 1}] head = [ {"name": "模块", "width": 10, "key": "module"}, {"name": "功能项", "width": 28, "key": "function"}, {"name": "唯一码", "width": 52, "key": "unique"}, {"name": "功能说明", "width": 62, "key": "info"}, {"name": "参数类型", "width": 10, "key": "type"}, {"name": "参数类型", "width": 15, "key": "value_info"}, {"name": "参数值", "width": 10, "key": "value"}, ] file_name = "1.xlsx" local_dir = os.path.dirname(os.path.abspath(__file__)) file_pth = os.path.join(local_dir, file_name) start_row, start_col = 2, 0 a = CppConfigXml(file_pth) a.set_head(head, start_row, start_col) a.write(data) a.add_filter() a.save() except Exception as e: print(e.message)