openpyxl编辑excel表(1)

打开excel表，修改内容

参考
(1) https://wenku.csdn.net/answer/faeec15ba44f40b4924216b42e030251
(2) https://www.jianshu.com/p/3a8967723178
(3) https://blog.csdn.net/fuyangkejia/article/details/123402107

1.导入openpyxl库

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font

2.打开excel文件,读取内容

need_modify_file=r'/home/wyw/zq.xlsx'
depend_file=r'/home/wyw/wp.xlsx'
#打开第一个excel文件:
#如果需要去公式，data_only改成TRUE；如果需要只读，read_only改成TRUE；
zqbook=openpyxl.load_workbook(need_modify_file, data_only="True")
#使用sheet名
zqSheet=zqbook['vul_descript']

#my_res = list(mySheet.rows)
# 按行获取单元格（第一行除外--标题，不是数据）
zqRows = list(zqSheet.values)[1:]

#打开第二个excel文件
wpbook = openpyxl.load_workbook(depend_file, data_only="True")
#定位sheet页签
wpSheet=wpbook['vul_report']
#按行获取单元格（第一行除外--标题，不是数据）
wpRows = list(wpSheet.values)[1:]

line=1
for z in zqRows:
    line += 1
    #如果包含如下关键字，则跳过
    if "server.jar" in  z[13] or "mpserver.tar.gz" in  z[13] or "mp_pre" in  z[13] or "package_business_hwzs.tar.gz" in  z[13] or "bc_pre" in  z[13] or "awd_package" in  z[13] or "awd_hwzs" in  z[13] or  "deploy_vuln.tar.gz" in  z[13] :
        continue
    
    #如果已经填过值了，则跳过，z[7]=str(z[7])—避免是None类型导致len函数报错
    if z[7] is not None and z[9] is not None :
        if len(z[7]) > 1 or len(z[9]) > 1:
            print('*********')
            continue

    for w in wpRows:
        #对比组件名 ，   CVE编号，  所属的文件路径 是否都一致
        if z[0] == w[0] and z[2] == w[2] and z[13] == w[13]:
            #如果是None类型则跳过:
            if w[7] is None or w[9] is None :
                if z[5] is not None and int(float(z[5])) < 7 :
                    zqSheet.cell(line, 8).value = "False positive"
                    zqSheet.cell(line, 9).value = "Other"
                    zqSheet.cell(line, 10).value = "cvss<7无需处理"
                continue
            #如果字段为空则跳过
            if len(w[7]) < 2 or len(w[8]) < 2 :
                if z[5] is not None and int(float(z[5])) < 7 :
                    #设置单元格的值
                    zqSheet.cell(line, 8).value = "False positive"
                    zqSheet.cell(line, 9).value = "Other"
                    zqSheet.cell(line, 10).value = "cvss<7无需处理"
                continue

            print("[",line,"]",":",w[7],"=---=",w[8],"=---=",w[9])
            zqSheet.cell(line, 8).value = w[7]
            zqSheet.cell(line, 9).value = w[8]
            zqSheet.cell(line, 10).value = w[9]
            print("--------------------")

保存文件，关闭文件

zqbook.save(need_modify_file)
#zqbook.save()
zqbook.close()
wpbook.close()

print('----------END-------------')

3.补充说明(行列、字体、字号、颜色修改):

zqRows = list(zqSheet.values)[1:]
print(type(zqRows))   #为list列表，每行数据为一个元组
print(zqRows)     #结果如下图：

分数90以上的都标记为:字体12号、加粗、红色字

#行列都是从1开始编号，数组是从0开始编号:
line=1
font = Font(name='Calibri',size=12,bold=True,italic=False,color='FF0000')
for r_v in zqRows:
    line += 1
    for col in range(3,8):
        if int(float(r_v[col])) >= 90 :
            zqSheet.cell(line, col+1).font = font

4.调整列宽、行高:

# 调整指定列的列宽
zqSheet.column_dimensions['A'].width = 10
zqSheet.column_dimensions['B'].width = 20
zqSheet.column_dimensions['C'].width = 20
# 调整指定行的行高
zqSheet.row_dimensions[1].height = 40

zqbook.save(need_modify_file)
zqbook.close()

4.2 调整所有行与列的宽高(get_column_letter)

from openpyxl.utils import get_column_letter
for i in range(1, zqSheet.max_row+1):
     zqSheet.row_dimensions[i].height = height
for i in range(1, zqSheet.max_column+1):
     zqSheet.column_dimensions[get_column_letter(i)].width = width
#比如,第一列宽度:get_column_letter(1)
zqSheet.column_dimensions[get_column_letter(1)].width = 60

5.合并单元格

参考： https://blog.csdn.net/m0_57793634/article/details/131899401
### 合并单元格，水平垂直居中:

from openpyxl.styles import Alignment
from openpyxl import load_workbook
 
wb = load_workbook("excel.xlsx")
 
ws = wb["Sheet1"]
 
ws.merge_cells('A1:G1')
ws.merge_cells(start_row = 3, start_column =1, end_row = 3, end_column = 4)
### 居中对齐方式1：
ws['A1'].alignment = Alignment(horizontal = "center", vertical = "center")

### 居中对齐方式2：
# 创建一个对齐对象并设置属性
align = Alignment(horizontal='center', vertical='center', wrap_text=True, indent=1, text_rotation=90) 
# 将对齐对象应用到单元格
ws.cell(row=2, column=2).alignment = align

## 居中对齐方式3——整列居中:
# 创建一个Alignment对象，设置水平对齐方式为居中(center)
alignment=Alignment(horizontal = "center", vertical = "center")
# 应用这个对齐方式到第一列的所有单元格
for cell in mubiao_sheet['B']:
    cell.alignment = alignment
for cell in mubiao_sheet['E']:
    cell.alignment = alignment
wb.save("excel.xlsx")

举例：

#合并excel表最后一行：
all_row=zqSheet.max_row+1
all_col=zqSheet.max_column+1
zqSheet.merge_cells(start_row = all_row, start_column =1, end_row = all_row, end_column = all_col)
zqSheet.cell(all_row, 1).value='最后一行'

6.单元格背景色

import openpyxl
from openpyxl.styles import Font            # 导入字体模块
from openpyxl.styles import PatternFill     # 导入填充模块


wb = openpyxl.Workbook()        # 创建一个excel文件
sheet = wb.active               # 获得一个的工作表
sheet.title = "控制颜色"

# 设置填充颜色
fille = PatternFill('solid', fgColor="FF00FF")
sheet.cell(1, 1, "洋红色").fill = fille

# 设置字体样式  字体大小为30， bold 加粗
font = Font(u'微软雅黑', size=30, bold=True, color="0000FF")
sheet.cell(1, 2, "纯蓝").font = font

wb.save("./data/设置颜色.xlsx")

6.2获取最后一行、最后一列

sheet.max_row属性获取工作表中的最大行数
sheet.max_column 获取工作表最大列数

all_row=zqSheet.max_row+1
all_col=zqSheet.max_column+1
print("最后一行:%s, 最后一列:%s" %(all_row, all_col))
#输出结果:
## 最后一行:15, 最后一列:10； 正常应该是9，这里最后一列虽没有内容但可能编辑过多了一列

分析-最后一行最后一列先打印出来，然后对照是否正确：

## 内容一共14行+1=15行
## 内容一共8列+1=9列，这里明显比预期多了1列，留意！！
  测试了别的excel打印是正确的，这个excel表可能最后一列有编辑过虽然没有内容，但也被统计了。

6.3 背景色举例：

fille = PatternFill('solid', fgColor="FF00FF")
#最后一行 第一列，填充颜色：
zqSheet.cell(all_row, 1, "洋红色").fill = fille