Python笔记——Python与Excel的交互，操作(读取、更改和存储)Excel电子表格

Excel简介

Excel是日常用于储存少量数据非常常用的工具，操作简单，并且较为美观。Excel拥有以下特征：

• 组织结构：电子表格应用的工作簿文件将单独的工作表管理起来，而工作表是管理单元格的工具。
• 数据：数据通常使用表格的形式存储，而单元格中包含的是具体的数据点（例如浮点数或字符串），可以为了显示的目的加入格式化信息（例如字体，颜色等），也可以加入某些计算机代码（例如，格点中的数据来自于数值计算的结果）
• 功能：给定单元格中存放的数据，你可以进行计算以及对数据进行操作，例如加和乘某个整数。
• 可视化：数据可以方便的进行可视化，例如使用饼图展示数据。
• 可编程：现代的电子表格应用允许较为灵活的编程，例如，通过Excel中内嵌的VBA。
• 可引用：完成功能或写入的主要工具是单元格引用，每个单元格有坐标（工作簿，工作表名称，列和行）来识别单元格。

以上就是Excel表格流行的一些原因。如果掌握了python工具，使用python操作Excel表格，可以完成更多的复杂操纵，便于数据处理以及复杂的可视化分析图表展示。

处理Excel的python库

xlrd和xlwt这两个库是比较流行且常用的，但是xlwt只能处理扩展名为.xls的文件。所以要处理扩展名为.xlsx的文件可以用xlsxwriter和OpenPvxl，下面依次介绍这几个库的使用。

编程环境

此处用的编程环境是anaconda3下的python3.7版本的jupyter notebook编程界面。

xlrd和xlwt

1.生成工作簿

# 载入相关库
import numpy as np
import pandas as pd
import xlwt

path="data/" #指定一个数据目录，用于后续存放文件的目录

# 生成一个有两个工作表（sheet表）的工作簿

wb=xlwt.Workbook() #建立workbook对象wb，在内存中生成一个工作簿（注*只是在内存中生成的，并没有真正的保存）
wb.add_sheet("first_sheet",cell_overwrite_ok="True") #在wb工作簿中生成一张工作簿，第一个参数是工作表的名称，第二个参数等于True是指覆盖之前的数据，等于False是指不覆盖之前的数据
ws_1=wb.get_sheet(0)#给第一张工作表命一个别名“ws_1”
ws_2=wb.add_sheet("second_sheet") #也可以在建工作表的时候直接命一个别名

# wb.get_active_sheet() 一个获取当前活动工作表的方法，即查看目前是哪个工作表在活动

# 分别给两个工作表写入数据

data=np.arange(1.0,65.0).reshape((8,8)) #生成一个8行8列的数组
for column in range(data.shape[0]): #外层循环是数组的列
    for row in range(data.shape[1]): #内层循环是数组的行
        ws_1.write(row,column,data[column,row]) #第一个工作表写入创建的数组
        ws_2.write(row,column,data[row,column]) #第二个工作表写入创建的数组的转置
wb.save(path+"workbook_1.xls") # 将工作表保存到数据目录中，并且命名为“workbook_1”

2.读取Excel表

#载入相关库
import numpy as np
import pandas as pd
import xlrd
book_1=xlrd.open_workbook("E:/python3/workbook_2.xlsx") #打开一个工作簿，并且赋予变量名为“book_1”，此时“book_1”是内存当中的一个工作簿
book_1.sheet_names() #用".sheet_name"方法查看工作簿中所有工作表的名称

# 有两种方法读取工作表，一个是指定工作表名称，另一个是指定第几张工作表
sheet_1=book_1.sheet_by_name("first_sheet") #读取名为“first_sheet”的工作表
sheet_2=book_1.sheet_by_index(1) #读取索引为1的工作表（即第二张工作表）
sheet_2.ncols,sheet_2.nrows #获取工作表的列数和行数

# 读取工作表的单元格
A1=sheet_1.cell(0,0) #读取第一行第一列单元格
A1.value #查看A1的值
# 获取单元格的类型
A1.ctype #例如查看A1单元格的类型，此时会返回一个数字，数字从0-6，每个数字代表不同的数据类型（见表1）

#访问工作表的整行或整列
sheet_1.row(2) #访问“first_sheet”工作表的第3行
sheet_1.col(2) #访问“first_sheet”工作表的第3列
sheet_2.col_values(1,start_rowx=2,end_rowx=5) #获取“second_sheet”工作表的第2列，从第3行开始到第6行结束的值
sheet_2.row_values(2,start_colx=3,end_colx=6) #获取“second_sheet”工作表的第3行，从第4列开始到第7列结束的值

#读取工作表所有的数据
data_1=pd.DataFrame() #创建一个空的DateFrame
for column in range(sheet_1.ncols):
    data_1[column]=sheet_1.col_values(column) #将工作表的每一列逐一添加到空的DateFrame中
data_1

  \  

$$表1查看工作表单元格数据类型时返回数字所代表的数据类型$$

Excel type	number	python type
empty	0	empty string
text	1	a unicode string
number	2	float
date	3	float
boolean	4	int(1=True,0=False)
error	5	int (代表excel内部代码)
blank	6	empty string (当且仅当 formatting info =True)

xlsxwriter和OpenPyxl

1.xlsxwriter生成工作簿

# 载入相关库
import numpy as np
import pandas as pd
import xlsxwriter
import os
os.chdir("路径") #更改工作路径（将生产的工作簿保存到指定路径中）
#生成一个包含两个工作表的工作簿
wb=xlsxwriter.Workbook(filename="workbook_2.xlsx") #建立workbook对象wb
ws_1=wb.add_worksheet("first_sheet") #建立第一个工作表，命名别名为"ws_1"
ws_2=wb.add_worksheet("second_sheet") #建立第二个工作表，命名别名为"ws_2"
#生成数据
data=np.arange(1,101).reshape((10,10)) #生成一个10x10的数组
#与之前的类似，分别给两个工作表写入数据
for column in range(data.shape[0]): #外层循环是数组的列
    for row in range(data.shape[1]): #内层循环是数组的行
        ws_1.write(row,column,data[column,row]) #第一个工作表写入创建的数组
        ws_2.write(row,column,data[row,column]) #第二个工作表写入创建的数组的转置
wb.close()

2.OpenPyxl生成工作簿

# 载入相关库
import numpy as np
import pandas as pd
import openpyxl as oxl

path="data/" #指定一个数据目录，用于后续存放文件的目录

wb=oxl.Workbook() #创建工作簿对象
ws_1=wb.create_sheet(title="工作表名称",index=0) #index=0 代表生成第一张工作表
data=np.arange(1,82).reshape((9,9)) #生成数据
for c in range(data.shape[0]):
    for r in range(data.shape[1]):
        ws_1.cell(row=r+1,column=c+1).value=data[c,r] #为什么cell（）中的参数要加1，因为row和column至少为1，而遍历是从0开始的。
wb.save(path+"workbook_3.xlsx") #保存文件，且命名为“workbook_3”

3.openpyxl读取工作簿

import numpy as np
import pandas as pd
import openpyxl as oxl

path="data/" #指定一个数据目录
wb=oxl.load_workbook(path+"workbook_3.xlsx") #读取工作簿
ws=wb.get_active_sheet() #获取当前活动的工作表

#获取单元格数据/工作表的切片
ws['B5'] #获取B列，第5行的单元格
ws['C8'].value #获取C列，第8行的数据
ws['D5':'D9'] #获取D列第5行到D列第9行的单元格

  \  

pandas直接读取和储存Excel

# 载入相关库
import pandas as pd

# 读工作簿的时候，需要一个工作表一个工作表的读取
wb_1=pd.read_excel("工作簿的文件路径","要读取的工作表名称",header=1) #读取excel文件,header=0是不将第一行作为字段名，header=1是将第一行作为字段名
wb_1 #查看数据

#将DataFrame格式的数据储存成Excel文件
wb_1.to_excel("保存的文件路径以及工作簿名称","工作表名称")

#用pandas将多个DataFrame格式的数据储存在Excel文件中，并且储存成不同的工作表
#先随机生产两个DataFrame格式的数据
data_1=pd.DataFrame(np.arange(1,65).reshape((8,8)))
data_2=pd.DataFrame(np.arange(1,82).reshape((9,9)))
wb_2=pd.ExcelWriter("要保存的路径以及工作簿名称")
data_1.to_excel(wb_2,"第一个工作表名称")
data_2.to_excel(wb_2,"第二个工作表名称")

用pandas的DataFrame格式与Excel交互的话，有一个缺点是，如果遇到数据量较大时，写入的速度是比减慢的，可以采用numpy.ndarray数组写入，速度会快很多。

免费的python库代替编写Excel脚本

目前有两个python库能使得我们可以将Excel当做一个灵活的GUI，使得python与Excel的交互更为灵活、强大。其中一个是商业化的DataNitro，这个库是需要收费的。还有一个是开源的python库，叫xlwings，这两个强大的python库，可以使我们利用python控制Excel，比如：自定义函数，作图等一系列操作，在金融行业较为常用，使得python的的分析能力更为强大。