设置分隔符

导入Break模块

from openpyxl.worksheet.pagebreak import Break

行分隔符

worksheet..row_breaks.append(Break(行号))

列分隔符

worksheet.col_breaks.append(Break(列号))

实例

import openpyxl

from openpyxl.worksheet.pagebreak import Break


wb = openpyxl.Workbook()

ws = wb.active

ws.append(['name','unit','number'])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])

ws.row_breaks.append(Break(1)) # 在第一行的后面添加一个分隔符
ws.col_breaks.append(Break(1)) # 在第一列的后面添加一个分隔符


wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

---

设置页眉页脚

设置页眉页脚用到HeaderFooter类，以下为可设置的参数：

                 differentOddEven=bool,
                 differentFirst=bool,
                 scaleWithDoc=bool,
                 alignWithMargins=bool,
                 oddHeader=None,
                 oddFooter=None,
                 evenHeader=None,
                 evenFooter=None,
                 firstHeader=None,
                 firstFooter=None,

参数详解

1. 前四个参数

2. 自定义页眉或者页脚又有以下三个部分可以设置

3. 每个部分又有四个属性可以设置

• text

  符号               含义
* &A              Inserts the worksheet name
* &B              Toggles bold
* &D or &[Date]   Inserts the current date
* &E              Toggles double-underline
* &F or &[File]   Inserts the workbook name
* &I              Toggles italic
* &N or &[Pages]  Inserts the total page count
* &S              Toggles strikethrough
* &T              Inserts the current time
* &[Tab]          Inserts the worksheet name
* &U              Toggles underline
* &X              Toggles superscript
* &Y              Toggles subscript
* &P or &[Page]   Inserts the current page number
* &P+n            Inserts the page number incremented by n
* &P-n            Inserts the page number decremented by n
* &[Path]         Inserts the workbook path
* &&              Escapes the ampersand character
* &"fontname"     Selects the named font
* &nn             Selects the specified 2-digit font point size

• font：字体
• size：字体大小
• color：字体颜色

结构图

注意：只有设置了 奇偶页不同 才需要分别对奇数页和偶数页的页眉和页脚分别设置，不然只需要对奇数页的页眉页脚进行设置就能应用到全部页。同样的道理只有设置了 首页不同，才需要对firsetHeader/Footer设置。

实例

import openpyxl


wb = openpyxl.Workbook()

ws = wb.active

ws.append(['name','unit','number'])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])
ws.append(['AA','kg',12])


ws.HeaderFooter.oddFooter.center.text = "总共 &N 页，这是第 &[Page] 页" 
ws.HeaderFooter.oddFooter.center.font = '宋体,Bold'
ws.HeaderFooter.oddFooter.center.size = 14
ws.HeaderFooter.oddFooter.center.color = 'FA4259' # 红色


wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

效果

---

设置页边距

设置页边距用到PageMargins类，有以下参数可以设置

left=0.75, right=0.75, top=1, bottom=1, header=0.5,footer=0.5

参数很简单就可以看懂，就不多说了。

实例

import openpyxl


wb = openpyxl.Workbook()

ws = wb.active

ws.append(['name','number','unit','price'])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append(['A',2,5,0])
ws.append([])
ws.append(['A',1,2,3])

ws.page_margins.left = 0.5
ws.page_margins.right = 0.5
ws.page_margins.top = 0.5
ws.page_margins.bottom = 0.5


wb.save(r'/Users/junliangchen/Desktop/test.xlsx')

---

还有一些打印设置下节讲（预告：设置打印区域，打印标题，打印的缩放、排版等）有点累了🥱