openpyxl 批量操作单元格

1-openpyxl基础用法

2-openpyxl单元格样式处理

3-openpyxl日期格式处理

4-openpyxl单元格合并

5-openpyxl批量操作单元格

6-openpyxl数字格式处理

openpyxl 批量操作单元格

获取一行或一列单元格

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment

filename = 'rangeCells.xlsx'
wb = Workbook()
ws = wb.active

rangeCell1 = ws['G10']

# 获取一列单元格
colD = ws['D']
for c in colD:
    c.value = 'D'
    c.alignment = Alignment(horizontal='center')

# 获取一行单元格
row5 = ws[5]
for c in row5:
    c.value = 5
    c.alignment = Alignment(horizontal='center')

worksheet创建时不包含任何单元格，遍历行或者列的时候最多可以访问到操作过的单元格的最大列或行，默认只能遍历第一个单元格
如果在ws = wb.active之后直接调用colD = ws[‘D’]，则colD只包含一个单元格D1
rangeCell1 = ws[‘G10’]之后colD = ws[‘D’]，则colD包含D1-D10,共10个单元格
同样ws = wb.active之后直接调用row5 = ws[5]，则row5只包含一个单元格A5
rangeCell1 = ws[‘G10’]之后crow5 = ws[5]，则row5包含A5-G5,共10个单元格

获取当前页所有行单元格

# 遍历当前页所有行单元格填充色设置为绿色
for row in ws.rows:
    for c in row:
        c.fill = PatternFill(fgColor='39FF00', fill_type='solid')

### 获取当前页所有列单元格
# 遍历当前页所有列单元格填充色设置为绿色
for col in ws.columns:
    for c in col:
        c.border = Border(bottom=Side(style='dotted', color='0000ff'),
                          right=Side(style='dotted', color='0000ff'),
                          left=Side(style='dotted', color='0000ff'),
                          top=Side(style='dotted', color='0000ff'))

获取范围内单元格

# B3到F7区域单元格设置双边框
rangeCell = ws['B3:F7']
for r in rangeCell:
    for c in r:
        c.border = Border(bottom=Side(style='double', color='0000ff'),
                          right=Side(style='double', color='0000ff'),
                          left=Side(style='double', color='0000ff'),
                          top=Side(style='double', color='0000ff'))

设置行获取范围内单元格

# A4-G6背景设置为天橙色
# 第4-6行，1-7列的单元格
for row in ws.iter_rows(min_row=4, max_row=6, max_col=7):
    for c in row:
        c.fill = PatternFill(fgColor='F5B114', fill_type='solid')

设置列获取范围内单元格

# C1-E10背景设置为天蓝色
# 第3-5列，1-10行的单元格
for col in ws.iter_cols(max_row=10, min_col=3, max_col=5):
    for c in col:
        c.fill = PatternFill(fgColor='00E9FF', fill_type='solid')

执行结果

完整代码

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment

filename = 'rangeCells.xlsx'
wb = Workbook()
ws = wb.active

rangeCell1 = ws['G10']

# D列单元格的值设置为D，居中
colD = ws['D']
for c in colD:
    c.value = 'D'
    c.alignment = Alignment(horizontal='center')

# 第五行单元格的值设置为5，居中
row5 = ws[5]
for c in row5:
    c.value = 5
    c.alignment = Alignment(horizontal='center')


# 遍历行单元格填充色设置为绿色
for row in ws.rows:
    for c in row:
        c.fill = PatternFill(fgColor='39FF00', fill_type='solid')

# 遍历列单元格填充色设置为绿色
for col in ws.columns:
    for c in col:
        c.border = Border(bottom=Side(style='dotted', color='0000ff'),
                          right=Side(style='dotted', color='0000ff'),
                          left=Side(style='dotted', color='0000ff'),
                          top=Side(style='dotted', color='0000ff'))

# B3到F7区域单元格设置双边框
rangeCell = ws['B3:F7']
for r in rangeCell:
    for c in r:
        c.border = Border(bottom=Side(style='double', color='0000ff'),
                          right=Side(style='double', color='0000ff'),
                          left=Side(style='double', color='0000ff'),
                          top=Side(style='double', color='0000ff'))

# A4-G6背景设置为天橙色
for row in ws.iter_rows(min_row=4, max_row=6, max_col=7):
    for c in row:
        c.fill = PatternFill(fgColor='F5B114', fill_type='solid')

# C1-E10背景设置为天蓝色
for col in ws.iter_cols(max_row=10, min_col=3, max_col=5):
    for c in col:
        c.fill = PatternFill(fgColor='00E9FF', fill_type='solid')


wb.save(filename)