day25
总结
一. 操作
1. Excel 文档
首先,让我们来看一些基本定义。一个 Excel 电子表格文档称为一个工作簿。一个 工作簿保存在扩展名为.xlsx 的文件中。每个工作簿可以包含多个表(也称为工作表)。用户当前查看的表(或关闭 Excel 前最后查看的表),称为活动表。 每个表都有一些列(地址是从 A 开始的字母)和一些行(地址是从 1 开始的数 字)。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。单元格形成的网格和数据构成了表。
2. 安装 openpyxl 模块
Python 没有自带 openpyxl,所以必须安装。如果不会请参考我的博文《python中安装第三方库的三种方法》。
3. Excel 文档的基本操作
本章的例子将使用一个电子表格 example.xlsx,它保存在工程目录的 files 文件夹中。下图是 example.xlsx 文件内容截图,主要包含了默认的两个表:表1、表2
3.1 用 openpyxl 模块打开 Excel 文档
在导入 openpyxl 模块后,就可以使用openpyxl.load_workbook()函数来打开指定的 excel 文件:
openpyxl.load_workbook(地址)
- 打开现有的excel文件openpyxl.Workbook()
- 新建一个excel文件
import openpyxl
# openpyxl.load_workbook(需要打开的excel文件路径)
wb = openpyxl.load_workbook('files/example.xlsx')
print(type(wb)) # 结果: <class 'openpyxl.workbook.workbook.Workbook'>
openpyxl.load_workbook()函数接受文件名,返回一个 workbook 数据类型的值。这 个 workbook 对象代表这个 Excel 文件,有点类似 File 对象代表一个打开的文本文件。
3.2 从工作簿中取得工作表
工作簿对象.sheetnames
- 获取当前工作簿中所有表的名字工作簿对象.active
- 获取当前活动表对应的Worksheet对象工作簿对象[表名]
- 根据表名获取指定表对象表对象.title
- 获取表对象的表名表对象.max_row
- 获取表中最多有多少行表对象.max_column
- 获取表有多少列
import openpyxl
# 打开工作簿
wb = openpyxl.load_workbook('files/example.xlsx')
# 获取所有表的表名
sheets_names = wb.sheetnames
print(sheets_names) # 结果: ['表1', '表2']
# 获取活动表对应的表对象(表对象就是Worksheet类的对象)
active_sheet = wb.active
print(active_sheet) # 结果:<Worksheet "表1">
# 根据表名获取工作簿中指定的表
sheet2 = wb['表2']
print(sheet2) # 结果:<Worksheet "表2">
# 根据表对象获取表的名字
sheet_name1 = active_sheet.title
sheet_name2 = sheet2.title
print(sheet_name1, sheet_name2) # 结果:表1 表2
3.3 从表中取得单元格
获取到表以后,咱们可以通过表去获取表中的单元格:
表对象['列号行号']
- 获取指定列的指定行对应的单元格对象(单元格对象是 Cell 类的对象,列号是从A开始,行号是从1开始)表对象.iter_rows()
- 一行一行的取表对象.iter_cols()
- 列表一列的取单元格对象.value
- 获取单元格中的内容单元格对象.row
- 获取行号(数字1开始)单元格对象.column
- 获取列号(数字1开始)单元格对象.coordinate
- 获取位置(包括行号和列号)
import openpyxl
# 打开excel文件
wb = openpyxl.load_workbook('files/example.xlsx')
# 获取活跃表对象
sheet = wb.active
# 获取单元格对应的 Cell 对象
a1 = sheet['A1'] # A1 表示A列中的第一行,这儿的列号采用的是从A开始的
print(a1)
# 获取单元格中的内容
content = a1.value
print(content) # 结果是: Rank
# 获取单元格的行和列信息
row = a1.row
print('行:', row) # 结果: 1
column = a1.column
print('列:', column) # 结果: 1
coordinate = a1.coordinate
print(coordinate) # 结果:A1
用字母来指定列,这在程序中可能有点奇怪,特别是在 Z 列之后,列开始使用两个字母:AA、AB、AC 等。作为替代,在调用表的 cell()方法时,可以传入整数 作为 row 和 column 关键字参数,也可以得到一个单元格。第一行或第一列的整数 是 1,不是 0。
表对象.cell(行号, 列号)
- 获取指定行指定列对应的单元格(这儿的行号和列好号都可以用数字)
# 获取第二列的所有内容
row_num = sheet.max_row # 获取当前表中最大的行数
for row in range(1, row_num+1):
cell = sheet.cell(row, 2)
print(cell.value)
'''
结果:
Language
Python
Java
C
C++
R
JavaScript
C#
Matlab
Swift
Go
'''
3.4 从表中取得行和列
咱们的表对象可以想列表或者字符串那样进行切片操作,来获取电子表格中一行、一列或一个矩形区域中的所有 Cell 对象。具体的用法如下:
表对象[位置1:位置2]
- 获取指定范围中的所有的单元格
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
# 打开excel文件
wb = openpyxl.load_workbook('files/example.xlsx')
# 获取表
sheet = wb.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>),)
for row_cells in row2:
for cell in row_cells:
print(cell.coordinate, cell.value)
# 2.获取整个列的单元格
max_row = sheet.max_row
columnB = sheet['A1':'A%d' % max_row]
# 获取B列对应的所有单元格对象
for column_cells in columnB:
for cell in column_cells:
print(cell.coordinate, cell.value)
# 3. 获取矩形区域中的单元格对象
cell_tuples = sheet['A1': 'C3']
for cells in cell_tuples:
for cell in cells:
print(cell.coordinate, cell.value)
二.新建
1. 创建并保存 Excel 文档
openpyxl.Workbook()
- 创建空的 Excel 文件对应的工作簿对象工作簿对象.save(文件路径)
- 保存文件
import openpyxl
# 创建空的Workbook对象(新建一个Excel文件对应的工作簿)
wb = openpyxl.Workbook()
# 获取工作簿中所有表的名字(默认情况下新建的 Excel 文件中只有一张名字是 'Sheet' 的表)
print(wb.sheetnames) # 结果: ['Sheet']
# 获取活跃表
sheet = wb.active
# 修改表的名字
sheet.title = '学生信息表'
# 保存 Excel 文件
wb.save('files/school.xlsx')
2. 创建和删除工作表
工作簿对象.create_sheet(title, index)
- 在指定工作簿中的指定位置(默认是最后)创建指定名字的表,并且返回表对象工作簿对象.remove(表对象)
- 删除工作簿中的指定表
import openpyxl
# 创建工作簿(默认有一张名字是 Sheet 的表)
wb = openpyxl.Workbook()
print(wb.sheetnames) # ['Sheet']
# 新建表
wb.create_sheet()
print(wb.sheetnames) # ['Sheet', 'Sheet1']
wb.create_sheet('财务')
print(wb.sheetnames) # ['Sheet', 'Sheet1', '财务']
wb.create_sheet(index=0, title='销售')
print(wb.sheetnames) # ['销售', 'Sheet', 'Sheet1', '财务']
# 删除表
wb.remove(wb['财务'])
print(wb.sheetnames) # ['销售', 'Sheet', 'Sheet1']
3. 将数据写入表格中
表对象[位置] = 值
- 在表中指定位置对应的单元格中写入指定的值,位置是字符串:‘A1’(第1列的第一行)、‘B1’(第二列的第一行)
import openpyxl
wb = openpyxl.load_workbook('files/school.xlsx')
sheet = wb.active
sheet['A1'] = '姓名'
sheet['B1'] = '年龄'
sheet['C1'] = '性别'
sheet['A2'] = '张三'
sheet['B2'] = 28
sheet['C2'] = '男'
wb.save('files/school.xlsx')
4. 将序列添加到入表格中
import openpyxl
wb = openpyxl.load_workbook('files/school.xlsx')
sheet = wb.active
list1 = [x for x in range(10)]
sheet.append(list1)
#sheet.insert_rows(idx, amount=1)
"""
Insert row or rows before row==idx
"""
#sheet.insert_cols(idx, amount=1)
"""
Insert column or columns before col==idx
"""
wb.save('files/school.xlsx')
个人实例
import openpyxl
import os
import csv
def save_to_xlsx(ls1, file, sheetname):
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = sheetname
try:
for ls in ls1:
sheet.append(ls)
finally:
wb.save(file)
def add_to_xlsx(ls1, file, sheetname, wb=None):
if not wb:
if os.path.exists(file):
wb = openpyxl.load_workbook(file)
else:
wb = openpyxl.Workbook()
if sheetname in wb.sheetnames:
sheet = wb[sheetname]
else:
sheet = wb.create_sheet(sheetname)
try:
for ls in ls1:
sheet.append(ls)
sheet
return wb
finally:
wb.save(file)
def csv_to_xlsx(csv_file, xlsx_file, sheetname):
if not os.path.exists(csv_file):
raise FileNotFoundError('csv文件目录无效...')
with open(csv_file, 'r', newline='', encoding='utf-8') as f:
reader = csv.reader(f)
save_to_xlsx(reader, xlsx_file, sheetname)
import csv
import openpyxl
from openpyxl.utils import get_column_letter
wb = openpyxl.Workbook()
# wb.create_sheet('京东csv')
# sheet = wb['京东csv'] # 通过这种方式获取sheet时, 表格无法添加到活动表格
sheet = wb.active
sheet.title = '京东'
# jd.csv - 要转换成excel的文件
with open(r'./files/jd.csv', 'r', newline='', encoding='utf-8') as f:
reader = csv.reader(f)
try:
row = 1
while True:
row_values = next(reader)
for index in range(len(row_values)):
column = get_column_letter(index+1)
location = f'{column}{row}'
sheet[location] = row_values[index]
row += 1
except StopIteration:
print('加载完成...')
finally:
# 保存到新xlsx文件
wb.save(r'./files/jd.xlsx')