• 第十七章 Excel操作

    目录

    一、安装openpyxl

    二、创建一个excel 文件，并写入不同类的内容

    三、创建一个sheet

    四、操作单元格 

    五、批量操作单元格 

    5.1 打印百分数或小数

    5.2 获取所有行对象

    5.3 获取所有列对象

    六、操作已存在的文件

    七、单元格类型

    八、使用公式 

    九、合并/拆分单元格 

    十、插入一个图片

    十一、隐藏单元格

    十二、画一个柱状图

    十三、画一个饼图

    ​十四、设定一个表格区域，并设定表格的样式

     十五、给单元格设定字体颜色

    十六、设定字体和大小 

    十七、设定行和列的字体 

    十八、设定单元格的边框、字体、颜色、大小和边框背景

    十九、常用的样式和属性设置

    二十、给某个范围设定样式

    一、安装openpyxl

    pip install openpyxl==3.0.10

    想要在文件中插入图片文件，需要安装pillow：

    py -3 -m pip install pillow

    二、创建一个excel 文件，并写入不同类的内容

    创建空excel文件，默认会有一个sheet

    1. from openpyxl import Workbook
    2.  
    3. wb = Workbook()
    4. wb.save(r"F:\python_18_excel\test.xlsx")

     

    1. # -*- coding: utf-8 -*-
    2. import locale
    3. from openpyxl import Workbook
    4. wb = Workbook()    #创建文件对象
    5.  
    6. # grab the active worksheet
    7. ws = wb.active     #获取第一个sheet
    8.  
    9. # Data can be assigned directly to cells
    10. ws['A1'] = 42      #写入数字
    11. ws['B1'] = "光荣之路"+"automation test" #写入中文
    12.  
    13. # Rows can also be appended
    14. ws.append([1, 2, 3])    #写入多个单元格
    15.  
    16. import datetime
    17. import time
    18. import locale
    19. ws['A3'] = datetime.datetime.now()    #写入一个当前时间
    20. #写入一个自定义的时间格式
    21. locale.setlocale(locale.LC_ALL,'en')
    22. locale.setlocale(locale.LC_CTYPE, 'chinese')
    23. ws['A4'] =time.strftime("%Y年%m月%d日 %H时%M分%S秒",time.localtime())
    24.  
    25. # Save the file
    26. wb.save(r"F:\python_18_excel\sample.xlsx")

    三、创建一个sheet

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. wb = Workbook()
    5.  
    6. ws = wb.create_sheet("Mysheet1") #创建一个sheet,名字：Mysheet1
    7. ws1 = wb.create_sheet("Mysheet") #创建一个sheet名字：Mysheet
    8. ws1.title = "New Title" #Mysheet改名字为New Title
    9. ws2 = wb.create_sheet("Mysheet", 0) #设定sheet的插入位置
    10. ws2.title = "光荣之路自动化测试培训" #设定一个sheet的名字
    11.  
    12. ws1.sheet_properties.tabColor = "1072BA"  #设定sheet的标签的背景颜色
    13.  
    14. #获取某个sheet对象
    15. print(wb["光荣之路自动化测试培训"])
    16. print(wb["New Title" ])
    17.  
    18. #获取全部sheet 的名字，遍历sheet名字
    19. print(wb.sheetnames)
    20. for sheet_name in wb.sheetnames:
    21.     print(sheet_name)
    22.  
    23. print ("*"*50)
    24.  
    25. #遍历获取sheet对象，按照sheet顺序获取
    26. for sheet in wb:
    27.     print(sheet)
    28.  
    29.  
    30. for sheet in wb:
    31.     print(sheet.title)
    32.  
    33. wb["New Title"]["A1"]="gloryroad"
    34. source = wb["New Title"]
    35. target = wb.copy_worksheet(source) #复制一个sheet
    36. #删除某个sheet
    37. del wb["New Title" ]
    38. # Save the file
    39. wb.save(r"F:\python_18_excel\sample2.xlsx")

     

    四、操作单元格 

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. wb = Workbook()
    5.  
    6. ws1 = wb.create_sheet("Mysheet") #创建一个sheet
    7.  
    8. ws1["A1"]=123.11
    9. ws1["B2"]="光荣之路"
    10. d = ws1.cell(row=4, column=2, value=10)
    11.  
    12. print(ws1["A1"].value)
    13. print(ws1["B2"].value)
    14. print(d.value)
    15. print(ws1.cell(row=4,column=2).value) #行号和列号从1开始
    16.  
    17. # Save the file
    18. wb.save(r"F:\python_18_excel\sample3.xlsx")

     

    五、批量操作单元格 

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. wb = Workbook()
    5.  
    6. ws1 = wb.create_sheet("Mysheet") #创建一个sheet
    7.  
    8. ws1["A1"]=1
    9. ws1["A2"]=2
    10. ws1["A3"]=3
    11. # ws1["A6"]=100
    12.  
    13. ws1["B1"]=4
    14. ws1["B2"]=5
    15. ws1["B3"]=6
    16.  
    17. ws1["C1"]=7
    18. ws1["C2"]=8
    19. ws1["C3"]=9
    20.  
    21. #操作单列
    22. print(ws1["A"])
    23. for cell in ws1["A"]:
    24.     print(cell.value)
    25.  
    26. #操作多列,获取每一个值
    27. print(ws1["A:C"])
    28. for column in ws1["A:C"]:
    29.     for cell in column:
    30.         print(cell.value)
    31.  
    32. #操作多行
    33. row_range = ws1[1:3]
    34. print(row_range)
    35. for row in row_range:
    36.     for cell in row:
    37.         print(cell.value)
    38.  
    39. print("*"*50)
    40. for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
    41.     for cell in row:
    42.         print(cell.value)
    43.  
    44. #获取所有行
    45. print(ws1.rows)
    46. for row in ws1.rows:
    47.     print(row)
    48.  
    49. print("*"*50)
    50. #获取所有列
    51. print(ws1.columns)
    52. for col in ws1.columns:
    53.     print(col)
    54.  
    55. print(ws1.max_row,ws1.max_column)
    56. print(ws1.min_row,ws1.min_column)
    57.  
    58. # Save the file
    59. wb.save(r"F:\python_18_excel\sample4.xlsx")
    1. ('Mysheet'.A1>, 'Mysheet'.A2>, 'Mysheet'.A3>)
    2. 1
    3. 2
    4. 3
    5. (('Mysheet'.A1>, 'Mysheet'.A2>, 'Mysheet'.A3>), ('Mysheet'.B1>, 'Mysheet'.B2>, 'Mysheet'.B3>), ('Mysheet'.C1>, 'Mysheet'.C2>, 'Mysheet'.C3>))
    6. 1
    7. 2
    8. 3
    9. 4
    10. 5
    11. 6
    12. 7
    13. 8
    14. 9
    15. (('Mysheet'.A1>, 'Mysheet'.B1>, 'Mysheet'.C1>), ('Mysheet'.A2>, 'Mysheet'.B2>, 'Mysheet'.C2>), ('Mysheet'.A3>, 'Mysheet'.B3>, 'Mysheet'.C3>))
    16. 1
    17. 4
    18. 7
    19. 2
    20. 5
    21. 8
    22. 3
    23. 6
    24. 9
    25. **************************************************
    26. 1
    27. 4
    28. 7
    29. 2
    30. 5
    31. 8
    32. 3
    33. 6
    34. 9
    35. object Worksheet._cells_by_row at 0x00000220039C6570>
    36. ('Mysheet'.A1>, 'Mysheet'.B1>, 'Mysheet'.C1>)
    37. ('Mysheet'.A2>, 'Mysheet'.B2>, 'Mysheet'.C2>)
    38. ('Mysheet'.A3>, 'Mysheet'.B3>, 'Mysheet'.C3>)
    39. **************************************************
    40. object Worksheet._cells_by_col at 0x00000220039C6570>
    41. ('Mysheet'.A1>, 'Mysheet'.A2>, 'Mysheet'.A3>)
    42. ('Mysheet'.B1>, 'Mysheet'.B2>, 'Mysheet'.B3>)
    43. ('Mysheet'.C1>, 'Mysheet'.C2>, 'Mysheet'.C3>)
    44. 3 3
    45. 1 1

     

     

    5.1 打印百分数或小数

    1. # -*- coding: utf-8 -*-
    2. from openpyxl import Workbook
    3. from openpyxl import load_workbook
    4.  
    5. wb = load_workbook(r"F:\python_18_excel\sample4.xlsx") #从现有的文件中读取内容，不会删除原有内容
    6. wb.guess_types = True #猜测单元格类型，True时将单元格的百分数显示为小数；False时将单元格显示为百分数
    7. ws=wb.active
    8. ws["D1"]="12%"
    9. print(type(ws["D1"].value))
    10. print(ws["D1"].value)
    11. ws["D3"]=12
    12. print(type(ws["D3"].value))
    13. print(ws["D3"].value)
    14. ws["D4"]=3.14
    15. print(type(ws["D4"].value))
    16. print(ws["D4"].value)
    17. ws["D5"]="test"
    18. print(type(ws["D5"].value))
    19. print(ws["D5"].value)
    20.  
    21. # Save the file
    22. wb.save(r"F:\python_18_excel\sample4.xlsx")

    但我不管设置 wb.guess_types = True orwb.guess_types = False时，print(ws["D1"].value)输出结果都是12%。不知道为什么？

     

    5.2 获取所有行对象

    1. #coding=utf-8
    2. from openpyxl import Workbook
    3. from openpyxl import load_workbook
    4. wb = load_workbook(r"F:\python_18_excel\sample6.xlsx")
    5. ws=wb.active #获取默认的sheet进行操作
    6. rows=[] #存储行对象
    7. for row in ws.iter_rows():#遍历所有行对象
    8.             rows.append(row)
    9.  
    10. print(rows) #所有行
    11. print(rows[0]) #获取第一行
    12. print(rows[0][0]) #获取第一行第一列的单元格对象
    13. print(rows[0][0].value) #获取第一行第一列的单元格对象的值
    14.  
    15. print(rows[len(rows)-1]) #获取最后行
    16. print(rows[len(rows)-1][len(rows[0])-1]) #获取第后一行和最后一列的单元格对象
    17. print(rows[len(rows)-1][len(rows[0])-1].value) #获取第后一行和最后一列的单元格对象的值
    18.  
    19. print(rows[-1]) #获取最后行
    20. print(rows[-1][len(rows[-1])-1]) #获取第后一行和最后一列的单元格对象
    21. print(rows[-1][len(rows[-1])-1].value) #获取第后一行和最后一列的单元格对象的值

     

    1. [('Sheet'.A1>, 'Sheet'.B1>, 'Sheet'.C1>, 'Sheet'.D1>, 'Sheet'.E1>), ('Sheet'.A2>, 'Sheet'.B2>, 'Sheet'.C2>, 'Sheet'.D2>, 'Sheet'.E2>), ('Sheet'.A3>, 'Sheet'.B3>, 'Sheet'.C3>, 'Sheet'.D3>, 'Sheet'.E3>)]
    2. ('Sheet'.A1>, 'Sheet'.B1>, 'Sheet'.C1>, 'Sheet'.D1>, 'Sheet'.E1>)
    3. 'Sheet'.A1>
    4. 1
    5. ('Sheet'.A3>, 'Sheet'.B3>, 'Sheet'.C3>, 'Sheet'.D3>, 'Sheet'.E3>)
    6. 'Sheet'.E3>
    7. 10
    8. ('Sheet'.A3>, 'Sheet'.B3>, 'Sheet'.C3>, 'Sheet'.D3>, 'Sheet'.E3>)
    9. 'Sheet'.E3>
    10. 10

    5.3 获取所有列对象

    1. #coding=utf-8
    2. from openpyxl import Workbook
    3. from openpyxl import load_workbook
    4.  
    5. wb = load_workbook(r"F:\python_18_excel\sample6.xlsx")
    6. ws=wb.active
    7. cols=[]
    8. cols = []
    9. for col in ws.iter_cols():
    10.     cols.append(col)
    11.  
    12. print(cols) #所有列
    13. print(cols[0]) #获取第一列
    14. print(cols[0][0]) #获取第一列的第一行的单元格对象
    15. print(cols[0][0].value) #获取第一列的第一行的值
    16.  
    17. print("*"*30)
    18. print(cols[len(cols)-1]) #获取最后一列
    19. print(cols[len(cols)-1][len(cols[0])-1])      #获取最后一列的最后一行的单元格对象
    20. print(cols[len(cols)-1][len(cols[0])-1].value)    #获取最后一列的最后一行的单元格对象的值
    1. [('Sheet'.A1>, 'Sheet'.A2>, 'Sheet'.A3>), ('Sheet'.B1>, 'Sheet'.B2>, 'Sheet'.B3>), ('Sheet'.C1>, 'Sheet'.C2>, 'Sheet'.C3>), ('Sheet'.D1>, 'Sheet'.D2>, 'Sheet'.D3>), ('Sheet'.E1>, 'Sheet'.E2>, 'Sheet'.E3>)]
    2. ('Sheet'.A1>, 'Sheet'.A2>, 'Sheet'.A3>)
    3. 'Sheet'.A1>
    4. 1
    5. ******************************
    6. ('Sheet'.E1>, 'Sheet'.E2>, 'Sheet'.E3>)
    7. 'Sheet'.E3>
    8. 10

    六、操作已存在的文件

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. from openpyxl import load_workbook
    5. wb = load_workbook(r"F:\python_18_excel\sample6.xlsx")
    6. wb.guess_types = True   #猜测格式类型
    7. ws=wb.active
    8. ws["D1"]="12%"
    9. print (ws["D1"].value)
    10.  
    11. # Save the file
    12. wb.save("e:\\sample.xlsx")
    13. #注意如果原文件有一些图片或者图标，则保存的时候可能会导致图片丢失

    七、单元格类型

    1. # -*- coding: utf-8 -*-
    2. from openpyxl import Workbook
    3. from openpyxl import load_workbook
    4. import datetime
    5.  
    6. wb = load_workbook(r"F:\python_18_excel\test.xlsx")
    7.  
    8. ws=wb.active
    9. wb.guess_types = True
    10.  
    11. ws["A1"]=datetime.datetime(2010, 7, 21)
    12. print(ws["A1"].number_format)
    13.  
    14. ws["A2"]="12%"
    15. print(ws["A2"].number_format)
    16.  
    17. ws["A3"]= 1.1
    18. print(ws["A3"].number_format)
    19.  
    20. ws["A4"]= "中国"
    21. print(ws["A4"].number_format)
    22. # Save the file
    23. wb.save(r"F:\python_18_excel\test.xlsx")

    八、使用公式 

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. from openpyxl import load_workbook
    5.  
    6. wb = load_workbook(r"F:\python_18_excel\test.xlsx")
    7.  
    8. ws1=wb.active
    9.  
    10. ws1["A1"]=1
    11. ws1["A2"]=2
    12. ws1["A3"]=3
    13.  
    14. ws1["A4"] = "=SUM(1, 1)"
    15. ws1["A5"] = "=SUM(A1:A3)"
    16.  
    17. print (ws1["A4"].value)  #打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
    18. print (ws1["A5"].value)  #打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
    19.  
    20. # Save the file
    21. wb.save(r"F:\python_18_excel\test.xlsx")

     

    九、合并/拆分单元格 

    1. from openpyxl import Workbook
    2. from openpyxl import load_workbook
    3.  
    4. wb = load_workbook(r"F:\python_18_excel\a.xlsx")
    5.  
    6. ws1=wb.active
    7.  
    8. from openpyxl.workbook import Workbook
    9.  
    10. wb = Workbook()
    11. ws = wb.active
    12.  
    13. ws.merge_cells('A5:D5') #合并单元格
    14. ws.unmerge_cells('A5:D5') #拆分单元格
    15.  
    16. # or equivalently
    17. ws.merge_cells(start_row=6,start_column=1,end_row=6,end_column=4)
    18. ws.unmerge_cells(start_row=6,start_column=1,end_row=2,end_column=4)
    19.  
    20. # Save the file
    21. wb.save(r"F:\python_18_excel\a.xlsx")

    十、插入一个图片

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import load_workbook
    4. from openpyxl.drawing.image import Image
    5.  
    6. wb = load_workbook(r"F:\python_18_excel\a.xlsx")
    7.  
    8. ws1=wb.active
    9.  
    10. img = Image(r"F:\python_18_excel\1.jpg")
    11. ws1.add_image(img, 'A1')
    12.  
    13.  
    14. # Save the file
    15. wb.save(r"F:\python_18_excel\a.xlsx")

    十一、隐藏单元格

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import load_workbook
    4. from openpyxl.drawing.image import Image
    5.  
    6. wb = load_workbook(r"F:\python_18_excel\a.xlsx")
    7.  
    8. ws1=wb.active
    9.  
    10. ws1.column_dimensions.group('A', 'D', hidden=True)   #隐藏a到d列范围内的列
    11.  
    12. # Save the file
    13. wb.save(r"F:\python_18_excel\a.xlsx")

    十二、画一个柱状图

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import load_workbook
    4. from openpyxl import Workbook
    5. from openpyxl.chart import BarChart, Reference, Series
    6.  
    7. wb = load_workbook(r"F:\python_18_excel\a.xlsx")
    8.  
    9. ws1=wb.active
    10.  
    11. wb = Workbook()
    12. ws = wb.active
    13. for i in range(10):
    14.     ws.append([i])
    15.  
    16.  
    17. values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    18. chart = BarChart()
    19. chart.title = "Bar Chart"
    20. chart.y_axis.title = 'SN'
    21. chart.x_axis.title = 'Sample value'
    22. chart.add_data(values)
    23. ws.add_chart(chart, "E15")
    24.  
    25. # Save the file
    26. wb.save(r"F:\python_18_excel\a.xlsx")

    十三、画一个饼图

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import load_workbook
    4. from openpyxl import Workbook
    5.  
    6.  
    7. from openpyxl.chart import (
    8.     PieChart,
    9.     ProjectedPieChart,
    10.     Reference
    11. )
    12. from openpyxl.chart.series import DataPoint
    13.  
    14. #5行数据
    15. data = [
    16.     ['Pie', 'Sold'],
    17.     ['Apple', 50],
    18.     ['Cherry', 30],
    19.     ['Pumpkin', 10],
    20.     ['Chocolate', 40],
    21. ]
    22.  
    23. wb = Workbook()
    24. ws = wb.active
    25.  
    26. for row in data:#把5行的数据加入到excel的sheet中
    27.     ws.append(row)
    28.  
    29. pie = PieChart()
    30. labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    31. data = Reference(ws, min_col=2, min_row=1, max_row=5)
    32. pie.add_data(data, titles_from_data=True)
    33. pie.set_categories(labels)
    34. pie.title = "Pies sold by category"
    35.  
    36. # Cut the first slice out of the pie
    37. slice = DataPoint(idx=0, explosion=20)#idx表示让第几行突出，explosion=0则不突出
    38. pie.series[0].data_points = [slice]
    39.  
    40. ws.add_chart(pie, "D1")
    41.  
    42. # 映射图
    43. ws = wb.create_sheet(title="Projection")
    44.  
    45. data = [
    46.     ['Page', 'Views'],
    47.     ['Search', 95],
    48.     ['Products', 4],
    49.     ['Offers', 0.5],
    50.     ['Sales', 0.5],
    51. ]
    52.  
    53. for row in data:
    54.     ws.append(row)
    55.  
    56. projected_pie = ProjectedPieChart()
    57. projected_pie.type = "pie"
    58. projected_pie.splitType = "val" # split by value
    59. labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    60. data = Reference(ws, min_col=2, min_row=1, max_row=5)
    61. projected_pie.add_data(data, titles_from_data=True)
    62. projected_pie.set_categories(labels)
    63.  
    64. ws.add_chart(projected_pie, "A10")
    65.  
    66. from copy import deepcopy
    67. projected_bar = deepcopy(projected_pie)
    68. projected_bar.type = "bar"
    69. projected_bar.splitType = 'pos' # split by position
    70.  
    71. ws.add_chart(projected_bar, "A27")
    72.  
    73. # Save the file
    74. wb.save(r"F:\python_18_excel\a.xlsx")

    映射图： 

     explosion=0时：

     十四、设定一个表格区域，并设定表格的样式

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import load_workbook
    4. from openpyxl import Workbook
    5. from openpyxl.worksheet.table import Table, TableStyleInfo
    6.  
    7. wb = Workbook()
    8. ws = wb.active
    9.  
    10. data = [
    11.     ['Apples', 10000, 5000, 8000, 6000],
    12.     ['Pears',   2000, 3000, 4000, 5000],
    13.     ['Bananas', 6000, 6000, 6500, 6000],
    14.     ['Oranges',  500,  300,  200,  700],
    15. ]
    16.  
    17. # add column headings. NB. these must be strings
    18. ws.append(["Fruit", "2011", "2012", "2013", "2014"])
    19. for row in data:
    20.     ws.append(row)
    21.  
    22. tab = Table(displayName="Table1", ref="A1:E5")
    23.  
    24. # Add a default style with striped rows and banded columns
    25. style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
    26. tab.tableStyleInfo = style
    27. ws.add_table(tab)
    28.  
    29. # Save the file
    30. wb.save(r"F:\python_18_excel\a.xlsx")

     十五、给单元格设定字体颜色

    1. from openpyxl import Workbook
    2. from openpyxl.styles import colors
    3. from openpyxl.styles import Font
    4.  
    5.  
    6. wb = Workbook()
    7. ws = wb.active
    8.  
    9. a1 = ws['A1']
    10. d4 = ws['D4']
    11. ft = Font(color='FFBB00')  # color="FFBB00"，颜色编码也可以设定颜色
    12. a1.font = ft
    13. d4.font = ft
    14.  
    15. a1.font = Font(name="楷体",color=colors.BLUE,size=50,bold=True,underline="single",family=14) # the change only affects A1# the change only affects A1
    16. a1.value = "abc"
    17.  
    18. #给某一个区域设置字体的格式：
    19. for i in range(1,4):
    20.     for j in range(1,4):
    21.         ws.cell(row=i, column=j).font = Font(name="楷体",color=colors.BLUE,size=50,bold=True,underline="single",family=1)
    22. # Save the file
    23. wb.save(r"F:\python_18_excel\a.xlsx")

    十六、设定字体和大小 

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. from openpyxl.styles import colors
    5. from openpyxl.styles import Font
    6.  
    7.  
    8. wb = Workbook()
    9. ws = wb.active
    10.  
    11. a1 = ws['A1']
    12. d4 = ws['D4']
    13. a1.value = "abc"
    14. d4.value = 123
    15. from openpyxl.styles import Font
    16. from copy import copy
    17.  
    18. ft1 = Font(name='宋体', size=14)
    19. ft2 = copy(ft1)   #复制字体对象
    20. ft2.name = "Tahoma"
    21.  
    22. print (ft1.name)
    23. print (ft2.name)
    24. print (ft2.size)
    25.  
    26. a1.font = ft1
    27. d4.font = ft2
    28. # Save the file
    29. wb.save(r"F:\python_18_excel\a.xlsx")

     

     

    十七、设定行和列的字体 

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. from openpyxl.styles import Font
    5. wb = Workbook()
    6. ws = wb.active
    7.  
    8. col = ws.column_dimensions['A']
    9.  
    10. col.font = Font(bold=True)   #将A列设定为粗体
    11. row = ws.row_dimensions[1]
    12. row.font = Font(underline="single")  #将第一行设定为下划线格式
    13.  
    14. col.width=10 #设定A列的宽度为10，否则A列会被隐藏
    15.  
    16. # Save the file
    17. wb.save(r"F:\python_18_excel\a.xlsx")

     

    十八、设定单元格的边框、字体、颜色、大小和边框背景

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. from openpyxl.styles import Font
    5. from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
    6.  
    7. wb = Workbook()
    8. ws = wb.active
    9.  
    10. highlight1 = NamedStyle(name="highlight")
    11. highlight1.font = Font(bold=True, size=20,color= "ff0100")
    12. highlight1.fill = PatternFill("solid", fgColor="DDDDDD")
    13. bd = Side(style='thick', color="000000")
    14. highlight1.border = Border(left=bd, top=bd, right=bd, bottom=bd)
    15.  
    16. #print (dir(ws["A1"]))
    17. ws["A1"].value="我们"
    18. ws["A1"].style =highlight1
    19.  
    20.  
    21. # Save the file
    22. wb.save(r"F:\python_18_excel\a.xlsx")

    十九、常用的样式和属性设置

    1. # -*- coding: utf-8 -*-
    2.  
    3. from openpyxl import Workbook
    4. from openpyxl.styles import Font
    5. from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
    6. from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
    7.  
    8. wb = Workbook()
    9. ws = wb.active
    10.  
    11. ft = Font(name=u'微软雅黑',
    12.     size=11,
    13.     bold=False,
    14.     italic=False,
    15.     vertAlign=None,
    16.     underline='none',
    17.     strike=False,
    18.     color='FF000000')
    19.  
    20. fill = PatternFill(fill_type="solid",
    21.     start_color='FFEEFFFF',
    22.     end_color='FF001100')
    23.  
    24. #边框可以选择的值为：'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
    25. #diagonal 表示对角线
    26. bd = Border(left=Side(border_style="thin",
    27.               color='FF001000'),
    28.     right=Side(border_style="thin",
    29.                color='FF110000'),
    30.     top=Side(border_style="thin",
    31.              color='FF110000'),
    32.     bottom=Side(border_style="thin",
    33.                 color='FF110000'),
    34.     diagonal=Side(border_style=None,
    35.                   color='FF000000'),
    36.     diagonal_direction=0,
    37.     outline=Side(border_style=None,
    38.                  color='FF000000'),
    39.     vertical=Side(border_style=None,
    40.                   color='FF000000'),
    41.     horizontal=Side(border_style=None,
    42.                    color='FF110000')
    43.                 )
    44.  
    45. alignment=Alignment(horizontal='general',
    46.         vertical='bottom',
    47.         text_rotation=0,
    48.         wrap_text=False,
    49.         shrink_to_fit=False,
    50.         indent=0)
    51.  
    52. number_format = 'General'
    53.  
    54. protection = Protection(locked=True,
    55.             hidden=False)
    56.  
    57. ws["B5"].font = ft
    58. ws["B5"].fill =fill
    59. ws["B5"].border = bd
    60. ws["B5"].alignment = alignment
    61. ws["B5"].number_format = number_format
    62.  
    63. ws["B5"].value ="glory road"
    64.  
    65. # Save the file
    66. wb.save(r"F:\python_18_excel\a.xlsx")

     

    二十、给某个范围设定样式

    示例1：给合并后的单元格添加样式

    1. from openpyxl.styles import Border,Side,PatternFill,Font,GradientFill,Alignment
    2. from openpyxl.styles import Font, Border, Side,PatternFill
    3. from openpyxl import Workbook
    4.  
    5. def style_range(ws,cell_range,border=Border(),fill=None,font=None,alignment=None):
    6.     """
    7. 	App styles to a range of cells as if they were a single cell.
    8. 	:param ws:
    9. 	:param cell_range:
    10. 	:param border:
    11. 	:param fill:
    12. 	:param font:
    13. 	:param alignment:
    14. 	:return:
    15.      """
    16.  
    17.     top = Border(top=border.top)
    18.     left = Border(left=border.left)
    19.     right = Border(right=border.right)
    20.     bottom = Border(bottom=border.bottom)
    21.  
    22.     first_cell = ws[cell_range.split(":")[0]]
    23.     if alignment:
    24.         ws.merge_cells(cell_range)
    25.         first_cell.alignment = alignment
    26.  
    27.     rows = ws[cell_range]
    28.     if font:
    29.         first_cell.font = font
    30.  
    31.     for cell in rows[0]:
    32.         cell.border = cell.border + top
    33.     for cell in rows[-1]:
    34.         cell.border = cell.border + bottom
    35.  
    36.     for row in rows:
    37.         L = row[0]
    38.         r = row[-1]
    39.         L.border = L.border + left
    40.         r.border = r.border + right
    41.         if fill:
    42.             for c in row:
    43.                 c.fill = fill
    44.  
    45. wb = Workbook()
    46. ws = wb.active
    47. my_cell = ws['B2']
    48. my_cell.value = "My Cell"
    49.  
    50. thin = Side(border_style="thin",color = "000000")
    51. double = Side(border_style="double",color="ff0000")
    52.  
    53. border = Border(top=double,left=thin,right=thin,bottom=double)
    54.  
    55. fill = PatternFill("solid",fgColor="DDDDDD")
    56. fill = GradientFill(stop=("000000","FFFFFF"))
    57.  
    58. font = Font(b=True,color="FF0000")
    59. al = Alignment(horizontal="center",vertical="center")
    60.  
    61. style_range(ws,'B2:F4',border=border,fill=fill,font=font,alignment=al)
    62. wb.save(r"F:\LiFuChe\光荣之路\课堂编码练习\python_18_excel\a.xlsx")

     

    示例2：给每个单元格设定相同的样式

    1. from openpyxl.styles import Border,Side,PatternFill,Font,GradientFill,Alignment
    2. from openpyxl.styles import Font, Border, Side,PatternFill
    3. from openpyxl import Workbook
    4.  
    5. def style_range(ws,cell_range,border=Border(),fill=None,font=None,alignment=None):
    6.     """
    7. 	App styles to a range of cells as if they were a single cell.
    8. 	:param ws:
    9. 	:param cell_range:
    10. 	:param border:
    11. 	:param fill:
    12. 	:param font:
    13. 	:param alignment:
    14. 	:return:
    15.      """
    16.  
    17.     top = Border(top=border.top)
    18.     left = Border(left=border.left)
    19.     right = Border(right=border.right)
    20.     bottom = Border(bottom=border.bottom)
    21.  
    22.     first_cell = ws[cell_range.split(":")[0]]#"A1:D4"，获取指定范围中的第一个单元格
    23.  
    24.     rows = ws[cell_range]#取到指定单元格的范围，是一个元组
    25.     if font:#是否传入了font参数，如有，用你传入的字体样式
    26.         first_cell.font = font
    27.  
    28.     for row in rows:
    29.         for cell in row:
    30.             cell.border = cell.border + top + bottom + right + left
    31.             cell.fill = fill
    32.             cell.font = font
    33.             cell.alignment = alignment#对齐方式
    34.  
    35. wb = Workbook()
    36. ws = wb.active
    37. my_cell = ws['B2']
    38. my_cell.value = "My Cell"
    39.  
    40. thin = Side(border_style="thin",color = "000000")
    41. double = Side(border_style="double",color="ff0000")
    42.  
    43. border = Border(top=double,left=thin,right=thin,bottom=double)
    44.  
    45. fill = PatternFill("solid",fgColor="DDDDDD")#设置渐变填充样式
    46. #fill = GradientFill(stop=("000000","FFFFFF"))
    47.  
    48. font = Font(b=True,color="FF0000")
    49. al = Alignment(horizontal="center",vertical="center")
    50.  
    51. style_range(ws,'B2:F4',border=border,fill=fill,font=font,alignment=al)
    52. wb.save(r"F:\python_18_excel\a.xlsx")

     

     

  • 相关阅读:
    Jira Bug管理工具的操作步骤 提Bug的流程
    swagger简介
    【Linux】线程池
    Xilinx FPGA 7系列 GTX/GTH Transceivers (2)--IBERT
    人工智能技术概述_3.机器学习
    vue实现单页面仿照购物车案例——基于mint-ui和vue2.0
    【数据结构】&&【C++】封装哈希表模拟实现unordered_map和unordered_set容器
    36.Python实现马尔科夫链
    如果通过Java来完成大数据开发，需要学习哪些内容
    OpenTCS客户端开发之Web客户端(一)
  • 原文地址：https://blog.csdn.net/qq_22895113/article/details/126106158