from openpyxl import load_workbook
from openpyxl.styles import PatternFill, colors, fills
import os.path
_EXCEL_4_TEST = 'DTS 单.xlsx'
def test_read_excel():
"""
读取已有的excel工作簿
:return:
"""
workbook0 = load_workbook(filename=_EXCEL_4_TEST, read_only=True)
print(workbook0.sheetnames) # ['webserver收编 DTS', 'webserver收编 IRTT', 'webserver DTS']
print(workbook0.active) # 切换worksheet时需要保存一下,这里才能看出来
# #worksheet0 = workbook0.worksheets[0]
worksheet0 = workbook0['webserver收编 DTS']
print(worksheet0.title)
# 读一个单元格
cell0 = worksheet0.cell(row=2, column=1)
# cell0 = worksheet0['A2']
print(cell0.value, cell0.coordinate, cell0.column, cell0.row)
# cell1 = worksheet0.cell(row=2, column=5, value='') # 修改单元格的值,而不是读不到返回默认值
# print(cell1.value)
# 读取整个sheet
# for row in worksheet0.rows:
# for cell in row:
# print(cell.value, end='\t')
# print(os.linesep)
# 读取一块区域(printarea)
for row in worksheet0.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
for cell in row:
print(cell.value, end='\t')
print(os.linesep)
workbook0.close()
def test_modify_excel():
"""
修改包含创建和删除
1. 修改excel工作簿
场景1.1,读取已有的文件,保存的时候与原文件名不一致,即“另存为”
场景1.2,读取已有的文件,保存的时候与原文件名一致,即“修改后保存”
场景1.3,从无到有
场景1.4,从有到无 -- 直接删掉文件即可
2.修改worksheet
场景2.1,修改sheet1,保存
场景2.2,复制sheet1,修改保存
场景2.3,新建sheet2
场景2.4,删除sheet2
3. 修改cell
场景3.1,修改cell的值
场景3.2, 修改列宽和行高
4. 修改一行,一列
补充场景:修改后不保存 -- 已试过
"""
origin_filename = _EXCEL_4_TEST
[origin_filename, origin_fileext] = os.path.splitext(origin_filename) # python也支持解构赋值
test_file_template = origin_filename + '%s' + origin_fileext
# 场景1.1, 2.1, 3.1
# workbook0 = openpyxl.load_workbook(filename=_EXCEL_4_TEST, read_only=False)
# #workbook0 = openpyxl.open(filename=_EXCEL_4_TEST)
# sheet0 = workbook0['webserver收编 DTS']
# sheet0['A1'].value = '场景1.1'
# #sheet0.cell(row=1, column=1, value='场景1.1')
# sheet0.append(['20191216010100', 'ZAM定时任务启动失败', '赵六', '待修改', '需要BME平台人员支撑']) # 增加一行
#
# for row in sheet0.rows:
# for cell in row:
# print(cell.value, end='\t')
# print(os.linesep)
# workbook0.save(filename=test_file_template % '-场景1.1')
# workbook0.close()
# 场景1.2, 2.2, 2.4
# wb1 = openpyxl.load_workbook(filename=test_file_template % '-场景1.1')
# ws1 = wb1.copy_worksheet(wb1['webserver收编 DTS'])
# ws1.title = '场景1.2' # 默认sheet名是原sheet名+空格+Copy
# #sheet1 = wb3['webserver收编 DTS'+' Copy']
# sheet1 = wb1.worksheets[-1] # 新建的sheet一般放在最后
# print(sheet1.title, sheet1.path, sheet1.values, sheet1.active_cell, sep=os.linesep) # sheet1.values 有点奇怪,可以研究下
# wb1.save(filename=test_file_template % '-场景1.2')
# wb1.close()
# 场景1.3, 2.3
# wb2 = openpyxl.Workbook() # 创建新的工作簿之后默认会有一个Sheet
# print(wb2.sheetnames)
# wb2.create_sheet(title='场景2.3')
# wb2.remove(wb2['Sheet'])
# wb2.save(filename=test_file_template % '-场景1.3')
# wb2.close()
# 场景3.2
wb3 = load_workbook(filename=test_file_template % '-场景1.1')
print(wb3.encoding)
sheet3 = wb3.worksheets[0]
sheet3.column_dimensions['B'].width = 80
sheet3.row_dimensions[3].height = 40
sheet3.sheet_format.defaultColWidth = 30
# sheet3.conditional_formatting.add(range_string='1:3', cfRule=)
fill_1 = PatternFill("solid", fgColor=colors.GREEN)
fill_2 = PatternFill(bgColor=colors.YELLOW, fill_type=fills.FILL_SOLID)
sheet3['B2'].fill = fill_1 # 给单元格设置背景色
sheet3.row_dimensions[5].fill = fill_2 # 只能在每一行的行尾空白位置添加背景色
wb3.save(filename=test_file_template % '-场景3.2')
wb3.close()
if __name__ == '__main__':
# test_read_excel()
test_modify_excel()
'''
sheet.rows, sheet.columns 是什么?
sheet['A'] 表示第一列? 是的,只读
sheet['A:E'] 是的,只读
sheet['1'] 表示第一行? 是的,只读
sheet['2:6'] 是的,只读
'''