Python读写Excel

发表于 更新于 Disqus：

在日常生活中时长面临操作大量Excel文件的情况, 如何有效的利用Python脚本来实现复杂的操作呢? 本文旨在介绍python中操作Excel文件的几个包: xlrd(读), xlwt(写), openpyxl(读,写), 以及pandas.

注意: 利用xlrd和xlwt操作Excel时, 只有读和写, 没有修改这一选项, 如果想要修改某个单元格, 需要将原有数据读出, 修改过后, 重新写入新的Excel文件.

xlrd 适用于任何版本的Excel文件, Python 2.7, 3.4+.

xlwt 适用于 MS Excel 97/2000/XP/2003 XLS files, Python 2.6, 2.7, 3.3+ (xlwt无法将文件保存为.xlsx文件, 且不能写入超过65535行,256列的数据).

openpyxl 适用于Excel 2010 xlsx/xlsm/xltx/xltm, Python >=3.6.

pandas 是进行数据分析和处理的常用包, 处理Excel只是其功能的很小一部分. 在利用其处理Excel时, 其依赖xlrd包, 需要安装xlrd后, 方可利用pandas 处理Excel. pandas读取Excel并处理的本质是将Excel数据读至DataFrame 进行操作, 本文所涉及的均是DataFrame 的常用操作.

Excel中的基本概念

工作簿: Excel 文件的另一种名称.

工作表: 又叫Worksheet, 一个工作簿可以包含多个工作表. 如下图中的 “微信”, “头条” 和”sheet2”均是一个Worksheet, 默认的Worksheet为Sheet1.

单元格: Worksheet的组成部分, Excel中存储数据的最小单元.

单元格地址: 单元格在工作表上的地址, 由字母和数字构成, 下图单元格地址为D28.

Excel

xlrd-读Excel

导入相关包 

import xlrd

打开工作簿(xlrd只能以只读形式打开Excel文件)

data=xlrd.open_workbook(filename)

查看所有工作表名称

data.sheet_names()

选择某个工作表

#获取第一个工作表
table=data.sheets()[0]

#通过索引获取第一个工作表
table=data.sheet_by_index(0)

#通过表名称选择工作表
table=data.sheet_by_name(u'微信')

获取当前工作表的行数和列数

nrows=table.nrows
ncols=table.ncols

获取某行和某列的值

table.row_values(number)
table.col_values(number)

查看某个单元格的值

#直接定位
cell=table.cell(0,0).value

#通过行来检索
cell=table.row(0)[0].value

#通过列来检索
cell=table.col(0)[0].value

求取上面图片中文章总数之和(共计144)

1
2
3
4
5
6
7
8
import xlrd
filename='example.xlsx'
data=xlrd.open_workbook(filename)
table=data.sheet_by_name(u'微信')
total=0
for value in table.col_values(0)[1:]:
    total+=int(value)
print(total)

xlwt-写Excel

导入相关包 

import xlwt

新建Excel文件

data=xlwt.Workbook()

新建工作表

table=data.add_sheet('sheet1')

写入数据到单元格

table.write(0,0,u'文章总数')

注意: 如果对同一个单元格重复操作, 会导致异常”Exception: Attempt to overwrite cell:”, 如果想对单元格重复操纵, 需要在新建工作表时指定cell_overwrite_ok=True

table=data.add_sheet('sheet1',cell_overwrite_ok=True)

设置单元格样式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#初始化样式
style=xlwt.XFStyle()

#创建字体
font=xlwt.Font()

#指定字体
font.name='Times New Roman'

#指定字号为11, 其中20为单位
font.height=11 * 20

#指定颜色0-63, 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...

font.colour_index = 0

#字体加粗, 下划线 underline, 斜体 italic
font.bold=True

#将该font设定为style的字体
style.font=font

#创建背景色图案
pattern = xlwt.Pattern()

#指定图案, 图案有[NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12]
pattern.pattern = xlwt.Pattern.SOLID_PATTERN 

#指定图案颜色
pattern.pattern_fore_colour = 5

#将该pattern设定为style的pattern
style.pattern = pattern # Add Pattern to Style

#写入到文件时使用该样式
table.write(0,2,'test 哈哈哈',style)

完整程序代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
import xlwt

data=xlwt.Workbook()
table=data.add_sheet('sheet1',cell_overwrite_ok=True)
#初始化样式
style=xlwt.XFStyle()

#创建字体
font=xlwt.Font()

#指定字体
font.name='Times New Roman'

#指定字号为11, 其中20为单位
font.height=11 * 20

#指定颜色0-63, 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...

font.colour_index = 0

#字体加粗, 下划线 underline, 斜体 italic
font.bold=True

#将该font设定为style的字体
style.font=font

#创建背景色图案
pattern = xlwt.Pattern()

#指定图案, 图案有[NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12]
pattern.pattern = xlwt.Pattern.SOLID_PATTERN 

#指定图案颜色
pattern.pattern_fore_colour = 5

#将该pattern设定为style的pattern
style.pattern = pattern # Add Pattern to Style

#写入到文件时使用该样式
table.write(0,2,'test 哈哈哈',style)

table.write(0,0,u'文章总数')
total=144
table.write(0,1,total)
data.save('xxx.xls')

openpyxl - 读写Excel

导入相关包 

from openpyxl import load_workbook
from openpyxl import Workbook

加载(创建)工作簿

wb = load_workbook('sample.xlsx') #加载
wb = Workbook() #创建

查看所有工作表

wb.sheetnames
wb.get_sheet_names()

激活活动的工作表

#默认激活最后创建的工作表
ws = wb.active

激活任意工作表

ws=wb.get_sheet_by_name("Sheet name")

查看工作表行列数目

ws.max_row
ws.max_column

查看单元格数据

# 使用WorkSheet的Cell方法
cell = ws.cell('A1')
cell = ws.cell(row=1, column=1)
# 通过坐标获取Cell
cell = ws['A1']
# 获取多个
cell = ws['A1:E5']  # 返回多行数据,类型为tuple

查看某行(列)数据
#查看第一列数据, 其中min_row 最小值为1, 当min_row=0时, 结果与min_row=1一致. min_col=1, max_col=1表示第一列(Excel中计数从1开始)
for data in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=1):
print(data[0].value)

#查看第二行数据,  min_row=2, max_row=2 表示第二行
for data in ws.iter_cols(min_col=1, max_col=ws.max_column, min_row=2, max_row=2): 
    print(data[0].value)

修改(赋值)单元格数据

# 使用WorkSheet的Cell方法
ws.cell(row=1, column=1, value=10)
ws['A1']=10

# 设置Cell对象value属性
cell = ws.cell('A1')
cell.value = 100

#在最后一行追加一行
ws.append([1, 2, 3])

#修改完毕后, 需要保存
wb.save('filename.xlsx')

设置单元格样式

#导入字体颜色包
from openpyxl.styles import Font, colors

#创建样式, 字号15, 字体Times New Roman,  颜色红色, 斜体, 加粗, 下划线{'double', 'single', 'doubleAccounting', 'singleAccounting'} #这里下划线样式较多, 是因为 openpyxl支持Excel的最新版本, 与xlwt不同
ft = Font(size=15, name='Times New Roman',color=colors.RED, italic=True, bold=True, underline='double')

#给单元格赋值样式
ws['A1']=10
ws['A1'].font=ft

#给单独某行(列)单元格赋值样式(对已经有数据的单元格无效, 打开Excel自行插入有效)
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")

修改样式切勿直接赋值, 需要重新创建Font实例, 方能修改成功, 也可通过复制其他样式实例进行修改

# 错误操作
ws['A1'].font.size=20

# 正确操作, 创建实例修改
ws['A1'].font=Font(size=20)

# 拷贝font, 并赋值
ftcopy=ft.copy(size=20)
ws['A1'].font=ftcopy

求取上面图片中文章总数之和(共计144), 并将结果写入该文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
from openpyxl import load_workbook
from openpyxl.styles import Font, colors
filename='example.xlsx'

wb = load_workbook(filename)

ws=wb.get_sheet_by_name(u'微信')

total=0
for data in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=1): 
    total+=int(data[0].value)
print(total)

ft = Font(size=15, name='Times New Roman',color=colors.RED, italic=True, bold=True, underline='double')
ws['A20']=total
ws['A20'].font=ft

ws.append(['文章总计',total])

wb.save(filename)

pandas - 读写Excel

注意, 在pandas中 数字0,1,…既可以表示索引, 也可以表示表头项. 在指定表头项且表头项不为数字时, 数字0,1表示索引, 否则表示表头项.

索引和表头项对比

1
2
3
4
5
6
7
8
import pandas as pd
filename='example.xlsx'
df=pd.ExcelFile(filename)
df =df.parse('微信')
print(df)
data=df.ix[0].values
print('-----------')
print(data)

表头项
索引

导入相关包 

import  pandas  as pd

打开工作簿(read_excel为打开Excel函数, ExcelFile为一个类, 其功能能为强大)

#默认读取Excel的第一个工作表
df=pd.read_excel(filename)

#通过sheet_name指定工作表
df=pd.read_excel(filename,sheet_name='微信')

#通过索引读取指定工作表, 索引顺序由左到右0, 1, ...
df=pd.read_excel(filename,sheet_name=0)

#混合方式读取多个工作表
df=pd.read_excel(filename,sheet_name=['微信',2])

#使用ExcelFile打开工作表, 同样适用索引, 或者混合模式
df=pd.ExcelFile(filename).parse('微信')

查看所有工作表名称

df=pd.ExcelFile(filename)
df.sheet_names

#选择工作表
df=df.parse('微信')

读取指定行列

#0表示第一行或 项目为0的那行, 
data=df.ix[0].values

#读取多行, 需要传入一个list
data=df.ix[[1,2]].values

#读取指定列
data=df['文章总数'].values

#读取多列
data=df[['文章总数', '文章总增量']].values

#读取指定单元格
data=df.ix[1,2]
data=df['文章总数'][0]

求取上面图片中文章总数之和, 并存入文件

1
2
3
4
5
6
7
8
9
10
11
12
import  pandas  as pd
filename='example.xlsx'
df=pd.ExcelFile(filename)
df =df.parse('微信')
data=sum(df['文章总数'].values)
xx=['' for i in range(df.shape[1])]
xx[1]=data
xx[0]='文章总数'
df.loc[df.shape[0]]=xx

df.to_excel('new.xlsx')