1、安装openpyxl
pip install openpyxl
2、打开已有工作簿
fbook = openpyxl.load_workbook('test.xlsx')
3、新建工作簿
fbook = Workbook(write_only=True) # 新建文件时不会自动创建一个sheet
fbook = Workbook() # 新建文件时会自动创建一个sheet
fbook.save('test.xlsx')
4、工作簿相关命令
1.创建sheet
fbook.create_sheet(title=,index=) # 新建sheet不会主动定位至该sheet,如果要对新建sheet进行操作还是要使用active命令
fbook.create_chartsheet(title=,index=) # 新建图表
2.移动sheet
# move_sheet(self, sheet, offset=0) sheet为sheet对象或sheetname,offset为偏移,正数表示向右移动,负数表示向左移动
fbook.move_sheet('Sheet',1)
3.删除sheet
#remove(self, worksheet)
fbook.remove(fbook['移动'])
4.获取sheet信息
fbook.sheetnames # 以列表的形式输出所有sheet名称,不区分worksheet、chartsheet
fbook.title # 输出当前工作表的表名
fbook.worksheets # 以列表形式输出全部worksheet对象,[<Worksheet "Sheet">, <Worksheet "test2">]
fbook.chartsheets # 以列表形式输出全部chartsheet对象,[<Chartsheet "test">]
5.指定sheet
若要在指定sheet内操作数据,可通过:指定表名获取sheet对象、active切换激活获取sheet对象。
fsheet = fbook[sheetname] # 获取指定表名的表对象,即worksheet对象
fbook.active = fb[sheet_name] # 设置active工作表,通过worksheet对象设置
fbook.active = 2 # 设置active工作表,通过sheet索引值设置
fsheet = fbook.active # 获取当前active工作表,返回worksheet对象
5.遍历所有sheet
for fsheet in fbook.worksheets:
print(fsheet)
for sheet in fbook.sheetnames:
fsheet = fbook[sheet]
for i in range(len(fbook.worksheets)+1):
fbook.active = i
fsheet = fbook.active
6.获取指定sheet索引
fbook.index(fsheet ) # 获取指定sheet的索引值,需传入一个worksheet对象
7.复制sheet
fbook.copy_worksheet(fsheet)
4、工作表相关命令
1.获取有效单元格区域
fsheet.calculate_dimension() # 获取含有内容的单元格的最小矩形区域。返回如:A1:C4
2.获取列数行数
fsheet.max_column #获取最大列值(int)
fsheet.min_column # 获取最小列值(int)
fsheet.max_row #获取最大行值(int)
fsheet.max_row #获取最小行值(int)
3.遍历行/列
fsheet.iter_rows() # 按行进行遍历返回单元格对象,返回一个生成器,需用for循环读取。
fsheet.rows # 同iter_rows()一样
fsheet.iter_cols() # 按列进行遍历返回单元格对象,返回一个生成器,需用for循环读取。
fsheet.columns
4.获取工作表内所有单元格数据
fsheet.values # 获取最小矩形区域内所有的单元格的数据,返回一个生成器,需用for循环读取,每一次循环获取一行单元格的数据,以tuple形式返回
for i in fsheet.values:
print(i)
#输出
'''
('1', '1', '1')
('1', '1', '1')
('1', '1', '1')
('1', '1', '1')
'''
5.获取指定单元格数据
fsheet.cell(1,2).value # cell()指定单元格,value取值
fsheet['A2'].value
6.按行遍历获取单元格数据
for row in fsheet.iter_rows():
for cell in row:
print(cell.value)
7.按列遍历获取单元格数据
for clo in fsheet.iter_cols():
for cell in clo:
print(cell.value)
8.写入单元格数据
# cell(self, row, column, value=None)
fsheet.cell(1,2,4)
fsheet['C5'] = 7
cell.value = 1 # cell需为cell对象
9.按行遍历写入单元格数据
for row in fs.iter_rows():
for cell in row:
cell.value = 1
10.按列遍历写入单元格数据
for clo in fsheet.iter_cols():
for cell in clo:
cell.value = 1
11.以行写入数据序列
从空白行开始写入数据,序列中每个值写入每列
fsheet.append([1,2,3])
fsheet.append({'A': 111,'B':2222,'C':3333}) # A、B对应列值
fsheet.append({1 : 'This is A1', 3 : 'This is C1'}) # 1、3对应列值
12.合并单元格
# merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
fsheet.merge_cells(start_row=1, start_column=1, end_row=2, end_column=2)
sheet.merge_cells('B1:G1')
13.获取合并单元格对象
fsheet.merged_cell_ranges # 以列表形式返回当前sheet内的合并单元格区域,返回一个列表,如:[<MergedCellRange A1:B2>]
14.取消合并单元格
fsheet.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=2)
15.插入行
# insert_rows(self, idx, amount=1) idx即行索引,amount即要插入的行数
fsheet.insert_rows(2, 2)
16.插入列
# insert_cols(self, idx, amount=1) idx即列索引,amount即要插入的列数
fsheet.insert_cols(2, 2)
17.删除行
# delete_rows(self, idx, amount=1)
fsheet.delete_rows(1,2)
18.删除列
# delete_cols(self, idx, amount=1)
fsheet.delete_cols(2,3)
19.移动单元格
# move_range(self, cell_range, rows=0, cols=0, translate=False) cell_range表示单元格,rows和clos要移动的行列数,如果原单元格有值会被覆盖掉
fsheet.move_range('C5',2,2)
5、单元格相关命令
1.获取单元格处于第几列
cell.col_idx # 单元格处于第几列,比如A1则返回1
2.获取单元格的具体行列数值
cell.coordinate # 单元格在第几列第几个单元格,返回形式为‘A1’,‘B2’
3.获取单元格的列值
cell.column_letter # 单元格的具体列值,返回形式为‘A’,‘B’
4.获取单元格数据
cell.value # 获取单元格的具体数据
5.写入单元格数据
cell.value = [value] # 设置单元格的值
6.获取指定单元格偏移的单元格对象
cell.offset(row=,column=) # 返回相对于此单元格的单元格位置,返回的是一个单元格对象
6、合并单元格相关命令
1.获取合并单元格对象
merge = fsheet.merged_cell_ranges # 获取当前工作表中所有的合并单元格对象列表,返回如:[<MergedCellRange A1:A4>, <MergedCellRange D1:D4>]
fm = merge[0]
2.获取合并单元格大小
fm.size # 获取合并单元格大小,即占几行几列,返回形式如:{'columns': 1, 'rows': 4}
3.获取合并单元格最大行数
fm.max_row # 合并单元格最大行数
4.获取合并单元格最小行数
fm.min_row # 合并单元格最小行数
5.获取合并单元格最大列数
fm.max_col
6.获取合并单元格最小列数
fm.min_col
7.按行获取合并单元格位置信息
fm.rows # 按行遍历合并单元格对象,返回一个生成器,for循环获取,每次读取一行,以列表形式返回当前行的合并单元格位置
for i in fm.rows:
print(i)
'''
[(1, 1), (1, 2)]
[(2, 1), (2, 2)]
'''
8.按列获取合并单元格位置信息
fm.cols #按列遍历合并单元格对象,返回一个生成器,for循环获取,每次读取一列,以列表形式返回当前列的合并单元格位置
for i in fm.cols:
print(i)
'''
[(1, 1), (2, 1)]
[(1, 2), (2, 2)]
'''
9.按列获取合并单元格位置信息
fm.cells # 按单元格遍历合并单元格对象,返回一个生成器,for循环获取,以tuple形式返回当前单元格位置
for i in fm.cells:
print(i)
'''
(1, 1)
(1, 2)
(2, 1)
(2, 2)
'''
10.获取合并单元格的区域
fm.bounds # 以tuple形式返回合并单元格的区域,如(1, 1, 2, 2),分别对应self.min_col, self.min_row, self.max_col, self.max_row
fm.coord # 返回形式为A1:B2
7、单元格属性相关命令
1.字体
from openpyxl.styles import Font, colors
# 字号24,字体为蓝色,字体加粗,字体为等线
fsheet['A1'].font = Font(name='等线', size=24, italic=True, color=colors.BLUE, bold=True)
2.对齐方式
from openpyxl.styles import Alignment
# 设置C1水平和垂直居中,右对齐和左对齐可取right、left
fsheet['C1'].alignment = Alignment(horizontal='center', vertical='center')
3.行高列宽
# 设置第2行行高
fsheet.row_dimensions[2].height = 40
# 设置C列列宽
fsheet.column_dimensions['C'].width = 30
8、辅助命令
1.列字母表示与数字表示的转换
from openpyxl.utils import get_column_letter, column_index_from_string
get_column_letter(4) # 数字表示转换为字母
column_index_from_string('F') # 字母表示转换为数字