Python Xlsxwriter 简明教程

Python XlsxWriter - Freeze & Split Panes

The freeze_panes() method

XlsxWriter 库中的 freeze_panes() 方法将工作表分成水平或垂直区域，称为窗格，并“冻结”其中任何一格或两格，这样，如果我们向下或向右滚动，窗格（分别为顶部或左侧）将保持不动。

The freeze_panes() method of Worksheet object in XlsxWriter library divides or splits the worksheet into horizontal or vertical regions known as panes, and "freezes" either or both of these panes so that if we scroll down or scroll down or scroll towards right, the panes (top or left respectively) remains stationary.

该方法需要参数 row 和 col 指定拆分的位置。应注意，拆分指定在单元格的顶部或左侧，并且该方法使用基于零的索引。如果您不想要垂直或水平拆分，可以将行和列参数之一设置为零。

The method requires the parameters row and col to specify the location of the split. It should be noted that the split is specified at the top or left of a cell and that the method uses zero based indexing. You can set one of the row and col parameters as zero if you do not want either a vertical or horizontal split.

Example

以下示例中的工作表在每一行中显示递增的列数的倍数，因此每个单元格都显示行号和列号的乘积。

The worksheet in the following example displays incrementing multiples of the column number in each row, so that each cell displays product of row number and column number.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
format1=wb.add_format({'bg_color':'#D9D9D9', 'bold':True})

for col in range(0, 15):
   worksheet.write(0, col, col+1, format1)

for row in range(1, 51):
   for col in range(0,15):
      if col==0:
         worksheet.write(row,col,(col+1)*(row + 1), format1)
      else:
         worksheet.write(row,col,(col+1)*(row + 1))
# Freeze pane on the top row.
worksheet.freeze_panes(1, 0)

wb.close()

Output

接下来，我们冻结 top row pane 。结果，在打开工作表后，如果单元格指针向下滚动，则顶部行始终显示在工作表上。

We then freeze the top row pane. As a result, after opening the worksheet, if the cell pointer is scrolled down, the top row always remains on the worksheet.

top row

同样，我们可以让 first column 固定。

Similarly, we can make the first column stationery.

# Freeze pane on the first column.
worksheet.freeze_panes(0, 1)

以下屏幕截图显示，即使我们向右滚动， column A 仍然可见。

The following screenshot shows that column A remains visible even if we scroll towards the right.

column a

通过将 freeze_panes() 方法中的行和列参数设置为 1，顶部行和最左侧列都将冻结。

By setting row and column parameter in freeze_panes() method to 1, both the top row and leftmost column will freeze.

# Freeze pane on the first row, first column.
worksheet.freeze_panes(1, 1)

打开最终工作表并滚动单元格光标。你会发现，已用粗体和背景色格式化的顶部行和最左侧列中的行号和列号始终可见。

Open the resulting worksheet and scroll the cell cursor around. You will find that row and column numbers in top row and leftmost column, which have been formatted in bold and with a background color, are visible always.

freeze panes

The split_panes() method

split_panes() 方法还会将工作表分成水平或垂直区域，称为窗格，但与 freeze_panes() 方法不同，窗格之间的拆分对用户可见，并且每个窗格都有自己独立的滚动条。

The split_panes() method also divides the worksheet into horizontal or vertical regions known as panes, but unlike freeze_panes() method, the splits between the panes will be visible to the user and each pane will have its own scroll bars.

该方法有参数 “y” 和 “x”，用于指定拆分的垂直和水平位置。这些参数以 Excel 使用的行高和列宽表示。缺省情况下，行高和列宽的值分别为行的 15 和列的 8.43。

The method has the parameters "y" and "x" that are used to specify the vertical and horizontal position of the split. These parameters are in terms of row height and column width used by Excel. The row heights and column widths have default values as 15 for a row and 8.43 for a column.

如果您不想要垂直或水平拆分，可以将 “y” 和 “x” 参数之一设置为零。

You can set one of the "y" and "x" parameters as zero if you do not want either a vertical or horizontal split.

要创建在第 10 行和第 7 列的拆分， split_panes() 方法如下：

To create a split at the 10th row and 7th column, the split_panes() method is used as follows −

worksheet.split_panes(15*10, 8.43*7)

你会在工作表的第 10 行和第 7 列找到拆分器。您可以向垂直拆分器的左右方向以及水平拆分的顶部和底部滚动窗格。请注意，其他窗格将保持不变。

You will find the splitters at 10th row and 7th column of the worksheet. You can scroll the panes to the left and right of vertical splitter and to the top and bottom of horizontal splitter. Note that the other panes will remain constant.

Example

以下是创建分隔符的完整代码示例，其下方是显示的输出 −

Here’s the complete code that creates the splitter, and below that the output is shown −

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
format1=wb.add_format({'bg_color':'#D9D9D9', 'bold':True})

for col in range(0, 15):
   worksheet.write(0, col, col+1, format1)

for row in range(1, 51):
   for col in range(0,15):
      if col==0:
         worksheet.write(row,col,(col+1)*(row + 1), format1)
      else:
         worksheet.write(row,col,(col+1)*(row + 1))
worksheet.split_panes(15*10, 8.43*7)

wb.close()

Output

运行该代码并使用 Excel 打开 hello.xlsx 。如我们所见，工作表在第 10 行和第 7 列被分隔成不同的窗格。

Run the code and open hello.xlsx using Excel. As we can see, the worksheet is split into different panes at 10th row and 7th column.

split panes