使用Python创建并保存Excel文件到字节串

在 Python 中，有多种方法可以创建 Excel 文件并将其保存为字节串（bytes）。以下是几种常用的实现方式：

方法一：使用 pandas + openpyxl/xlsxwriter（推荐）

安装依赖

pip install pandas openpyxl xlsxwriter

实现代码

import pandas as pd
from io import BytesIO

def create_excel_as_bytes_pandas():
    # 创建示例数据
    data = {
        '姓名': ['张三', '李四', '王五', '赵六'],
        '年龄': [25, 30, 35, 28],
        '城市': ['北京', '上海', '广州', '深圳'],
        '薪资': [8000, 12000, 15000, 10000]
    }
    
    # 创建 DataFrame
    df = pd.DataFrame(data)
    
    # 将 DataFrame 写入字节缓冲区
    excel_buffer = BytesIO()
    df.to_excel(excel_buffer, index=False, engine='openpyxl')
    
    # 获取字节串
    excel_bytes = excel_buffer.getvalue()
    excel_buffer.close()
    
    return excel_bytes

# 使用示例
excel_data = create_excel_as_bytes_pandas()
print(f"生成的Excel字节串长度: {len(excel_data)}")
print(f"前100字节: {excel_data[:100]}")

# 保存到文件验证（可选）
with open('output_pandas.xlsx', 'wb') as f:
    f.write(excel_data)

多工作表示例

import pandas as pd
from io import BytesIO

def create_multi_sheet_excel():
    # 创建多个 DataFrame
    df1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
    df2 = pd.DataFrame({'X': ['a', 'b', 'c'], 'Y': ['d', 'e', 'f']})
    df3 = pd.DataFrame({'ID': [101, 102, 103], 'Value': [10.5, 20.3, 30.7]})
    
    # 写入字节缓冲区
    excel_buffer = BytesIO()
    
    with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
        df1.to_excel(writer, sheet_name='Sheet1', index=False)
        df2.to_excel(writer, sheet_name='Sheet2', index=False)
        df3.to_excel(writer, sheet_name='数据表', index=False)
    
    excel_bytes = excel_buffer.getvalue()
    excel_buffer.close()
    
    return excel_bytes

# 使用
multi_sheet_data = create_multi_sheet_excel()

---

方法二：使用 openpyxl 直接操作

安装依赖

pip install openpyxl

实现代码

from openpyxl import Workbook
from io import BytesIO

def create_excel_openpyxl():
    # 创建工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "员工信息"
    
    # 添加表头
    headers = ['姓名', '年龄', '部门', '工资']
    ws.append(headers)
    
    # 添加数据
    data = [
        ['张三', 28, '技术部', 8500],
        ['李四', 32, '销售部', 9200],
        ['王五', 25, '市场部', 7800],
        ['赵六', 35, '人事部', 8800]
    ]
    
    for row in data:
        ws.append(row)
    
    # 设置列宽
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 8
    ws.column_dimensions['C'].width = 12
    ws.column_dimensions['D'].width = 10
    
    # 保存到字节缓冲区
    excel_buffer = BytesIO()
    wb.save(excel_buffer)
    excel_bytes = excel_buffer.getvalue()
    excel_buffer.close()
    
    return excel_bytes

# 使用示例
excel_data = create_excel_openpyxl()

带样式的 Excel

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from io import BytesIO

def create_styled_excel():
    wb = Workbook()
    ws = wb.active
    
    # 定义样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    center_alignment = Alignment(horizontal="center", vertical="center")
    
    # 添加带样式的表头
    headers = ['产品名称', '季度销量', '增长率', '销售额']
    ws.append(headers)
    
    # 应用表头样式
    for col in range(1, len(headers) + 1):
        cell = ws.cell(row=1, column=col)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_alignment
    
    # 添加数据
    data = [
        ['产品A', 1500, '15%', 750000],
        ['产品B', 2300, '22%', 1150000],
        ['产品C', 890, '8%', 445000],
        ['产品D', 3100, '35%', 1550000]
    ]
    
    for row in data:
        ws.append(row)
    
    # 设置数字格式
    for row in range(2, len(data) + 2):
        ws.cell(row=row, column=4).number_format = '"¥"#,##0'
    
    excel_buffer = BytesIO()
    wb.save(excel_buffer)
    return excel_buffer.getvalue()

---

方法三：使用 xlsxwriter（功能强大）

安装依赖

pip install XlsxWriter

实现代码

import xlsxwriter
from io import BytesIO

def create_excel_xlsxwriter():
    # 创建字节缓冲区
    excel_buffer = BytesIO()
    
    # 创建工作簿
    workbook = xlsxwriter.Workbook(excel_buffer)
    
    # 创建工作表
    worksheet = workbook.add_worksheet('销售数据')
    
    # 定义格式
    header_format = workbook.add_format({
        'bold': True,
        'font_color': 'white',
        'bg_color': '#366092',
        'align': 'center',
        'valign': 'vcenter'
    })
    
    money_format = workbook.add_format({'num_format': '¥#,##0'})
    percent_format = workbook.add_format({'num_format': '0.0%'})
    
    # 写入表头
    headers = ['月份', '销售额', '增长率', '利润']
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    # 写入数据
    data = [
        ['1月', 500000, 0.15, 150000],
        ['2月', 600000, 0.20, 180000],
        ['3月', 750000, 0.25, 225000],
        ['4月', 800000, 0.067, 240000]
    ]
    
    for row, row_data in enumerate(data, start=1):
        worksheet.write(row, 0, row_data[0])
        worksheet.write(row, 1, row_data[1], money_format)
        worksheet.write(row, 2, row_data[2], percent_format)
        worksheet.write(row, 3, row_data[3], money_format)
    
    # 设置列宽
    worksheet.set_column('A:A', 10)
    worksheet.set_column('B:D', 12)
    
    # 添加图表
    chart = workbook.add_chart({'type': 'column'})
    chart.add_series({
        'name': '销售额',
        'categories': '=销售数据!$A$2:$A$5',
        'values': '=销售数据!$B$2:$B$5',
    })
    chart.set_title({'name': '月度销售额'})
    worksheet.insert_chart('F2', chart)
    
    # 关闭工作簿（重要：必须先关闭才能获取字节数据）
    workbook.close()
    
    # 获取字节串
    excel_bytes = excel_buffer.getvalue()
    excel_buffer.close()
    
    return excel_bytes

---

方法四：使用 pyexcel（简单易用）

安装依赖

pip install pyexcel pyexcel-xlsx

实现代码

import pyexcel as pe
from io import BytesIO

def create_excel_pyexcel():
    # 创建数据
    data = [
        ['姓名', '科目', '分数'],
        ['张三', '数学', 95],
        ['张三', '英语', 87],
        ['李四', '数学', 88],
        ['李四', '英语', 92]
    ]
    
    # 写入字节缓冲区
    excel_buffer = BytesIO()
    pe.save_as(array=data, dest_file_obj=excel_buffer)
    
    excel_bytes = excel_buffer.getvalue()
    excel_buffer.close()
    
    return excel_bytes

---

完整的工具函数封装

from io import BytesIO
import pandas as pd
from openpyxl import Workbook
import xlsxwriter

class ExcelCreator:
    @staticmethod
    def create_from_dataframe(df_dict, sheet_names=None):
        """
        从字典创建多工作表Excel
        df_dict: {'sheet1': df1, 'sheet2': df2}
        """
        buffer = BytesIO()
        
        with pd.ExcelWriter(buffer, engine='openpyxl') as writer:
            if sheet_names is None:
                sheet_names = df_dict.keys()
            
            for sheet_name, df in zip(sheet_names, df_dict.values()):
                df.to_excel(writer, sheet_name=sheet_name, index=False)
        
        return buffer.getvalue()
    
    @staticmethod
    def create_from_list(data_list, headers=None, sheet_name='Sheet1'):
        """
        从列表数据创建Excel
        data_list: [[1,2,3], [4,5,6]] 或 [{'a':1}, {'a':2}]
        """
        buffer = BytesIO()
        
        if headers is None and data_list and isinstance(data_list[0], dict):
            headers = list(data_list[0].keys())
        
        if headers and data_list and isinstance(data_list[0], dict):
            # 字典列表转DataFrame
            df = pd.DataFrame(data_list)
        elif headers:
            # 添加表头
            data_with_header = [headers] + data_list
            df = pd.DataFrame(data_with_header[1:], columns=data_with_header[0])
        else:
            df = pd.DataFrame(data_list)
        
        df.to_excel(buffer, sheet_name=sheet_name, index=False)
        return buffer.getvalue()

# 使用示例
creator = ExcelCreator()

# 从DataFrame字典创建
df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'X': ['a', 'b'], 'Y': ['c', 'd']})
excel_bytes = creator.create_from_dataframe({'表1': df1, '表2': df2})

# 从列表创建
data = [['Alice', 25], ['Bob', 30], ['Charlie', 35]]
headers = ['姓名', '年龄']
excel_bytes2 = creator.create_from_list(data, headers, '员工信息')

---

主要特点对比

方法	优点	缺点	适用场景
​pandas​	简单易用，数据处理强	依赖较多	数据分析、快速导出
​openpyxl​	格式控制精细，支持样式	API 较繁琐	需要复杂格式的报表
​xlsxwriter​	功能最强大，支持图表	只写不读	需要图表、复杂格式
​pyexcel​	接口统一，简单易用	功能相对简单	简单数据导出

选择哪种方法取决于你的具体需求：

• 快速导出数据 → ​pandas​
• 需要精细控制格式 → ​openpyxl​ 或 ​xlsxwriter​
• 简单需求 → ​pyexcel