Excel读取
读取对应表格
2003年版本的是xls格式，2007和2007年之后的版本是xlsx格式。
xlsx格式通过 openpyxl 模块打开；
xls格式通过 xlwt 模块写，xlrd 模块读取。
import warningswarnings.filterwarnings('ignore')import openpyxl
查看属性
wb = openpyxl.load_workbook('用户行为偏好.xlsx')type(wb)
openpyxl.workbook.workbook.Workbook
打开已经存在的Excel表格，查询对应sheet的名称
exl = openpyxl.load_workbook(filename = '用户行为偏好.xlsx')print(exl.sheetnames)
['订单时长分布', 'Sheet3']
# 通过传递表名字符串读取表、类型和名称sheet = exl.get_sheet_by_name('Sheet3')sheet.title
'Sheet3'
# 读取工作簿的活动表# 活动表是工作簿在Excel中打开时出现的工作表，再取得Worksheet对象后，可通过title属性取得它的名称anotherSheet = exl.activeanotherSheet
<Worksheet "订单时长分布">
获取Excel 内容占据的大小
第一个参数表示列所占的区域，第二个参数表示行数
sheet.dimensions
'A1:O29'
anotherSheet.dimensions
'A1:D102883'
读取单元格
Cell对象有一个value属性，包含这个单元格中保存的值。
Cell对象也有row、column和coordinate属性，提供该单元格的位置信息。
Excel用字母指定列，在Z列之后，列开始使用两个字母：AA、AB等，所以在调用的cell()方法时，可传入整数作为row和column关键字参数，也可以得到一个单元格。
注：第一行或第一列的整数取1，而不是0.
# 获取表格名称exl = openpyxl.load_workbook('用户行为偏好.xlsx')exl.get_sheet_names()
['订单时长分布', 'Sheet3']
sheet = exl.get_sheet_by_name('订单时长分布')a = sheet['A2']a.value
71401.30952380953
'Row ' + str(a.row) + ', Column ' + str(a.column) + ' is ' + str(a.value)
'Row 2, Column 1 is 71401.30952380953'
'Cell ' + a.coordinate + ' is ' + str(a.value)
'Cell A2 is 71401.30952380953'
# 打出B列前8行的奇数行单元格的值for i in range(1,8,2): print(i,sheet.cell(row=i,column=2).value)
1 日期3 2020-07-24 00:00:005 2020-07-24 00:00:007 2020-07-24 00:00:00
# 确定表格的最大行数和最大列数，即表的大小sheet.max_rowsheet.max_column
读取多个格子的值
# A1到C8区域的值cells = sheet['A1:C8']# 用enumerate包装一个可迭代对象，同时使用索引和迭代项for index, item in enumerate(sheet['A1:C8']): if index >= 1: print("\n") for cell in item: print(cell.value,end=" ")
编号 日期 行为时长 71401.30952380953 2020-07-24 00:00:00 a 71401.30952380953 2020-07-24 00:00:00 b 71401.30952380953 2020-07-24 00:00:00 c 71401.30952380953 2020-07-24 00:00:00 d 71401.30952380953 2020-07-24 00:00:00 e 71401.30952380953 2020-07-24 00:00:00 f 71401.30952380953 2020-07-24 00:00:00 g
# 行获取for row in sheet.iter_rows(min_row = 1, max_row = 3, min_col = 2, max_col = 4): print(row)# 一列由多个单元格组成，若需要获取每个单元格的值则循环获取即可 for cell in row: print(cell.value)
(<Cell '订单时长分布'.B1>, <Cell '订单时长分布'.C1>, <Cell '订单时长分布'.D1>)日期行为时长次数(<Cell '订单时长分布'.B2>, <Cell '订单时长分布'.C2>, <Cell '订单时长分布'.D2>)2020-07-24 00:00:00a718.832012012012(<Cell '订单时长分布'.B3>, <Cell '订单时长分布'.C3>, <Cell '订单时长分布'.D3>)2020-07-24 00:00:00b728.862012012012
# 列获取for col in sheet.iter_cols(min_row = 1, max_row = 5, min_col = 2, max_col = 4): print(col) for cell in col: print(cell.value)
(<Cell '订单时长分布'.B1>, <Cell '订单时长分布'.B2>, <Cell '订单时长分布'.B3>, <Cell '订单时长分布'.B4>, <Cell '订单时长分布'.B5>)日期2020-07-24 00:00:002020-07-24 00:00:002020-07-24 00:00:002020-07-24 00:00:00(<Cell '订单时长分布'.C1>, <Cell '订单时长分布'.C2>, <Cell '订单时长分布'.C3>, <Cell '订单时长分布'.C4>, <Cell '订单时长分布'.C5>)行为时长abcd(<Cell '订单时长分布'.D1>, <Cell '订单时长分布'.D2>, <Cell '订单时长分布'.D3>, <Cell '订单时长分布'.D4>, <Cell '订单时长分布'.D5>)次数718.832012012012728.862012012012390.792012012012228.542012012012
Excel写入
写入数据并保存
原有工作簿中写入数据并保存
# 已有的表格赋值保存exl = openpyxl.load_workbook(filename = '用户行为偏好.xlsx')sheet = exl.activesheet['A1'] = 'hello world'# 或者cell = sheet['A1']# cell.value = 'hello world'exl.save(filename = '用户行为偏好.xlsx') #存入原Excel表中，若创建新文件则可命名为不同名称
创建新的表格写入数据并保存
wb = openpyxl.Workbook()# 创建一个sheetsh = wb.activesh.title = 'My Worksheet' # 写入excel# 参数对应 行, 列, 值sh.cell(1,1).value = 'this is test'# 保存wb.save('new_test.xlsx')
将公式写入单元格保存
exl = openpyxl.load_workbook(filename = '用户行为偏好.xlsx')sheet = exl.get_sheet_by_name('Sheet3')sheet.dimensions # 先查看原有表格的单元格范围，防止替代原有数据sheet['A30'] = '=SUM(A1:D1)'exl.save(filename='用户行为偏好.xlsx')
插入数据
# 插入列数据# 第2列前插入5列 sheet.insert_cols(idx=2, amount=5)
# 插入行数据# 插入多行sheet.insert_rows(idx=2, amount=5)
exl.save(filename='用户行为偏好.xlsx')
# 删除多列sheet.delete_cols(idx=5, amount=2)
# 删除多行sheet.delete_rows(idx=2, amount=5)
exl.save(filename='用户行为偏好.xlsx')
当数字为正即向下或向右，为负即为向上或向左
sheet.move_range('B3:E16',rows=1,cols=-1)
Sheet表操作
创建新的sheet
workbook = openpyxl.Workbook()sheet = workbook.activeworkbook.save(filename='new_test.xlsx')exl.create_sheet('new_sheet')
<Worksheet "new_sheet">
修改sheet表名
sheet = exl.activesheet.title = 'newname'
Excel 样式
设置字体样式
Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)
from openpyxl import Workbookfrom openpyxl.styles import Font
workbook = Workbook()sheet = workbook.activecell = sheet['A1']font = Font(name='字体', size=10, bold=True, italic=True, color='FF0000')cell.font = fontworkbook.save(filename='new_test')
设置多个格子的字体样式
workbook = Workbook()sheet = workbook.activecells = sheet[2]font = Font(name='字体', size=10, bold=True, italic=True, color='FF000000')for cell in cells: cell.font = fontworkbook.save(filename='new_test')
设置对齐样式
水平对齐： distributed, justify, center, left, fill, centerContinuous, right, general
垂直对齐： bottom, distributed, justify, center, top
设置单元格边框样式
Side ：变现样式，边线颜色等
Border ：左右上下边线
from openpyxl.styles import Border,Sideworkbook = Workbook()sheet = workbook.activecell = sheet['A1']side = Side(border_style='thin', color='FF000000')# 先定好side的格式border = Border(left=side, right=side, top=side, bottom=side)# 代入边线中cell.border = borderworkbook.save(filename='new_test')
设置单元格边框样式
边线样式： double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font, GradientFillworkbook = Workbook()sheet = workbook.activecell = sheet['A1']pattern_fill = PatternFill(fill_type='solid',fgColor="DDDDDD")cell.fill = pattern_fill# 单色填充cell2 = sheet['A3']gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000'))cell2.fill = gradient_fill# 渐变填充workbook.save(filename='new_test')
设置行高与列宽
workbook = Workbook()sheet = workbook.activesheet.row_dimensions[1].height = 50sheet.column_dimensions['C'].width = 20workbook.save(filename='new_test')
合并、取消合并单元格
sheet.merge_cells('A1:B2')sheet.merge_cells(start_row=1, start_column=3,end_row=2, end_column=4)
sheet.unmerge_cells('A1:B2')sheet.unmerge_cells(start_row=1, start_column=3,end_row=2, end_column=4)