目录
- 一、读取数据
- 1.1 从工作簿中取得工作表
- 1.2 从表中取得单元格
- 1.3 从表中取得行和列
- 二、写入数据
- 2.1 创建Workbook对象来创建Excel文件并保存
- 2.2 案例分析一 ：爬取数据并保存excel中
- 2.3 案例分析二： 操作单元格中内容样式并保存数据
- 2.4 案例分析三：将列表数据写入excel中
openpyxl模块是一个读写Excel 文档的Python库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。
- openpyxl.load_workbook(地址) - 打开给定的文件名并返回 工作簿
- openpyxl.Workbook() - 新建一个 Workbook（工作簿）对象 ，即excel文件
一、读取数据
数据如下图所示：
示例代码：
| import openpyxl | |
| # 打开给定的文件名并返回 工作簿 | |
| data = openpyxl.load_workbook('data/体检表.xlsx') | |
| print(type(data)) | |
| print(data) # 返回一个 workbook 数据类型的值 |
运行结果：
1.1 从工作簿中取得工作表
- 工作簿对象.sheetnames - 获取当前工作簿中 所有表的名字
- 工作簿对象.active - 获取当前 活动表1 对应的Worksheet对象
- 工作簿对象[表名] - 根据表名获取指定 表对象
- 表对象.title - 获取表对象的 表名
- 表对象.max_row - 获取表的 最大有效行数
- 表对象.max_column - 获取表的 最大有效列数
示例代码：
| import openpyxl | |
| # 打开给定的文件名并返回 工作簿 | |
| data = openpyxl.load_workbook('data/体检表.xlsx') | |
| print(type(data)) | |
| print(data) # 返回一个 workbook 数据类型的值 | |
| # 获取所有表的表名 | |
| sheets_names = data.sheetnames | |
| print(sheets_names) | |
| # 获取活动表对应的表对象(表对象就是Worksheet类的对象) | |
| active_sheet = data.active | |
| print(active_sheet) | |
| # 根据表名获取工作簿中指定的表 | |
| sheet2 = data['Sheet2'] | |
| print(sheet2) | |
| # 根据表对象获取表的名字 | |
| sheet_name1 = active_sheet.title | |
| sheet_name2 = sheet2.title | |
| print(sheet_name1, sheet_name2) | |
| # 获取第二列的所有内容 | |
| sheet = data.active | |
| row_num = sheet.max_row # 获取当前表中最大的行数 | |
| for row in range(1, row_num+1): | |
| cell = sheet.cell(row, 2) | |
| print(cell.value) |
运行结果：
1.2 从表中取得单元格
- 表对象['列号行号'] - 获取指定列的指定行对应的单元格对象（单元格对象是 Cell 类的对象，列号是从A开始，行号是从1开始）
- 表对象.cell(行号, 列号) - 获取指定行指定列对应的单元格（这儿的行号和列好号都可以用数字）
- 表对象.iter_rows() - 一行一行的取
- 表对象.iter_cols() - 列表一列的取
- 单元格对象.value - 获取单元格中的内容
- 单元格对象.row - 获取行号（数字1开始）
- 单元格对象.column - 获取列号（数字1开始）
- 单元格对象.coordinate - 获取位置（包括行号和列号）
示例代码：
| import openpyxl | |
| # 打开给定的文件名并返回 工作簿 | |
| data = openpyxl.load_workbook('data/体检表.xlsx') | |
| print(type(data)) | |
| print(data) # 返回一个 workbook 数据类型的值 | |
| # 获取活跃表对象 | |
| sheet = data.active | |
| # 获取单元格对应的 Cell 对象 | |
| a1 = sheet['A1'] # A1 表示A列中的第一行，这儿的列号采用的是从A开始的 | |
| print(a1) | |
| # 获取单元格中的内容 | |
| content = a1.value | |
| print(content) | |
| # 调用表的 cell()方法时，可以传入整数 作为 row 和 column 关键字参数，也可以得到一个单元格 | |
| content2 = sheet.cell(2, 2).value | |
| print(content2) | |
| # 获取单元格的行和列信息 | |
| row = a1.row | |
| print('行：', row) | |
| column = a1.column | |
| print('列:', column) | |
| coordinate = a1.coordinate | |
| print(coordinate) | |
| print("*" * 100) | |
| # 获取第二列的所有内容 | |
| row_num = sheet.max_row # 获取当前表中最大的行数 | |
| for row in range(1, row_num+1): | |
| cell = sheet.cell(row, 2) | |
| print(cell.value) |
运行结果：
注意：在 Z 列之后，列开始使用两个字母:AA、AB、AC 等。作为替代，在调用表的 cell()方法时，可以传入整数 作为 row 和 column 关键字参数，也可以得到一个单元格。
注意：第一行或第一列的对应的整数 是 1，不是 0。
1.3 从表中取得行和列
表对象也可以像列表或者字符串那样进行 切片 操作，来获取电子表格中一行、一列或一个 矩形区域 中的所有 Cell 对象，然后就可以对数据进行相应的操作。
- 表对象[位置1:位置2] - 获取指定范围中的所有的单元格
示例代码：
| import openpyxl | |
| from openpyxl.utils import get_column_letter | |
| # 打开给定的文件名并返回 工作簿 | |
| data = openpyxl.load_workbook('data/体检表.xlsx') | |
| # print(type(data)) | |
| # print(data) # 返回一个 workbook 数据类型的值 | |
| # 获取表对象 | |
| sheet = data.active | |
| # 1.获取整个一行的单元格 | |
| max_column = sheet.max_column # 获取最大列数 | |
| column = get_column_letter(max_column) # 获取最大列数对应的字母列号 | |
| # 获取第一行所有单元格对象 | |
| row2 = sheet['A1':'%s1' % column] # ((<Cell '表1'.A1>, <Cell '表1'.B1>, <Cell '表1'.C1>),) | |
| print(row2) | |
| for row_cells in row2: | |
| for cell in row_cells: | |
| print(cell.coordinate, cell.value) | |
| print("*" * 100) | |
| # 2.获取整个列的单元格 | |
| max_row = sheet.max_row | |
| columnB = sheet['B1':'B%d' % max_row] | |
| # 获取B列对应的所有单元格对象 | |
| for column_cells in columnB: | |
| for cell in column_cells: | |
| print(cell.coordinate, cell.value) | |
| print("*" * 100) | |
| # 3. 获取矩形区域中的单元格对象 | |
| cell_tuples = sheet['A1': 'C3'] | |
| print(cell_tuples) | |
| for cells in cell_tuples: | |
| for cell in cells: | |
| print(cell.coordinate, cell.value) |
运行结果：
二、写入数据
2.1 创建Workbook对象来创建Excel文件并保存
- openpyxl.Workbook() - 创建空的 Excel 文件对应的工作簿对象
- 工作簿对象.save(文件路径) - 保存文件
- 工作簿对象.create_sheet(title, index) - 在指定工作簿中的指定位置（默认是最后）创建指定名字的表，并且返回表对象
- 工作簿对象.remove(表对象) - 删除工作簿中的指定表
- 表对象[位置] = 值 - 在表中指定位置对应的单元格中写入指定的值，位置是字符串：‘A1’（第1列的第一行）、‘B1’（第二列的第一行）
示例代码：
| import openpyxl | |
| # 创建空的Workbook对象 | |
| w_data = openpyxl.Workbook() | |
| # 获取所有表名 | |
| print(w_data.sheetnames) # ['Sheet'] | |
| # 可知默认情况下，新建的Workbook对象对应的Excel 文件中只有一张名字是 'Sheet' 的表 | |
| # 获取活动表 | |
| sheet_active = w_data.active | |
| # 修改表的名字 | |
| sheet_active.title = 'first_table' | |
| # 保存至文件 | |
| w_data.save(filename='data/info.xlsx') | |
| # 新建表 | |
| w_data.create_sheet() | |
| w_data.create_sheet() | |
| # for i in range(20): | |
| # wb.create_sheet() | |
| print(w_data.sheetnames) # ['first_table', 'Sheet', 'Sheet1'] | |
| # 新建表时，从Sheet开始一直到Sheet n | |
| w_data.create_sheet('second_table') | |
| # `工作簿对象.create_sheet(title, index)` - 在指定工作簿中的指定位置（默认是最后）创建指定名字的表，并且返回表对象 | |
| w_data.create_sheet('third_table', 1) | |
| print(w_data.sheetnames) # ['first_table', 'third_table', 'Sheet', 'Sheet1', 'second_table'] | |
| # 删除表 | |
| w_data.remove(w_data['Sheet1']) | |
| w_data.save(filename='data/info.xlsx') | |
| # 写入数据 | |
| w_data = openpyxl.load_workbook('data/info.xlsx') | |
| sheet = w_data['first_table'] # 获取表 | |
| sheet['A1'] = '姓名' | |
| sheet['B1'] = '年龄' | |
| sheet['C1'] = '性别' | |
| sheet['A2'] = '张三' | |
| sheet['B2'] = 25 | |
| sheet['C2'] = '男' | |
| w_data.save('data/info.xlsx') |
运行结果：
2.2 案例分析一 ：爬取数据并保存excel中
示例代码：
| # 利用requests获取天行数据中疫情数据，并且将获取到的数据使用excel文件保存到表中。 | |
| import requests | |
| import openpyxl | |
| from openpyxl.utils import get_column_letter | |
| # 1.获取数据 | |
| url = 'http://api.tianapi.com/txapi/ncovabroad/index?key=c9d408fefd8ed4081a9079d0d6165d43' | |
| rep = requests.get(url) | |
| news_list = rep.json()['newslist'] | |
| # 2.设置表头信息 | |
| headers = {'continents': ('洲', 'A'), | |
| 'provinceName': ('国家', 'B'), | |
| 'currentConfirmedCount': ('现有确诊', 'C'), | |
| 'confirmedCount': ('累计确诊', 'D'), | |
| 'curedCount': ('治愈', 'E'), | |
| 'deadCount': ('死亡', 'F')} | |
| # 3.创建工作表 | |
| wb = openpyxl.Workbook() | |
| sheet = wb.active | |
| # 4.写入数据 | |
| # 先写入第一行的表头 | |
| column_num = 1 | |
| for key in headers: | |
| column = get_column_letter(column_num) | |
| location = f'{column}1' | |
| sheet[location] = headers[key][0] | |
| column_num += 1 | |
| # 再从第二行开始写入爬取到的数据 | |
| row = 2 | |
| for news in news_list: # 遍历每条数据项，一个数据项对应一个字典 | |
| for key in news: # 遍历数据键值 | |
| if key in headers: # 保证键是表头中的某一项我们需要的数据 | |
| location = f'{headers[key][1]}{row}' # 存在表中的位置 | |
| value = news[key] # 需要的数据 | |
| sheet[location] = value # 写入 | |
| row += 1 | |
| wb.save(filename='data/epidemic.xlsx') |
运行结果：
2.3 案例分析二： 操作单元格中内容样式并保存数据
示例代码：
| import openpyxl | |
| from openpyxl.styles import Font, PatternFill, Border, Side, Alignment # 字体、填充图案、边框、侧边、对齐方式 | |
| # 1.打开工作簿 | |
| data = openpyxl.load_workbook('data/体检表.xlsx') | |
| sheet = data.active | |
| # 2.设置单元格字体样式 | |
| """ | |
| Font( | |
| name=None, # 字体名，可以用字体名字的字符串 | |
| strike=None, # 删除线，True/False | |
| color=None, # 文字颜色 | |
| size=None, # 字号 | |
| bold=None, # 加粗, True/False | |
| italic=None, # 倾斜，Tue/False | |
| underline=None # 下划线, 'single', 'singleAccounting', 'double','doubleAccounting' | |
| ) | |
| """ | |
| # 创建字体对象，并调整合适的参数 | |
| font1 = Font( | |
| name='微软雅黑', | |
| size=15, | |
| italic=False, | |
| color='ff0000', | |
| bold=False, | |
| strike=False, | |
| underline='single', | |
| ) | |
| # 设置指定单元格的字体 | |
| # 单元格对象.font = 字体对象 | |
| area = sheet['A1':'E1'] | |
| for row in area: | |
| for _ in row: | |
| _.font = font1 # 调整字体 | |
| # 3.设置单元格填充样式 | |
| """ | |
| PatternFill( | |
| fill_type=None, # 设置填充样式: 'darkGrid', 'darkTrellis', 'darkHorizontal', 'darkGray', 'lightDown', 'lightGray', 'solid', 'lightGrid', 'gray125', 'lightHorizontal', 'lightTrellis', 'darkDown', 'mediumGray', 'gray0625', 'darkUp', 'darkVertical', 'lightVertical', 'lightUp' | |
| # '深色网格“，”深色网格“，”深色水平“，”深色灰色“，”浅色向下“，”浅灰色“，”纯色“，”浅色网格“，”灰色125“，”浅色水平“，”浅色网格“，”深色向下“，”中灰色“，”灰色0625“，”深色向上“，”深色垂直“，”浅色垂直“，”浅色向上“ | |
| start_color=None# 设置填充颜色 | |
| ) | |
| """ | |
| # 设置填充对象 | |
| fill = PatternFill(fill_type='solid', start_color='FFC0CB') | |
| # 设置单元格的填充样式 | |
| # 单元格对象.fill = 填充对象 | |
| area = sheet['A1':'E1'] | |
| for row in area: | |
| for _ in row: | |
| _.fill = fill # 调整填充格式 | |
| # 4. 设置单元格对齐样式 | |
| # 创建对象 | |
| al = Alignment( | |
| horizontal='right', # 水平方向:center, left, right | |
| vertical='top', # 垂直方向: center, top, bottom | |
| ) | |
| # 设置单元格的对齐方式 | |
| sheet['B2'].alignment = al | |
| # 5. 设置边框样式 | |
| # 设置边对象（四个边的边可以是一样的也可以不同，如果不同就创建对个Side对象） | |
| # border_style取值('dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin') | |
| # 单点划线，双点划线，点划线，双划线，头发，中划线，单点中划线，双点中划线，中划线，斜划线，粗划线，细划线 | |
| side = Side(border_style='thin', color='000000') | |
| # 设置边框对象 | |
| # left、right、top、bottom表示的是边框的四个边，四个边可以使用一个边对象 | |
| bd = Border(left=side, right=side, top=side, bottom=side) | |
| # 设置单元格的边框 | |
| area = sheet['A1':'E1'] | |
| for row in area: | |
| for _ in row: | |
| _.border = bd | |
| # 6.设置单元格的宽度和高度 | |
| # 设置列宽 | |
| sheet.column_dimensions['A'].width = 10 | |
| # 设置行高 | |
| sheet.row_dimensions[1].height = 30 | |
| # 7. 保存 | |
| data.save(filename='data/体检表2.xlsx') |
运行结果：
2.4 案例分析三：将列表数据写入excel中
示例代码：
| import openpyxl | |
| import datetime | |
| datas = [ | |
| ('学号', '姓名', '年龄', '专业', '考试时间'), | |
| ('B00001', '张1', 18, '语文', datetime.datetime(2019, 6, 18, 0, 0)), | |
| ('B00002', '张2', 19, '数学', datetime.datetime(2019, 6, 19, 0, 0)), | |
| ('B00003', '张3', 20, '英语', datetime.datetime(2019, 6, 20, 0, 0)), | |
| ('B00004', '张4', 21, '物理', datetime.datetime(2019, 6, 21, 0, 0)), | |
| ('B00005', '张5', 22, '化学', datetime.datetime(2019, 6, 22, 0, 0)), | |
| ('B00006', '张6', 23, '生物', datetime.datetime(2019, 6, 23, 0, 0)), | |
| ('B00007', '张7', 24, '历史', datetime.datetime(2019, 6, 24, 0, 0)) | |
| ] | |
| # 创建空的Workbook对象 | |
| w_data = openpyxl.Workbook() | |
| # 获取活动表 | |
| sheet = w_data.active | |
| for i in range(1, len(datas) + 1): | |
| for j in range(1, 6): | |
| sheet.cell(row=i, column=j, value=datas[i - 1][j - 1]) | |
| w_data.save('data/info.xlsx') |
运行结果：
