12. Python操作excel

excel在Python中的应用

存测试数据

有的时候大批量的数据，存到txt文件里显然不是最佳的方式，我们可以存到excel文件里面，第一方便我们存储数据和做数据，一方面方便我们读取数据，比较明朗。测试的时候就从数据库中读取出来。

存测试结果

可以批量把结果存到excel中，也比较好整理数据，比txt方便。

excel库

python中有好几个和excel操作相关的模块。

xlrd库：从excel中读取数据。支持xls，xlsx。
xlwt库：对excel进行修改操作。不支持对xlsx格式的修改。
xlutils：在xlwt和xlrd中，对一个已存在的文件进行修改。
openpyxl：主要针对xlsx格式的excel文件进行读取和编辑。

官方解释：
openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

网址：

http://www/python-excel.org/

或直接pip安装：

pip3 install openpyxl

离线方式安装:

Excel 中三大对象：

WorkBook
Sheet
Cell
此外记住row：行；column：列。
在excel中，主要的操作是在sheet中对数据进行读、写、修改的操作。

操作流程：

打开/创建一个excel文件，生成一个工作簿（WorkBook）对象。
获取某个表单（sheet）对象。
在表单对象中的单元格（cell）中读、写、修改数据。

openpyxl - 常用读操作

from openpyxl import load_workbook  #加载load_workbook函数
wb=load_workbook("sample.xlsx") #打开一个已经存在的excel文件
sh=wb.get_sheet_by_name("sheet1")   #打开某个表单
rows=sh.max_row #获取总行数
columns=sh.max_column   #获取总列数
cl=sh.cell(row=3,column=7).value    #获取指定的（第3行，第7列）的单元格值
sh.cell(row=1,column=1).value="hello"   #修改某个值
wb.save("hello.xlsx")   #保存workbook，这样才可以保存刚刚写入的值。

以下尝试进行excel数据读取：

from openpyxl import load_workbook
path="C:/Users/Hello/Desktop/temp/demo.xlsx"

#1.打开工作簿
wb=load_workbook(path)

#2.定位到所要操作的表单
sheet_obj=wb.get_sheet_by_name("lemon")

#3.读取数据,索引从1,1开始
result=sheet_obj.cell(1,2).value

print(result)

D:Pythonpython.exe D:/Work/Tools/python_workspace/python_2017/class_excel/test_1.py
D:/Work/Tools/python_workspace/python_2017/class_excel/test_1.py:9: DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
  sheet_obj=wb.get_sheet_by_name("lemon")
B2

可以看到，读取出demo.xlsx中lemon页（1,2）单元格数据为B2。

更新单元格值：

#4.修改数据，更新值
sheet_obj.cell(1,2).value="B2_2"    #更新第一行第二列的单元格值
sheet_obj.cell(4,8).value="test"    #更新第四行第八列的单元格值
wb.save("C:/Users/Hello/Desktop/temp/demo_1.xlsx")
#更新数据后，记得保存文件为另外的名字，类似于手动另存为。

获取行数与列数：

#5 获取行数与列数（这里获取到的是具有数据的行与列，不会获取到空行和空列）：
row=sheet_obj.max_row
column=sheet_obj.max_column
print(row,column)

openpyxl - 常用写操作

from openpyxl import Workbook   加载Workbook函数
wb=Workbook("hello.xlsx")   #创建一个新的excel对象
sh_new=wb.create_sheet("新建的sheet")  #创建一个新的sheet
wb.save("hello.xlsx")   #保存当前workbook

#再次打开workbook才能对值进行修改，此处操作略
sh.cell(row=1,column=1).value=("hello") #修改指定单元格值
wb.save("hello.xlsx")   #保存当前workbook中所有的修改

注意： openpyxl中列和行的起始标识是1，但是别的操作数据的未必如此哦！

__author__ = 'Hello'
from openpyxl import Workbook   #加载Workbook函数
wb=Workbook("hello.xlsx")   #创建一个新的excel对象
sh_new=wb.create_sheet("sh_0624")  #创建一个新的sheet
wb.save("hello.xlsx")   #保存当前workbook

#再次打开workbook才能对值进行修改
from openpyxl import load_workbook
wb=load_workbook("hello.xlsx")
sheet_obj=wb.get_sheet_by_name("sh_0624")
sheet_obj.cell(row=1,column=1).value=("sh_0624") #修改指定单元格值
wb.save("hello.xlsx")   #保存当前workbook中所有的修改

小练习：循环读取excel表格中第一列的数据。

__author__ = 'Hello'
from openpyxl import Workbook,load_workbook

path="C:/Users/Hello/Desktop/temp/test1.xlsx"

wb=load_workbook(path)
sheet_obj=wb.get_sheet_by_name("Sheet1")
row=sheet_obj.max_row
column=sheet_obj.max_column

list=[]
for i in range(1,row+1):
    result=sheet_obj.cell(i,1).value
    #print(result)
    list.append(result)

print(list)

D:Pythonpython.exe D:/Work/Tools/python_workspace/python_2017/class_excel/test_3.py
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]
D:/Work/Tools/python_workspace/python_2017/class_excel/test_3.py:7: DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
range(0, 5)
  sheet_obj=wb.get_sheet_by_name("Sheet1")

Process finished with exit code