Improve Excel stats

[bwbroersma]

See attached sample with filtering additions: sample.xlsx
The reverse domain notation is Excel > 2019, so would be nice to add that in python instead (for sorting).

The improvements in this sample.xlsx are:

• Add column B with in row 12 'Reverse domain name' and either =TEXTJOIN(".";FALSE;SORTBY(TEXTSPLIT($C13;;".");SEQUENCE(LEN($C13)-LEN(SUBSTITUTE($C13;".";""))+1;1;0;1);-1)), currently this is only supported in LibreOffice and Excel > 2019, so if this is easy to do in python it would be preferred to be added as a value. This column is useful for sorting (to group the apex to www, etc.).
• All conditional formatting is changed to the range G13:CE100013 to support 100.000 at least in the formatting of Excel, instead of the current 5038 (5050-12), see Extend Excel conditional formatting and statistics to support > 5038 results #460.
  Current code location:
  

  Internet.nl-dashboard/dashboard/internet_nl_dashboard/logic/report_to_spreadsheet.py

  Lines 225 to 231 in 73a64d4

  	# Set the measurements to green/red depending on value using conditional formatting.
  	# There is no true/false, but we can color based on value.
  	for grade, pattern in conditional_rules.items():
  	worksheet.conditional_formatting.add(
  	'F13:CD5050',
  	CellIsRule(operator='=', formula=[f'"{grade}"'], stopIfTrue=True, fill=pattern)
  	)

• Added a static dropdown in A2 with 'Total' or 'Filtered selection' (default on last).
  XML in the xl/worksheets/sheet1.xml for it is in this case:

  <dataValidations count="1">
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="A5 A2:B2" xr:uid="{A70861FE-4697-4421-A5DE-EAF9AF14210A}">
      <formula1>"Total,Filtered selection"</formula1>
    </dataValidation>
  </dataValidations>

• Column H for row 1 to 8 get the values:
  • <>
  • passed
  • warning
  • failed
  • not_tested
  • error
  • not_applicable
• Column H for row 12 gets the header 'InStats' or something, from row 12 the formula =OR($A$2="Total";SUBTOTAL(103;$A13)=1), this will result in FALSE if A2 is set to 'Filtered selection' and the row is not filtered (credits to @WKobes).
• Cell G1 get the formula =COUNTIFS($F$13:$F$100013;TRUE;G$13:G$100013;$F4), this can be 'dragged out' to all other header calculation rows, this will take into account if the stats should limit itself to visible (filtered) rows.
  Current formula code location here:
  

  Internet.nl-dashboard/dashboard/internet_nl_dashboard/logic/report_to_spreadsheet.py

  Lines 178 to 192 in 73a64d4

  	worksheet[f'{cell}1'] = f'=COUNTA({cell}13:{cell}5050)'
  	# todo: also support other values
  	worksheet[f'{cell}2'] = f'=COUNTIF({cell}13:{cell}5050, "passed")'
  	worksheet[f'{cell}3'] = f'=COUNTIF({cell}13:{cell}5050, "info")'
  	worksheet[f'{cell}4'] = f'=COUNTIF({cell}13:{cell}5050, "warning")'
  	worksheet[f'{cell}5'] = f'=COUNTIF({cell}13:{cell}5050, "failed")'
  	worksheet[f'{cell}6'] = f'=COUNTIF({cell}13:{cell}5050, "not_tested")'
  	worksheet[f'{cell}7'] = f'=' \
  	f'COUNTIF({cell}13:{cell}5050, "error")+' \
  	f'COUNTIF({cell}13:{cell}5050, "unreachable")+' \
  	f'COUNTIF({cell}13:{cell}5050, "untestable")+' \
  	f'COUNTIF({cell}13:{cell}5050, "not_testable")'
  	worksheet[f'{cell}8'] = f'=' \
  	f'COUNTIF({cell}13:{cell}5050, "no_mx")+' \
  	f'COUNTIF({cell}13:{cell}5050, "not_applicable")'

• Preferable the filter is enabled with row 12 as header row.

[stitch]

The number 103 is not clear in this function: =OR($A$2="Total",SUBTOTAL(103,$A13)=1).

Pyexcel does not support autofiltering and the static dropdown i also can't find (even with data validation).

[bwbroersma]

Sorry, I should have added the documentation, the first argument of SUBTOTAL is function_num:

Function_num  Required.
The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

Function_num (includes hidden rows)	Function_num (ignores hidden rows)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Maybe we can think of something more generic than a drop-down.
Ideally all spreadsheet functionality would also be ported to ODS, for conditional formatting this is not an issues, for the other (new) stuff I don't know, therefore generic common stuff is preferred over the maybe exotic drop-down that I suggested.