File size: 16,964 Bytes
22087d0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
from core.agentpress.tool import tool_metadata
from core.sandbox.tool_base import SandboxToolsBase
from core.agentpress.thread_manager import ThreadManager

@tool_metadata(
    display_name="Spreadsheet Guide",
    description="Reference guide for creating and manipulating Excel spreadsheets via CLI Python",
    icon="Table",
    color="bg-green-100 dark:bg-green-800/50",
    weight=75,
    visible=True,
    usage_guide="""
## πŸ—£οΈ COMMUNICATION RULES - HOW TO TALK TO USERS

**THE USER IS NON-TECHNICAL. NEVER expose implementation details.**

**DO:**
- βœ… "I'll create that spreadsheet for you"
- βœ… "Here's your budget spreadsheet with the calculations"
- βœ… "I've organized the data into a spreadsheet"
- βœ… "I've added a new sheet for Q2 data"
- βœ… "The totals are automatically calculated"

**DON'T:**
- ❌ "I'll use openpyxl to create an .xlsx file"
- ❌ "I'm executing a Python script via execute_command"
- ❌ "I'll load_workbook and update cells"
- ❌ "I'm creating a temporary Python file"
- ❌ "I'll use PatternFill and Font classes"

**TONE:**
- Friendly and conversational
- Focus on WHAT you're creating, not HOW you're building it
- Describe the outcome, not the technical process
- Make it feel effortless and natural

**EXAMPLES:**

User: "Can you create a budget spreadsheet?"
❌ BAD: "I'll create a Python script using openpyxl to generate an .xlsx file with formulas."
βœ… GOOD: "I'll create a budget spreadsheet for you with all the formulas set up!"

User: "Add a column for expenses"
❌ BAD: "I'll load the workbook and insert a new column using ws.insert_cols()."
βœ… GOOD: "I'll add an expenses column for you!"

User: "Can you make the headers prettier?"
❌ BAD: "I'll apply PatternFill with #1F4E79 and Font with color FFFFFF."
βœ… GOOD: "I'll style the headers to make them look nicer!"

---

### SPREADSHEET OPERATIONS - Using CLI Python via execute_command

**PURPOSE:**
Create interactive Excel (.xlsx) files that users can view, edit, and download. Uses native Excel format with full formula support, formatting, and multi-sheet capabilities.

**WHEN TO USE:**
- User asks to create/organize data in spreadsheet format
- Data needs calculations, formulas, or structured presentation
- User wants downloadable Excel files
- Data visualization with formatting and colors

**HOW TO USE:**
Create temporary Python files and execute them using `execute_command`. This approach is cleaner and easier to debug than inline Python code.
Save spreadsheets anywhere in `/workspace/` - use logical paths based on context (e.g., `/workspace/reports/budget.xlsx`, `/workspace/data/analysis.xlsx`, or `/workspace/spreadsheets/` if organizing multiple spreadsheets).

**PATTERN:**
1. Create a temporary Python file using `create_file` with a `.py` extension
2. Execute it using `execute_command` with `python3 /path/to/temp_file.py`
3. Optionally clean up the temp file after execution
4. **🚨 MANDATORY FINAL STEP:** Use `ask` or `complete` with the spreadsheet in `attachments`

**FEATURES:**
- Native Excel (.xlsx) format - fully compatible
- Formulas (=SUM, =AVERAGE, =IF, etc.)
- Cell formatting (colors, fonts, alignment)
- Auto-styled headers (dark blue with white text)
- Interactive viewing in chat interface
- Downloadable Excel files
- Multi-sheet workbooks

---

## OPERATIONS

### 1. CREATE NEW SPREADSHEET
Use `openpyxl.Workbook()` to create a new workbook:

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_create_spreadsheet.py",
    file_contents='''import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Q1 Sales"

# Headers with styling
headers = ["Product", "Revenue", "Profit", "Margin %"]
header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal='left', vertical='center')

# Data rows (starting at row 2)
data = [
    ["Product A", 50000, 15000, "=IFERROR(C2/B2*100,0)"],
    ["Product B", 75000, 22500, "=IFERROR(C3/B3*100,0)"],
    ["Total", "=SUM(B2:B3)", "=SUM(C2:C3)", "=IFERROR(C4/B4*100,0)"]
]

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# Auto-width columns
for col in range(1, len(headers) + 1):
    ws.column_dimensions[get_column_letter(col)].width = 15

wb.save("/workspace/sales_report.xlsx")
print("Created: /workspace/sales_report.xlsx")'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_create_spreadsheet.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_create_spreadsheet.py")
```

**Step 4: 🚨 MANDATORY - Deliver to user**
```
complete(
    text="Here's your sales report! I've added formulas to calculate totals and margins automatically.",
    attachments="/workspace/sales_report.xlsx"
)
```
OR if you need user feedback:
```
ask(
    text="I've created your sales report with automatic calculations. Would you like me to adjust anything?",
    attachments="/workspace/sales_report.xlsx"
)
```

**Remember: Talk about RESULTS, not implementation. Focus on what the user gets, not how you built it.**

### 2. ADD SHEET TO EXISTING FILE
Use `wb.create_sheet()` to add sheets - this preserves existing sheets:

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_add_sheet.py",
    file_contents='''import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

wb = openpyxl.load_workbook("/workspace/sales_report.xlsx")
ws = wb.create_sheet(title="Q2 Sales")

headers = ["Product", "Revenue", "Profit"]
header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font

data = [
    ["Product A", 60000, 18000],
    ["Product B", 80000, 24000]
]

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

for col in range(1, len(headers) + 1):
    ws.column_dimensions[get_column_letter(col)].width = 15

wb.save("/workspace/sales_report.xlsx")
print("Added sheet: Q2 Sales")'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_add_sheet.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_add_sheet.py")
```

### 3. UPDATE EXISTING CELLS
Use `load_workbook()` and modify cells directly:

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_update_cells.py",
    file_contents='''import openpyxl

wb = openpyxl.load_workbook("/workspace/sales_report.xlsx")
ws = wb.active  # or wb["Sheet Name"]

# Update specific cells
ws["A1"] = "Product Name"
ws["B5"] = 100000
ws["C5"] = "=B5*0.3"

wb.save("/workspace/sales_report.xlsx")
print("Updated cells")'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_update_cells.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_update_cells.py")
```

### 4. READ SPREADSHEET DATA

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_read_spreadsheet.py",
    file_contents='''import openpyxl

wb = openpyxl.load_workbook("/workspace/sales_report.xlsx")
ws = wb.active

print(f"Sheet: {ws.title}")
print(f"Dimensions: {ws.dimensions}")
print(f"Sheets in workbook: {wb.sheetnames}")

for row in ws.iter_rows(min_row=1, max_row=10, values_only=True):
    print(row)'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_read_spreadsheet.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_read_spreadsheet.py")
```

### 5. FORMAT CELLS

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_format_cells.py",
    file_contents='''import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

wb = openpyxl.load_workbook("/workspace/sales_report.xlsx")
ws = wb.active

# Apply formatting to a range
for row in ws["A1:D1"]:
    for cell in row:
        cell.fill = PatternFill(start_color='4CAF50', end_color='4CAF50', fill_type='solid')
        cell.font = Font(bold=True, color='FFFFFF', size=12)
        cell.alignment = Alignment(horizontal='center')

# Add borders
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

for row in ws["A1:D10"]:
    for cell in row:
        cell.border = thin_border

wb.save("/workspace/sales_report.xlsx")
print("Formatted")'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_format_cells.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_format_cells.py")
```

### 6. ADD ROWS TO EXISTING SHEET

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_add_rows.py",
    file_contents='''import openpyxl

wb = openpyxl.load_workbook("/workspace/sales_report.xlsx")
ws = wb.active

# Append new rows at the end
ws.append(["Product C", 60000, 18000, 30])
ws.append(["Product D", 45000, 13500, 30])

wb.save("/workspace/sales_report.xlsx")
print("Added rows")'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_add_rows.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_add_rows.py")
```

### 7. CROSS-SHEET REFERENCES

**Step 1: Create temporary Python file**
```
create_file(
    file_path="temp_cross_sheet.py",
    file_contents='''import openpyxl
from openpyxl.styles import Font, PatternFill

wb = openpyxl.load_workbook("/workspace/sales_report.xlsx")

# Create summary sheet
ws = wb.create_sheet(title="Summary")

headers = ["Metric", "Value"]
header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font

# Reference other sheets
data = [
    ["Q1 Total Revenue", "=SUM('Q1 Sales'!B2:B10)"],
    ["Q2 Total Revenue", "=SUM('Q2 Sales'!B2:B10)"],
    ["Combined Total", "=B2+B3"]
]

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

wb.save("/workspace/sales_report.xlsx")
print("Created summary with cross-sheet references")'''
)
```

**Step 2: Execute the Python file**
```
execute_command(
    command="python3 temp_cross_sheet.py",
    blocking=true
)
```

**Step 3: Clean up (optional)**
```
delete_file(file_path="temp_cross_sheet.py")
```

---

## IMPORTANT: DO NOT CREATE CORRUPT SPREADSHEETS

### CRITICAL - AVOIDING CIRCULAR REFERENCES

**ROW NUMBERING:**
Headers are in ROW 1. Data rows start at ROW 2.
- Row index 0 in your data array = Excel Row 2
- Row index 1 in your data array = Excel Row 3
- etc.

**CORRECT Example (headers in row 1, data starts row 2):**
If you have 3 data rows (rows 2-4), a SUM formula in row 5 should be: `=SUM(B2:B4)`
NEVER reference the cell you're placing the formula in.
Total row formulas should reference ONLY the data rows ABOVE them.

```python
# CORRECT: 3 products + 1 total row
headers = ["Product", "Revenue", "Profit"]
data = [
    ["Product A", 50000, 15000],      # Row 2
    ["Product B", 75000, 22500],      # Row 3  
    ["Product C", 60000, 18000],      # Row 4
    ["Total", "=SUM(B2:B4)", "=SUM(C2:C4)"]  # Row 5 - formulas sum rows 2-4
]
```

**WRONG (causes circular reference):**
```python
# WRONG: This creates circular reference!
data = [
    ["Product A", 50000, 15000],
    ["Product B", 75000, 22500],
    ["Product C", 60000, 18000],
    ["Total", "=SUM(B2:B5)", "=SUM(C2:C5)"]  # BAD: B5/C5 is THIS row!
]
```

### CRITICAL - PREVENTING #DIV/0! ERRORS

ALWAYS wrap division formulas with IFERROR to handle divide-by-zero:
- Use: `=IFERROR(A1/B1,0)` instead of `=A1/B1`
- For percentage: `=IFERROR(C4/B4*100,0)` instead of `=C4/B4*100`

```python
# CORRECT: Safe division with IFERROR
data = [
    ["Product A", 50000, 35000, "=IFERROR(C2/B2*100,0)"],
    ["Product B", 75000, 45000, "=IFERROR(C3/B3*100,0)"],
    ["Total", "=SUM(B2:B3)", "=SUM(C2:C3)", "=IFERROR(C4/B4*100,0)"]
]
```

---

## BEST PRACTICES

1. Count your data rows carefully before writing formulas
2. Total/summary formulas should be in the LAST row
3. Formula ranges should end at the row BEFORE the formula row
4. Use explicit values instead of formulas when unsure
5. Test mentally: "Does this formula reference its own cell?" β†’ If yes, FIX IT
6. ALWAYS use IFERROR() around any division to prevent #DIV/0! errors
7. Use `wb.create_sheet()` to add sheets - NEVER recreate the workbook
8. **🚨 CRITICAL:** ALWAYS use `ask` or `complete` with spreadsheet in `attachments` - user CANNOT see it otherwise!

---

## COMMON FORMULAS

- SUM: `=SUM(A1:A10)`
- AVERAGE: `=AVERAGE(B2:B10)`
- COUNT: `=COUNT(A1:A100)`
- IF: `=IF(A1>100,"High","Low")`
- VLOOKUP: `=VLOOKUP(A1,Sheet2!A:B,2,FALSE)`
- SUMIF: `=SUMIF(A:A,"Product A",B:B)`
- Safe division: `=IFERROR(C2/B2*100,0)`
- Cross-sheet: `=SUM('Sheet Name'!B2:B10)`

---

## FORMATTING OPTIONS

**Colors (hex without #):**
- 4CAF50 (green)
- F44336 (red)
- 2196F3 (blue)
- FFC107 (yellow)
- 1F4E79 (dark blue - default header)
- FFFFFF (white)
- 000000 (black)

**Styles:**
- PatternFill(start_color='HEX', end_color='HEX', fill_type='solid')
- Font(bold=True, italic=True, color='HEX', size=12, name='Arial')
- Alignment(horizontal='center', vertical='center', wrap_text=True)
- Border with Side(style='thin'/'medium'/'thick')

---

## FILE PATHS

- Save spreadsheets anywhere in `/workspace/` - use logical paths based on context
- Examples:
  - `/workspace/budget.xlsx` - simple file in workspace root
  - `/workspace/reports/q1_sales.xlsx` - organized in subdirectory
  - `/workspace/data/analysis.xlsx` - data directory
  - `/workspace/spreadsheets/` - optional directory for organizing multiple spreadsheets
- Use `load_workbook()` to modify existing files
- Use `Workbook()` only for NEW files (overwrites existing!)
- Create directories if needed: `import os; os.makedirs('/workspace/reports', exist_ok=True)`

---

## 🚨🚨🚨 MANDATORY FINAL STEP: DELIVER SPREADSHEET TO USER 🚨🚨🚨

**AFTER CREATING/UPDATING ANY SPREADSHEET, YOU MUST USE `ask` OR `complete` WITH ATTACHMENT:**

```
complete(
    text="Here's your [spreadsheet name]! I've set up [what it does in user-friendly terms].",
    attachments="/workspace/your_spreadsheet.xlsx"
)
```
OR if you need user feedback:
```
ask(
    text="I've created your [spreadsheet name] with [features]. Would you like me to adjust anything?",
    attachments="/workspace/your_spreadsheet.xlsx"
)
```

**COMMUNICATION REMINDERS:**
- βœ… Describe what the spreadsheet DOES, not how you built it
- βœ… Use friendly, conversational language
- βœ… Mention key features users will care about (calculations, formatting, organization)
- ❌ NEVER mention technical details (openpyxl, Python, execute_command, etc.)
- ❌ NEVER say "I created the spreadsheet at /workspace/file.xlsx" without attaching it
- ❌ NEVER end task without using `ask` or `complete` with the spreadsheet in `attachments`

**THIS IS NOT OPTIONAL - IT IS MANDATORY:**
- Users see deliverables ONLY through `ask`/`complete` attachments parameter
- Without attachment, the spreadsheet is INVISIBLE to the user
- This breaks the entire user experience - user paid for a deliverable they can't see

**GOOD EXAMPLES:**
- "Here's your budget spreadsheet! All the totals calculate automatically, and I've color-coded the sections for easy reading."
- "I've created your sales report with quarterly breakdowns. The formulas will update automatically when you add new data."
- "Your inventory spreadsheet is ready! I've organized it by category with automatic stock calculations."
"""
)
class SandboxSpreadsheetTool(SandboxToolsBase):
    """
    Guide-only tool for spreadsheet operations.
    Use create_file + execute_command with Python/openpyxl scripts - see usage_guide for examples.
    Creates temporary Python files instead of using inline Python code for better maintainability.
    """

    def __init__(self, project_id: str, thread_manager: ThreadManager):
        super().__init__(project_id, thread_manager)