字典列表导出为 Excel
字典列表是业务开发中最常见的数据结构，例如数据库查询结果、API 响应数据等，每条记录以键值对形式存在。
实现思路
- 从首条数据提取键名作为表头
- 遍历每条字典记录，按键的顺序写入对应列
- 支持空值容错处理
- 对数值、布尔等特殊字段做类型区分写入
完整代码
from spire.xls import * from spire.xls.common import * def dict_list_to_excel(dict_list, output_path, sheet_name="数据", custom_headers=None): """将字典列表导出为Excel表格""" if not dict_list: raise ValueError("数据列表不能为空") workbook = Workbook() workbook.Worksheets.Clear() sheet = workbook.Worksheets.Add(sheet_name) # 确定表头：优先使用自定义表头，否则使用字典键名 if custom_headers: headers = custom_headers keys = list(dict_list[0].keys()) else: keys = list(dict_list[0].keys()) headers = keys # 写入表头 for col_idx, header in enumerate(headers): cell = sheet.Range[1, col_idx + 1] cell.Value = header cell.Style.Font.IsBold = True cell.Style.HorizontalAlignment = HorizontalAlignType.Center # 写入数据 for row_idx, item in enumerate(dict_list): for col_idx, key in enumerate(keys): cell = sheet.Range[row_idx + 2, col_idx + 1] value = item.get(key, "") # 根据类型智能写入 if value is None: cell.Value = "" elif isinstance(value, bool): cell.BooleanValue = value elif isinstance(value, (int, float)): cell.NumberValue = float(value) else: cell.Value = str(value) # 格式优化 data_range = sheet.Range[1, 1, len(dict_list) + 1, len(headers)] data_range.BorderAround(LineStyleType.Thin) data_range.BorderInside(LineStyleType.Thin) sheet.AllocatedRange.AutoFitColumns() workbook.SaveToFile(output_path, ExcelVersion.Version2016) workbook.Dispose() # 调用示例 if name == "main": orders = [ {"order_id": "ORD2024001", "product": "笔记本支架", "quantity": 2, "price": 89.9, "status": "已发货"}, {"order_id": "ORD2024002", "product": "USB集线器", "quantity": 5, "price": 45.0, "status": "待出库"}, {"order_id": "ORD2024003", "product": "散热底座", "quantity": 1, "price": 129.0, "status": "已签收"}, ] dict_list_to_excel(orders, "orders.xlsx", sheet_name="订单数据")