error when reading worksheets, the indexes of the columns read in dart do not match excel, it is as if the columns change order randomly

[insinfo]

error when reading spreadsheets, the indexes of the columns read in dart do not match excel, which makes it impossible to import to the database because there is inconsistency, it is as if the columns change order randomly.

given this worksheet.

AUTO DE INFRACAO e AUTO DE CONSTATACAO.xlsx

the reading result is this: see that the columns do not match

void main(List<String> args) async {

  var spreadsheetFilePath =
      'C:/MyDartProjects/notifis/notifis_backend/db/planilhas/AUTO DE INFRACAO e AUTO DE CONSTATACAO.xlsx';
  var bytes = File(spreadsheetFilePath).readAsBytesSync();
  var excel = Excel.decodeBytes(bytes);
  var worksheetsNames = excel.tables.keys;
  for (var sheetName in worksheetsNames) {
    
    if (sheetName == 'AUTO DE INFRACAO') {
      var sheet = excel.tables[sheetName];
      var sheetRows = sheet.rows;
      var rowsLength = sheetRows.length;

      for (var idx = 0; idx < rowsLength; idx++) {
        var row = sheetRows[idx];
        // print(" ${row.map((e) => '${e?.value}').join(' | ')}");
        print(
            "${row[0]?.value} | ${row[1]?.value} | ${row[2]?.value} | ${row[3]?.value} | ${row[4]?.value} | ${row[5]?.value} | ${row[6]?.value} | ${row[7]?.value} | ${row[8]?.value}");
      
      }
      break;
    }    
  }
  exit(0);
}

the column with the value "ENEL" was supposed to be in index 2 but to be in index 4, there is also a confusion regarding row data appearing in the wrong row and wrong column.

[alexandremcp]

The Lib is experiencing errors when importing the data, shuffling the information displaying in incorrect order.

[ldsouza93]

same problem here

[d3ndesign]

Hi. Will need to look out for this issue, as am hoping to be able to use this library -- so thanks for the heads-up. I think the issue may be related to async/futures, in that some column/row(s) are processed quicker and return faster than previous calls. There may be ways around this? no async expert, but maybe an id could be passed as part of the request, then re-ordered after all columns/rows are returned. More familiar with networking where bytes can be received out of order across the network and then the need to reorder these before presentation levels.

May be wrong about the above, looking at the office open xml spec there are indexes stored on the cell, i.e. 'r="C1"' which may help, if accessible/exposed by the library to reorder content. Maybe that when a user moves columns or rows in the sheet the underlying excel xml does not always reorder the position and only updates the 'r' values.

Personally I think I should study both JustKawals code and the xml spec so I know how to handle issues like the above.

[justkawal]

Sorry for the trouble. Can you please confirm that are you having this trouble in latest version: excel: 2.0.1

[YancyHsu]

Sorry for the trouble. Can you please confirm that are you having this trouble in latest version: excel: 2.0.1

version 2.0.1 has the problem

[DaKar00]

i am working on a project to extract some data from an excel file, when the project was almost done i noticed that some rows had "shifted" the array one step and was giving me the data from the wrong cell. I have tried changeing the data in the excel file, changed the order of the rows but it just wont give me the correct data, at this point i dont know how to proceed. Is there a another package i can try?

[navyzhou926]

I have the same problem. Could not find any solutions

[marcoredz]

same issue

[youssefhegab14]

Same issue

[danielR2001]

same issue, any updates?

[insinfo]

@FauconSpartiate @justkawal @take4blue any updates?

[FauconSpartiate]

Nope, but any help from anyone that is motivated is welcome 👍

[FauconSpartiate]

@YancyHsu @insinfo @danielR2001 @youssefhegab14 @marcoredz @navyzhou926 @DaKar00 @ldsouza93

I managed to reproduce the error with the test file on the original comment, but only on 2.0.0-null-safety-3. On all versions since 2.0.1, the columns seem to be at the right index. Are you sure that you're using the latest version of the package? Are you using the correct version dependency excel: ^2.0.0 in your pubspec.yaml and have executed flutter pub upgrade in your terminal? If yes, could you provide another file where the issue is still reproducible?

[FauconSpartiate]

Closing, as this has been fixed.

[insinfo]

I just did a test with version 3.0.0 and it seems to be working correctly now

import 'dart:io';
import 'package:excel/excel.dart';
import 'package:http/http.dart' as http;

void main(List<String> args) async {
  final url =
      'https://github.com/justkawal/excel/files/8808016/AUTO.DE.INFRACAO.e.AUTO.DE.CONSTATACAO.xlsx';
  final resp = await http.get(Uri.parse(url));

  final bytes = resp.bodyBytes;
  final excel = Excel.decodeBytes(bytes);
  final worksheetsNames = excel.tables.keys;
  for (var sheetName in worksheetsNames) {
    if (sheetName == 'AUTO DE INFRACAO') {
      final sheet = excel.tables[sheetName]!;
      final sheetRows = sheet.rows;
      final rowsLength = sheetRows.length;

      for (var idx = 0; idx < rowsLength; idx++) {
        final row = sheetRows[idx];
        // print(" ${row.map((e) => '${e?.value}').join(' | ')}");
        print(
            '${row[0]?.value} | ${row[1]?.value} | ${row[2]?.value} | ${row[3]?.value} | ${row[4]?.value} | ${row[5]?.value} | ${row[6]?.value} | ${row[7]?.value} | ${row[8]?.value}');
      }
      break;
    }
  }
  exit(0);
}