python-openpyxl 常见操作

python-openpyxl 常见操作

文件格式的转换

openpyxl只能操作xlsx文件，当我们遇到xls文件的时候就需要进行转化。

• 手动转换
• 使用pywin32模块进行转换

import os
import win32com.client as win32
filename = r'C:\Users\fghjk\Desktop\1.xls'
Excelapp = win32.gencache.EnsureDispatch('Excel.Application')
workbook = Excelapp.Workbooks.Open(filename)
# 转xlsx时: FileFormat=51,
# 转xls时:  FileFormat=56,
workbook.SaveAs(filename.replace('xls', 'xlsx'), FileFormat=51)
workbook.Close()
Excelapp.Application.Quit()
# 删除源文件
# os.remove(filename)

# 如果想将xlsx的文件转换为xls的话，则可以使用以下的代码：
# workbook.SaveAs(filename.replace('xlsx', 'xls'), FileFormat=56)

• 使用pandas模块进行转换

import pandas as pd
filename = r'C:\Users\fghjk\Desktop\1.xls'
filename2 = r'C:\Users\fghjk\Desktop\1.xlsx'
read_res = pd.read_excel(filename)
read_res.to_excel(filename2, index=False)

基本操作

主要流程：

• 获取工作簿对象：加载工作簿，根据工作簿对象可以操作工作表【获取工作表对象，获取工作表名，删除工作表等】
• 获取工作表对象：根据工作表对象，可以获取工作表相关的信息，可以操作具体的单元格，
• 操作单元格

工作簿

增：

创建工作簿：Workbook()、save()

from openpyxl import Workbook

# 创建一个工作簿，包含工作表my_sheet1
# 创建一个工作簿对象
wb = Workbook()
# 在索引为0的位置创建一个名为my_sheet1的工作表
ws = wb.create_sheet("my_sheet1",0)
# 保存
wb.save('Test.xlsx')
wb.close()

加载工作簿：load_workbook

from openpyxl import load_workbook
wb = load_workbook(file_name)

删：

import os
os.remove('Test.xlsx')

查：wb.sheetnames

查询所有工作簿中包含的表

from openpyxl import load_workbook
wb = load_workbook(r'''C:\Users\fghjk\Desktop\个人文件\Py_RW_Data\Test.xlsx''')
wb.sheetnames # ['my_sheet1', 'my_sheet2', 'my_sheet3', 'Sheet']

# 也可以使用wb.get_sheet_names()获取所有表名，但这个接口会有警告，建议使用wb.sheetnames

工作表

增：create_sheet(sheet_name,sheet_index)

创建my_sheet1、my_sheet2、my_sheet3三个工作表，加上默认的Sheet，共四个工作表

from openpyxl import Workbook

# 创建一个工作簿对象
wb = Workbook()
# 在索引为0的位置创建一个名为my_sheet1的工作表
ws1 = wb.create_sheet("my_sheet1",0)
# 在索引为1的位置创建一个名为my_sheet2的工作表
ws2 = wb.create_sheet("my_sheet2",1)
# 在索引为2的位置创建一个名为my_sheet3的工作表
ws3 = wb.create_sheet("my_sheet3",2)
# 保存
wb.save('Test.xlsx')
wb.close()

删：del wb[sheet_name]

删除指定的表名

del wb['Sheet']
wb.sheetnames # ['my_sheet1', 'my_sheet2', 'my_sheet3']

改

• 修改表名

# 修改表名
wb.sheetnames # ['my_sheet1', 'my_sheet2', 'my_sheet3']
ws = wb['my_sheet2'] 	# 获取表对象
ws.title = "sheet2" 	# 修改表名
wb.sheetnames		    # ['my_sheet1', 'sheet2', 'my_sheet3']

查：

查询工作表相关的信息

# 商品品类品牌09071639.xlsx 以此文件为示例
'''
1-加载工作簿
2-获取所有表名
3-获取对应的的工作表对象
4-查询对应的工作表信息
'''
from openpyxl import load_workbook
wb = load_workbook(r'''C:\Users\fghjk\Desktop\个人文件\Py_RW_Data\商品品类品牌09071639.xlsx''')
sheetnames = wb.sheetnames   # ['Sheet0']
ws = wb[sheetnames[0]]		# 获取第一个工作表对象
# 以下内容为获取工作表相关的基础信息
max_row = ws.max_row 		# 最大行数 56815
max_column = ws.max_column	  # 最大列数 8
ws.dimensions # 已启用的单元格范围  'A1:H56815'
ws.encoding		# 编码类型	utf-8

单元格

单元格的操作可以分为：读取内容，修改内容，修改单元格相关的样式

读取单元格内容：

ws.cell(index_i.index_j).value  # index_i,index_j表示单元格行和列

获取单元格的格式

ws.cell(index_i.index_j).fill
'''
ws.cell(1,1).fill

<openpyxl.styles.fills.PatternFill object>
Parameters:
patternType=None, fgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb', bgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'
'''

修改单元格内容

ws.cell(index_i.index_j).value = new_value
#  ws.cell(row=row_index, column=column_index+1, value=new_value)

修改单元格样式

from openpyxl.styles import Border, Alignment, Side
# 居中样式
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 边框样式
border = Border(left=Side(border_style='thin'),
                right=Side(border_style='thin'),
                top=Side(border_style='thin'),
                bottom=Side(border_style='thin'))
                
ws.cell(i,j).border = border
ws.cell(i,j).alignment = align

获取单元格对象后再执行操作

cell = ws["a6"] 					#修改单个单元格值
cell = ws.cell(6, 1, "冰冷的希望")  	 #修改单个单元格值（row_index,column_index,value）
# print(cell.value)  				#值
# print(cell.coordinate)  			 #单元格位置 A6
# print(cell.row)  					#行 6
# print(cell.column)  				#列 1
# print(cell.col_idx)  				#列 1
# print(cell.column_letter)  		 #列	A

合并、插入、删除单元格

# ws.merge_cells("b2:d4")  #合并单元格
# ws.unmerge_cells("b2:d4")  #取消合并单元格
# ws.insert_cols(2, 3)  #插入列
# ws.insert_rows(1, 2)  #插入行
# ws.delete_cols(2, 2)  #删除列
# ws.delete_rows(2, 2)  #删除行
# ws.move_range("c4:d5", 2, -2)# 行， 列

总结

对WorkSheet的操作：

self = Workbook类	说明
self.active -> Worksheet	读取当前激活的Worksheet，返回Worksheet类
self.create_sheet(sheetname, index) -> Worksheet	创建sheet, index可省略，返回Worksheet类。index从0开始，省略值为最后
self.copy_worksheet(Worksheet) -> Worksheet	复制sheet，返回Worksheet类，默认名称是原名 Copy。只能复制同一个Workbook下的Worksheet
self[sheetname] -> Worksheet	选中指定Worksheet，返回Worksheet类
self.sheetnames -> list of str	返回sheetname组成的列表
self.remove(sheetname)	删除指定Worksheet
del self[sheetname]	删除指定Worksheet

对Worksheet的信息查询和操作：

self=Worksheet类	说明
self.title -> str	读sheetnamet名称，字符串类型
self.title = str	修改sheetnamet名称
self.max_row -> num	读取最大列，从1开始，返回数字
self.min_row -> num	读取最小列，从1开始，返回数字
self.max_column -> num	读取最大行，从1开始，返回数字
self.min_column -> num	读取最小列，从1开始，返回数字

对Worksheet样式相关的调整（行高、列宽）

self=Worksheet类	说明
sheet.row_dimensions.height = 整表行高	调整整个表格所有行的行高
sheet.column_dimensions.width = 整表列宽	调整整个表格所有列的列宽
sheet.row_dimensions[行编号].height = 行高	调整指定行的行高
sheet.column_dimensions[列编号].width = 列宽	调整指定列的列宽

按行或按列操作：

self=Worksheet类	说明
self.append(list)	追加一行写入内容，list->字符串的列表
self.delete_rows(idx, amount=1)	从idx行起删除amount行，idx从1开始
self.delete_cols(idx, amount=1)	从idx列起删除amount列，idx从1开始
self.insert_rows(idx, amount=1)	在idx行前插入amount行，idx从1开始
self.insert_cols(idx, amount=1)	在idx列前插入amount列，idx从1开始
self.rows -> Iter(tuple(Cell))	所有行打包用于迭代，返回迭代器
self.columns -> Iter(tuple(Cell))	将所有列打包用于迭代，返回迭代器
self.iter_rows(min_row, max_row, min_col,<br/>max_col, valuse_only=False) -> Iter(tuple(Cell)) self.iter_rows(min_row, max_row, min_col,<br/>max_col, valuse_only=True) -> Iter(tuple(value))	在指定范围生成行迭代器 参数都可省略，返回迭代器, min_row/col默认为1，max_row/col默认为max
self.iter_cols(min_row, max_row, min_col,<br/>max_col, valuse_only=False) -> Iter(tuple(Cell)) self.iter_cols(min_row, max_row, min_col,<br/>max_col, valuse_only=True) -> Iter(tuple(value))	在指定范围生成列迭代器 参数都可省略，返回迭代器 min_row/col默认为1，max_row/col默认为max