python怎样操作excel表格进行自动化

在日常办公中，Excel表格是我们经常会用到的工具。有时候需要批量处理、修改大量的数据，手动操作费时费力。而Python作为一种高效而且易学的语言，可以通过某些库来实现Excel表格的自动化处理。本文将介绍Python中常用的操作Excel的库以及实现自动化的方法。

操作Excel的库

Python中主要有三个操作Excel的库：xlwt、xlrd和openpyxl。xlwt用来写Excel文件，xlrd用来读Excel文件，而openpyxl则达到了读写Excel表格的完美统一，因为它不仅可以读写Excel数据，还能操作Excel图表、公式、条件格式等各种功能。

下面针对这三个库进行详细的介绍：

xlwt

xlwt是Python的Excel写入库，它可以生成xls格式的文件。

我们先看个简单的例子，将一个“Hello world”写入Excel中：

import xlwt

wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Sheet1') 
ws.write(0, 0, 'Hello world')
wb.save('hello_world.xls')

以上代码的执行结果是：生成一个名为hello_world.xls的Excel文件，并将‘Hello world’写入第一个单元格。

xlrd

xlrd是Python的Excel读取库，可以读取xls格式的文件。

以下代码是读取Excel表格的例子，首先需要安装xlrd库：

!pip install xlrd

import xlrd

book = xlrd.open_workbook('example.xls')
sheet = book.sheet_by_index(0)

for row_index in range(sheet.nrows):
    for col_index in range(sheet.ncols):
        print(sheet.cell(row_index, col_index).value)

运行以上代码后，会逐行打印出Excel表格中每一个单元格的值。

openpyxl

openpyxl是一个支持xlsx格式的Excel操作库，更加先进和强大。它支持Excel文件的读取和创建，数据写入等。

以下示例代码建立了一个空的xlsx文件，并向”sheet1″写入一些数据：

!pip install openpyxl

from openpyxl import Workbook

# 新建Workbook对象
wb = Workbook()

# 获取当前sheet
ws = wb.active

# 指定写入元素的位置，‘A1’表示第一行第一列
ws['A1'] = 'this is the first demo'

# 新增一行，写入一些数据
ws.append([1, 2, 3])

# 存储，指定文件名称
wb.save('example.xlsx')

以上代码，生成了一个example.xlsx文件，并在”sheet1″中写入了一些数据。

自动化处理Excel

在实际应用中，我们通常需要对Excel表格进行大量的数据处理。一些自动化技巧可以让我们用更短的时间完成这些繁琐的工作。本文将介绍以下几个自动化处理Excel的技巧：

自动插入筛选

假设我们有一个销售表，需要对数据进行分类。我们可以通过在Excel中使用筛选功能实现分类。但当数据量非常大时，人工操作显然不现实。我们可以通过Python脚本来实现自动批量添加筛选。

from openpyxl import load_workbook

wb = load_workbook("data.xlsx")

ws = wb.active
ws.auto_filter.ref = ws.dimensions

wb.save("data_with_filter.xlsx")

以上代码实现了自动添加筛选的操作。通过load_workbook()加载excel，并设置当前sheet进行筛选，将其绑定到表格参考的范围中，最后调用Workbook对象中的save()将写入的修改保存到文件中。运行完毕后，文件data_with_filter.xlsx将添加自动筛选。

自动填充公式

有时候我们需要对一整列或整行的数据进行运算，例如求平均值或求和。在Excel中我们可以使用公式快速实现。但当数据量非常大时，也可以使用Python来自动填充公式。

以下代码实现了自动填充公式的操作。假设我们有一个名为data.xlsx的Excel表格，我们需要在最后一列中自动填充求和公式。

from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string
from openpyxl.utils.cell import coordinate_from_string

# 根据行和列号返回对应的单元格名称
def get_coordinate(row, column):
    return f"{column_index_from_string(column)}{row}"

wb = load_workbook("data.xlsx")

ws = wb.active

# 获取最后一列的列名
last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter

# 获取最后一行的行号
last_row = ws.max_row

# 待填充的公式
formula = f"SUM(A2:{last_col_letter}2)"

for row in range(2, last_row+1):
    # 获取填充公式的单元格名称
    coordinate = get_coordinate(row, last_col_letter)

    # 填充公式
    ws[coordinate] = f"={formula}"

wb.save("data_with_formula.xlsx")

以上代码中，我们首先获取Excel中最后一列的列名和最后一行的行号。然后根据待填充公式，通过公式中最后一列的列名计算出公式需要覆盖的区域。接着对每一行进行循环，并计算出当前行需要填充公式的单元格名称，最后将公式填充到该单元格中。运行完毕后，文件data_with_formula.xlsx将自动进行了求和操作。

自动格式化表格

数据格式化是Excel表格中经常需要处理的一个问题。在处理大量数据时，手动调整表格的格式十分耗费时间。下面是一个使用openpyxl库来自动调整Excel表格格式的例子：

from openpyxl.utils.dataframe import dataframe_to_rows

wb = load_workbook("data.xlsx")

ws = wb.active

# 进行数据格式转换
data = ws.values
columns = next(data)
df = pd.DataFrame(data, columns=columns)

# 设置列宽
for col in ws.columns:
    col_letter = col[0].column_letter
    col_width = max(len(str(cell.value)) for cell in col)
    ws.column_dimensions[col_letter].width = col_width

# 将数据写入表格
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

wb.save("data_formatted.xlsx")

以上代码中，我们首先将Excel表格转换成DataFrame，然后通过设置列宽的方法实现了自动调整Excel表格宽度的操作。最后将DataFrame中的数据写入Excel表格中，生成一个格式化的Excel文件，文件名为data_formatted.xlsx。

结论

本文介绍了Python中常用的操作Excel的三个库：xlwt、xlrd和openpyxl。同时针对新手的需要，本文也介绍了如何实现Excel表格的自动化处理，包括自动插入筛选、自动填充公式和自动格式化表格等几个具体的实现方法。希望本文能对Python进行Excel自动化处理的初学者有所帮助。

网络风向标