前言
不知道大家的朋友圈有没有被python自动化办公刷屏,本来不想学习的,最近的诺安真的是直线下跌,为了配合股票爬取后的的数据处理,就了解一下通过Python程序自动化Excel的数据处理。点击链接跳转参考原文
读取Excel中的数据
xlrd 库—— 读取 Excel文件里面的数据
安装xlrd库命令如下:
pip install xlrd
如果人工处理Excel,我们就需要打开对相应的.xlsx文件,进入对应的sheet表单,操作相应的单元格。用python就需要按照如下步骤:
- 打开文件,获取sheet表单名
import xlrd
#打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
#获取 Excel 文件中表单(sheet) 的数量和所有表单(sheet) 的名字
print(f"包含表单数量 {book.nsheets}")
print(f"表单的名分别为: {book.sheet_names()}")
运行结果如下:
包含表单数量 3
表单的名分别为: ['2018', '2017', '2016']
- 打开工作簿后,获取对应表单
# 表单索引从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}")
运行结果如下:
表单名:2018
表单索引:0
表单行数:13
表单列数:2
- 获取表单对象后,读取指定单元格的内容
- 使用cell_value方法,参数为行号和列号,读取指定单元格的内容。如下所示:
import xlrd
#打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
# 表单索引从0开始,获取第一个表单对象
sheet = book.sheet_by_index(0)
#读取指定单元格的内容
# 行号、列号都是从0开始计算
print(f"单元格A1内容是: {sheet.cell_value(rowx=0, colx=0)}")
运行结果如下:
单元格A1内容是: 月份
- 使用 row_values 方法,参数为行号,读取指定行所有单元格的内容,存放在一个列表中返回。
import xlrd
#打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
# 表单索引从0开始,获取第一个表单对象
sheet = book.sheet_by_index(0)
#读取指定单元格的内容
# 行号、列号都是从0开始计算
print(f"第一行内容是: {sheet.row_values(rowx=0)}")
运行结果:
第一行内容是: ['月份', '收入']
- 使用 col_values 方法,参数为列号,读取指定列所有单元格的内容,存放在一个列表中返回。
import xlrd
#打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
# 表单索引从0开始,获取第一个表单对象
sheet = book.sheet_by_index(0)
#读取指定单元格的内容
# 行号、列号都是从0开始计算
print(f"第一列内容是: {sheet.col_values(colx=0)}")
运行结果:
第一列内容是: ['月份', 1.0, 2.0, '3*', 4.0, 5.0, 6.0, '7*', 8.0, 9.0, 10.0, 11.0, 12.0]
到此为止,打开xlsx文件,获取表单对象,读取指定单元格就完成了。下面就可以对获取的单元格进行简单的数据处理了,比如计算2018年全年的收入就可以使用如下的代码:
import xlrd
#打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
# 表单索引从0开始,获取第一个表单对象
#sheet = book.sheet_by_index(0)
sheet = book.sheet_by_name('2018')
#读取第二列的收入内容,并赋给incomes
# 行号、列号都是从0开始计算
incomes = sheet.col_values(colx=1,start_rowx=1)
print(f"2018年收入为: {sum(incomes)}")
运行结果:
2018年收入为: 361302.0
其实,学习python处理Excel并不是为了完成这么简单的求和,我们需要的是利用python,快速完成有条件的求和。如:**在汇总2018年收入时,如何排除包含星号的月份收入呢?**下面使用的思路是,用总收入减去包含星号的月份收入。
import xlrd
#打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
# 表单索引从0开始,获取第一个表单对象
#sheet = book.sheet_by_index(0)
sheet = book.sheet_by_name('2018')
#读取第二列的收入内容,并赋给incomes
# 行号、列号都是从0开始计算
incomes = sheet.col_values(colx=1,start_rowx=1)
#之前那段代码,运行结果有以为小数点,这里使用int进行了转换
print(f"2018年的账面收入为: {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"2018年真实收入为: {int(sum(incomes)- toSubstract)}")
运行结果:
2018年的账面收入为: 361302
3* 30105.0
7* 30109.0
2018年真实收入为: 301088
如果要继续得到3年的收入,就要获取所有的sheet对象,采用上面的计算方法,最后把收入相加。
import xlrd
# 打开income.xlsx文件
book = xlrd.open_workbook("income.xlsx")
# 获取表单对象
# sheet = book.sheet_by_index(0) #表单索引从0开始,获取第一个表单对象
# sheet = book.sheet_by_name('2018') #通过sheet名获取表单对象
sheets = book.sheets() # 得到所有sheet对象
# 给三年收入赋初值
income_of_3years = 0
for sheet in sheets:
# 读取第二列的收入内容,并赋给incomes
# 行号、列号都是从0开始计算
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}年真实收入为: {int(sum(incomes) - actualincome)}")
income_of_3years += actualincome
print(f'三年全部收入为{income_of_3years}')
运行结果:
3* 30105.0
7* 30109.0
2018年真实收入为: 60214
6* 20774.0
2017年真实收入为: 20774
9* 19374.0
2016年真实收入为: 19374
三年全部收入为742032
新建excel,写入数据
xlrd库——只能读取Excel内容,不能写入数据
openpyxl库——新建Excel并写入数据
**openpyxl 库既可以读文件、也可以写文件、也可以修改文件。**但是,openpyxl 库不支持老版本 Office2003 的 xls 格式的Excel文档,如果要读写xls格式的文档,可以使用 Excel 进行相应的格式转化。
既然要使用这个库,那就执行下列命令安装
pip install openpyxl
如果我们想创建前面income这样的表格,怎么操作呢?代码如下:
import openpyxl
# 创建一个Excel workbook 对象
book = openpyxl.Workbook()
# 创建时,会自动产生一个sheet,通过active获取
sh = book.active
# 修改当前 sheet 标题为'2017年'
sh.title = '2017年'
# 保存文件
book.save('income_1.xlsx')
# 增加一个名为 '2016' 的sheet,放在最后
sh1 = book.create_sheet('2016')
# 增加一个名为'2019'sheet,放在最前
sh2 = book.create_sheet('2019',0)
# 增加一个名为'2018'sheet,指定为第2个表单
sh3 = book.create_sheet('2018',1)
# 根据名称获取某个sheet对象,这里获取2019
sh = book['2019']
# 给A1单元格写入内容'月份',给B1单元格写入内容'收入'
sh['A1'] = '月份'
sh['B1'] = '收入'
# 获取某个单元格内容,这里获取A1和A2的单元格内容
print(sh['A1'].value)
print(sh['B1'].value)
# 根据行号列号, 给单元格写入内容
# 注意和 xlrd 不同,是从 1 开始
sh.cell(2,1).value = '1'
sh.cell(2,2).value = '30200'
# 根据行号列号, 获取某个单元格内容
print(sh.cell(2, 1).value)
print(sh.cell(2, 2).value)
book.save('income_1.xlsx')
运行结果如下:
月份
收入
1
30200
下面的示例代码 将 保存在字典中的收入的内容 写入到excel文件中
import openpyxl
month_income = {
'1' : 30103,
'2' : 30104,
'3' : 30105,
'4' : 30106,
'5' : 30107,
'6' : 30108,
'7' : 30109,
'8' : 30110,
'9' : 30111,
'10' : 30112,
'11' : 30113,
'12' : 30114,
}
# 创建一个Excel workbook 对象
book = openpyxl.Workbook()
# 创建时,会自动产生一个sheet,通过active获取
sh = book.active
# 修改当前 sheet 标题为'2018'
sh.title = '2018'
# 写标题栏
sh['A1'] = '月份'
sh['B1'] = '收入'
# 写入内容
row = 2
for month,income in month_income.items():
sh.cell(row, 1).value = month
sh.cell(row, 2).value = income
row += 1
# 保存文件
book.save('信息.xlsx')
运行结果:
如果数据在一个列表或者元组中,可以使用append方法在sheet的末尾添加新行,写入数据,比如
import openpyxl
month_income = [
['1' , 30103],
['2' , 30104],
['3*' , 30105],
['4' , 30106],
['5' , 30107],
['6' , 30108],
['7*' , 30109],
['8' , 30110],
['9' , 30111],
['10' , 30112],
['11' , 30113],
['12' , 30114]
]
# 创建一个Excel workbook 对象
book = openpyxl.Workbook()
# 创建时,会自动产生一个sheet,通过active获取
sh = book.active
# 修改当前 sheet 标题为'2018'
sh.title = '2018'
# 写标题栏
sh['A1'] = '月份'
sh['B1'] = '收入'
for row in month_income:
# 添加到下一行的数据
sh.append(row)
# 保存文件
book.save('信息.xlsx')
修改Excel中的数据
openpyxl库——修改已经存在的Excel 文件
比如修改income.xlsx文件
修改单元格内容
import openpyxl
# 加载 excel 文件
wb = openpyxl.load_workbook('income.xlsx')
# 得到sheet名为'2018'的对象
sheet = wb['2018']
#修改A1,B1单元格为英文
sheet['A1'] = 'month'
sheet['B1'] = 'income'
# 指定不同的文件名,可以另存为别的文件
wb.save('income-1.xlsx')
运行结果如下:
插入行、插入列
insert_rows ——插入行
insert_cols —— 插入列
import openpyxl
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 在第2行的位置插入1行
sheet.insert_rows(2)
# 在第3行的位置插入3行
sheet.insert_rows(3,3)
# 在第2列的位置插入1列
sheet.insert_cols(2)
# 在第2列的位置插入3列
sheet.insert_cols(2,3)
#指定不同的文件名,可以另存为别的文件
wb.save('income-1.xlsx')
运行结果如下:
删除行、删除列
delete_rows——删除行
delete_cols ——删除列
import openpyxl
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 在第2行的位置删除1行
sheet.delete_rows(2)
# 在第3行的位置删除3行
sheet.delete_rows(3,3)
# 在第2列的位置删除1列
sheet.delete_cols(2)
# 在第3列的位置删除3列
sheet.delete_cols(3,3)
## 指定不同的文件名,可以另存为别的文件
wb.save('income-1.xlsx')
运行结果如下:
文字 颜色、字体、大小
单元格里面的 样式风格 (包括 颜色、字体、大小、下划线 等) 都是通过 Font 对象设定的
import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import Font,colors
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 指定单元格字体颜色(这个地方报错了,说是RED不存在)
#sheet['A1'].font = Font(color=colors.RED, #使用预置的颜色常量
# size=15, # 设定文字大小
# bold=True, # 设定为粗体
# italic=True # 设定为斜体
# )
# 也可以使用RGB数字表示的颜色
sheet['B1'].font = Font(color="981818")
# 指定整行 字体风格, 这里指定的是第3行
font = Font(color="981818")
for y in range(1, 100): # 第 1 到 100 列
sheet.cell(row=3, column=y).font = font
# 指定整列 字体风格, 这里指定的是第2列
font = Font(bold=True)
for x in range(1, 100): # 第 1 到 100 行
sheet.cell(row=x, column=2).font = font
wb.save('income-1.xlsx')
运行结果如下:
背景色
import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import PatternFill
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 指定 某个单元格背景色
sheet['A1'].fill = PatternFill("solid", "E39191")
# 指定 整行 背景色, 这里指定的是第2行
fill = PatternFill("solid", "E39191")
for y in range(1, 100): # 第 1 到 100 列
sheet.cell(row=2, column=y).fill = fill
wb.save('income-1.xlsx')
插入图片
插入图片需要安装第三方库pillow,否则报错
pip install Pillow
import openpyxl
from openpyxl.drawing.image import Image
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 在第1行,第3列 的位置插入图片
sheet.add_image(Image('1.png'), 'C1')
## 指定不同的文件名,可以另存为别的文件
wb.save('income-1.xlsx')
运行结果如下:
后记
学这点东西,完全不够用呀,文献插图用origin不香吗?除非进一步深入学习!