Roadbound-BRR/lib/exporters/arriva_brr_exporter.dart

import "dart:convert";
import "dart:typed_data";
import "package:archive/archive.dart";
import "package:excel/excel.dart";
import "package:flutter/services.dart";
import "../models/operations/trip.dart";
import "../models/brr_metadata.dart";
import "brr_exporter.dart";

class ArrivaBRRExporter implements BRRExporter {
  // Numbers xlsx export leaves numFmtId="0" in custom formats which the
  // excel package rejects. Strip it out before decoding.
  List<int> _patchTemplateBytes(List<int> bytes) {
    final archive = ZipDecoder().decodeBytes(bytes);
    final output = Archive();

    for (final file in archive) {
      if (file.name == "xl/styles.xml" && file.isFile) {
        var xml = utf8.decode(file.content as List<int>);

        // strip the whole numFmts block — Numbers export puts built-in IDs
        // in there which the excel package rejects
        xml = xml.replaceAll(
          RegExp(r'<numFmts[^>]*>.*?</numFmts>', dotAll: true),
          "",
        );

        // reset all numFmtId refs in xf elements to 0 (General)
        // so nothing tries to look up the stripped formats
        xml = xml.replaceAll(RegExp(r'numFmtId="\d+"'), 'numFmtId="0"');

        final patched = utf8.encode(xml);
        output.addFile(ArchiveFile(file.name, patched.length, patched));
      } else {
        output.addFile(file);
      }
    }

    return ZipEncoder().encode(output)!;
  }

  static const int _dataStartRow = 8; // row 9 (0-indexed)
  static const int _templateDataRows = 15; // rows 9–23

  @override
  Future<Uint8List> export(List<Trip> trips, BRRMetadata metadata) async {
    final templateBytes = await rootBundle.load("assets/arriva_brr.xlsx");
    final patched = _patchTemplateBytes(templateBytes.buffer.asUint8List());
    final excel = Excel.decodeBytes(patched);

    final sheetName = excel.sheets.keys.first;
    final sheet = excel[sheetName];

    if (trips.length > _templateDataRows) {
      _shiftRowsDown(sheet, trips.length - _templateDataRows);
    }

    _populateData(sheet, trips);

    final bytes = excel.encode();
    if (bytes == null) throw Exception("Failed to encode Excel");
    return Uint8List.fromList(bytes);
  }

  // Shifts all rows from (_dataStartRow + _templateDataRows) onwards down by extraRows
  void _shiftRowsDown(Sheet sheet, int extraRows) {
    final firstRowToShift =
        _dataStartRow + _templateDataRows; // row 24 (index 23)

    // figure out how many rows exist beyond the data block
    final maxRow = sheet.rows.length;

    // copy from the bottom up to avoid overwriting
    for (var r = maxRow - 1; r >= firstRowToShift; r--) {
      final destRow = r + extraRows;
      if (r >= sheet.rows.length) continue;

      final srcRow = sheet.rows[r];
      for (var c = 0; c < srcRow.length; c++) {
        final cell = srcRow[c];
        if (cell == null) continue;

        sheet
            .cell(CellIndex.indexByColumnRow(columnIndex: c, rowIndex: destRow))
            .value = cell
            .value;
        sheet
            .cell(CellIndex.indexByColumnRow(columnIndex: c, rowIndex: destRow))
            .cellStyle = cell
            .cellStyle;
      }
    }

    // clear the original rows that were shifted
    for (var r = firstRowToShift; r < firstRowToShift + extraRows; r++) {
      if (r >= sheet.rows.length) break;
      for (var c = 0; c < 18; c++) {
        sheet
                .cell(CellIndex.indexByColumnRow(columnIndex: c, rowIndex: r))
                .value =
            null;
      }
    }
  }

  void _populateData(Sheet sheet, List<Trip> trips) {
    for (var i = 0; i < trips.length; i++) {
      final trip = trips[i];
      final row = _dataStartRow + i;

      sheet
          .cell(CellIndex.indexByColumnRow(columnIndex: 0, rowIndex: row))
          .value = TextCellValue(
        trip.scheduledTime,
      );
      sheet
          .cell(CellIndex.indexByColumnRow(columnIndex: 1, rowIndex: row))
          .value = TextCellValue(
        trip.tripNumber,
      );

      if (trip.actualDepartureTime != null) {
        sheet
            .cell(CellIndex.indexByColumnRow(columnIndex: 2, rowIndex: row))
            .value = TextCellValue(
          trip.actualDepartureTime!,
        );
      }

      if (trip.actualFleetNumber != null) {
        sheet
            .cell(CellIndex.indexByColumnRow(columnIndex: 3, rowIndex: row))
            .value = TextCellValue(
          trip.actualFleetNumber!,
        );
      }

      sheet
          .cell(CellIndex.indexByColumnRow(columnIndex: 4, rowIndex: row))
          .value = TextCellValue(
        trip.dutyNumber,
      );
      sheet
          .cell(CellIndex.indexByColumnRow(columnIndex: 5, rowIndex: row))
          .value = TextCellValue(
        trip.busWorkNumber,
      );

      final didOperate =
          trip.actualDepartureTime != null && trip.actualFleetNumber != null;
      sheet
          .cell(CellIndex.indexByColumnRow(columnIndex: 6, rowIndex: row))
          .value = TextCellValue(
        didOperate ? "Y" : "N",
      );
    }
  }
}