openpyxl处理EXCEL

旧城等待， 2022-08-30 15:50 188阅读 1赞

### 目录 ###

*   *  一： 数据操作：
     *   *  1: 创建文件，创建表，保存文件
         *  2： 加载文件，列出所有表，切换表
         *  3： 定位单元格与写入单元格：
         *  4： 遍历单元格，打印数据
         *  5： 删除指定的行和列，获取最大的行与列：
         *  6： 合并单元格：
     *  二：样式操作：
     *   *  1： 颜色设置：
         *  2： 字体设置：
         *  3： 边框设置：
         *  4： 对齐设置：
         *  5： 链接设置：
         *  7： 行高与列宽的设置：
         *  8： 多样式设置：

## 一： 数据操作： ##

### 1: 创建文件，创建表，保存文件 ###

from openpyxl import Workbook
    
    # 1: 实例化一个Workbook对象，注意是大写的W
    book = Workbook()
    # 2: 创建一个两个表，指定第几个，从0开始。
    sheet1 = book.create_sheet("工作表1", 0)
    sheet2 = book.create_sheet("工作表2", 1)
    # 3： 将这个文件进行保存
    book.save("测试文件.xlsx")

### 2： 加载文件，列出所有表，切换表 ###

from openpyxl import Workbook, load_workbook
    
    # 1: 加载文件
    book = load_workbook("测试文件.xlsx")
    # 2：打印所有表的名称,默认名称Sheet
    print(book.sheetnames) # ['工作表1', '工作表2', 'Sheet']
    # 3: 打开指定的表
    sheet1 = book["工作表1"]
    # 工作表1
    print(sheet1.title) 
    # 4: 默认打开文件就是第一个此时可以使用book.active表示打开时的表
    sheet2 = book.active
    # 工作表1
    print(sheet2.title)

### 3： 定位单元格与写入单元格： ###

from openpyxl import load_workbook
    
    # 1: 打开文件
    book = load_workbook("测试文件.xlsx")
    # 2: 选择一个表，默认打开的那个
    sheet1 = book.active
    # 3: 选中一个单元格
    this_cell = sheet1['A1']
    print(this_cell)
    # 4: 选择一行
    row_cells = sheet1[1]
    # (<Cell '工作表1'.A1>,)
    print(row_cells)
    # 5: 选择一列
    col_cells = sheet1['A']
    # (<Cell '工作表1'.A1>,)
    print(col_cells)
    # 6: 选择单元格并赋值
    cell_1_1 = sheet1.cell(1, 1)
    cell_1_1.value = 20
    # 7: 追加一行
    sheet1.append([1, 2, 3])
    # 8: 进行保存
    book.save('测试文件.xlsx')

### 4： 遍历单元格，打印数据 ###

from openpyxl import load_workbook
    
    book = load_workbook('测试文件.xlsx')
    sheet1 = book.active
    
    # 1: 得到所有的行
    for row in sheet1.values:
        # 得到元组类型的值
        print(row)
    # 2：指定范围遍历
    for row_object in sheet1.iter_rows(min_col=1, max_col=3, min_row=1, max_row=3):
        # 遍历到的是每行的cell对象
        print(row_object)
    # 3：遍历对象和值的形式
    for row in sheet1.rows:
        for row_object in row:
            # 遍历单个对象和值
            print(row_object, row_object.value)
    
    # 4： 遍历所有的列
    for col in sheet1.columns:
        for col_object in col:
            print(col_object, col_object.value)
    
    # 5: 范围遍历列对象
    for col in sheet1.iter_cols(min_col=1, max_col=3, min_row=1, max_row=3):
        print(col)

### 5： 删除指定的行和列，获取最大的行与列： ###

from openpyxl import load_workbook
    
    book = load_workbook('测试文件.xlsx')
    sheet1 = book.active
    # 1: 删除指定的列
    sheet1.delete_cols(1)
    # 2： 删除指定的行
    sheet1.delete_rows(1)
    book.save('测试文件.xlsx')

# 3: 获取最大列
    print(sheet1.max_column)
    # 4：获取最大的行
    print(sheet1.max_row)

### 6： 合并单元格： ###

*  1: 构造初始测试数据：
    
        from openpyxl import load_workbook
        import random
        book = load_workbook("测试文件.xlsx")
        sheet = book.get_sheet_by_name("工作表2")
        for i in range(100):
            name = 'name%d' % i
            age = 'age%d' % i
            sex = 'sex%d' % random.randint(1, 2)
            sheet.append([name, age, sex])
        book.save("测试文件.xlsx")
 *  2： 合并单元格操作：
    
        from openpyxl import load_workbook
        
        book = load_workbook("测试文件.xlsx")
        sheet = book.get_sheet_by_name("工作表2")
        # 1： 合并相邻的指定的两个单元格
        # 合并之后B1的数据就没有了，但是单元格合并了。
        sheet.merge_cells("A1:B1")
        # 2：指定一个范围合并
        # 合并之后，数据只有左上角的数据了，其余的都没有了
        sheet.merge_cells(start_column=2, end_column=3, start_row=2, end_row=3)
        
        # 3: 打印存在合并的单元格, 只是输出左上角和右下角的坐标
        print(sheet.merged_cells)
        # A1:B1 B2:C3
        
        # 4: 输出的一样，只不过这个对象可以遍历
        for cell in sheet.merged_cell_ranges:
            print(cell)
        
        book.save("测试文件.xlsx")

## 二：样式操作： ##

### 1： 颜色设置： ###

*  索引设置0到63， 一共64种颜色。

Color(index=0) # 根据索引进行填充# Color(rgb='00000000') # 根据rgb值进行填充# index COLOR_INDEX = (    '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4    '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9    '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14    '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19    '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24    '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29    '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34    '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39    '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44    '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49    '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54    '00969696', '00003366', '00339966', '00003300', '00333300', #55-59    '00993300', '00993366', '00333399', '00333333',  #60-63)BLACK = COLOR_INDEX[0]WHITE = COLOR_INDEX[1]RED = COLOR_INDEX[2]DARKRED = COLOR_INDEX[8]BLUE = COLOR_INDEX[4]DARKBLUE = COLOR_INDEX[12]GREEN = COLOR_INDEX[3]DARKGREEN = COLOR_INDEX[9]YELLOW = COLOR_INDEX[5]DARKYELLOW = COLOR_INDEX[19]

*  案例： 设置A4单元格字体为红色：
    
        from openpyxl import load_workbookfrom openpyxl.styles import Font, Colorbook = load_workbook("测试文件.xlsx")sheet = book.get_sheet_by_name("工作表2")# 将A4单元格字体修改成红色sheet.cell(4, 1).font = Font(color=Color(index=2))book.save("测试文件.xlsx")

### 2： 字体设置： ###

*  案例: 设置A5单元格，字体大小15， 字体仿宋，字体加粗，字体倾斜。
    
        from openpyxl import load_workbookfrom openpyxl.styles import Font, Colorbook = load_workbook("测试文件.xlsx")sheet = book.get_sheet_by_name("工作表2")# name设置字体样式，size设置字体大小，b设置加粗，i设置倾斜sheet.cell(5, 1).font = Font(name='仿宋', color=Color(index=3), size=15, b=True, i=True)book.save("测试文件.xlsx")

### 3： 边框设置： ###

*  案例： 给A6单元格设置边框加粗，黑色。
    
        from openpyxl import load_workbookfrom openpyxl.styles import Font, Color, Side, Borderbook = load_workbook("测试文件.xlsx")sheet = book.get_sheet_by_name("工作表2")# 给A6设置边框加粗，颜色为黑色side = Side(style='thick', color=Color(index=0))border = Border(left=side, right=side, bottom=side, top=side)sheet.cell(6, 1).border = border# style可选项# style = ('dashDot','dashDotDot', 'dashed','dotted',# 'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',# 'mediumDashed', 'slantDashDot', 'thick', 'thin')#  'medium' 中粗#  'thin'  细#  'thick'  粗#  'dashed'  虚线#  'dotted'  点线book.save("测试文件.xlsx")

### 4： 对齐设置： ###

*  案例：给A7单元格设置：垂直居中和水平居中。
    
        from openpyxl import load_workbookfrom openpyxl.styles import Alignmentbook = load_workbook('测试文件.xlsx')sheet = book.get_sheet_by_name("工作表2")# 设置单元格，水平居中和垂直居中sheet.cell(7, 1).alignment = Alignment(horizontal='center', vertical='center')# # 参数可选项# horizontal = {'fill', 'distributed', 'centerContinuous', 'right',#               'justify', 'center', 'left', 'general'}# # vertical = {'distributed', 'justify', 'center', 'bottom', 'top'}book.save("测试文件.xlsx")

### 5： 链接设置： ###

*  案例： 给A8单元格设置链接，指向百度搜索网址。
    
        from openpyxl import load_workbook
        from openpyxl.styles import NamedStyle, Font, colors
        from openpyxl.worksheet.hyperlink import Hyperlink
        
        book = load_workbook("测试文件.xlsx")
        sheet = book.get_sheet_by_name("工作表2")
        # 1：给8，1单元格设置百度链接
        sheet.cell(8, 1).hyperlink = Hyperlink(ref='www.baidu.com', location='', target='')
        # 2：给9， 1单元格设置跳转到8，1单元格
        sheet.cell(9, 1).hyperlink = Hyperlink(ref='', location='工作表2!A9', target='')
        # 3: 给 10 ， 1设置跳转到本地的一个文件目录
        sheet.cell(10, 1).hyperlink = Hyperlink(ref='', location='', target='./测试文件2.xlsx')
        # 设置链接颜色和下划线
        link = NamedStyle(name='link', font=Font(color=colors.BLUE, underline='single'))
        sheet.cell(8, 1).style = link
        sheet.cell(9, 1).style = link
        sheet.cell(10, 1).style = link
        book.save("测试文件.xlsx")

### 7： 行高与列宽的设置： ###

*  案例： 调整整个文档的行高与列宽：
    
        from openpyxl.utils import get_column_letter
            # 注意：取列的时候是A,B,C，不是1， 2， 3所以可以使用工具类获取列名。
            for i in range(1, row+1):
                    sheet.row_dimensions[i].height = '20'
                for j in range(1, col+1):  
                    sheet.column_dimensions[get_column_letter(j)].width = '30'

### 8： 多样式设置： ###

*  1： 使用NamedStyle设置综合样式。
 *  2: 将这个样式追加到sheet中。
 *  3： 在需要该样式的单元格中使用该样式。
 *  4: 注意：给单元格设置样式的时候，要使用名字，尽量不要使用对象，会报一些错误。

案例： 设置综合样式： 样式名称为：simple\_style, 颜色设置为蓝色， 边框设置 为前后左右都设置成黑色加粗，然后再设置水平居中和垂直居中。给A11, A12, A13设置成这个样式。

from openpyxl import load_workbook
    from openpyxl.styles import NamedStyle, Font, colors, PatternFill, Color, Side, Border, Alignment
    
    book = load_workbook("测试文件.xlsx")
    sheet = book.get_sheet_by_name("工作表2")
    
    my_style = NamedStyle(name='simple_style',
                          font=Font(color=colors.BLUE),
                          border=Border(left=Side(style='thick', color=Color(index=0)),
                                        right=Side(style='thick', color=Color(index=0)),
                                        top=Side(style='thick', color=Color(index=0)),
                                        bottom=Side(style='thick', color=Color(index=0)),
                                        ),
                          alignment=Alignment(horizontal='center', vertical='center')
                          )
    # 追加到book中
    book.add_named_style(my_style)
    # 给单元格设置这个样式： 注意使用名称（字符串）
    sheet.cell(11, 1).style = 'simple_style'
    sheet.cell(12, 1).style = 'simple_style'
    sheet.cell(13, 1).style = 'simple_style'
    
    # 保存到本地
    book.save("测试文件.xlsx")