testskill11113

intent

use this skill when the task involves creating, reading, or modifying Excel workbooks (.xlsx, .xlsm, .xls) or spreadsheet files (.csv, .tsv) where formulas, cell formatting, data types, dates, merged cells, workbook structure, or cross-platform compatibility matter. specifically use it when the artifact must stay "live" (formulas intact, not flattened to values), when precision past 15 digits or leading-zero preservation is critical, or when an existing template structure must be preserved. avoid this skill if the job is pure data reshaping or analysis that doesn't depend on Excel features; use pandas for that instead.

inputs

python libraries

• openpyxl (required): handles .xlsx/.xlsm with full formula, style, and structure preservation. does NOT calculate formulas; only reads/writes them.
• pandas (optional): useful for CSV reads, data reshaping, and type inference, but does not preserve Excel-specific features like formulas or merged cells.
• csv module (optional): lightweight plain-text tabular import/export.

file inputs

• source workbook path (local or remote)
• sheet name(s) to target (if not specified, assumes active sheet)
• read mode: values only vs. formulas (openpyxl distinguishes these)

external context

• knowledge of existing template structure (sheet names, hidden rows/columns, named ranges, conditional formatting, data validation rules, print areas, filter state)
• date system in use (1900 = Microsoft default; 1904 = legacy Mac Excel; impacts serial-number interpretation)
• data type requirements (e.g., phone numbers, ZIP codes, long IDs must be text to avoid truncation or auto-parse damage)

edge cases to handle

• network timeouts if fetching remote files
• memory spikes on large files (1000+ rows/cols); streaming or chunked reads may be required
• auth expiry if accessing cloud-hosted workbooks via API
• rate limits on repeated file opens/saves
• cached formula values that are stale after prior edits
• external references that break if source files move
• macro-enabled files (.xlsm) that may not recalculate in all contexts

procedure

1. identify the spreadsheet job and choose the tool

   • input: task description, file extension, required features (formulas, styles, structure)
   • if the task is "reshape or analyze tabular data and output CSV or simple workbook", use pandas
   • if the task involves "formulas, merged cells, styles, comments, or template preservation", use openpyxl
   • if the task is "plain CSV exchange with no Excel-specific features", use csv module
   • output: decision on which library/approach to use

2. load the workbook with appropriate mode

   • input: file path, sheet name(s), read mode requirement (data_only=False to get formulas, data_only=True to get cached values)
   • if using openpyxl: open with load_workbook(filename, data_only=False) to preserve formulas
   • if data_only=True is used, note that cached values may be stale and formulas are not accessible
   • if using pandas: pd.read_excel(file, sheet_name=...) with explicit dtype and date_parser args to prevent silent type corruption
   • output: workbook object or dataframe; confirm sheet names and dimensions

3. inspect date system and data types before reading cells

   • input: workbook metadata, sample cell values, column headers
   • check workbook.date_1904 attribute (True = Mac 1904 system, False = standard 1900)
   • identify columns that hold phone numbers, ZIP codes, IDs, or leading-zero values; mark as text-only
   • identify datetime columns and note their current display format
   • output: documented data-type map and date-system flag

4. read cell values with type safety

   • input: target range, column types from step 3
   • for numeric columns, check for precision beyond 15 digits; if found, read as text and convert with explicit rounding/truncation rules
   • for mixed text-number columns, read as text and parse separately
   • for dates, read as serial numbers and convert using the correct date system (1900 or 1904)
   • do NOT rely on Excel's auto-detected types; override with explicit dtype or cell.data_type
   • output: dictionary or dataframe with correct types; no silent truncation or auto-parse damage

5. preserve workbook structure before edits

   • input: existing workbook object (from step 2), list of changes to make
   • document sheet order, row/column widths, frozen panes, filters, print areas, conditional formatting, data validation rules, named ranges, external references
   • if the workbook is a template, note visual conventions (input cells vs. formula cells, color schemes, wrapped text)
   • output: snapshot of structure; proceed only if changes do not contradict template intent

6. write formulas instead of hardcoded values when the workbook must stay live

   • input: target cells, formula logic, assumption cells (e.g., parameter cells that drive the formula)
   • write formula strings into cells, not calculated results from Python
   • use cell references, not magic numbers (e.g., =A1+B1, not =42)
   • prefer named ranges or structured references over column indices (more readable, less drift)
   • output: cells with formula strings; formulas are not yet calculated in openpyxl

7. validate formulas before save

   • input: workbook with new or modified formulas
   • iterate over all formula cells; check for #REF!, #DIV/0!, #VALUE!, #NAME?, or circular references
   • verify that copied formulas have correct absolute/relative references (test a few cells by hand)
   • verify denominators, named-range targets, and precedent cells exist and are correct
   • if formulas depend on external references, confirm source files are accessible
   • output: list of errors (if any); do not save if errors remain

8. recalculate or externally verify formula values

   • input: workbook with formulas, recalc requirement (does user need current values or formulas only?)
   • openpyxl does NOT calculate formulas; if user needs values, open the file in Excel, LibreOffice Calc, or another tool that recalculates and re-save
   • OR use a headless Excel or LibreOffice API call (slower, requires external install)
   • if formulas are new and user needs to see values immediately, consider providing both: the .xlsx with formulas plus a temporary CSV export of current values
   • output: workbook with formulas calculated (or note that formulas are not yet calculated)

9. apply or preserve formatting

   • input: existing styles (from step 5), new cell content (from steps 4, 6)
   • for new cells, match existing style (font, fill, number format, alignment) unless task explicitly changes style
   • for dates, apply the correct number format code (e.g., "mm/dd/yyyy") that matches the date system
   • for text-as-number (phone, ZIP), ensure the cell format is "Text" or "@" to prevent auto-parse on re-open
   • if task requires new styling and no template exists, keep inputs visually distinct from formulas (e.g., light fill for inputs, white for formulas)
   • preserve merged cells, wrapped text, and hidden rows/columns unless task changes them
   • output: workbook with styles applied

10. save the workbook with correct settings

    • input: modified workbook object, output file path, save mode (preserve formulas or flatten to values?)
    • use workbook.save(filename) with openpyxl; this preserves formulas, styles, and structure
    • do NOT open and re-save in data_only mode unless task explicitly requires flattening
    • confirm output file extension matches intended format (.xlsx, .xlsm, .xls, .csv)
    • output: saved file at specified path

11. visual spot-check before delivery (if layout or display matters)

    • input: saved workbook file
    • open in Excel, LibreOffice Calc, or Google Sheets and visually scan for: wrapped text overflow, clipped labels, misaligned merged cells, formula errors, stale cached values, filters or conditional formats that hide data unexpectedly
    • if formulas exist, confirm at least one cell recalculated correctly
    • output: sign-off that workbook displays correctly or list of visual issues to fix

decision points

if the file is a CSV or TSV (not Excel native):

• use pandas or csv module to read/write
• note that CSV does not store formulas, styles, merged cells, or multiple sheets
• if user needs Excel features, convert to .xlsx after CSV import and then apply formatting in step 9

if formulas must be preserved and user needs current calculated values:

• save the .xlsx with formulas intact (step 10)
• provide a secondary export (CSV or separate sheet) showing current values, OR
• note that formulas are present but not calculated in Python; user must open in Excel or provide a recalc step via external tool

if the workbook is a template with existing styles, structure, and validation rules:

• preserve all template features in step 5 snapshot
• only change content, not structure, unless task explicitly says otherwise
• do NOT override color schemes, fonts, or widths to impose a generic house style

if data types are mixed or ambiguous (e.g., column contains "123", "ABC", "45.6"):

• read as text (step 4) and parse with explicit logic
• do NOT rely on pandas type inference; override with dtype="str"

if the file is large (1000+ rows or complex formulas) and loading fails due to memory:

• use openpyxl with read_only=True mode (can't modify, only inspect)
• or use pandas with chunksize= parameter for streaming reads
• narrow the column and row ranges to essentials; avoid full-sheet loads

if external references exist (one workbook links to another):

• confirm both files are present and accessible before save
• note that external references can break if source files move or are renamed
• consider documenting the reference path and backup strategy

if the workbook contains macros (.xlsm):

• openpyxl preserves the macro binary but does not execute it
• note that macros may not run in all contexts (Linux, headless, cloud apps)
• if macro behavior is critical, test in the target environment after save

output contract

successful completion delivers:

• a saved Excel or CSV file at the specified output path
• file extension matches the intended format (.xlsx, .xlsm, .xls, or .csv)
• all formulas are syntactically correct (no #REF!, #DIV/0!, #VALUE!, #NAME?, or circular references)
• all cell data types are correct (no silent truncation of 16+ digit numbers, no stripped leading zeros, no mangled dates)
• all existing template structure, styles, and conventions are preserved (or changes are documented)
• if the file is a template, sheet order, frozen panes, filters, data validation, conditional formatting, print areas, and visual conventions remain unchanged unless task explicitly changes them
• if formulas are new, they use cell references and named ranges, not hardcoded values
• if the task requires calculated values, either formulas are provided (user recalculates in Excel) OR values are calculated externally and provided as static data (with note that formulas are flattened)
• optional: a CSV export or visual inspection report if layout, display, or value correctness is critical

outcome signal

the skill worked if:

• the output file opens without errors in Excel, LibreOffice Calc, or Google Sheets
• all cell values and formulas display correctly (no corruption, no hidden errors)
• data types are intact (long IDs are still text, dates still show as dates, numbers have correct precision)
• if formulas exist, at least one formula recalculates correctly when user opens the file and presses Ctrl+Shift+F9 (or equivalent recalc command)
• if the workbook is a template, the user confirms that structure, styles, and conventions match the original
• if the task required formula validation, the user or a secondary tool confirms zero formula errors
• if layout matters, the user visually confirms that text wrapping, column widths, merged cells, and conditional formatting display as intended