Excel读取
读取对应表格
2003年版本的是xls格式,2007和2007年之后的版本是xlsx格式。
xlsx格式通过 openpyxl 模块打开;
xls格式通过 xlwt 模块写,xlrd 模块读取。
import warnings
warnings.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.active
anotherSheet
<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:00
5 2020-07-24 00:00:00
7 2020-07-24 00:00:00
# 确定表格的最大行数和最大列数,即表的大小
sheet.max_row
sheet.max_column
102883
4
读取多个格子的值
# 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:00
a
718.832012012012
(<Cell '订单时长分布'.B3>, <Cell '订单时长分布'.C3>, <Cell '订单时长分布'.D3>)
2020-07-24 00:00:00
b
728.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:00
2020-07-24 00:00:00
2020-07-24 00:00:00
2020-07-24 00:00:00
(<Cell '订单时长分布'.C1>, <Cell '订单时长分布'.C2>, <Cell '订单时长分布'.C3>, <Cell '订单时长分布'.C4>, <Cell '订单时长分布'.C5>)
行为时长
a
b
c
d
(<Cell '订单时长分布'.D1>, <Cell '订单时长分布'.D2>, <Cell '订单时长分布'.D3>, <Cell '订单时长分布'.D4>, <Cell '订单时长分布'.D5>)
次数
718.832012012012
728.862012012012
390.792012012012
228.542012012012
Excel写入
写入数据并保存
- 原有工作簿中写入数据并保存
# 已有的表格赋值保存
exl = openpyxl.load_workbook(filename = '用户行为偏好.xlsx')
sheet = exl.active
sheet['A1'] = 'hello world'
# 或者cell = sheet['A1']
# cell.value = 'hello world'
exl.save(filename = '用户行为偏好.xlsx') #存入原Excel表中,若创建新文件则可命名为不同名称
- 创建新的表格写入数据并保存
wb = openpyxl.Workbook()
# 创建一个sheet
sh = wb.active
sh.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.active
workbook.save(filename='new_test.xlsx')
exl.create_sheet('new_sheet')
<Worksheet "new_sheet">
- 修改sheet表名
sheet = exl.active
sheet.title = 'newname'
Excel 样式
-
设置字体样式
Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)
from openpyxl import Workbook
from openpyxl.styles import Font
workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
font = Font(name='字体', size=10, bold=True, italic=True, color='FF0000')
cell.font = font
workbook.save(filename='new_test')
- 设置多个格子的字体样式
workbook = Workbook()
sheet = workbook.active
cells = sheet[2]
font = Font(name='字体', size=10, bold=True, italic=True, color='FF000000')
for cell in cells:
cell.font = font
workbook.save(filename='new_test')
设置对齐样式
-
水平对齐: distributed, justify, center, left, fill, centerContinuous, right, general
-
垂直对齐: bottom, distributed, justify, center, top
-
设置单元格边框样式
Side :变现样式,边线颜色等
Border :左右上下边线
from openpyxl.styles import Border,Side
workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
side = Side(border_style='thin', color='FF000000')
# 先定好side的格式
border = Border(left=side, right=side, top=side, bottom=side)
# 代入边线中
cell.border = border
workbook.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, GradientFill
workbook = Workbook()
sheet = workbook.active
cell = 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.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['C'].width = 20
workbook.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)