openpyxl操作excel

import openpyxl
import warnings
warnings.filterwarnings("ignore")
"""
https://openpyxl.readthedocs.io/en/stable/styles.html
"""
#Python Excel Create and Save files:
mywb = openpyxl.Workbook()   #<openpyxl.workbook.workbook.Workbook object at 0x000001E6CBA69580>
res=mywb.get_sheet_names()
print(res)   #[‘Sheet‘]
sheet = mywb.active
print(sheet)   #<Worksheet "Sheet">
title=sheet.title
print(title)   #Sheet

#Set sheet name
sheet.title = ‘MyNewTitle‘
print(sheet.title)
res=mywb.get_sheet_names()
print(res)   #[‘MyNewTitle‘]

#Save excel
mywb.save(‘NewExcelFile.xlsx‘)



#Loading an already existing excel file in Python and saving a copy of it:
mywb = openpyxl.load_workbook(‘NewExcelFile.xlsx‘)
sheet = mywb.active
sheet.title = ‘Working on Save as‘
mywb.save(‘example_filetest.xlsx‘)



#Creating and Removing Sheets in Excel:
mywb = openpyxl.Workbook()
sheet=mywb.get_sheet_names()
print(sheet)   #[‘Sheet‘]
mywb.create_sheet()
sheet=mywb.get_sheet_names()
print(sheet)   #[‘Sheet‘, ‘Sheet1‘]
mywb.create_sheet(index=0, title=‘1st Sheet‘)
sheet=mywb.get_sheet_names()
print(sheet)   #[‘1st Sheet‘, ‘Sheet‘, ‘Sheet1‘]
mywb.create_sheet(index=2, title=‘2nd Sheet‘)
sheet=mywb.get_sheet_names()
print(sheet)   #[‘1st Sheet‘, ‘Sheet‘, ‘2nd Sheet‘, ‘Sheet1‘]


# Removing sheets from Excel Workbook:
mywb.remove_sheet(mywb.get_sheet_by_name(‘1st Sheet‘))
sheet=mywb.get_sheet_names()
print(sheet)   #[‘Sheet‘, ‘2nd Sheet‘, ‘Sheet1‘]
mywb.remove_sheet(mywb.get_sheet_by_name(‘Sheet‘))
sheet=mywb.get_sheet_names()
print(sheet)   #[‘2nd Sheet‘, ‘Sheet1‘]


# Python excel Writing Values in Cells:
from openpyxl.styles import *
from openpyxl.styles import *
Color = [‘ffffff‘, ‘00C0C0C0‘]  # 黑白

mysheet = mywb.get_sheet_by_name(‘2nd Sheet‘)
mysheet[‘F6‘] = ‘Writing new Value!‘
mysheet.cell(row=20,column=20,value="who are you")
# mysheet.cell(row=20,column=20,value="").font=font
mysheet[‘G7‘] = ‘Writing two Value!‘
mysheet[‘H8‘] = ‘Writing three Value!‘
ft = Font(color="FF0000",bold=True)
a1=mysheet[‘F6‘]
a2=mysheet[‘G7‘]
a3=mysheet[‘H8‘]
a1.font=ft
a2.font=ft
a3.font=ft
fill = PatternFill("solid", fgColor="DDDDDD")
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
border=Border(top=double, left=thin, right=thin, bottom=double)
alignment = Alignment(horizontal="center", vertical="center")
a1.fill=fill
a2.border=border
a3.alignment=alignment
print(mysheet[‘F6‘] .value,mysheet[‘G7‘] .value,mysheet[‘H8‘] .value,)   #Writing new Value! Writing two Value! Writing three Value!

mywb.save("new.xlsx")

[‘Sheet‘]
<Worksheet "Sheet">
Sheet
MyNewTitle
[‘MyNewTitle‘]
[‘Sheet‘]
[‘Sheet‘, ‘Sheet1‘]
[‘1st Sheet‘, ‘Sheet‘, ‘Sheet1‘]
[‘1st Sheet‘, ‘Sheet‘, ‘2nd Sheet‘, ‘Sheet1‘]
[‘Sheet‘, ‘2nd Sheet‘, ‘Sheet1‘]
[‘2nd Sheet‘, ‘Sheet1‘]
Writing new Value! Writing two Value! Writing three Value!

Process finished with exit code 0