import ExcelJS from 'exceljs';

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Sales Report');

// Headers with styling
sheet.columns = [
  { header: 'Product', key: 'product', width: 20 },
  { header: 'Quantity', key: 'qty', width: 12 },
  { header: 'Price', key: 'price', width: 12 },
  { header: 'Total', key: 'total', width: 15 },
];

// Style header row
sheet.getRow(1).font = { bold: true };
sheet.getRow(1).fill = {
  type: 'pattern',
  pattern: 'solid',
  fgColor: { argb: 'FF4472C4' }
};

// Add data
const data = [
  { product: 'Widget A', qty: 100, price: 10 },
  { product: 'Widget B', qty: 50, price: 25 },
];

data.forEach((item, index) => {
  sheet.addRow({
    product: item.product,
    qty: item.qty,
    price: item.price,
    total: { formula: `B${index + 2}*C${index + 2}` }
  });
});

// Add totals row
const lastRow = sheet.rowCount + 1;
sheet.addRow({
  product: 'TOTAL',
  total: { formula: `SUM(D2:D${lastRow - 1})` }
});

// Currency formatting
sheet.getColumn('price').numFmt = '$#,##0.00';
sheet.getColumn('total').numFmt = '$#,##0.00';

await workbook.xlsx.writeFile('report.xlsx');

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = 'Sales Report'

# Headers
headers = ['Product', 'Quantity', 'Price', 'Total']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color='FFFFFF')
    cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

# Data
data = [
    ('Widget A', 100, 10),
    ('Widget B', 50, 25),
    ('Widget C', 75, 15),
]

for row_idx, (product, qty, price) in enumerate(data, 2):
    ws.cell(row=row_idx, column=1, value=product)
    ws.cell(row=row_idx, column=2, value=qty)
    ws.cell(row=row_idx, column=3, value=price)
    ws.cell(row=row_idx, column=4, value=f'=B{row_idx}*C{row_idx}')

# Totals row
total_row = len(data) + 2
ws.cell(row=total_row, column=1, value='TOTAL')
ws.cell(row=total_row, column=4, value=f'=SUM(D2:D{total_row-1})')

# Number formatting
for row in range(2, total_row + 1):
    ws.cell(row=row, column=3).number_format = '$#,##0.00'
    ws.cell(row=row, column=4).number_format = '$#,##0.00'

wb.save('report.xlsx')

import pandas as pd

# Read Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Analysis
summary = df.groupby('Category').agg({
    'Sales': 'sum',
    'Quantity': 'mean'
}).round(2)

# Write to Excel with formatting
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Raw Data', index=False)
    summary.to_excel(writer, sheet_name='Summary')

    # Auto-adjust column widths
    for sheet in writer.sheets.values():
        for column in sheet.columns:
            max_length = max(len(str(cell.value)) for cell in column)
            sheet.column_dimensions[column[0].column_letter].width = max_length + 2

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = 'Sales by Product'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales'

# Data range (assumes column D contains the series and row 1 is headers)
max_row = ws.max_row
data_ref = Reference(ws, min_col=4, min_row=1, max_row=max_row, max_col=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4

ws.add_chart(chart, 'F2')

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill

# Color scale (heatmap)
ws.conditional_formatting.add(
    'D2:D100',
    ColorScaleRule(
        start_type='min', start_color='FF0000',
        end_type='max', end_color='00FF00'
    )
)

# Highlight cells above threshold
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
ws.conditional_formatting.add(
    'D2:D100',
    FormulaRule(formula=['D2>1000'], fill=red_fill)
)

Excel Task: [What do you need?]
    ├─ Create new spreadsheet?
    │   ├─ Simple data export → pandas to_excel()
    │   ├─ Formatted report → exceljs or openpyxl
    │   └─ With charts → openpyxl charts module
    │
    ├─ Read/analyze existing?
    │   ├─ Data analysis → pandas read_excel()
    │   ├─ Preserve formatting → openpyxl load_workbook()
    │   └─ Fast parsing → xlsx (SheetJS)
    │
    ├─ Modify existing?
    │   ├─ Add data → openpyxl (preserves formatting)
    │   └─ Update formulas → openpyxl
    │
    └─ Complex features?
        ├─ Pivot tables → pandas summary tables or xlwings (native pivots)
        ├─ Data validation → openpyxl DataValidation
        └─ Macros → preserve only; use xlwings for Excel automation