Python办公自动化：用xlwt、openpyxl、xlutils等库轻松写入Excel数据

xlwt

概述

xlwt库是一个python用于操作Excel的第三方库。它的主要功能是用来写入Excel。通常会与xlrd 、 xlutils组合进行使用。它可以用于生成新的Excel 文件，并支持多种格式设置。

注意事项:

文件格式：xlwt仅支持.xls格式，不支持.xlsx格式。如果需要处理.xlsx 文件，可以使用openpyxl。

限制：xlwt对 Excel 文件的行数和列数有一定限制（最多 65536 行和 256 列）。

格式设置：xlwt还支持单元格格式设置，如字体、颜色、边框等，可以通过xlwt.XFStyle() 来实现。

安装xlwt库

首先确保安装xlwt

pip install xlwt

基本使用

创建一个工作簿对象和一个工作表对象，然后使用sheet.write(row, col, value)方法在指定的单元格中写入数据，最后将工作簿保存到文件中。

import xlwt

# 创建一个工作簿
workbook = xlwt.Workbook()

# 添加一个工作表
sheet = workbook.add_sheet('Sheet1')

# 写入数据
sheet.write(0, 0, 'Hello')  # 第一行第一列
sheet.write(0, 1, 'World')  # 第一行第二列
sheet.write(1, 0, 123)       # 第二行第一列
sheet.write(1, 1, 456)       # 第二行第二列

# 保存文件
workbook.save('test.xls')

更具体使用示例如下：

import xlwt

# 创建一个工作簿
workbook = xlwt.Workbook()

# 创建一个工作表
sheet = workbook.add_sheet('Sheet1')

# 写入标题
sheet.write(0, 0, '姓名')  # A1
sheet.write(0, 1, '年龄')  # B1
sheet.write(0, 2, '城市')  # C1

# 写入数据
data = [
    ['Alice', 30, 'New York'],
    ['Bob', 25, 'Los Angeles'],
    ['Charlie', 35, 'Chicago']
]

for row_index, row_data in enumerate(data, start=1):
    for col_index, value in enumerate(row_data):
        sheet.write(row_index, col_index, value)

# 保存工作簿
workbook.save('test.xls')

设置单元格样式

import xlwt

# 创建一个工作簿
workbook = xlwt.Workbook()

# 添加一个工作表
sheet = workbook.add_sheet('Styled Sheet')

# 创建一个样式
style = xlwt.XFStyle()
style.font.bold = True  # 设置字体为粗体
style.alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中

# 写入数据并应用样式
sheet.write(0, 0, 'Header 1', style)  # 第一行第一列
sheet.write(0, 1, 'Header 2', style)  # 第一行第二列

# 写入普通数据
sheet.write(1, 0, 'Row 1, Col 1')
sheet.write(1, 1, 'Row 1, Col 2')

# 保存文件
workbook.save('styled.xls')

合并单元格

import xlwt

# 创建一个工作簿
workbook = xlwt.Workbook()

# 添加一个工作表
sheet = workbook.add_sheet('Merged Cells')

# 创建一个样式
style = xlwt.XFStyle()
style.font.bold = True  # 设置字体为粗体
style.alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中

# 合并单元格
sheet.write_merge(0, 0, 0, 1, 'Merged Header', style)  # 合并第一行第一列和第二列

# 写入其他数据
sheet.write(1, 0, 'Row 2, Col 1')
sheet.write(1, 1, 'Row 2, Col 2')

# 保存文件
workbook.save('merged.xls')

添加公式

import xlwt

# 创建一个工作簿
workbook = xlwt.Workbook()

# 添加一个工作表
sheet = workbook.add_sheet('Formulas')

# 写入数据
sheet.write(0, 0, 5)  # A1
sheet.write(0, 1, 10)  # B1

# 添加公式
sheet.write(0, 2, xlwt.Formula('A1 + B1'))  # C1 = A1 + B1

# 保存文件
workbook.save('formula.xls')

openpyxl

概述

openpyxl是一个用于读写Excel 文件（.xlsx 格式）的Python 库。它功能强大，支持多种Excel操作，包括创建、修改和读取Excel文件，尤其适用于需要处理复杂Excel文件的场景。

主要功能:

读取和写入Excel文件：支持打开现有的Excel文件并进行修改，也可以创建新的Excel文件。

支持多种数据类型：可以处理字符串、数字、日期等多种数据类型。

单元格格式化：支持对单元格进行各种格式设置，包括字体、颜色、边框、对齐方式等。

图表支持：可以在 Excel 中创建和修改图表。

公式支持：可以在单元格中写入公式并计算结果。

支持图像：可以在 Excel 文件中插入图像。

注意事项:

文件格式：openpyxl支持.xlsx格式，不支持.xls格式。

性能：在处理大量数据时，openpyxl性能较好，但在某些情况下可能会比其他库（如pandas）慢。

复杂功能：对于需要处理复杂 Excel 文件（如图表、公式等）的用户，openpyxl 提供了丰富的功能和灵活性。

安装

要使用 openpyxl，首先需要安装它。

pip install openpyxl

基本使用

首先导入openpyxl，选择创建一个新的工作簿或加载一个现有的工作簿，通过名称或索引选择工作表，使用sheet.cell(row, column, value)方法在指定的单元格中写入数据，最后将工作簿保存到文件中

import openpyxl

# 创建一个新的工作簿
workbook = openpyxl.Workbook()
# 或者加载一个现有的Excel文件
# workbook = openpyxl.load_workbook('test.xlsx')

# 选择活动工作表
sheet = workbook.active
sheet.title = 'Sheet1'
# 或者通过名称选择工作表
# sheet = workbook['Sheet1']

# 写入标题
sheet['A1'] = '姓名' # 第一行第一列
sheet['B1'] = '年龄' # 第一行第二列
sheet['C1'] = '城市'

# 写入数据
data = [
    ['Alice', 30, 'New York'],
    ['Bob', 25, 'Los Angeles'],
    ['Charlie', 35, 'Chicago']
]

for row_index, row_data in enumerate(data, start=2):
    for col_index, value in enumerate(row_data, start=1):
        sheet.cell(row=row_index, column=col_index, value=value)

# 保存工作簿
workbook.save('test.xlsx')

设置单元格样式

import openpyxl
from openpyxl.styles import Font, Alignment

# 创建一个工作簿
workbook = openpyxl.Workbook()

# 选择活动工作表
sheet = workbook.active
sheet.title = 'Styled Sheet'

# 创建样式
bold_font = Font(bold=True, color='FF0000')  # 粗体红色字体
center_alignment = Alignment(horizontal='center')  # 水平居中

# 写入数据并应用样式
sheet['A1'] = 'Header 1'
sheet['A1'].font = bold_font
sheet['A1'].alignment = center_alignment

sheet['B1'] = 'Header 2'
sheet['B1'].font = bold_font
sheet['B1'].alignment = center_alignment

# 写入普通数据
sheet['A2'] = 'Row 2, Col 1'
sheet['B2'] = 'Row 2, Col 2'

# 保存文件
workbook.save('styled.xlsx')

合并单元格

import openpyxl

# 创建一个工作簿
workbook = openpyxl.Workbook()

# 选择活动工作表
sheet = workbook.active
sheet.title = 'Merged Cells'

# 合并单元格
sheet.merge_cells('A1:B1')  # 合并 A1 和 B1
sheet['A1'] = 'Merged Header'  # 设置合并单元格的值

# 写入其他数据
sheet['A2'] = 'Row 2, Col 1'
sheet['B2'] = 'Row 2, Col 2'

# 保存文件
workbook.save('merged.xlsx')

添加公式

import openpyxl

# 创建一个工作簿
workbook = openpyxl.Workbook()

# 选择活动工作表
sheet = workbook.active
sheet.title = 'Formulas'

# 写入数据
sheet['A1'] = 5  # A1
sheet['B1'] = 10  # B1

# 添加公式
sheet['C1'] = '=A1 + B1'  # C1 = A1 + B1

# 保存文件
workbook.save('formula.xlsx')

设置列宽和行高

import openpyxl

# 创建一个工作簿
workbook = openpyxl.Workbook()

# 选择活动工作表
sheet = workbook.active
sheet.title = 'Adjust Size'

# 设置列宽
sheet.column_dimensions['A'].width = 10  # 设置 A 列宽度为 10
sheet.column_dimensions['B'].width = 30  # 设置 B 列宽度为 30

# 设置行高
sheet.row_dimensions[1].height = 40  # 设置第一行高度为 40

# 写入数据
sheet['A1'] = '1111111111'
sheet['B1'] = '222222222222222222'

# 保存文件
workbook.save('size_adjusted.xlsx')

添加图表

使用 openpyxl 可以轻松地将图表添加到 Excel 文件中。

import openpyxl
from openpyxl.chart import BarChart, Reference

# 创建一个工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = 'Chart Example'

# 写入数据
data = [
    ['Month', 'Sales'],
    ['January', 100],
    ['February', 120],
    ['March', 140],
]
for row in data:
    sheet.append(row)

# 创建图表
chart = BarChart()
chart.title = "Monthly Sales"
chart.x_axis.title = "Month"
chart.y_axis.title = "Sales"

# 指定数据范围
data_ref = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=4)
categories_ref = Reference(sheet, min_col=1, min_row=2, max_row=4)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)

# 将图表添加到工作表
sheet.add_chart(chart, "D2")

# 保存文件
workbook.save('chart_example.xlsx')

条件格式化

可以根据单元格的值设置条件格式，以便更直观地显示数据。

import openpyxl
from openpyxl.styles import PatternFill

# 创建一个工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = 'Conditional Formatting'

# 写入数据
data = [10, 20, 30, 40, 50]
for i, value in enumerate(data, start=1):
    sheet[f'A{i}'] = value

# 设置条件格式
fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
for cell in sheet['A']:
    if cell.value > 30:
        cell.fill = fill  # 如果值大于30，填充为红色

# 保存文件
workbook.save('conditional_formatting.xlsx')

插入图片

可以将图片插入到 Excel 文件中。

需要安装Pillow

pip install Pillow

import openpyxl
from openpyxl.drawing.image import Image

# 创建一个工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = 'Image with Size'

# 插入图片
img = Image('test.png')  # 替换为你的图片路径

# 设置图片大小
img.width = 300  # 设置宽度为300
img.height = 200  # 设置高度为200

# 在指定单元格插入图片
sheet.add_image(img, 'A1')  # 在 A1 单元格插入图片

# 保存文件
workbook.save('image_example.xlsx')

自动调整列宽

可以根据内容自动调整列宽。

import openpyxl

# 创建一个工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = 'Auto Fit Columns'

# 写入数据
data = ['Short', 'A bit longer', 'The longest cell in this column']
for i, value in enumerate(data, start=1):
    sheet[f'A{i}'] = value

# 自动调整列宽
column_length = max(len(value) for value in data) + 2  # +2 为了留白
sheet.column_dimensions['A'].width = column_length

# 保存文件
workbook.save('auto_fit_columns.xlsx')

读写结合使用

可以结合使用xlrd和openpyxl，实现从一个 Excel 文件中读取数据，然后将其写入另一个文件。

import openpyxl
import xlrd

# 读取.xls 文件
workbook = xlrd.open_workbook('test.xls')
sheet = workbook.sheet_by_index(0)

# 创建一个新的.xlsx文件
new_workbook = openpyxl.Workbook()
new_sheet = new_workbook.active
new_sheet.title = 'Sheet1'

# 将读取的数据写入新的 .xlsx 文件
for row in range(sheet.nrows):
    for col in range(sheet.ncols):
        new_sheet.cell(row=row + 1, column=col + 1, value=sheet.cell_value(row, col))

# 保存新的文件
new_workbook.save('output.xlsx')

xlutils

xlutils是一个用于处理Excel文件的Python库，主要用于与xlrd和xlwt库配合使用。它的主要功能是提供对Excel文件的读取和写入操作，特别是在需要对现有的Excel文件进行修改时。

主要功能:

复制工作簿：可以将现有的Excel文件复制到新的工作簿中，以便进行修改。

读取和写入：结合xlrd和xlwt，xlutils可以实现对Excel文件的读取和写入操作。

数据处理：可以在读取数据后对其进行处理，然后将结果写回到Excel文件中。

注意事项:

xlutils 只支持 .xls 格式的文件，不支持 .xlsx 格式。如果你需要处理 .xlsx 文件，建议使用 openpyxl。

xlutils 依赖于 xlrd 和 xlwt，因此在使用之前需要确保这两个库已经安装。

安装

要使用xlutils，需要确保已安装xlrd和xlwt。可以使用以下命令进行安装：

pip install xlrd xlwt xlutils

写入数据

import xlwt

# 创建一个工作簿
workbook = xlwt.Workbook()

# 添加一个工作表
sheet = workbook.add_sheet('Sheet1')

# 写入数据
sheet.write(0, 0, 'A')  # 第一行第一列
sheet.write(0, 1, 'B')  # 第一行第二列
sheet.write(1, 0, 123)       # 第二行第一列
sheet.write(1, 1, 456)       # 第二行第二列

# 保存文件
workbook.save('test.xls')

使用xlutils

# 从 xlutils.copy 导入 copy 函数，用于复制 Excel 工作簿
from xlutils.copy import copy
# 导入 xlrd 库，用于读取 Excel 文件
import xlrd
# 导入 xlwt 库，用于写入 Excel 文件
import xlwt

# 打开名为 'test.xls' 的 Excel 文件，并保留格式信息
tem_excel = xlrd.open_workbook('test.xls', formatting_info=True)
# 选择 Excel 文件中的第一个工作表
tem_sheet = tem_excel.sheet_by_index(0)

# 复制打开的 Excel 工作簿，以便进行写入操作
new_excel = copy(tem_excel)
# 获取复制后的工作簿中的第一个工作表
new_sheet = new_excel.get_sheet(0)


# 创建一个新的样式对象
style = xlwt.XFStyle()

# 创建字体对象并设置属性
font = xlwt.Font()
font.name = '微软雅黑'  # 设置字体为微软雅黑
font.bold = True  # 设置字体为粗体
font.height = 100  # 设置字体高度
style.font = font  # 将字体应用到样式

# 创建边框对象并设置边框样式
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN  # 设置上边框为细线
borders.bottom = xlwt.Borders.THIN  # 设置下边框为细线
borders.left = xlwt.Borders.THIN  # 设置左边框为细线
borders.right = xlwt.Borders.THIN  # 设置右边框为细线
style.borders = borders  # 将边框应用到样式

# 创建对齐对象并设置对齐方式
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中对齐
alignment.vert = xlwt.Alignment.VERT_CENTER  # 垂直居中对齐
style.alignment = alignment  # 将对齐方式应用到样式

# 在指定单元格写入新数据，并应用样式
new_sheet.write(1, 0, 'New Data', style)  # 在第二行第一列写入 'New Data'

# 保存修改后的新 Excel 文件为 'output.xls'
new_excel.save('output.xls')