读写Excel

我们经常需要导入数据到Excel、从Excel导出数据、对Excel中的数据进行处理。

如果要处理的数据量很大，人工操作非常费时间。

我们可以通过Python程序，自动化Excel的数据处理，帮我们节省大量的时间。

读取Excel中的数据

如果我们只是要读取 Excel文件里面的数据进行处理，可以使用 xlrd 这个库。

首先我们安装xlrd库，执行下面的命令

pip install xlrd==1.2.0

注意：xlrd 新版本只支持 xls 格式，所以我们这里指定安装 1.2.0 老版本，可以支持xlsx格式。

这个文件里面有 3 个表单，分别记录了2018、2017、2016年的月收入，如下所示

如果我们想用程序统计 2016、2017、2018 三年所有月收入的总和，但是不要包含打星号的那些月份。

怎么做？

一步步来，我们先学会如何用Python程序读取Excel单元格中的内容。

xlrd 库里面的 open_workbook 函数打开Excel文件，并且返回一个 Book对象，这个对象代表打开的 Excel 文件。

可以通过这个Book对象得到Excel文件的很多信息，比如获取 Excel 文件中表单(sheet) 的数量和所有表单(sheet) 的名字。

我们可以用如下代码，读取该文件中表单的数量和名称：

import xlrd

book = xlrd.open_workbook("income.xlsx")

print(f"包含表单数量 {book.nsheets}")
print(f"表单的名分别为: {book.sheet_names()}")

要读取某个表单里单元格中的数据，必须要先获取表单（sheet）对象。

可以根据表单的索引或者表单名获取表单对象，使用如下对应的方法

# 表单索引从0开始，获取第一个表单对象
book.sheet_by_index(0)

# 获取名为2018的表单对象
book.sheet_by_name('2018')

# 获取所有的表单对象，放入一个列表返回
book.sheets()

获取了表单对象后，可以根据其属性得到：

表单行数（nrows）
列数（ncols）
表单名（name）
表单索引（number）

代码如下：

import xlrd

book = xlrd.open_workbook("income.xlsx")

sheet = book.sheet_by_index(0)
print(f"表单名：{sheet.name} ")
print(f"表单索引：{sheet.number}")
print(f"表单行数：{sheet.nrows}")
print(f"表单列数：{sheet.ncols}")

#获取了表单对象后，可以使用 cell_value 方法，参数为行号和列号，读取指定单元格中的文本内容。如下所示：
# 行号、列号都是从0开始计算
print(f"单元格A1内容是: {sheet.cell_value(rowx=0, colx=0)}")
#运行结果输出

#单元格A1内容是: 月份


# 还可以使用 row_values 方法，参数为行号，读取指定行所有单元格的内容，存放在一个列表中返回。如下所示：
# 行号、列号都是从0开始计算
print(f"第一行内容是: {sheet.row_values(rowx=0)}")
#运行结果输出

#第一行内容是: ['月份', '收入']


#还可以使用 col_values 方法，参数为列号，读取指定列所有单元格的内容，存放在一个列表中返回。如下所示：
# 行号、列号都是从0开始计算
print(f"第一列内容是: {sheet.col_values(colx=0)}")
运行结果输出

第一列内容是: ['月份', 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0]

有了这些方法，我们就可以完成一些数据处理任务了。比如我们要计算 2017年全年的收入就可以这样

import xlrd

book = xlrd.open_workbook("income.xlsx")

sheet = book.sheet_by_name('2017')

# 收入在第2列
incomes = sheet.col_values(colx=1,start_rowx=1)

print(f"2017年收入为: {sum(incomes)}")

那么我们怎么在汇总收入中，去掉包含星号的月份收入呢？

就需要我们查出哪些月份是带星号的，不要统计在内。

参考下面的代码

import xlrd

book = xlrd.open_workbook("income.xlsx")

sheet = book.sheet_by_name('2017')

# 收入在第2列
incomes = sheet.col_values(colx=1,start_rowx=1)

print(f"2017年账面收入为: {int(sum(incomes))}")

# 去掉包含星号的月份收入
toSubstract = 0
# 月份在第1列
monthes = sheet.col_values(colx=0)

for row,month in enumerate(monthes):
    if type(month) is str and month.endswith('*'):
        income = sheet.cell_value(row,1)
        print(month,income)
        toSubstract += income

print(f"2017年真实收入为: {int(sum(incomes)- toSubstract)}")

最后，要得到3年的收入，就要获取所有的sheet对象，采用上面的计算方法，最后把收入相加。

如下所示：

import xlrd

book = xlrd.open_workbook("income.xlsx")

# 得到所有sheet对象
sheets = book.sheets()

incomeOf3years = 0
for sheet in sheets:
    # 收入在第2列
    incomes = sheet.col_values(colx=1,start_rowx=1)
    # 去掉包含星号的月份收入
    toSubstract = 0
    # 月份在第1列
    monthes = sheet.col_values(colx=0)

    for row,month in enumerate(monthes):
        if type(month) is str and month.endswith('*'):
            income = sheet.cell_value(row,1)
            print(month,income)
            toSubstract += income

    actualIncome = int(sum(incomes)- toSubstract)
    print(f"{sheet.name}年真实收入为: {actualIncome}")
    incomeOf3years += actualIncome

print(f'全部收入为{incomeOf3years}')

新建Excel，写入数据

xlrd 只能读取Excel内容，如果你要创建一个新的Excel并写入数据，可以使用 openpyxl 库。

openpyxl 库既可以读文件、也可以写文件、也可以修改文件。

但是，openpyxl 库不支持老版本 Office2003 的 xls 格式的Excel文档，如果要读写xls格式的文档，可以使用 Excel 进行相应的格式转化。

执行 pip install openpyxl 安装该库()

查看openpyxl参考文档

下面的代码，演示了 openpyxl 的一些基本用法。

    import openpyxl

    # 创建一个Excel workbook 对象
    book = openpyxl.Workbook()

    # 创建时，会自动产生一个sheet，通过active获取
    sh = book.active

    # 修改当前 sheet 标题为 工资表
    sh.title = '工资表'

    # 保存文件
    book.save('信息.xlsx')

    # 增加一个名为 '年龄表' 的sheet，放在最后
    sh1 = book.create_sheet('年龄表-最后')

    # 增加一个 sheet，放在最前
    sh2 = book.create_sheet('年龄表-最前',0)

    # 增加一个 sheet，指定为第2个表单
    sh3 = book.create_sheet('年龄表2',1)

    # 根据名称获取某个sheet对象
    sh = book['工资表']

    # 给第一个单元格写入内容
    sh['A1'] = '你好'

    # 获取某个单元格内容
    print(sh['A1'].value)

    # 根据行号列号， 给第一个单元格写入内容，
    # 注意和 xlrd 不同，是从 1 开始
    sh.cell(2,2).value = '白月黑羽'

    # 根据行号列号， 获取某个单元格内容
    print(sh.cell(1, 1).value)

    book.save('信息.xlsx')

下面的示例代码将保存在字典中的年龄表的内容写入到excel文件中

import openpyxl

name2Age = {
    '张飞' :  38,
    '赵云' :  27,
    '许褚' :  36,
    '典韦' :  38,
    '关羽' :  39,
    '黄忠' :  49,
    '徐晃' :  43,
    '马超' :  23,
}

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()

# 创建时，会自动产生一个sheet，通过active获取
sh = book.active

sh.title = '年龄表'

# 写标题栏
sh['A1'] =  '姓名'
sh['B1'] =  '年龄'

# 写入内容
row = 2

for name,age in name2Age.items():
    sh.cell(row, 1).value = name
    sh.cell(row, 2).value = age
    row += 1

# 保存文件
book.save('信息.xlsx')

如果你的数据在一个列表或者元组中，可以使用append方法在sheet的末尾添加新行，写入数据，比如

import openpyxl

name2Age = [
    ['张飞' ,  38 ] ,
    ['赵云' ,  27 ] ,
    ['许褚' ,  36 ] ,
    ['典韦' ,  38 ] ,
    ['关羽' ,  39 ] ,
    ['黄忠' ,  49 ] ,
    ['徐晃' ,  43 ] ,
    ['马超' ,  23 ]
]

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()
sh = book.active
sh.title = '年龄表'

# 写标题栏
sh['A1'] =  '姓名'
sh['B1'] =  '年龄'

for row in name2Age:
    # 添加到下一行的数据
    sh.append(row)

# 保存文件
book.save('信息.xlsx')

python

Excel

python excel

openpyxl python excel

xlrderror supported python excel

python pandas excel

openpyxl python问题excel

数据python excel

526互联

python 之读写Excel

读写Excel

读取Excel中的数据

新建Excel，写入数据