python-读写Excel(一)-xlrd库、openpyxl库

目录

读取Excel中的数据

Book文件对象

Sheet表单对象

表单对象的属性

读取指定单元格文本内容

读取指定行所有单元格的内容

读取指定列所有单元格的内容

新建Excel文件和写入数据

修改Excel文件数据

修改单元格内容

插入行和列、删除行和列

文字的颜色、字体、大小

背景色

插入图片

---

读取Excel中的数据

读取Excel中的数据需要使用xlrd库，安装：pip install xlrd

Xlrd库中的open_workbook函数打开Excel文件，返回的是一个Book对象，这个对象代表的是打开Excel文件，再打开文件后就是对文件中的表单数据的操作了。

Book文件对象

获取Book对象中包含的表单数量：使用对象的nsheets方法

获取Book对象中包含的表单名：使用对象的sheet_names方法（获取所有表单名，并存放到列表中）

import xlrd

# 打开文件open_workbook方法，参数名为要打开的文件位置及文件名
ex1 = xlrd.open_workbook("income.xlsx")
# 打印打开文件的类型
print(type(ex1))
# 获取文件对象的表单数量,文件对象的nsheets方法
print(ex1.nsheets)
# 获取文件对象中的所有表单名并将其存放到列表中，文件对象的sheet_names方法
print(ex1.sheet_names())

  

Sheet表单对象

表单对象的所有属性及方法参考官方文档：https://xlrd.readthedocs.io/en/latest/api.html#xlrd-sheet

获取表单对象：使用文件对象的  sheet_by_index  或  sheet_by_name及sheets  方法

# 获取表单对象，使用文件对象的sheet_by_index或sheet_by_name及sheets方法
sh1 = ex1.sheet_by_index(0)  # 使用sheet_by_index方法时参数需指明表单的索引
sh2 = ex1.sheet_by_name('2018')  # 使用sheet_by_name方法时参数需指明表单的名称
sh = ex1.sheets()  #使用sheets方法获得文件对象的所有表单对象并将其存放在列表中
print(f'{sh}\n{sh1}\n{sh2}')

  

表单对象的属性

表单名：表单对象的name方法

表单索引：number方法

表单行数：nrows方法

表单列数：ncols方法

print(f'sh1的表单信息：表单索引{sh1.number}，表单名{sh1.name}，表单行数{sh1.nrows}，表单列数{sh1.ncols}')

  

读取指定单元格文本内容

在获得表单对象后，使用表单对象的  cell_value  方法读取指定单元格中的文本内容，参数为指定行和列的索引，索引从0开始。使用cell_value方法获得数字对象为浮点数。

import xlrd

ex2 = xlrd.open_workbook('income.xlsx')
sheet1 = ex2.sheet_by_name('2018')
# 通过使用表单对象的cell_value方法获取对应的表格信息内容，参数为指定的列和行的索引，索引从0开始
# 获取第2行，第2列内容
print(sheet1.cell_value(rowx=1,colx=1 ))

  

读取指定行所有单元格的内容

使用表单对象的  row_values  方法读取指定行所有单元格的内容，参数为行的索引，获取到的内容将存放在一个列表中返回。row_len可以获得指定行的长度。

  

# 获取指定行的所有单元格内容
row1 = sheet1.row_values(0)
print(row1)

  

读取指定列所有单元格的内容

使用表单对象的  col_values  方法读取指定列所有单元格的内容，参数为列的索引，获得的内容存存放到列表中返回。

# 获取指定列的所有单元格内容
row1 = sheet1.col_values(1)
print(f'返回第二列的所有数据{row1}')

   可以通过指定获取数据开始和结束行的索引参数指定多个连续的单元格中的数据

row1 = sheet1.col_values(1,start_rowx=1,end_rowx=4)
print(f'返回第二列2到5行的所有数据{row1}')
print(f'对获得数据求和{sum(row1)}')

通过循环的方法对需要的数据进行查找处理很方便

新建Excel文件和写入数据

新建Excel文件和写入数据需要使用openpyxl库，openpyxl库也支持读文件和修改文件，（openpyxl库仅支持Office2007新版本，对于Office2003老版本的xls格式的Excel文档不支持）。Xlrd库对于新版本和老版本的都支持，但不支持写文件和修改文件。如需读写和修改老版本可以将Excel文件先手动转换成新版本。

openpyxl库安装：pip install openpyxl

import openpyxl

# 创建一个Excel对象,使用openpyxl中的Workbook类方法，注意大写为类方法
cel = openpyxl.Workbook()
# 通过上面实例化对象后，会自动产生一个名为sheet的表单对象，可以通过实例化对象的active方法获取
sh1 = cel.active
# 修改获取到的表单对象并改名，使用表单对象的tiller方法赋值
sh1.title = '甘肃'
# 增加表单，使用文件对象的create_sheet方法，不指定索引时为增加到最后
sh2 = cel.create_sheet('宁夏')
# 增加表单对象时可以通过指定索引决定表单在文件中的顺序
sh3 = cel.create_sheet('陕西',1)
# 可以通过名称获取文件对象中对应的表单对象，使用类似于字典
GS = cel['甘肃']
# 给指定单元格中添加信息
GS['A1'] = '兰州市'
# 获取某个指定单元格的内容，使用单元格的value方法
print(GS['A1'].value)
# 根据行号和列号写入和读取表单内容，使用表单对象的cell方法，参数为行号和列号，不同于xlrd中的索引从0开始，行号和列号从1开始
sh1.cell(2,1).value = '天水市'
# 获取指定单元格内容也可以使用表单对象的行号和列号
print(sh1.cell(1,1).value)
# 要想使创建或写入的文件或内容生效就需要用文件对象的save方法,参数为文件名及文件类型后缀.xlsx
cel.save('中国各省土地信息.xlsx')

修改Excel文件数据

修改已存在的Excel文件，使用openpyxl库。

修改单元格内容

使用openpyxl的load_workbook方法打开一个要修改的文件对象，找到要修改的表单对象后，对表单对象中的单元格内容重新赋值，操作完成后需要使用文件对象的save方法对文件进行保存才能生效。保存时指定的文件名和打开的文件名不一致时会另存为别的文件，这样可以防止保证原来的数据表格不动的情况下复制一份后再修改。

import openpyxl

cel2 = openpyxl.load_workbook('中国各省土地信息.xlsx')
sheet = cel2['甘肃']
sheet['A2'] = '平凉市'
cel2.save('中国各省土地信息-副表.xlsx')

插入行和列、删除行和列

使用表单对象的插入方法：插入行insert_rows和插入列insert_cols

使用表单对象的删除方法：删除行delete_rows和删除列delete_cols

import openpyxl

cel2 = openpyxl.load_workbook('中国各省土地信息-副表.xlsx')
sheet = cel2['甘肃']
sheet.delete_rows(2)    # 删除第2行
sheet.delete_cols(3,2)    # 删除从第三列开始的两列
cel2.save('中国各省土地信息-副表.xlsx')

删除行列与插入行列方法都一样，括号中指定的参数中，第一个表示要删除的行(列)号，第二个表示要删除的行(列)数，不指定删除的行列数，默认只删除一行。

文字的颜色、字体、大小

单元格中的样式风格(颜色、字体、大小、下划线等)都是通过Font和colors对象设置的，需要使用openpyxl.styles中Font和colors。使用单元格对象的font方法

import openpyxl

from openpyxl.styles import Font,colors
cel2 = openpyxl.load_workbook('中国各省土地信息-副表.xlsx')
sheet = cel2['甘肃']
# 指定要修改的单元格修改
sheet['A1'].font = Font(color=colors.RED, #使用预置的颜色常量
                        size=15,    # 设定文字大小
                        bold=True,  # 设定为粗体
                        italic=True # 设定为斜体
                        )
# 也可以使用RGB数字表示的颜色
sheet['B1'].font = Font(color="981818")
cel2.save('中国各省土地信息-副表.xlsx')

  

背景色

需要导入openpyxl.styles中PatternFill对象，使用单元格对象的fill方法。

import openpyxl

from openpyxl.styles import Font,colors,PatternFill
cel2 = openpyxl.load_workbook('中国各省土地信息-副表.xlsx')
sheet = cel2['甘肃']
# 指定要修改的单元格修改
sheet['A1'].fill = PatternFill("solid", "E39191")
cel2.save('中国各省土地信息-副表.xlsx')

  

插入图片

插入图片需要导入openpyxl.drawing.image中的Image对象

import openpyxl
from openpyxl.drawing.image import Image

cel2 = openpyxl.load_workbook('中国各省土地信息-副表.xlsx')
sheet = cel2['甘肃']
# 使用单元格对象的add_image方法，参数为图片位置及图片名(格式后缀)和插入的表单单元格位置
sheet.add_image(Image(r'C:\Users\Administrator\Desktop\t01d909190723b23054.jpg'),'B4')
cel2.save('中国各省土地信息-副表.xlsx')

运行时报错：

  

在命令行中输入pip install pil，结果如下，需要安装pillow：pip install pillow（老版本python只支持pil，新版本需要安装pillow）