Python3 集成openpyxl

墨蓝 2023-10-10 12:26 18阅读 0赞

## openpyxl简介 ##

openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易 注意：如果文字编码是“gb2312” 读取后就会显示乱码，请先转成Unicode

### openpyxl操作excel核心对象 ###

#### Excel文件三个对象 ####

`workbook： 工作簿，一个excel文件包含多个sheet。`

`sheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。`

`cell： 单元格，存储数据对象`

### openpyxl 核心对象功能代码片段 ###

#### excel 读取: ####

#导入openpyxl package
    import openpyxl
    
    #打开一个已有工作簿
    f = openpyxl.load_workbook('test.xlsx')

#### sheet读取相关操作 ####

#获取sheet名称列表
    name_list = f.sheetnames
    name_list = f.get_sheet_names()
    
    #通过名称调用sheet
    table = f['name']
    table = f.get_sheet_by_name('name')
    
    #通过检索调用sheet
    table = f.get_sheet_by_name(sheet_names[index])
    
    #调用正在运行的sheet，一般为最后一个
    table = f.active    	
    
    #改变sheet的名字
    table.title = 'newname'

#### 单元格读取相关操作 ####

#读取单元格或范围切片
    c = table['A1']			#获取'A1'，返回class
    c = table.cell(row = 1,column = 1)	#按行列数获读取
    
    row5 = table[5]     	#获取第5行，返回元组
    colA = table['C']    	#获取C列
    cell_range = table['A1':'B4']    #获取范围切片元组
    row_range = table[5:8]	#5到8行
    col_range['A:D']		#A到D列
    
    #按行列数读取范围切片
    for row in table.iter_rows(min_row=1, max_col=3, max_row=2):    #按行读取
    for col in table.iter_cols(min_row=1, max_col=3, max_row=2):    #按列读取
    
    table.max_row    #最大行数
    table.max_column #最大列数
    table.rows    	 #按行遍历
    table.columns    #按列遍历
    
    #读取单元格的值
    c = table['A2'].value	#按位置读取
    c = table.cell(row = 1,column = 1).value	#按行列数读取

注意：openpyxl中按行列数检索时参数名‘row =’及‘column =’ 不能省略，而且均从1开始计数，这与xlrd有所不同

#### openpyxl 读取指定\*.xlsx 文件实例: ####

# 引入第三库:openpyxl
    import openpyxl
    
    # 加载指定*.xlsx文件
    file = openpyxl.load_workbook("C:\\11.xlsx")
    # 获取指定名称的Sheet
    sheet_1 = file.get_sheet_by_name('Sheet1')
    #获取有数据的最大行数
    row_number = sheet_1.max_row 
    
    print(row_number)
    # 遍历输出行记录
    for i in range(2,row_number):
        print(i)
        # 输出行记录，指定单元格内容值
        value1 = sheet_1.cell(i,1).value
        print(value1)

#### excel的创建及更改 ####

#导入openpyxl package
    import openpyxl
    
    #创建一个工作簿
    f = openpyxl.Workbook()

#### sheet新建相关操作 ####

每次新建一个工作簿会默认生成一个名称为“Sheet1”的工作表，可以先将其利用起来，操作与前面相同，比如:

table = f.active
    #or
    table = f['Sheet1']

当需要更多的工作表时，可以**创建新的sheet**

#创建sheet
    table = f.create_sheet('AD')    	#创建并插入末尾
    table = f.create_sheet('AD',0)      #创建并插入首位
    
    #可以通过RGB色值设置sheet标签颜色
    table.sheet_properties.tabColor = 'RRGGBB'
    
    #复制一个工作表
    target = f.copy_worksheet(table)

#### 单元格写入相关操作 ####

table['A2'] = 4    			 #单元格写入
    table['A1'] = '=SUM(1,1)'    #按公式写入
    
    #按行列数写入
    table.cell(row = 3,column = 1,value = 10)
    table.cell(row = 3,column = 1).value = 10
    
    #单元格合并与分解
    table.merge_cells('A1:B1')	  #按位置
    table.unmerge_cells('A1:B1')
    
    table.merge_cells(star_row = 2,star_column = 1,end_row = 2,end_column = 4)	#按行列号
    table.unmerge_cells(start_row = 2,star_column = 1,end_row = 2,end_column = 4)
    
    #插入图片
    img = openpyxl.drawing.image.Image('demo.png')
    table.add_image(img,'A1')
    
    #保存文件
    f.template = True    #存为模板
    f.save('demo.xlsx', as_template = False)

#### excel 格式控制 ####

openpyxl中提供的Style方法用于调整表格的外观，如字体格式、边框、对齐方式、单元格背景、保护等。

# 导入相关模块
    from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
    
    #字体格式
    font0 = Font(name='Calibri',
                size=11,
                bold=False,
                italic=False,
                vertAlign=None,    #Maybe：'baseline', 'superscript', 'subscript'
                underline='none',  #Maybe：'single','double','singleAccounting','doubleAccounting'
                strike=False,
                color='FF00FF00')
    
    #单元格填充
    fill0 = PatternFill(fill_type=None,
    #Maybe：'lightUp', 'darkVertical', 'darkGrid', 'solid', 'darkHorizontal', 'darkUp', 'lightVertical', 'lightGray', 'darkTrellis', 'lightDown', 'gray125', 'gray0625', 'mediumGray', 'lightTrellis', 'darkGray', 'darkDown', 'lightHorizontal', 'lightGrid'
                start_color='FFFFFFFF',
                end_color='FF000000')
    
    #边框
    border0 = Border(left=Side(border_style=None,color='FF000000'),
    #style Maybe：'mediumDashDotDot', 'dotted', 'thick', 'medium', 'dashDotDot', 'double', 'dashed', 'mediumDashed', 'dashDot', 'mediumDashDot', 'hair', 'slantDashDot', 'thin'
    				right=Side(border_style=None,color='FF000000'),
    				top=Side(border_style=None,color='FF000000'),
    				bottom=Side(border_style=None,color='FF000000'),
    				diagonal=Side(border_style=None,color='FF000000'),
    				diagonal_direction=0,
    				outline=Side(border_style=None,color='FF000000'),
    				vertical=Side(border_style=None,color='FF000000'),
    				horizontal=Side(border_style=None,color='FF000000')
    				)
    
    #对齐方式
    alignment0 = Alignment(horizontal='general',	#Maybe:'centerContinuous', 'fill', 'right', 'distributed', 'justify', 'general', 'center', 'left'
                    	vertical='bottom',
                        text_rotation=0,
                        wrap_text=False,
                        shrink_to_fit=False,
                        indent=0)
    
    #表格保护
    protection0 = Protection(locked=True,
                             hidden=False)

#### excel 格式应用实践 ####

# 导入相关模块
    from openpyxl.styles import NamedStyle
    
    # 创建格式
    style0 = NamedStyle(name = 'style_example')
    
    #格式赋值
    style0.font = font0
    style0.alignment = alignment0
    style0.border = border0
    style0.fill = fill0
    style0.Protection = protection0
    
    #格式调用
    #单属性调用
    table['A1'].font = font0
    table['A1'].alignment = alignment0
    table.cell(row = 1,column = 1).border = border0
    
    #按名称调用
    table['A1'].style = style0
    table['A1'].style = 'style_example'
    table.cell(row = 1,column = 1).style = style0

#### openpyxl 格式化成绩单实例: ####

import openpyxl
    from openpyxl.styles import NamedStyle, Border, Side, Alignment
    
    # 生成工作簿及sheet
    f = openpyxl.Workbook()
    table = f.active
    table.title = '成绩单'
    
    # 信息列表
    subject_list = ['语文','思想品德','数学','科学']
    info_list = [
    ['小明',22,80,85,90,77],
    ['小红',23,91,88,95,90],
    ['李华',24,75,70,98,100]
    ]
    
    # 创建表头
    table.merge_cells('A1:A2')
    table.cell(row = 1,column = 1,value = '姓名')
    table.merge_cells('B1:B2')
    table.cell(row = 1,column = 2,value = '学号')
    table.merge_cells('C1:D1')
    table.cell(row = 1,column = 3,value = '文科')
    table.merge_cells('E1:F1')
    table.cell(row = 1,column = 5,value = '理科')
    
    for i in range(4):
    	table.cell(row = 2,column = i+3,value = subject_list[i])
    
    # 写入信息
    for obs in range(3):
    	for info in range(6):
    		table.cell(row = obs+3,column = info+1,value = info_list[obs][info])
    
    # 设置单元格格式，其中字体及背景为默认
    style0 = NamedStyle('style0')
    style0.border = Border(left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
    style0.alignment = Alignment(horizontal='center',vertical='center',)
    
    # 格式应用
    for row in table.rows:
    	for cell in row:
    		cell.style = style0
    
    # 保存文件
    f.save("score.xlsx")