xlsx

xlsx - Intelligent Spreadsheet Processing Tool

Skill Overview

xlsx is an intelligent tool specialized for handling Excel files and CSV data. It supports batch creation and editing of spreadsheets, automatic recalculation of formulas, and provides data analysis and visualization features.

Applicable Scenarios

Batch processing of report files

When you need to process multiple Excel files at once, xlsx can read, modify, and save files in bulk, standardize formats, and automatically recalculate all formulas. Suitable for financial reports, sales data, and other scenarios that require regular updates.

Automated data analysis

Supports extracting data from spreadsheets for statistical analysis and generating visual charts to quickly identify trends and anomalies. Suitable for business analysis scenarios that require periodic generation of data insights.

Formula validation and repair

Automatically scans formulas in spreadsheets for errors (such as #REF!, #DIV/0!, etc.), locates problematic cells, and provides suggestions. Ensures error-free published spreadsheets, suitable for finance and audit contexts where data accuracy is critical.

Core Features

Spreadsheet creation and editing

Supports creating new xlsx files or editing existing sheets. You can set cell formats, add formulas, adjust styles, and fully retain the spreadsheet's formulas and formatting structure.

Automatic formula recalculation

Uses a built-in recalc script to automatically recalculate all formulas in the spreadsheet and detect common errors like #REF!, #DIV/0!, #VALUE!, ensuring the spreadsheet data is accurate.

Data analysis and visualization

Integrates with pandas for pivoting and statistical analysis, and supports generating various charts to help extract valuable insights from spreadsheet data.

Frequently Asked Questions

How to batch process multiple xlsx files?

xlsx can read all Excel files in a specified directory, modify them one by one (such as updating data or adjusting formats), and then save them. It supports custom processing logic, making it suitable for scenarios that require regularly updating multiple reports.

Which Excel formulas and functions are supported?

xlsx supports standard Excel formulas and function syntax, including common functions like SUM, AVERAGE, IF, VLOOKUP, etc. When creating spreadsheets, write formula strings directly; the recalculation script will automatically compute the results.

How to automatically recalculate formulas in a spreadsheet?

Use the built-in recalc.py script to automatically recalculate all formulas. The script will scan the spreadsheet for formula errors and return a detailed report, including error types and exact locations, making it easy to locate and fix issues.

Can it read and edit CSV files?

CSV file reading and writing are supported. You can use pandas to quickly process CSV data, or convert it to Excel format for further editing and formatting.

How to verify whether spreadsheet formulas have errors?

Use the recalc.py script to scan the spreadsheet; it will return detailed information on all formula errors, including error types (such as #REF!, #DIV/0!) and specific cell locations, making it easy to fix them one by one.