利用Python第三方模块操作Excel文件

一、简介

        我们可以利用python第三方库对Excel进行操作，常用第三方库有xlrd、xlwt和openpyxl库。xlrd和xlwt能够兼容所有版本的文件，openpyxl 针对 excel 2003版本之前的兼容性可能不好，但是功能更加强大。2003版本之前 excel的文件后缀是xls， 2003版本之后 excel的文件后缀是xlsx、csv。

二、具体用法

        windwos系统下安装方法：win+r打开运行对话框，输入cmd，打开命令提示符窗口：pip install xlrd/xlwt/openpyxl即可安装（需要联网）。

1、xlrd模块简介

（1）打开工作簿

wb = xlrd.open_workbook(filename = r'e:\excel\aa.xlsx')

（2）获取sheet表格

table = wb.sheets()[0]

table = wb.sheet_by_index(0)

table = wb.sheet_by_name(sheet_name='Sheet1')

names = wb.sheet_names()

（3）获取sheet中有多少行和列

row = table.nrows #获取有效行数

col = table.ncols #获取有效列数

num = table.row_len(0) #获取一行中有多少列数据

（4）获取单元格中的数据

value = table.cell_value(rowx=0, colx=1) #获取指定单元格内的值

value = table.cell(rowx=0, colx=1) #获取单元格内的组成对象和数据

value = table.cell_type(rowx=0, colx=1) #获取单元格的数据类型

（5）获取指定行或列中的数据

table_list = table.row_values(rowx=0, start_colx=0, end_colx=None) #行数据

table_list = table.col_values(colx=0, start_rowx=0, end_rowx=None) #列数据

2、xlwt模块简介

（1）安装和使用

pip3 install xlwt

import xlwt

（2）创建工作簿和工作表

import xlwt

wb = xlwt.Workbook(encoding='utf-8')

ws = wb.add_sheet('mySheet',cell_overwrite_ok=True)

（3）向sheet中添加数据

ws.write(0,0,"hello,world")

（4）设置写入文件格式

xlwt主要通过XFStyle这个类来完成。

字体设置：style = xlwt.XFStyle() #初始化样式

font = xlwt.Font()

font.name='黑体'

font.bold = True

font.color_index=3

style.font = font

borders = xlwt.Borders()

borders.left = xlwt.Borders.THIN

style.borders = borders #设置边框

ws.write(0,0,'我爱中国',style)

wb.save('aa.xls')

（5）设置宽度

ws.col(3).width=200*20

（6）多行列合并写入

worksheet.write_merge()

3、openpyxl模块简介

（1）创建Excel文件 Workbook()

from openpyxl import Workbook
wb = Workbook()#创建Excel文件
wb.save('aa.xlsx')#保存为名字为aa后缀名为xlsx的excel文件

（2）创建工作表 Workbook.create_sheet()

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('信息表',0)
wb.save('aa.xlsx')

（3）修改工作表的名称 worksheet.title

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('用户表',0)
ws.title = 'user table'
print(ws.tile)
wb.save('aa.xlsx')

（4）修改表明标签背景颜色 ws.sheet_properties.tabColor = 'ff0000'

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('用户表',0)
ws.sheet_properties.tabColor = 'ff0000'
wb.save('aa.xlsx')

（5）查看所有工作表的名称 Workbook.sheetnames

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('user',0)
print(wb.sheetnames)

（6）创建工作表的副本 Workbook.copy_worksheet()

from openpyxl import load_workbook
wb = load_workbook(filename = 'aa.xlsx')
ws = wb.active
wb.copy_worksheet(ws)
wb.save('aa.xlsx')

（7）访问单元格 worksheet.cell(row=?,column=?,value=?)或者worksheet['A1']

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('用户表',0)
ws['A1'] = "hello,world!"
ws.cell(row = 1, column = 2,value = 'Lifu Ma'
wb.save('aa.xlsx')

（8）同时插入多个数据 Worksheet.append()

workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
data = [
    ["张三",1],
    ["刘二",2],
    ["王五",3],
    ["李四",4],    
]
for row in data:
    sheet.append(row)
workbook.save(filename="新建表.xlsx")

（9）插入一行或一列  insert_rows(idx=数字编号)、nsert_cols(idx=数字编号)

（10）删除一行或一列 delete_rows(idx=数字编号,amount=要删除的行数)、delete_cols(idx=数字编号,amount=要删除的列数)

（10）向单元格内插入公式 worksheet["C3"] = "=AVERAGE(A1:B2)"

（11）移动格子 move_range(“C1:D4”,rows=2,cols=-2)，正整数为向【下】/【右】，负整数为向【上】/【左】。

（12）删除数据表 workbook.remove(workbooksheet)