python自动化办公之对Excel的操作

1.1 xlrd的使用

• book为excel对象

方法	功能
xlrd.open_wordbook(name)	打开excel表格
book.nsheets	获取工作表的个数
book.sheet_names()	获取工作表的名字
book.sheets()[index]	通过索引获取工作表
book.sheet_by_index(index)	通过索引获取工作表
book.sheet_by_name(name)	通过工作表名获取工作表
book.sheet_loaded(name)	判断工作表是否存在
book.sheet_loaded(index)	判断工作表是否存在
book.sheet_loaded(name or index)	判断工作表是否存在

1.2 对行数据的操作

方法	功能
sheet_one.nrows	获取工作表有效总行数
sheet_one.row(index)	获取第几行
sheet_one.row_slice(row,start_colx=0, end_colx=None)	获取第row行到第colx列的内容
sheet_one.row_types(rowx, start_colx=0, end_colx=None)	查看第row行第colx列的数据类型(对照表见下图)
sheet_one.row_values(rowx, start_colx=0, end_colx=None)	查看第row行第colx列的值
sheet_one.row_len(rows)	查看第row行的有效值

1.3 对列数据的操作

方法	功能
sheet_one.ncols	读取工作表的有效列数
sheet_one**.**col(colx, start_rowx=0,end_rowx=None)	获取第colx列第m行到第n行的数据
sheet_one**.**col_slice(colx, start_rowx=0,end_rowx=None)	获取第colx列到第row行的内容
sheet_one.col_types(colx, start_rowx=0,end_rowx=None)	查看第colx列第row行的数据类型(对照表见下图)
sheet_one.col_values(colx, start_rowx=0,end_rowx=None)	查看第colx列第row行的值

1.4 数据类型对照表

ctype
0	empty(空值)
1	string
2	number
3	data(日期)
4	boolean
5	error(错误)

1.5 对单元格数据的操作

方法	功能
sheet_one.cell(row,col)	获取第row行，第col列的内容
sheet_one.cell_value(row,col)	获取第row行，第col列的内容
sheet_one.cell(row,col).value	获取第row行，第col列的内容
sheet_one.cell_type(row,col)	获取第row行，第col列的数据类型
sheet_one.cell(row,col).ctype	获取第row行，第col列的数据类型

1.6 对合并单元格的操作

方法	功能
sheet1.merged_cells	获取合并单元格的索引(开始行，结束行，开始列，结束列)
sheet1.cell_value(row,col)	获取row行，col列的数据
sheet1.cell(row,col).value	获取row行，col列的数据
sheet1.cell(row,col).ctype	获取row行，col列的数据类型

1.6 案例：将表格转化为指定列表格式

• 知识点
  • 将数据转发为时间格式:xlrd.xldate_as_datetime
  • 将数据转发为时间格式:date.strftime('%Y-%m-%d %H:%M:%S')

import xlrd
import datetime
book=xlrd.open_workbook('class_info.xls')#打开表格
table=book.sheet_by_index(0)#选择第一个sheet表格
key=table.row_values(0) #保存表头，既第一行的数据
row_num=table.nrows #保存行数
col_num=table.ncols #保存列数
class_info_data=[]
for i in range(1,row_num):
    table_data={
   }
    for j in range(1,col_num):
        c_value=table.cell(i,j).value #保存每一个单元格的数据
        c_type=table.cell_type(i,j) #保存每一个单元格的数据类型
        if c_type==2 and c_value%1==0:
            c_value=int(c_value)
        elif c_type==3:
#             c_value=xlrd.xldate_as_datetime(c_value,0)#将数据转发为时间格式一 0: 1900, 1: 1904
            c_value=date.strftime('%Y-%m-%d %H:%M:%S') #将数据转发为时间格式二
        table_data[key[j]]=c_value #将数据保存到字典中
#     print(table_data)
    class_info_data.append(table_data) #将数据保存入列表
print(class_info_data)
#[{'年龄': 22, '语言': 'Python', '考试时间': '2020-03-18 00:00:00'}, {'年龄': 21, '语言': 'Python', '考试时间': '2020-03-18 00:00:00'}, {'年龄': 20, '语言': 'Python', '考试时间': '2020-03-18 00:00:00'}, {'年龄': 18, '语言': 'Python', '考试时间': '2020-03-18 00:00:00'}]

2.1 xlwt 的使用

• 导包：import xlwt

• 创建工作簿：xlwt.Workbook(encoding='utf-8')

• 添加sheet文件并指定为可覆盖的：workbook.add_sheet('sheet1',cell_overwrite_ok=True)

• 在指定行列写入数据：worksheet.write(row,col,label)

• 保存工作簿：workbook.save(path)

2.2 excel 表格字体样式的设置

• xlwt中有XFStyle这个库来对单元格的样式进行规范操作，所以当我们想要设置单元格样式时，通常需要先实例化XFStyle类，以便于调用该类的方法。

  style=xlwt.XFStyle()	创建格式对象
  Formatting.Font()	字体设置
  Formatting.Alignment()	对齐设置
  Formatting.Borders()	边框设置
  Formatting.Pattern()	背景颜色设置

  字体样式设置
  xlwt.Font()	创建字体对象
  xlwt.Font().name=‘name’	设置字体
  xlwt.Font().bold=True	设置加粗(True是加粗，False是不加粗)
  xlwt.Font().underline=True	设置下划线
  xlwt.Font().italic=True	设置倾斜
  xlwt.Font().color=‘十六进制数’	设置字体颜色
  xlwt.Font().colour_index=number	根据颜色索引设置颜色
  xlwt.Font().size=number	设置字体大小
  xlwt.Font().height=number	设置行高
  xlwt.Font().struck_out=True	设置删除线
  worksheet.col(index).width=256*m	设置单元格宽度

  import xlrd
  import xlwt
  workbook=xlwt.Workbook(encoding='utf-8')
  worksheet=workbook.add_sheet('sheet1',cell_overwrite_ok=True)
  style=xlwt.XFStyle()
  font=xlwt.Font()
  font.name='Yu Gothic Light'
  font.bold=True
  font.underline=True
  font.italic=True
  font.color='FF000000'
  font.colour_index=0 #0表示黑色
  font.size=10
  font.heigt=300
  font.struck_out=True
  style.font=font #设置单元格样式为我们制定的font样式
  worksheet.write(0,0,'姓名',style)
  worksheet.write