xlsx-skill（XLSX 技能）

XLSX Skill v2.0

Overview

Complete Excel spreadsheet processing using openpyxl for creation/editing and pandas for data analysis. Supports formulas, formatting, charts, and complex data operations.

Installation & Dependencies

Required

pip install pandas openpyxl xlsxwriter

Optional

# For chart support
pip install numpy

# For PDF export
brew install --cask libreoffice

Quick Start

Read Excel File

import pandas as pd

# Read first sheet
df = pd.read_excel('file.xlsx')

# Read specific sheet
df = pd.read_excel('file.xlsx', sheet_name='Sheet1')

# Read all sheets
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)

Create Excel File

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 'Hello'
ws['B1'] = 'World'
wb.save('output.xlsx')

Add Formula

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = '=SUM(A1:A2)'  # Excel formula
wb.save('formula.xlsx')

Complete API Reference

Reading Data with pandas

import pandas as pd

# Basic read
df = pd.read_excel('data.xlsx')

# Read specific columns
df = pd.read_excel('data.xlsx', usecols=['A', 'B', 'C'])

# Read with header row
df = pd.read_excel('data.xlsx', header=0)

# Read without header
df = pd.read_excel('data.xlsx', header=None)

# Read specific rows
df = pd.read_excel('data.xlsx', skiprows=range(1, 10))  # Skip rows 1-9

# Read multiple sheets
sheets_dict = pd.read_excel('data.xlsx', sheet_name=None)
for sheet_name, df in sheets_dict.items():
    print(f"Sheet: {sheet_name}, Rows: {len(df)}")

# Data exploration
df.head()        # First 5 rows
df.tail()        # Last 5 rows
df.info()        # Column info
df.describe()    # Statistics
df.shape         # (rows, columns)
df.columns       # Column names
df.dtypes        # Data types

Writing Data with pandas

import pandas as pd

# Create DataFrame
df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Age': [25, 30, 35],
    'City': ['NYC', 'LA', 'Chicago']
})

# Write to Excel
df.to_excel('output.xlsx', index=False)

# Write to specific sheet
df.to_excel('output.xlsx', sheet_name='Data', index=False)

# Write multiple sheets
with pd.ExcelWriter('multi-sheet.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

Creating with openpyxl

from openpyxl import Workbook
from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment, Color
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Data"

# Write values
ws['A1'] = 'Header 1'
ws['B1'] = 'Header 2'
ws.append(['Row 1 Col 1', 'Row 1 Col 2'])
ws.append(['Row 2 Col 1', 'Row 2 Col 2'])

# Cell formatting
ws['A1'].font = Font(bold=True, size=14, color='FFFFFF')
ws['A1'].fill = PatternFill('solid', start_color='4472C4')
ws['A1'].alignment = Alignment(horizontal='center')

# Column width
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15

# Row height
ws.row_dimensions[1].height = 25

# Merge cells
ws.merge_cells('A1:B1')

# Save
wb.save('formatted.xlsx')

Formulas

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Basic formulas
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = '=SUM(A1:A2)'      # Sum
ws['A4'] = '=AVERAGE(A1:A2)'  # Average
ws['A5'] = '=MAX(A1:A2)'      # Maximum
ws['A6'] = '=MIN(A1:A2)'      # Minimum
ws['A7'] = '=COUNT(A1:A2)'    # Count numbers

# Cross-sheet references
wb.create_sheet('Sheet2')
ws2 = wb['Sheet2']
ws2['A1'] = '=Data!A1'  # Reference to Sheet1

# Save and recalculate
wb.save('formulas.xlsx')

Charts

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, LineChart, PieChart

wb = Workbook()
ws = wb.active

# Add data
data = [
    ['Category', 'Value'],
    ['A', 10],
    ['B', 15],
    ['C', 20],
    ['D', 25]
]
for row in data:
    ws.append(row)

# Create bar chart
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Sales Chart"
chart.y_axis.title = 'Value'
chart.x_axis.title = 'Category'

# Define data range
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
cats = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, "E1")

wb.save('chart.xlsx')

Tables

from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

# Add data
data = [
    ['Name', 'Age', 'City'],
    ['Alice', 25, 'NYC'],
    ['Bob', 30, 'LA'],
    ['Charlie', 35, 'Chicago']
]
for row in data:
    ws.append(row)

# Create table
tab = Table(displayName="Table1", ref="A1:C4")
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True,
                       showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)

wb.save('table.xlsx')

Conditional Formatting

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

# Add data
for i in range(1, 11):
    ws.cell(row=i, column=1, value=i * 10)

# Highlight cells greater than 50
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
ws.conditional_formatting.add(
    'A1:A10',
    CellIsRule(operator='greaterThan', formula=['50'], fill=red_fill)
)

# Highlight even numbers with formula
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
ws.conditional_formatting.add(
    'A1:A10',
    FormulaRule(formula=['IS EVEN(A1)'], fill=green_fill)
)

wb.save('conditional.xlsx')

Data Validation

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

# Dropdown list
dv = DataValidation(type="list", formula1='"Yes,No,Maybe'", allow_blank=True)
dv.error = "Please select from the list"
dv.errorTitle = "Invalid Selection"
ws.add_data_validation(dv)
dv.add('A1:A10')

# Number range
dv2 = DataValidation(type="whole", operator="between", formula1=["1", "100"])
dv2.error = "Enter a number between 1 and 100"
ws.add_data_validation(dv2)
dv2.add('B1:B10')

wb.save('validation.xlsx')

Complete Examples

Example 1: Financial Report

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Financial Report"

# Styles
header_font = Font(bold=True, size=12, color='FFFFFF')
header_fill = PatternFill('solid', start_color='4472C4')
border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
center_align = Alignment(horizontal='center')

# Headers
headers = ['Category', 'Q1', 'Q2', 'Q3', 'Q4', 'Total']
ws.append(headers)

# Style header row
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.border = border
    cell.alignment = center_align

# Data (blue for inputs, black for formulas)
data = [
    ['Revenue', 100000, 120000, 115000, 130000],
    ['COGS', 40000, 48000, 46000, 52000],
    ['Gross Profit', '=B2-B3', '=C2-C3', '=D2-D3', '=E2-E3'],
    ['Expenses', 30000, 32000, 31000, 33000],
    ['Net Income', '=B4-B5', '=C4-C5', '=D4-D5', '=E4-E5']
]

for row in data:
    ws.append(row)

# Add Total formula for Q1 column
last_row = len(data) + 2
ws[f'B{last_row}'] = '=SUM(B2:B6)'

# Format as currency
for row in ws.iter_rows(min_row=2, max_row=last_row, min_col=2, max_col=6):
    for cell in row:
        cell.number_format = '$#,##0'
        cell.border = border

# Column widths
ws.column_dimensions['A'].width = 15
for col in range(2, 7):
    ws.column_dimensions[get_column_letter(col)].width = 12

wb.save('financial-report.xlsx')
print("✓ Financial report created!")

Example 2: Sales Dashboard with pandas

import pandas as pd
import numpy as np

# Create sample sales data
np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=100, freq='D')
products = ['Product A', 'Product B', 'Product C']
regions = ['North', 'South', 'East', 'West']

data = {
    'Date': np.random.choice(dates, 100),
    'Product': np.random.choice(products, 100),
    'Region': np.random.choice(regions, 100),
    'Units': np.random.randint(1, 100, 100),
    'Price': np.random.uniform(10, 100, 100)
}

df = pd.DataFrame(data)
df['Revenue'] = df['Units'] * df['Price']

# Analysis
summary = df.groupby('Product').agg({
    'Units': 'sum',
    'Revenue': 'sum'
}).round(2)

region_summary = df.groupby('Region').agg({
    'Units': 'sum',
    'Revenue': ['sum', 'mean']
}).round(2)

# Write to Excel with multiple sheets
with pd.ExcelWriter('sales-dashboard.xlsx', engine='openpyxl') as writer:
    # Raw data
    df.to_excel(writer, sheet_name='Raw Data', index=False)
    
    # Product summary
    summary.to_excel(writer, sheet_name='Product Summary')
    
    # Region summary
    region_summary.to_excel(writer, sheet_name='Region Summary')
    
    # Pivot table
    pivot = pd.pivot_table(df, values='Revenue', index='Product', 
                          columns='Region', aggfunc='sum')
    pivot.to_excel(writer, sheet_name='Pivot Table')

print("✓ Sales dashboard created!")

Example 3: Invoice Generator

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from datetime import datetime

def create_invoice(invoice_num, client_name, items, output_file):
    """
    items: list of dicts with 'description', 'quantity', 'unit_price'
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "Invoice"
    
    # Styles
    title_font = Font(bold=True, size=18)
    header_font = Font(bold=True, size=11)
    border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # Header
    ws['A1'] = "INVOICE"
    ws['A1'].font = title_font
    ws.merge_cells('A1:C1')
    
    # Invoice details
    ws['A3'] = f"Invoice #: {invoice_num}"
    ws['A4'] = f"Date: {datetime.now().strftime('%Y-%m-%d')}"
    ws['A6'] = f"Bill To: {client_name}"
    
    # Table headers
    headers = ['Description', 'Quantity', 'Unit Price', 'Amount']
    start_row = 8
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=start_row, column=col, value=header)
        cell.font = header_font
        cell.border = border
    
    # Items
    row = start_row + 1
    for item in items:
        ws.cell(row=row, column=1, value=item['description'])
        ws.cell(row=row, column=2, value=item['quantity'])
        ws.cell(row=row, column=3, value=item['unit_price'])
        amount = item['quantity'] * item['unit_price']
        ws.cell(row=row, column=4, value=amount)
        
        for col in range(1, 5):
            ws.cell(row=row, column=col).border = border
        row += 1
    
    # Total row
    total_row = row
    ws.cell(row=total_row, column=3, value="Total:")
    ws.cell(row=total_row, column=3).font = Font(bold=True)
    total_formula = f"=SUM(D{start_row+1}:D{total_row-1})"
    ws.cell(row=total_row, column=4, value=total_formula)
    ws.cell(row=total_row, column=4).font = Font(bold=True)
    ws.cell(row=total_row, column=4).border = border
    
    # Column widths
    ws.column_dimensions['A'].width = 40
    ws.column_dimensions['B'].width = 12
    ws.column_dimensions['C'].width = 15
    ws.column_dimensions['D'].width = 15
    
    wb.save(output_file)
    print(f"✓ Invoice created: {output_file}")

# Usage
items = [
    {'description': 'Web Development', 'quantity': 40, 'unit_price': 100},
    {'description': 'Design', 'quantity': 20, 'unit_price': 80},
    {'description': 'Consulting', 'quantity': 10, 'unit_price': 150}
]
create_invoice("INV-2024-001", "ABC Corporation", items, "invoice.xlsx")

Example 4: Data Cleaning Pipeline

import pandas as pd
from openpyxl import load_workbook

def clean_and_export(input_file, output_file):
    """Clean messy Excel data and export formatted version"""
    
    # Read raw data
    df = pd.read_excel(input_file)
    
    # Cleaning operations
    # 1. Remove duplicates
    df = df.drop_duplicates()
    
    # 2. Handle missing values
    df = df.fillna('N/A')
    
    # 3. Standardize text
    text_cols = df.select_dtypes(include=['object']).columns
    for col in text_cols:
        df[col] = df[col].str.strip().str.title()
    
    # 4. Remove invalid rows
    df = df[df['Status'] != 'Cancelled']  # Example filter
    
    # 5. Add calculated columns
    if 'Quantity' in df.columns and 'Price' in df.columns:
        df['Total'] = df['Quantity'] * df['Price']
    
    # Export with formatting
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='Cleaned Data', index=False)
        
        # Format the output
        wb = writer.book
        ws = writer.sheets['Cleaned Data']
        
        # Header formatting
        for cell in ws[1]:
            cell.font = Font(bold=True)
            cell.fill = PatternFill('solid', start_color='4472C4')
            cell.font = Font(color='FFFFFF', bold=True)
        
        # Auto-adjust column widths
        for column in ws.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[column_letter].width = adjusted_width
    
    print(f"✓ Cleaned data exported to {output_file}")
    return df

# Usage
# clean_and_export('raw_data.xlsx', 'cleaned_data.xlsx')

Financial Modeling Best Practices

Color Coding Standards

Implementation

from openpyxl.styles import Font

# Blue for inputs
input_font = Font(color='0000FF')
ws['B2'].font = input_font  # Hardcoded value

# Black for formulas (default)
ws['B3'] = '=B2*1.1'  # Formula stays black

# Green for internal references
# (When linking between sheets)
ws['Sheet2!B2'] = '=Sheet1!B2'

Number Formatting

# Currency
ws['A1'].number_format = '$#,##0'

# Currency with thousands separator
ws['A2'].number_format = '$#,##0;($#,##0);-'

# Percentage with one decimal
ws['A3'].number_format = '0.0%'

# Date
ws['A4'].number_format = 'YYYY-MM-DD'

# Thousands with K suffix
ws['A5'].number_format = '#,##0,"K"'

# Millions with M suffix
ws['A6'].number_format = '#,##0,,"M"'

Error Handling

Common Errors

Error: "Formula not calculating"

# Solution: Recalculate after saving
wb.save('file.xlsx')

# Then use LibreOffice to recalculate
# python scripts/recalc.py file.xlsx

Error: "Chart not displaying"

# Solution: Ensure data range is correct
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)  # Check row numbers

Error: "File corrupted"

# Solution: Use data_only=True when reading
wb = load_workbook('file.xlsx', data_only=True)

Testing Your Setup

# test-xlsx.py
import pandas as pd
from openpyxl import Workbook

print("Testing xlsx setup...")

# Test 1: pandas read/write
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df.to_excel('test-output.xlsx', index=False)
df_read = pd.read_excel('test-output.xlsx')
assert len(df_read) == 3
print("✓ pandas test passed")

# Test 2: openpyxl with formula
wb = Workbook()
ws = wb.active
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = '=SUM(A1:A2)'
wb.save('test-formula.xlsx')
print("✓ openpyxl test passed")

print("✓ All tests passed!")

Run test:

python test-xlsx.py

License

MIT License - See LICENSE file for details.