Python XlsxWriter – Outlining and Grouping

In Excel, you can group rows or columns with the same value in a specific column (or row) so that they can be hidden or shown with a single mouse click. This feature is called Outlining and Grouping. It is useful for displaying subtotals or summaries. This feature can be found in the Data → Outlining group in MS Excel.

To use this feature, all rows in the data range must be sorted by the value of a column. Let’s say we have sales figures for different items. After sorting by item name, click the Subtotal option in the Outlining group. The following dialog box will pop up.

The worksheet displays the sales subtotals by item and the final total. The left side of the worksheet shows the outline levels. The raw data is at level 3, the subtotals are at level 2, and the totals are at level 1.

Working with Outlining and Grouping

To achieve this with XlsxWriter, we need to use the level attribute of the set_row() method. The data row is set to level 2.

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

The subtotal row has level 1.

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

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

Example

Below is the complete code

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

Run the code and open hello.xlsx in Excel. You can see the outline displayed on the left.

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

The image shows that all rows at Level 2 are collapsed. It now displays a plus sign in its outline, indicating that the data rows can be expanded. If you click the minus sign in Layer 1, only the total data will remain on the worksheet.