2021-06-06

import 'dart:io';
import 'package:dl_get_mac_teacher/sql.dart';
import 'package:dl_get_mac_teacher/student_model.dart';
import 'package:path_provider/path_provider.dart';

import 'package:path/path.dart';
import 'package:spreadsheet_decoder/spreadsheet_decoder.dart';

class ExcelManager {
  void inputExcel(String filePath) async {
    var bytes = File(filePath).readAsBytesSync();
    var decoder = SpreadsheetDecoder.decodeBytes(bytes, update: true);
    for (var table in decoder.tables.keys) {
      for (var row in decoder.tables[table]?.rows) {
        for (var item in row) {
          StudentModel model = StudentModel(
            name: item[0],
            studentCode: item[1],
            classCode: item[2],
            macAddress: item[3],
            signinCount: 0,
            unsigninCount: 0,
            delayCount: 0,
            offCount: 0,
            signinRecords: [],
            signinTimes: [],
          );
          await SQLManager().insertStudent(model);
        }
      }
    }
  }

  Future outputExcel() async {
    var fileAll = '/storage/emulated/0/documents/all.xlsx';

    List<StudentModel> students = await SQLManager().students();
    var bytes = File(fileAll).readAsBytesSync();
    var decoder = SpreadsheetDecoder.decodeBytes(bytes, update: true);

    var sheet = decoder.tables.keys.first;
    int i = 0;
    for (var student in students) {
      decoder
        ..insertRow(sheet, i)
        ..insertColumn(sheet, i)
        ..updateCell(sheet, i, 0, student.name)
        ..insertColumn(sheet, 1)
        ..updateCell(sheet, i, 1, student.studentCode)
        ..insertColumn(sheet, 2)
        ..updateCell(sheet, i, 2, student.classCode)
        ..insertColumn(sheet, 3)
        ..updateCell(sheet, i, 3, student.signinCount)
        ..insertColumn(sheet, 4)
        ..updateCell(sheet, i, 4, student.unsigninCount)
        ..insertColumn(sheet, 5)
        ..updateCell(sheet, i, 5, student.delayCount)
        ..insertColumn(sheet, 6)
        ..updateCell(sheet, i, 6, student.offCount);
      i += 1;
    }

    File(join('/storage/emulated/0/documents/signin_teacher/all.xlsx'))
      ..createSync(recursive: true)
      ..writeAsBytesSync(decoder.encode());
  }
}