[Python操作Excel指南：读取与写入完整手册]

概要

在数据处理和分析中，读取和写入Excel文件是常见而重要的任务。Python提供了多种库和方法，使得这些操作变得轻松高效。下面是一个简要的指南，介绍了在数据处理和分析中使用Python进行Excel读写的常见方法。

写入内容

其中的三种常用方法：
方法一：使用openpyxl库
安装openpyxl库
首先，确保你已经安装了openpyxl库。如果没有安装，可以通过以下命令进行安装：

pip install openpyxl

创建和保存Excel文件

import openpyxl

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

# 获取默认的工作表
sheet = workbook.active

# 写入数据
sheet['A1'] = '姓名'
sheet['B1'] = '年龄'

# 添加一行数据
sheet.append(['Alice', 25])

# 保存工作簿
workbook.save('example_openpyxl.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

数据读写和格式化

import pandas as pd

# 读取Excel文件
df = pd.read_excel('example.xlsx')

# 打印数据框内容
print(df)
1
2
3
4
5
6
7

上述代码使用pandas库的read_excel函数，方便地读取Excel文件内容，并将数据存储在一个DataFrame中。

遍历和操作单元格是 Excel 文件处理中常见的任务，使用 openpyxl 库可以轻松实现这些操作。以下是一些关于遍历和操作单元格的代码示例：
遍历A列的值

# 假设 sheet 是你的工作表对象
for cell in sheet['A']:
    print(cell.value)
1
2
3

这段代码遍历了 A 列的所有单元格，并打印出它们的值。

设置单元格格式

# 设置单元格 A1 的字体大小为14，加粗，并添加黄色填充
sheet['A1'].font = openpyxl.styles.Font(size=14, bold=True)
sheet['A1'].fill = openpyxl.styles.PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
1
2
3

这段代码设置了单元格 A1 的字体大小和粗细，同时添加了黄色填充。
创建图表

from openpyxl.chart import BarChart, Reference

# 创建柱状图
chart = BarChart()

# 设置图表数据范围
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=5)
categories = Reference(sheet, min_col=1, min_row=2, max_row=5)

# 添加数据到图表
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# 将图表添加到工作表，位置在 E5 单元格
sheet.add_chart(chart, "E5")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

这段代码使用 openpyxl 的图表模块创建了一个柱状图，并将其数据范围设置为工作表中的一部分，最后将图表添加到工作表中。
合并和拆分单元格

# 合并 B2 到 C2 单元格
sheet.merge_cells('B2:C2')

# 设置合并后单元格的值
sheet['B2'] = 'Merged Cells'

# 拆分单元格
sheet.unmerge_cells('B2:C2')
1
2
3
4
5
6
7
8

这段代码合并了 B2 到 C2 单元格，并设置了合并后单元格的值，然后又拆分了这些单元格。

批量处理数据

# 假设成绩数据在第二列，从第二行到最后一行
grades = [sheet.cell(row=i, column=2).value for i in range(2, sheet.max_row + 1)]

# 计算平均分
average_grade = sum(grades) / len(grades)

# 输出结果
print(f"平均分: {average_grade}")
1
2
3
4
5
6
7
8

方法二：使用pandas库

import pandas as pd

# 创建一个DataFrame
data = {'姓名': ['Bob', 'Alice'], '年龄': [30, 25]}
df = pd.DataFrame(data)

# 将DataFrame写入Excel文件
df.to_excel('example_pandas.xlsx', index=False)
1
2
3
4
5
6
7
8

方法三：使用xlwt

import xlwt

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

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

# 写入数据
sheet.write(0, 0, '姓名')
sheet.write(0, 1, '年龄')
sheet.write(1, 0, 'Bob')
sheet.write(1, 1, 30)

# 保存工作簿
workbook.save('example_xlwt.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

或者是：
写入列表数据

import xlwt

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

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

# 要写入的列表数据
data_list = ['Alice', 25, 'Bob', 30, 'Charlie', 35]

# 写入列表数据到Excel表格
for index, value in enumerate(data_list):
    sheet.write(index, 0, value)

# 保存工作簿
workbook.save('list_data_example.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这段代码创建了一个Excel表格，并将列表中的元素逐行写入Excel表格的第一列。

写入字典数据

import xlwt

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

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

# 要写入的字典数据
data_dict = {'Name': ['Alice', 'Bob', 'Charlie'],
             'Age': [25, 30, 35]}

# 写入字典数据到Excel表格
for col_num, key in enumerate(data_dict.keys()):
    sheet.write(0, col_num, key)
    for row_num, value in enumerate(data_dict[key]):
        sheet.write(row_num + 1, col_num, value)

# 保存工作簿
workbook.save('dict_data_example.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这段代码创建了一个Excel表格，并将字典中的键作为表头，值逐行写入Excel表格。

写入仓库数据

import xlwt
import numpy as np

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

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

# 要写入的仓库数据
data_array = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])

# 写入仓库数据到Excel表格
for row_num, row_data in enumerate(data_array):
    for col_num, value in enumerate(row_data):
        # 将numpy.int32类型转换为int类型
        sheet.write(row_num, col_num, int(value))

# 保存工作簿
workbook.save('array_data_example.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这段代码创建了一个Excel表格，并将仓库中的元素逐行写入Excel表格。

以上是三种常见的向Excel写入内容的方法，根据个人习惯和项目需求选择其中一种.

读取

方法二：使用openpyxl库

import openpyxl

# 打开Excel文件
workbook = openpyxl.load_workbook('example.xlsx')

# 获取默认的工作表
sheet = workbook.active

# 读取数据
for row in sheet.iter_rows(values_only=True):
    print(row)
1
2
3
4
5
6
7
8
9
10
11

这段代码使用openpyxl库打开Excel文件，并遍历工作表的每一行，将数据打印出来。
方法三：使用xlrd库

import xlrd

# 打开Excel文件
workbook = xlrd.open_workbook('example.xls')

# 获取默认的工作表
sheet = workbook.sheet_by_index(0)

# 读取数据
for row_num in range(sheet.nrows):
    row = sheet.row_values(row_num)
    print(row)
1
2
3
4
5
6
7
8
9
10
11
12

上述代码使用xlrd库打开Excel文件，并遍历工作表的每一行，将数据打印出来。
方法四：使用pyexcel库

import pyexcel

# 读取Excel文件
data = pyexcel.get_array(file_name='example.xlsx')

# 打印数据
for row in data:
    print(row)
1
2
3
4
5
6
7
8

这段代码使用pyexcel库的get_array函数读取Excel文件，并将数据打印出来。pyexcel提供了简单易用的接口，适用于快速读取数据的场景。

例子：
创建并写入示例数据到 Excel 文件

import pandas as pd

# 创建示例数据
data = {'姓名': ['Alice', 'Bob'], '年龄': [25, 30]}
df = pd.DataFrame(data)

# 将示例数据写入Excel文件
df.to_excel('example.xlsx', index=False)
1
2
3
4
5
6
7
8

读取已有的 Excel 文件并添加更多数据

import pandas as pd

# 读取已有的Excel文件
existing_data = pd.read_excel('example.xlsx')

# 添加更多的数据
new_data = {'姓名': ['Charlie', 'David'], '年龄': [28, 35]}
new_df = pd.DataFrame(new_data)

# 合并数据
combined_data = pd.concat([existing_data, new_df], ignore_index=True)

# 将合并后的数据写入Excel文件，不保存索引列
combined_data.to_excel('example_updated.xlsx', index=False)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

读取合并后的数据并绘制曲线图

import matplotlib.pyplot as plt

# 读取合并后的数据
combined_data = pd.read_excel('example_updated.xlsx')

# 提取姓名和年龄列
names = combined_data['姓名']
ages = combined_data['年龄']

# 绘制曲线图
plt.plot(names, ages, marker='o', linestyle='-')
plt.title('年龄曲线图')
plt.xlabel('姓名')
plt.ylabel('年龄')
plt.grid(True)
plt.show()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

小结

在数据处理和分析中，Excel文件的读取和写入是常见而重要的任务。Python为这些操作提供了多种库和方法，使得处理数据变得轻松高效。本指南介绍了使用pandas、openpyxl、xlrd、pyexcel等库的方法，涵盖了读取和写入Excel的常见场景。

写入内容：
    使用openpyxl库创建新的Excel工作簿，添加数据，并保存。
    使用pandas库创建DataFrame，然后将其写入Excel文件。
    使用xlwt和xlrd库创建工作簿和工作表，写入数据，然后保存。

读取Excel文件：
    使用pandas库的read_excel函数读取整个Excel文件或特定工作表。
    使用openpyxl库打开Excel文件，遍历工作表的每一行，读取数据。
    使用xlrd库打开Excel文件，遍历工作表的每一行，读取数据。
    使用pyexcel库的get_array函数快速读取Excel文件。
1
2
3
4
5
6
7
8
9
10