Python Xlsxwriter 简明教程

Python XlsxWriter - Outlines & Grouping

在 Excel 中，你可以对具有相同特定列（或行）值的行或列进行分组，以便可以用单击隐藏或显示它们。此功能称为 outlines and grouping 。它有助于显示小计或合计。可以在 MS excel 软件的 Data→Outline 组中找到此功能。

In Excel, you can group rows or columns having same value of a particular column (or row)) so that they can be hidden or displayed with a single mouse click. This feature is called to as outlines and grouping. It helps in displaying sub-totals or summaries. This feature can be found in MS excel software’s Data→Outline group.

若要使用此功能，数据范围的所有行都应按一列中值分类。假设我们有不同项目的销售数据。按项目名称对范围排序之后，单击“大纲”组中的“小计”选项。将弹出以下对话框。

To use this feature, the data range must have all rows should be in the sorted order of values in one column. Suppose we have sales figures of different items. After sorting the range on name of item, click on the Subtotal option in the Outline group. Following dialog box pops up.

outline

该工作表显示按项目划分的销售小计，最后是总计。该工作表左侧显示大纲级别。原始数据位于第 3 级，小计位于第 2 级，总计位于第 1 级。

The worksheet shows item-wise subtotal of sales and at the end the grand total. On the left of the worksheet, the outline levels are shown. The original data is at level 3, the subtotals at level 2 and grand total at level 1.

item and sales

Working with Outlines and Grouping

若要使用 XlsxWriter 执行此操作，我们需要使用“@ {s0}”方法的 level 属性。数据行设置在第 2 级。

To do this using XlsxWriter, we need to use the level property of the set_row() method. The data rows are set at level 2.

ws.set_row(row, None, None, {'level': 2})

小计行位于第 1 级。

The rows for subtotal are having level 1.

ws.set_row(row, None, None, {'level': 1})

我们使用“@ {s1}”函数计算和显示一组中的销售额总和。

We use SUBTOTAL() function to calculate and display the sum of sales figures in one group.

Example

以下是完整代码 −

The complete code is given below −

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

headings=['Item', 'Sales']
data=[
   ['Apple', 45], ['Apple', 84], ['Apple', 125],
   ['Mango', 32], ['Mango', 65], ['Mango', 90],
   ['Oranges', 60], ['Oranges', 75], ['Oranges',100],
]
ws.write_row('A1', headings)
item='Apple'
rownum=1
startrow=1
for row in data:
   if row[0]==item:
      ws.set_row(rownum, None, None, {'level': 2})
      ws.write_row(rownum,0, row)
      rownum+=1
else:
   ws.set_row(rownum, None, None, {'level': 1})
   ws.write(rownum, 0, item+' Subtotal')
   cellno='B{}:B{}'.format(startrow,rownum)
   print (cellno)
   ws.write(rownum,1,'=SUBTOTAL(9,'+cellno+')')
   # rownum+=1
   item=data[rownum][0]
   rownum+=1
   ws.set_row(rownum, None, None, {'level': 2})
   ws.write_row(rownum,0, row)
   rownum+=1
   startrow=rownum
else:
   ws.set_row(rownum, None, None, {'level': 1})
   ws.write(rownum, 0, item+' Subtotal')
   cellno='B{}:B{}'.format(startrow,rownum)
   ws.write(rownum,1,'=SUBTOTAL(9,'+cellno+')')
rownum+=1
ws.write(rownum, 0, 'Grand Total')
cellno='B{}:B{}'.format(1,rownum)
ws.write(rownum,1,'=SUBTOTAL(9,'+cellno+')')

wb.close()

Output

运行代码并使用 Excel 打开“@ {s2}”。正如我们所见，大纲显示在左侧。

Run the code and open hello.xlsx using Excel. As we can see, the outlines are displayed on the left.

outlines

在各个级别，减号表示可以折叠行并仅显示小计行。

At each level, the minus sign indicates that the rows can be collapsed and only the subtotal row will be displayed.

subtotal row

此图显示“@ {s3}”中的所有行已折叠。它现在在大纲中显示正号，这意味着可以展开数据行。如果您单击“@ {s4}”处的减号，则工作表上将仅保留总计。

This figure shows all rows at level 2 have been collapsed. It now shows plus symbol in the outline which means that the data rows can be expanded. If you click the minus symbol at level 1, only the grand total will remain on the worksheet.

grand total