python美化excel_【Python】Excel-4（样式设置）-CSDN博客

#练习：#封装一个ExcelUtil的模块（构造函数是excel的路径），里面提供封装的方法：#1 获取某个sheet对象#2 打印所有sheet名称#3 给某个sheet的某个单元格写入内容#4 从某个sheet的某个单元读出入内容#5 保存对excel对象的修改#6 读取某一行的内容#7 读取某一列的内容

from openpyxl importWorkbookfrom openpyxl importload_workbookclassExcelUtil():def __init__(self,path):

self.path=path

self.wb=load_workbook(path)

self.ws=self.wb.active#1 获取某个sheet对象

defget_Excel_sheet(self,sheet_name):

self.ws=self.wb[sheet_name]#2 打印所有sheet名称

defget_all_sheet(self):returnself.wb.sheetnames#3 给某个sheet的某个单元格写入内容

defwrite_sheet(self,row1,column1,value1):

self.ws.cell(row=row1,column=column1,value=value1)#4 从某个sheet的某个单元读出内容

defread_sheet(self,row1,column1):return self.ws.cell(row=row1,column=column1).value#5 保存对excel对象的修改

defsave_sheet(self):

self.wb.save(self.path)#6 读取某一行的内容

defread_row_sheet(self,row_no):

rows=[]for row inself.ws.iter_rows():

rows.append(row)

row_content=[]for cell in rows[row_no-1]:

row_content.append(cell.value)returnrow_content#for cell in rows[row_no-1]:

#row_content.append(cell.value)

#return row_content

#7 读取某一列的内容

defread_column_sheet(self,col_no):

cols=[]for col inself.ws.iter_cols():

cols.append(col)

col_content=[]for cell in cols[col_no-1]:

col_content.append(cell.value)returncol_contentif __name__=="__main__":

Excel_obj=ExcelUtil("e:\\test4\\s2.xlsx")

Excel_obj.get_Excel_sheet("wangjing")printExcel_obj.get_all_sheet()

Excel_obj.write_sheet(2,3,"10")print Excel_obj.read_sheet(2,3)

Excel_obj.save_sheet()print Excel_obj.read_row_sheet(2)print Excel_obj.read_column_sheet(2)#练习：

from openpyxl importload_workbookfrom openpyxl importWorkbookfrom openpyxl.worksheet.table importTable, TableStyleInfo

wb=Workbook()

ws=wb.active

data=[

['Apples', 10000, 5000, 8000, 6000],

['Pears', 2000, 3000, 4000, 5000],

['Bananas', 6000, 6000, 6500, 6000],

['Oranges', 500, 300, 200, 700],

]

ws.append(["Fruit", "2011", "2012", "2013", "2014"])for row indata:

ws.append(row)

tab= Table(displayName="Table1", ref="A1:E5")

style= TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,

showLastColumn=False, showRowStripes=False, showColumnStripes=True)

tab.tableStyleInfo=style

ws.add_table(tab)

wb.save('e:\\test4\\s2.xlsx')#练习：自定义字体颜色

from openpyxl importWorkbookfrom openpyxl.styles importcolorsfrom openpyxl.styles importFont

wb=Workbook()

ws=wb.active

a1= ws['A1']

d4= ws['D4']

ft= Font(color=colors.GREEN) #color="FFBB11"，颜色编码也可以设定颜色

a1.font =ft

d4.font=ft

a1.font= Font(color=colors.BLUE, italic=True)

a1.value= "abc"wb.save('e:\\test4\\s2.xlsx')#练习：设置某个单元格字体

from openpyxl importWorkbookfrom openpyxl.styles importcolorsfrom openpyxl.styles importFontfrom copy importcopy

wb=Workbook()

ws=wb.active

a1= ws['A1']

d4= ws['D4']

a1.value= "abc"ft1= Font(name=u'宋体', size=14)

ft2= copy(ft1) #复制字体对象

ft2.name = "Tahoma"

printft1.nameprintft2.nameprintft2.size

wb.save('e:\\test4\\s2.xlsx')#练习

from openpyxl importWorkbookfrom openpyxl.styles importFont

wb=Workbook()

ws=wb.active

col= ws.column_dimensions['A']

col.font= Font(bold=True) #将A列设定为粗体

ws["A1"]="99"row= ws.row_dimensions[1]

row.font= Font(underline="single") #将第一行设定为下划线格式

#Save the file

wb.save('e:\\test4\\s2.xlsx')#练习：设置单元格背景色

from openpyxl importWorkbookfrom openpyxl.styles importFontfrom openpyxl.styles importNamedStyle, Font, Border, Side,PatternFill

wb=Workbook()

ws=wb.active

highlight= NamedStyle(name="highlight")

highlight.font= Font(bold=True, size=20,color= "ff8888") #设置字体颜色

highlight.fill = PatternFill("solid", fgColor="eeE100") #设置背景色

bd = Side(style='thin', color="444444") #设置边框thin是细，thick是粗

highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) #设置边框

print dir(ws["A1"])

ws["A1"].style =highlight#Save the file

wb.save('e:\\test4\\s2.xlsx')#练习：设置单元格边框和字体

from openpyxl importWorkbookfrom openpyxl.styles importFontfrom openpyxl.styles importNamedStyle, Font, Border, Side,PatternFillfrom openpyxl.styles importPatternFill, Border, Side, Alignment, Protection, Font

wb=Workbook()

ws=wb.active

ft= Font(name=u'微软雅黑',

size=11,

bold=False,

italic=False,

vertAlign=None,

underline='none',

strike=False,

color='FF000000')

fill= PatternFill(fill_type="solid",

start_color='FFEEFFFF',