XLSX Toolkit
Comprehensive spreadsheet creation, editing, and analysis with support for formulas, formatting, data analysis, and visualization.
Prerequisites
- Python 3.8+
- Libraries: openpyxl, pandas
- Optional: LibreOffice (for formula recalculation)
Instructions
Read and analyze Excel data with pandas
import pandas as pd
df = pd.read_excel('file.xlsx')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)
df.describe() # Statistics
Create new Excel files with openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Hello'
sheet['B2'] = '=SUM(A1:A10)' # Use formulas, not hardcoded values
sheet['A1'].font = Font(bold=True)
wb.save('output.xlsx')
Edit existing files preserving formulas
from openpyxl import load_workbook
wb = load_workbook('existing.xlsx')
sheet = wb.active
sheet['A1'] = 'New Value'
wb.save('modified.xlsx')
Always use Excel formulas instead of hardcoding
# WRONG: sheet['B10'] = total
# CORRECT:
sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'
Financial model color standards
- Blue text: Hardcoded inputs
- Black text: All formulas
- Green text: Links from other sheets
- Yellow background: Key assumptions
Number formatting
- Currency: $#,##0 with units in headers
- Percentages: 0.0% format
- Negative numbers: Use parentheses (123) not -123
Error Handling
- If formulas show errors (#REF!, #DIV/0!), verify cell references
- Use
data_only=True to read calculated values (but formulas are lost)
- For large files, use
read_only=True or write_only=True
Notes
- Use pandas for data analysis and bulk operations
- Use openpyxl for formulas, formatting, and Excel-specific features
- Cell indices are 1-based in openpyxl
- Recalculate formulas with LibreOffice if needed
Source: anthropics/skills
1---2name: xlsx-toolkit3description: XLSX Toolkit4---5# XLSX Toolkit67Comprehensive spreadsheet creation, editing, and analysis with support for formulas, formatting, data analysis, and visualization.89## Prerequisites1011- Python 3.8+12- Libraries: openpyxl, pandas13- Optional: LibreOffice (for formula recalculation)1415## Instructions16171. **Read and analyze Excel data with pandas**18 ```python19 import pandas as pd20 df = pd.read_excel('file.xlsx')21 all_sheets = pd.read_excel('file.xlsx', sheet_name=None)22 df.describe() # Statistics23 ```24252. **Create new Excel files with openpyxl**26 ```python27 from openpyxl import Workbook28 from openpyxl.styles import Font, PatternFill2930 wb = Workbook()31 sheet = wb.active32 sheet['A1'] = 'Hello'33 sheet['B2'] = '=SUM(A1:A10)' # Use formulas, not hardcoded values34 sheet['A1'].font = Font(bold=True)35 wb.save('output.xlsx')36 ```37383. **Edit existing files preserving formulas**39 ```python40 from openpyxl import load_workbook41 wb = load_workbook('existing.xlsx')42 sheet = wb.active43 sheet['A1'] = 'New Value'44 wb.save('modified.xlsx')45 ```46474. **Always use Excel formulas instead of hardcoding**48 ```python49 # WRONG: sheet['B10'] = total50 # CORRECT:51 sheet['B10'] = '=SUM(B2:B9)'52 sheet['C5'] = '=(C4-C2)/C2'53 sheet['D20'] = '=AVERAGE(D2:D19)'54 ```55565. **Financial model color standards**57 - Blue text: Hardcoded inputs58 - Black text: All formulas59 - Green text: Links from other sheets60 - Yellow background: Key assumptions61626. **Number formatting**63 - Currency: $#,##0 with units in headers64 - Percentages: 0.0% format65 - Negative numbers: Use parentheses (123) not -1236667## Error Handling6869- If formulas show errors (#REF!, #DIV/0!), verify cell references70- Use `data_only=True` to read calculated values (but formulas are lost)71- For large files, use `read_only=True` or `write_only=True`7273## Notes7475- Use pandas for data analysis and bulk operations76- Use openpyxl for formulas, formatting, and Excel-specific features77- Cell indices are 1-based in openpyxl78- Recalculate formulas with LibreOffice if needed7980Source: anthropics/skills