-
Notifications
You must be signed in to change notification settings - Fork 528
use cases excel headcount
Persona: HR Specialist | Difficulty: Intermediate | Tags:
excelcreatehr
Create a department headcount spreadsheet with styled headers, salary budgets, a totals row, and a calculated target headcount column — all without opening Excel.
Download: headcount-q2.xlsx · headcount-q2.py
Build a Q2 Headcount and Salary Budget Tracker
See also: Set Cell Values · Add a Column · Batch Commands · Create a Workbook
User:
Hey, I need to put together a headcount tracking sheet for our Q2 planning meeting next week. We have four departments: Engineering, Sales, Marketing, and Operations. Each department should have columns for current headcount, open roles, and the monthly salary budget. Here's the data I have so far: Engineering: 52 employees, 6 open roles, 487,000ドル monthly budget Sales: 31 employees, 4 open roles, 298,000ドル monthly budget Marketing: 18 employees, 2 open roles, 164,000ドル monthly budget Operations: 24 employees, 1 open role, 201,000ドル monthly budget Can you get that into a nice clean spreadsheet? Also add a totals row at the bottom so I can see the company-wide numbers.
Agent creates the workbook and populates all headers, data, and totals in one batch:
Show commands (29 commands)
officecli create headcount-q2.xlsx officecli set headcount-q2.xlsx '/Sheet1/A1' \ --prop value=Department \ --prop bold=true \ --prop fill=4472C4 \ --prop font.color=FFFFFF \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/B1' \ --prop value="Current Headcount" \ --prop bold=true \ --prop fill=4472C4 \ --prop font.color=FFFFFF \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/C1' \ --prop value="Open Roles" \ --prop bold=true \ --prop fill=4472C4 \ --prop font.color=FFFFFF \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D1' \ --prop value="Monthly Salary Budget" \ --prop bold=true \ --prop fill=4472C4 \ --prop font.color=FFFFFF \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/A2' --prop value=Engineering --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/B2' \ --prop value=52 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/C2' \ --prop value=6 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D2' \ --prop value=487000 \ --prop type=number \ --prop numFmt="$#,##0" \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/A3' --prop value=Sales --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/B3' \ --prop value=31 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/C3' \ --prop value=4 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D3' \ --prop value=298000 \ --prop type=number \ --prop numFmt="$#,##0" \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/A4' --prop value=Marketing --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/B4' \ --prop value=18 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/C4' \ --prop value=2 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D4' \ --prop value=164000 \ --prop type=number \ --prop numFmt="$#,##0" \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/A5' --prop value=Operations --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/B5' \ --prop value=24 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/C5' \ --prop value=1 \ --prop type=number \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D5' \ --prop value=201000 \ --prop type=number \ --prop numFmt="$#,##0" \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/A6' \ --prop value=Total \ --prop bold=true \ --prop fill=D9E1F2 \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/B6' \ --prop formula="=SUM(B2:B5)" \ --prop bold=true \ --prop fill=D9E1F2 \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/C6' \ --prop formula="=SUM(C2:C5)" \ --prop bold=true \ --prop fill=D9E1F2 \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D6' \ --prop formula="=SUM(D2:D5)" \ --prop bold=true \ --prop fill=D9E1F2 \ --prop numFmt="$#,##0" \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/col[A]' --prop width=18 officecli set headcount-q2.xlsx '/Sheet1/col[B]' --prop width=20 officecli set headcount-q2.xlsx '/Sheet1/col[C]' --prop width=14 officecli set headcount-q2.xlsx '/Sheet1/col[D]' --prop width=24
User:
Oh wait, I just heard back from the Sales director — they actually have 5 open roles, not 4. Can you fix that? And also, their monthly budget went up to 312,000ドル because we approved two new senior account exec positions.
Agent corrects the Sales row and refreshes the SUM totals in one batch:
officecli set headcount-q2.xlsx '/Sheet1/C3' --prop value=5 officecli set headcount-q2.xlsx '/Sheet1/D3' --prop value=312000 officecli set headcount-q2.xlsx '/Sheet1/C6' --prop formula="=SUM(C2:C5)" officecli set headcount-q2.xlsx '/Sheet1/D6' --prop formula="=SUM(D2:D5)"
User:
This looks good! One more thing though — can you squeeze in a column between the open roles and the monthly budget? I want to track the target headcount, which is just current employees plus open roles added together. So it should calculate automatically for each row if possible.
Agent inserts a column at index 4 and wires up =B+C formulas for every row:
officecli add headcount-q2.xlsx '/Sheet1' --type col --index 4 officecli set headcount-q2.xlsx '/Sheet1/D1' \ --prop value="Target Headcount" \ --prop bold=true \ --prop fill=4472C4 \ --prop font.color=FFFFFF \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D2' \ --prop formula="=B2+C2" \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D3' \ --prop formula="=B3+C3" \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D4' \ --prop formula="=B4+C4" \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D5' \ --prop formula="=B5+C5" \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/D6' \ --prop formula="=B6+C6" \ --prop bold=true \ --prop fill=D9E1F2 \ --prop halign=center \ --prop border.all=thin officecli set headcount-q2.xlsx '/Sheet1/col[D]' --prop width=18 officecli set headcount-q2.xlsx '/Sheet1/E6' --prop formula="=SUM(E2:E5)"
Based on OfficeCLI v1.0.34