Python XlsxWriter - Outpnes & Grouping

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 cpck. This feature is called to as outpnes and grouping. It helps in displaying sub-totals or summaries. This feature can be found in MS excel software s Data→Outpne 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, cpck on the Subtotal option in the Outpne group. Following dialog box pops up.

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

Working with Outpnes and Grouping

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})

The rows for subtotal are having level 1.

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

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

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

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

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