worksheet 2.3.0

worksheet: ^2.3.0 copied to clipboard

example/main.dart

import 'dart:math' as math;

import 'package:flutter/material.dart' hide BorderStyle;
import 'package:worksheet/worksheet.dart';

void main() {
  runApp(const WorksheetExampleApp());
}

/// Converts a slider value (0.0 to 1.0) to a zoom level (0.1 to 4.0).
///
/// Uses a non-linear scale where:
/// - 0.0 → 10% zoom (0.1)
/// - 0.5 → 100% zoom (1.0)
/// - 1.0 → 400% zoom (4.0)
double sliderToZoom(double sliderValue) {
  if (sliderValue <= 0.5) {
    // Left half: exponential from 0.1 to 1.0
    // zoom = 0.1 * 10^(sliderValue * 2)
    return 0.1 * math.pow(10, sliderValue * 2);
  } else {
    // Right half: exponential from 1.0 to 4.0
    // zoom = 4^(2 * sliderValue - 1)
    return math.pow(4, 2 * sliderValue - 1).toDouble();
  }
}

/// Converts a zoom level (0.1 to 4.0) to a slider value (0.0 to 1.0).
///
/// Inverse of [sliderToZoom].
double zoomToSlider(double zoom) {
  if (zoom <= 1.0) {
    // Left half: zoom = 0.1 * 10^(slider * 2)
    // slider = log10(zoom / 0.1) / 2 = log10(zoom * 10) / 2
    return (math.log(zoom * 10) / math.ln10) / 2;
  } else {
    // Right half: zoom = 4^(2 * slider - 1)
    // slider = (log4(zoom) + 1) / 2
    return ((math.log(zoom) / math.log(4)) + 1) / 2;
  }
}

class WorksheetExampleApp extends StatelessWidget {
  const WorksheetExampleApp({super.key});

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Worksheet Example',
      theme: ThemeData(
        colorScheme: ColorScheme.fromSeed(seedColor: Colors.blue),
        useMaterial3: true,
      ),
      home: const WorksheetExample(),
    );
  }
}

class WorksheetExample extends StatefulWidget {
  const WorksheetExample({super.key});

  @override
  State<WorksheetExample> createState() => _WorksheetExampleState();
}

class _WorksheetExampleState extends State<WorksheetExample> {
  // Modern Excel dimensions (Excel 2007+)
  static const int _rowCount = 1048576; // 2^20 rows
  static const int _columnCount = 16384; // 2^14 columns (A to XFD)

  // Excel default sizes (approximately)
  static const double _defaultRowHeight =
      20.0; // Excel default ~15 points = ~20 pixels
  static const double _defaultColumnWidth =
      64.0; // Excel default 8.43 characters ≈ 64 pixels

  late final SparseWorksheetData _data;
  late final WorksheetController _controller;
  late final EditController _editController;

  @override
  void initState() {
    super.initState();

    _data = SparseWorksheetData(rowCount: _rowCount, columnCount: _columnCount);
    _populateSampleData();

    _controller = WorksheetController();
    _editController = EditController();
  }

  void _populateSampleData() {
    final random = math.Random(42); // Fixed seed for reproducibility

    // === Sheet 1: Sales Data (realistic business spreadsheet) ===

    // Header row with formatting
    final headers = [
      'ID',
      'Date',
      'Customer',
      'Region',
      'Product',
      'Category',
      'Quantity',
      'Unit Price',
      'Total',
      'Discount',
      'Net Total',
      'Status',
      'Sales Rep',
      'Notes',
    ];

    // Header style with thick bottom border
    const headerStyle = CellStyle(
      backgroundColor: Color(0xFF4472C4),
      textColor: Color(0xFFFFFFFF),
      fontWeight: FontWeight.bold,
      textAlignment: CellTextAlignment.center,
      borders: CellBorders(
        bottom: BorderStyle(
          width: 2.0,
          color: Color(0xFF2E5A94),
          lineStyle: BorderLineStyle.solid,
        ),
      ),
    );

    for (var col = 0; col < headers.length; col++) {
      _data.setCell(CellCoordinate(0, col), CellValue.text(headers[col]));
      _data.setStyle(
        const CellCoordinate(0, 0).copyWith(column: col),
        headerStyle,
      );
    }

    // Sample data arrays
    final customers = [
      'Acme Corp',
      'TechStart Inc',
      'Global Industries',
      'Smith & Co',
      'Johnson LLC',
      'Pacific Trading',
      'Atlantic Imports',
      'Central Services',
      'Northern Supplies',
      'Southern Distribution',
      'Eastern Partners',
      'Western Logistics',
      'Metro Solutions',
      'Urban Enterprises',
      'Rural Products',
      'Coastal Goods',
    ];

    final regions = ['North', 'South', 'East', 'West', 'Central'];

    final products = [
      'Widget A',
      'Widget B',
      'Gadget X',
      'Gadget Y',
      'Tool Pro',
      'Tool Basic',
      'Device Alpha',
      'Device Beta',
      'Component 1',
      'Component 2',
      'Assembly Kit',
      'Repair Kit',
      'Starter Pack',
      'Premium Pack',
      'Enterprise Suite',
    ];

    final categories = [
      'Electronics',
      'Hardware',
      'Software',
      'Services',
      'Accessories',
    ];

    final statuses = [
      'Completed',
      'Pending',
      'Shipped',
      'Processing',
      'Cancelled',
    ];

    final salesReps = [
      'Alice Johnson',
      'Bob Smith',
      'Carol White',
      'David Brown',
      'Emma Davis',
      'Frank Wilson',
      'Grace Lee',
      'Henry Taylor',
    ];

    // Number styles
    const currencyStyle = CellStyle(textAlignment: CellTextAlignment.right);

    const numberStyle = CellStyle(textAlignment: CellTextAlignment.right);

    // Alternating row colors
    const evenRowStyle = CellStyle(backgroundColor: Color(0xFFF2F2F2));

    // Generate 50,000 rows of sales data (simulating a large dataset)
    final baseDate = DateTime(2024, 1, 1);

    for (var row = 1; row <= 50000; row++) {
      final date = baseDate.add(Duration(days: random.nextInt(365)));
      final customer = customers[random.nextInt(customers.length)];
      final region = regions[random.nextInt(regions.length)];
      final product = products[random.nextInt(products.length)];
      final category = categories[random.nextInt(categories.length)];
      final quantity = random.nextInt(100) + 1;
      final unitPrice = (random.nextDouble() * 500 + 10).roundToDouble();
      final total = quantity * unitPrice;
      final discountPercent = random.nextInt(20);
      final discount = total * discountPercent / 100;
      final netTotal = total - discount;
      final status = statuses[random.nextInt(statuses.length)];
      final salesRep = salesReps[random.nextInt(salesReps.length)];

      // Set cell values
      _data.setCell(CellCoordinate(row, 0), CellValue.number(row.toDouble()));
      _data.setCell(
        CellCoordinate(row, 1),
        CellValue.text(
          '${date.year}-${date.month.toString().padLeft(2, '0')}-${date.day.toString().padLeft(2, '0')}',
        ),
      );
      _data.setCell(CellCoordinate(row, 2), CellValue.text(customer));
      _data.setCell(CellCoordinate(row, 3), CellValue.text(region));
      _data.setCell(CellCoordinate(row, 4), CellValue.text(product));
      _data.setCell(CellCoordinate(row, 5), CellValue.text(category));
      _data.setCell(
        CellCoordinate(row, 6),
        CellValue.number(quantity.toDouble()),
      );
      _data.setCell(CellCoordinate(row, 7), CellValue.number(unitPrice));
      _data.setCell(CellCoordinate(row, 8), CellValue.number(total));
      _data.setCell(
        CellCoordinate(row, 9),
        CellValue.text('$discountPercent%'),
      );
      _data.setCell(CellCoordinate(row, 10), CellValue.number(netTotal));
      _data.setCell(CellCoordinate(row, 11), CellValue.text(status));
      _data.setCell(CellCoordinate(row, 12), CellValue.text(salesRep));

      // Add occasional notes
      if (random.nextInt(10) == 0) {
        _data.setCell(
          CellCoordinate(row, 13),
          CellValue.text('Follow up required'),
        );
      }

      // Apply alternating row style
      if (row.isEven) {
        for (var col = 0; col < headers.length; col++) {
          _data.setStyle(CellCoordinate(row, col), evenRowStyle);
        }
      }

      // Apply number alignment
      _data.setStyle(CellCoordinate(row, 0), numberStyle);
      _data.setStyle(CellCoordinate(row, 6), numberStyle);
      _data.setStyle(CellCoordinate(row, 7), currencyStyle);
      _data.setStyle(CellCoordinate(row, 8), currencyStyle);
      _data.setStyle(CellCoordinate(row, 10), currencyStyle);

      // Highlight cancelled orders in red
      if (status == 'Cancelled') {
        _data.setStyle(
          CellCoordinate(row, 11),
          const CellStyle(
            textColor: Color(0xFFCC0000),
            fontWeight: FontWeight.bold,
          ),
        );
      }
    }

    // === Add summary section ===
    const summaryStartRow = 50002;

    _data.setCell(
      const CellCoordinate(summaryStartRow, 0),
      CellValue.text('SUMMARY'),
    );
    _data.setStyle(
      const CellCoordinate(summaryStartRow, 0),
      const CellStyle(
        fontWeight: FontWeight.bold,
        fontSize: 14,
        borders: CellBorders(
          top: BorderStyle(width: 2.0, lineStyle: BorderLineStyle.double),
        ),
      ),
    );

    // Add dashed border to a few cells for demonstration
    for (var col = 1; col < 5; col++) {
      _data.setStyle(
        CellCoordinate(summaryStartRow, col),
        const CellStyle(
          borders: CellBorders(
            top: BorderStyle(
              width: 1.0,
              lineStyle: BorderLineStyle.dashed,
              color: Color(0xFF888888),
            ),
          ),
        ),
      );
    }

    _data.setCell(
      const CellCoordinate(summaryStartRow + 1, 0),
      CellValue.text('Total Records:'),
    );
    _data.setCell(
      const CellCoordinate(summaryStartRow + 1, 1),
      CellValue.number(50000),
    );

    _data.setCell(
      const CellCoordinate(summaryStartRow + 2, 0),
      CellValue.text('Report Generated:'),
    );
    _data.setCell(
      const CellCoordinate(summaryStartRow + 2, 1),
      CellValue.text(DateTime.now().toString().substring(0, 19)),
    );

    _data.setCell(
      const CellCoordinate(summaryStartRow + 3, 0),
      CellValue.text('Grid Size:'),
    );
    _data.setCell(
      const CellCoordinate(summaryStartRow + 3, 1),
      CellValue.text('1,048,576 rows × 16,384 columns (XFD)'),
    );

    // === Additional data in columns O onwards (simulating more sheets/data) ===
    // Add a separate "lookup table" starting at column Q (index 16)

    _data.setCell(
      const CellCoordinate(0, 16),
      CellValue.text('PRODUCT CATALOG'),
    );
    _data.setStyle(
      const CellCoordinate(0, 16),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFF70AD47),
        textColor: Color(0xFFFFFFFF),
      ),
    );

    final catalogHeaders = ['Code', 'Name', 'Base Price', 'In Stock'];
    for (var col = 0; col < catalogHeaders.length; col++) {
      _data.setCell(
        CellCoordinate(1, 16 + col),
        CellValue.text(catalogHeaders[col]),
      );
      _data.setStyle(
        CellCoordinate(1, 16 + col),
        const CellStyle(
          backgroundColor: Color(0xFFE2EFDA),
          fontWeight: FontWeight.bold,
        ),
      );
    }

    for (var i = 0; i < products.length; i++) {
      _data.setCell(
        CellCoordinate(2 + i, 16),
        CellValue.text('PRD-${(i + 1).toString().padLeft(3, '0')}'),
      );
      _data.setCell(CellCoordinate(2 + i, 17), CellValue.text(products[i]));
      _data.setCell(
        CellCoordinate(2 + i, 18),
        CellValue.number((random.nextDouble() * 400 + 50).roundToDouble()),
      );
      _data.setCell(
        CellCoordinate(2 + i, 19),
        CellValue.number((random.nextInt(1000) + 50).toDouble()),
      );
    }

    // === Data at far corners to test large grid navigation ===

    // Data at row 100,000
    _data.setCell(
      const CellCoordinate(100000, 0),
      CellValue.text('DATA AT ROW 100,001'),
    );
    _data.setStyle(
      const CellCoordinate(100000, 0),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFFFFEB9C),
      ),
    );
    for (var col = 1; col < 10; col++) {
      _data.setCell(
        CellCoordinate(100000, col),
        CellValue.number((random.nextDouble() * 1000).roundToDouble()),
      );
    }

    // Data at row 500,000
    _data.setCell(
      const CellCoordinate(500000, 0),
      CellValue.text('DATA AT ROW 500,001'),
    );
    _data.setStyle(
      const CellCoordinate(500000, 0),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFFFFEB9C),
      ),
    );
    for (var col = 1; col < 10; col++) {
      _data.setCell(
        CellCoordinate(500000, col),
        CellValue.number((random.nextDouble() * 1000).roundToDouble()),
      );
    }

    // Data at the last row (1,048,575)
    _data.setCell(
      const CellCoordinate(1048575, 0),
      CellValue.text('LAST ROW (1,048,576)'),
    );
    _data.setStyle(
      const CellCoordinate(1048575, 0),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFFFF6B6B),
        textColor: Color(0xFFFFFFFF),
      ),
    );

    // Data at column 1000 (ALM)
    _data.setCell(
      const CellCoordinate(0, 1000),
      CellValue.text('COLUMN 1001 (ALM)'),
    );
    _data.setStyle(
      const CellCoordinate(0, 1000),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFF9B59B6),
        textColor: Color(0xFFFFFFFF),
      ),
    );
    for (var row = 1; row <= 100; row++) {
      _data.setCell(
        CellCoordinate(row, 1000),
        CellValue.number((random.nextDouble() * 500).roundToDouble()),
      );
    }

    // Data at column 10000 (NTQ)
    _data.setCell(
      const CellCoordinate(0, 10000),
      CellValue.text('COLUMN 10001 (NTQ)'),
    );
    _data.setStyle(
      const CellCoordinate(0, 10000),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFF3498DB),
        textColor: Color(0xFFFFFFFF),
      ),
    );
    for (var row = 1; row <= 50; row++) {
      _data.setCell(
        CellCoordinate(row, 10000),
        CellValue.number((random.nextDouble() * 500).roundToDouble()),
      );
    }

    // Data at last column (16383 = XFD)
    _data.setCell(
      const CellCoordinate(0, 16383),
      CellValue.text('LAST COL (XFD)'),
    );
    _data.setStyle(
      const CellCoordinate(0, 16383),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFFFF6B6B),
        textColor: Color(0xFFFFFFFF),
      ),
    );

    // Corner cell - last row, last column
    _data.setCell(
      const CellCoordinate(1048575, 16383),
      CellValue.text('XFD1048576'),
    );
    _data.setStyle(
      const CellCoordinate(1048575, 16383),
      const CellStyle(
        fontWeight: FontWeight.bold,
        backgroundColor: Color(0xFF2ECC71),
        textColor: Color(0xFFFFFFFF),
      ),
    );
  }

  @override
  void dispose() {
    _controller.dispose();
    _editController.dispose();
    _data.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('Worksheet Demo - Sales Data'),
        actions: [
          // Zoom slider with non-linear scale
          ListenableBuilder(
            listenable: _controller,
            builder: (context, _) => Row(
              mainAxisSize: MainAxisSize.min,
              children: [
                const Text('10%', style: TextStyle(fontSize: 11)),
                SizedBox(
                  width: 180,
                  child: Slider(
                    value: zoomToSlider(_controller.zoom),
                    onChanged: (value) {
                      final zoom = sliderToZoom(value);
                      _controller.setZoom(zoom);
                      setState(() {});
                    },
                    divisions: 100,
                  ),
                ),
                const Text('400%', style: TextStyle(fontSize: 11)),
                const SizedBox(width: 8),
                SizedBox(
                  width: 45,
                  child: Text(
                    '${(_controller.zoom * 100).round()}%',
                    style: const TextStyle(fontWeight: FontWeight.bold),
                  ),
                ),
              ],
            ),
          ),
          IconButton(
            icon: const Icon(Icons.refresh),
            onPressed: () {
              _controller.resetZoom();
              setState(() {});
            },
            tooltip: 'Reset to 100%',
          ),
        ],
      ),
      body: Column(
        children: [
          _buildSelectionInfo(),
          _buildInstructions(),
          Expanded(
            child: WorksheetTheme(
              data: WorksheetThemeData(
                showHeaders: true,
                showGridlines: true,
                defaultRowHeight: _defaultRowHeight,
                defaultColumnWidth: _defaultColumnWidth,
                rowHeaderWidth: 40.0, // Narrower like Excel
                columnHeaderHeight: 20.0, // Shorter like Excel
                fontSize: 11.0, // Smaller font like Excel
              ),
              child: Worksheet(
                data: _data,
                controller: _controller,
                editController: _editController,
                rowCount: _rowCount,
                columnCount: _columnCount,
              ),
            ),
          ),
        ],
      ),
    );
  }

  Widget _buildSelectionInfo() {
    return ListenableBuilder(
      listenable: _controller,
      builder: (context, _) {
        final selection = _controller.selectedRange;
        final focus = _controller.focusCell;
        final cellValue = focus != null ? _data.getCell(focus) : null;

        String text;
        if (selection != null) {
          final start = CellCoordinate(
            selection.startRow,
            selection.startColumn,
          );
          final end = CellCoordinate(selection.endRow, selection.endColumn);
          if (start == end) {
            text = 'Selected: ${start.toNotation()}';
          } else {
            text = 'Selected: ${start.toNotation()}:${end.toNotation()}';
          }
          if (cellValue != null) {
            text += ' = ${cellValue.displayValue}';
          }
        } else {
          text = 'No selection - click a cell to select';
        }

        return Container(
          padding: const EdgeInsets.all(8.0),
          color: Colors.grey[200],
          width: double.infinity,
          child: Text(text, style: const TextStyle(fontSize: 12)),
        );
      },
    );
  }

  Widget _buildInstructions() {
    return Container(
      padding: const EdgeInsets.symmetric(horizontal: 8.0, vertical: 4.0),
      color: Colors.blue[50],
      width: double.infinity,
      child: const Text(
        'Type to edit | Enter/Tab to commit & navigate | Escape to cancel | Double-click or F2 to edit | Drag headers to resize',
        style: TextStyle(fontSize: 11, color: Colors.blueGrey),
      ),
    );
  }
}