锐客网

  1. 首页 > 睿知 > it技术 > >

Python|python操作excel（xlrd、xlwt、xlwings）

Python


文章目录

  • 一、xlrd库
        • 1、工作簿（book）
          • (1) 创建工作簿对象
        • 2、工作表（sheet）
          • (1) 创建工作表对象
        • 3、单元格（range）
          • （1）获取单个单元格的值（两种方法）
          • （2）获取单行或单列的值
        • 4、获取工作表中的总行列数
  • 二、xlwt库
        • 1、工作簿（book）
          • （1）创建工作簿对象
          • （2）保存工作簿
        • 2、工作表(sheet)
          • （1）创建工作表对象
          • （2）写入数据
        • 3、XFStyle风格样式
          • （1）Font（字体样式）
          • （2）Borders（边界）
          • （3）Alignment（对齐）
          • （4）Pattern（模式）
        • 附加知识：设置行高列宽
  • 三、xlwings库
        • 1、Excel程序
          • （1）打开Excel程序
          • （2）退出Excel程序
        • 2、工作簿（book）
          • （1）创建工作簿对象
          • （2）保存工作簿
          • （3）关闭工作簿
          • （4）工作簿属性
        • 3、工作表（sheet）
          • （1）创建工作表对象
          • （2）批量创建工作表对象
          • （4）常用API
        • 4、单元格（range）
          • （1）创建单元格对象
          • （2）向单元格写入数据
          • （3) 读取表格中的数据
          • （4）获取有效内容的数据
          • （5）获取表格有效内容的最后位置
          • （6）灵活获取Range有效范围的内容
          • (7) 常用API
  • 附页

一、xlrd库 1、工作簿（book） (1) 创建工作簿对象
import xlrd workbook=xlrd.open_workbook("文件路径")

2、工作表（sheet） (1) 创建工作表对象 创建工作表的对象一般调用关于sheet的方法，一共有三种调用方式。
第一种：通过工作表索引创建
worksheet01=workbook.sheet_by_index(index)

第二种：通过工作表名称
worksheet02=workbook.sheet_by_name(sheet_name)

第三种：通过sheets()方法
worksheet03=workbook.sheets()[index] worksheet03=workbook.sheets()#可以创建批量sheet对象

实例与结果：
Python|python操作excel（xlrd、xlwt、xlwings）
文章图片

3、单元格（range） （1）获取单个单元格的值（两种方法） 第一种：通过调用cell()方法中的value属性
cell_value01=worksheet.cell(row,clo).value

第二种：通过调用cell_value()方法
cell_value02=worksheet.cell_value(rowx= ,clox= )

实例与结果：
Python|python操作excel（xlrd、xlwt、xlwings）
文章图片

（2）获取单行或单列的值 获取单行的值：
row_value01=worksheet.row(行索引值)#通过调用row()方法 row_value01=worksheet.row_values(行索引值)#通过调用row_values()方法

获取单列的值：
clo_value01=worksheet.clo(列索引值)#通过调用clo()方法 clo_value02=worksheet.clo_values(列索引值)#通过调用clo_values()方法

实例与结果：
Python|python操作excel（xlrd、xlwt、xlwings）
文章图片

import xlrd workbook=xlrd.open_workbook(r"D:\learning\商品销售表.xls") worksheet=workbook.sheets()[0] clo_value01=worksheet.col_values(0) clo_value02=worksheet.col_values(5) row_value01=worksheet.row_values(2) row_value02=worksheet.row_values(5) print("clo_value01:",clo_value01) print("clo_value02:",clo_value01) print("row_value01:",row_value01) print("row_value02:",row_value02)

【Python|python操作excel（xlrd、xlwt、xlwings）】Python|python操作excel（xlrd、xlwt、xlwings）
文章图片

4、获取工作表中的总行列数
total_rows=worksheet.nrows#获取总行数 total_cols=worksheet.ncols#获取总列数

实例与结果：
Python|python操作excel（xlrd、xlwt、xlwings）
文章图片

二、xlwt库 1、工作簿（book） （1）创建工作簿对象
import xlwt workbook=xlwt.Workbook("文件路径")

（2）保存工作簿
workbook.save()#传参只能是地址，如有传参则是另存为，若无传参功能类似于保存

2、工作表(sheet) （1）创建工作表对象
new_worksheet=workbook.add_sheet("工作表名称")#创建一个新的工作表，并将其返回给实例对象

注意?：
worksheet=workbook.sheets()[index/sheet_name] 此方法会报错哦！因为xlwt.Workbook中没有sheets()方法，因为不能使用xlwt库来创建已有的工作表
（2）写入数据
worksheet.write(row,clo,value)#通过write()方法将值写入指定的行列中

3、XFStyle风格样式 （1）Font（字体样式） 第一步：初始化XFStyle实例对象
import xlwt style=xlwt.XFStyle()

第二步：创建属性对象
font=xlwt.Font()#通过调用Font()方法创建属性对象

第三步：对font的属性值初始化
font.name="宋体"#设置字体 font.blod=False/True #设置加粗 font.height= 字号*20#设置字体字号

第四步：将属性对象赋予给XFStyle对象中的font属性
style.font=font

第五步：将XFStyle实例对象写入工作表对象中
worksheet.write(row,clo,value,style)

（2）Borders（边界）
  1. 边框线粗细
borders.top=#设置上边框线大小 borders.bottom=#设置下边框线大小 borders.left=#设置左边框线大小 borders.right=#设置右边框线大小

  1. 边框线颜色
borders.top_colour=#设置上边框线颜色 borders.bottom_colour=#设置下边框线颜色 borders.left_colour=#设置左边框线颜色 borders.right_colour=#设置右边框线颜色

  1. 代码实例
import xlwt style=xlwt.XFStyle() borders=xlwt.Borders() borders.left=1 borders.right_colour=33 style.borders=borders worksheet.write(row,clo,value,style)

（3）Alignment（对齐）
  1. 属性介绍
属性代码 描述说明
alignment.vert = xlwt.Alignment.VERT_TOP 水平方向—向上对齐
alignment.vert = xlwt.Alignment.VERT_CENTER 水平方向—居中对齐
alignment.vert = xlwt.Alignment.VERT_BOTTOM 水平方向—向下对齐
alignment.horz = xlwt.Alignment.HORZ_TOP 垂直方向—向左对齐
alignment.horz = xlwt.Alignment.HORZ_CENTER 垂直方向—居中对齐
alignment.horz = xlwt.Alignment.HORZ_BOTTOM 垂直方向—向右对齐
  1. 代码实例
import xlwt style=xlwt.XFStyle() alignment=xlwt.Alignment()#创建alignment对象 alignment.vert=xlwt.Alignment.VERT_CENTER#设置文字水平居中 alignment.horz=xlwt.Alignment.HORZ_BOTTOM#设置文字垂直靠底部 style.alignment=alignment#将alignment对象直接赋给style对象的alignment属性 worksheet.write(row,clo,value,style)#将style写入对应的单元格中

（4）Pattern（模式）
  1. 属性介绍
pattern.pattern=xlwt.Pattern.SOLID_PATTERN#第一个pattern指的是对象，第二个pattern代表属性 pattern.pattern_fore_colour=33#设置单元格背景颜色

  1. 代码实例
impotr xlwt style=xlwt.XFStyle() pattern=xlwt.Pattern() pattern.pattern=xlwt.Pattern.SOLID_PATTERN#第一个pattern指的是对象，第二个pattern代表属性 pattern.pattern_fore_colour=33#设置单元格背景颜色 style.pattern=pattern worksheet.write(row,clo,value,style) #将style写入对应的单元格中

附加知识：设置行高列宽 1、设置列宽
worksheet.col(index).width=256*n#通过clo()方法中的width属性设置

2、设置行高
worksheet.row(index).height_mismatch=True#初始化数据 worksheet.row(index).height=n*m#通过row()方法中的height属性设置

三、xlwings库 1、Excel程序 （1）打开Excel程序
import xlwings as xw app=xw.App(visible=True,add_book=False)#打开excel程序 #visible是界面可视属性，add_book代表是否新建一个工作簿

（2）退出Excel程序
app.quit#关闭excel程序

2、工作簿（book） （1）创建工作簿对象 创建工作簿对象一共有两种方式，
第一种方式：在excel程序中添加新的工作簿或者打开已有工作簿，通过调用app对象，
new_workbook=app.book.add()#创建新的工作簿,无传参时直接新建一个工作簿 workbook=app.books.open("文件路径")#打开已有工作簿

第二种方式：通过调用xlwings库中的Book()方法
workbook=xw.Book("文件路径")#打开已有工作簿

Python|python操作excel（xlrd、xlwt、xlwings）
文章图片
（2）保存工作簿
workbook.save()#若填写地址参数则相当于另存为，若无地址参数则相当于保存

（3）关闭工作簿
workbook.close()#该操作把工作表都关闭，但是excel程序没有被关闭

（4）工作簿属性
  1. 获取工作簿路径：workbook.fullname
  2. 获取工作簿名称：workbook.name
3、工作表（sheet） （1）创建工作表对象
worksheeet=workbook.sheets[index/sheet_name]

（2）批量创建工作表对象
worksheeet_list=workbook.sheets #返回此工作簿里所有的工作表 #print(worksheeet_list)---------> Sheets([, ...,,]) # print(worksheeet_list[index].name) ---------> 工作表名称

（4）常用API
  1. 获取所有工作表：
    listbook=workbook.sheets
  2. 打开原工作表：
    sheet=workbook.sheets["工作表名称或索引位置"]
  3. 激活活动工作表：
    sheet.activate()
  4. 表格清除：
    sheet.clear() 清除内容和样式
    sheet.clear_contents() 删除内容
    sheet.delete() 删除工作簿
4、单元格（range） （1）创建单元格对象
workcontent=worksheeet.range("单元格位置").value #参数可以是单个单元格也可以是个范围 #workcontent=worksheeet.range("A1:E1").value=https://www.it610.com/article/['姓名','级别','学历','薪资','地区'] #workcontent=worksheeet.range("A1").value='https://www.it610.com/article/姓名' #workcontent=worksheeet.range("B1").value='https://www.it610.com/article/级别'

（2）向单元格写入数据 写入单个数据：
workcontent=worksheeet.range("B2").value='https://www.it610.com/article/级别' workcontent=worksheeet.range("B2").value='https://www.it610.com/article/=SUM(A1:A3)'# 输入公式，excel表中计算出结果

写入一行数据：
workcontent=worksheeet.range("A1:E1").value=https://www.it610.com/article/['姓名','级别','学历','薪资','地区'] #向A1:E1写入数据 workcontent=worksheeet.range("A2").value=https://www.it610.com/article/('姓名','级别','学历','薪资','地区')# 默认向A2:E3写入数据 workcontent=worksheeet.range('A1').options(transpose=True).value = https://www.it610.com/article/['姓名','安其拉','鲁西','狒狒'] #因为transpose的作用将数据转置，再向A2:A4写入数据

写入多行数据：
worksheet.range('a2').value=https://www.it610.com/article/[['小A','男','三年一班'], ['小B','女','三年二班'], ['小C','女','三年一班'], ['小D','女','三年二班']]# 或者二维元组 worksheet.range('a2').value=https://www.it610.com/article/df#传入DataFrame类型时，会直接将整个DataFrame表格数据直接写入

注意：不能写入集合，如果传入一个完整的字典时只会插入key。
fg.options(converter,ndim=None,dates=None,transpose=False,expand=None) # converter:(object) 转换函数dict、np.array、pd.DataFrame、pd.Series # ndim：(int) 维度数 # dates：(type)``datetime.date`` defaults to ``datetime.datetime`` # transpose：(Boolean) 是否转置 # expand：(str) 读取移动方向，table(向下向右)、down(向下)、right(向右)

（3) 读取表格中的数据
worksheet.range('a2').value# 返回A2单元格的数据 worksheet.range('a2:b8').value # 返回a2:b8单元格范围的数据,返回的类型为n元列表 worksheet.range('a2:b8').value.option(np.array,)

（4）获取有效内容的数据
workdata=https://www.it610.com/article/worksheeet.used_range.value#返回excel表格中有效的数据，并将数据封装成list类型

（5）获取表格有效内容的最后位置
workaddress=worksheeet.used_range.address #返回excel数据最后一个单元格的绝对地址，但是该地址为str类型 #如果要将绝对引用地址变为相对地址则需要对字符处理，worksheeet.used_range.address.replace("$","")

Python|python操作excel（xlrd、xlwt、xlwings）
文章图片

（6）灵活获取Range有效范围的内容
workdata=https://www.it610.com/article/sheet.range('A2').expand('table').value# 从指定的A2开始获取有效内容 #或 workdata=https://www.it610.com/article/sheet.range('A2').options(expand='table').value

(7) 常用API
  1. 获取当前单元格对象或者单元格范围对象：fg=sheet.range(范围)
  2. 获取单元格的行列标：fg.row / fg.column
  3. 获取单元格的行高列宽：fg.row_height / fg.column_width
  4. 单元格行高列宽自适应：fg.rows.autofit() / fg.columns.autofit()
  5. 添加超链接/设定超链接：fg.add_hyperlink(网址,显示名称,提示) / fg.hyperlink=www.baidu.com
  6. 获取单元格范围地址：fg.get_address()
  7. 清除单元格的内容和格式：fg.clear()
  8. 清除单元格的内容：fg.clear_contents()
  9. 获取单元格背景颜色/设置单元格背景颜色：fg.color / fg.color=(r,g,b)
  10. 合并单元格：worksheet.range('A1:C6').api.Merge()
  11. 获取单元格公式：fg.formula_array
  12. 在单元格内输入公式：fg.formula='=公式'
  13. 单元格所在的行列：
#1.column的意义 column #返回所在的列标 columns #返回指定范围的列对象 fg.columns[下标]#取对应列的值并返回列对象 len(fg.columns)#返回列的长度 fg.column_width #返回所在列的列宽 #2.row的意义 row #返回所在的行标 rows #返回指定范围的行对象 fg.rows[下标]#取对应行的值并返回行对象 len(fg.rows)#返回行的长度 fg.row_width #返回所在行的列宽 #3.自动调节行高列宽 fg.autofit

附页 xlrd库 、xlwt库、 xlwings库三者区别
xlrd库 xlwt库 xlwings库
创建工作簿对象 open_workbook() Workbook（） Book() / app.book.add() / app.books.open()
创建工作表对象 sheet_by_name() / sheet_by_index() / sheets()[ index] / 批量:sheets() add_sheet() / 没有sheets sheets[index/sheet_name] / 批量: sheets
操作单元格对象 cell(row,clo).value / cell_value(rowx= ,clox= ) write(row,clo,value) range("单元格位置").value

    推荐阅读


    上一篇：python|python自动化办公（excel篇，从此做表不加班）

    下一篇：python|python中使用xlrd、xlwt操作excel表格详解