利用Python操作excel表格之openyxl介绍
by:授客 QQ:1033553122
欢迎加入全国软件测试交流qq群(群号:7156436),免费获取以下性能监控工具(类似Nmon精简版)
实验环境
python 3.4.0
penpyxl-2.5.3-py3.4
网盘下载地址:
下载地址:https://pan.baidu.com/s/1RC6O7tKavz8ffPgPOJ4jdg
下载地址:https://bitbucket.org/openpyxl/openpyxl/downloads/
测试代码
from openpyxl import load_workbook
from openpyxl.chart import LineChart, AreaChart
from openpyxl.chart import Reference, Series
from openpyxl import Workbook
## 加载已存在工作簿
work_book = load_workbook('mydata.xlsx')
## 获取工作簿拥有的所有Sheet名称
sheet_names = work_book.sheetnames
print('工作簿拥有的所有Sheet名称:%s' % sheet_names)
print('工作簿拥有的所有Sheet名称:')
for sheet in work_book:
print('%s ' % sheet.title, end=' ')
## 根据名称获取的Sheet工作表
sheet = work_book['CPUALL']
## 获取Sheet工作表的名称
sheet_name = sheet.title
print('\n工作表名称:%s\n' % sheet_name)
## 获取指定工作表的行数
min_row = sheet.min_row
print('CPUALL工作表的最小行数:', min_row)
# 注意:row, column最小值是从1开始的
max_row = sheet.max_row
print('CPUALL工作表的最大行数:%s\n' % max_row)
## 获取指定工作表的列数
min_column = sheet.min_column
print('CPUALL工作表的最小列数:', min_column)
max_column = sheet.max_column
print('CPUALL工作表的最大列数:%s\n' % max_column)
## 获取单元格
A2_cell = sheet.cell(row=2, column=1)
#A2_cell = sheet['A2']
## 获取单元格的值
value_for_A2_cell = A2_cell.value
print('A2单元格的值:%s\n' % value_for_A2_cell)
# 修改单元格的值
B2_cell = sheet['B2']
B2_cell.value = 30
print('B2单元格的值:%s\n' % B2_cell.value)
print('B3单元格的值:%s\n' % sheet.cell(row=3, column=2, value=40))
## 获取单元格区域、行、列区域
print('获取单元格区域A2:D7\n')
for row in sheet['A2': 'D7']:
count = 0
for cell in row:
count = count + 1
print(cell.value, end='\t')
if count == len(row):
print()
# 获取指定列
colC = sheet['C'] # 获取第C列
col_range = sheet['C:D'] # 获取C到D列
# 获取指定行
row3 = sheet[3] # 获取第3行
row_range = sheet[2:4] # 获取第2到第4行
## 遍历行
print('\n遍历行')
# 方法1
for row in sheet.rows:
count = 0
for cell in row:
count = count + 1
print(cell.value, end='\t')
if count == len(row):
print()
# 方法2
for row in sheet.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(row)
## 遍历列
print('\n遍历列')
# 方法1:
for column in sheet.columns:
count = 0
for cell in column:
count = count + 1
print(cell.value, end='\t')
if count == len(column):
print()
# 方法2:
for col in sheet.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell)