Wrong column width when Excel.save()

[hieugia312]

When load an excel file from rootBundle and then save it in another directory, the width of the column in the output file doesn't match with the original input file.

The function I used:

Future<void> test() async {
    //Get excel file from assets
    const filePath = "assets/test_input.xlsx";
    final data = await rootBundle.load(filePath);
    List<int> bytes =
        data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    Excel excel =  Excel.decodeBytes(bytes);
    //Save the file in Downloads Directory
    final appDir = await getDownloadsDirectory();
    final fileBytes = excel.save();
    if (fileBytes != null) {
      File(join(appDir!.path, 'exported', 'test_output.xlsx'))
        ..createSync(recursive: true)
        ..writeAsBytesSync(fileBytes);
    }
  }

I have tested and found that the excel.save() is the cause of this behavior:

///copy the file to another folder instead of excel.save()
Future<void> _copyToDownloadFolder(String ogFilePath, File file) async {
    final ByteData data = await rootBundle.load(ogFilePath);
    List<int> bytes =
        data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    File(file.path).writeAsBytesSync(bytes);
  }
///When open the file, the column width doesn't get changed

How can I fix this?

The test files:
test_input.xlsx
test_output.xlsx

Flutter doctor:

Doctor summary (to see all details, run flutter doctor -v):
[!] Flutter (Channel stable, 3.13.4, on Mac OS X 10.15.7 19H15 darwin-x64, locale en)
    ! Warning: `flutter` on your path resolves to /usr/local/Caskroom/flutter/2.2.3/flutter/bin/flutter, which is not inside your current Flutter SDK checkout at /Users/tranthang/fvm/versions/3.13.4. Consider adding /Users/tranthang/fvm/versions/3.13.4/bin to the front of your path.
    ! Warning: `dart` on your path resolves to /usr/local/Caskroom/flutter/2.2.3/flutter/bin/dart, which is not inside your current Flutter SDK checkout at /Users/tranthang/fvm/versions/3.13.4. Consider adding /Users/tranthang/fvm/versions/3.13.4/bin to the front of your path.
[✓] Android toolchain - develop for Android devices (Android SDK version 31.0.0)
[!] Xcode - develop for iOS and macOS (Xcode 12.4)
    ✗ Flutter requires Xcode 14 or higher.
      Download the latest version or update via the Mac App Store.
[✓] Chrome - develop for the web
[✓] Android Studio (version 2020.3)
[✓] VS Code (version 1.83.1)
[✓] Connected device (2 available)
[✓] Network resources

Tested on an android physical device with the latest version of Microsoft Excel on Google Play Store.

[FauconSpartiate]

Reproducible on main.

The issue is not in excel.save(). The width is not parsed in the first place in the document.

Confirmed with this code:

  const path = "test_input.xlsx";
  final data = File(path).readAsBytesSync();
  Excel excel = Excel.decodeBytes(data);
  print(excel.sheets['Sheet1']!.cell(CellIndex.indexByColumnRow(columnIndex: 0, rowIndex: 0)).value); //VERY LONG CENTERED BOLD TEXT
  print(excel.sheets['Sheet1']!.getColumnWidths[0]); //null
  print(excel.sheets['Sheet1']!.getColumnWidths[1]); //null
  final fileBytes = excel.save();
  if (fileBytes != null) {
    File("test_output.xlsx").writeAsBytesSync(fileBytes);
  }

[hieugia312]

So, how can I parse the width in the document? I really need to keep the width of the original file though. And I doesn't mean one column only but the width of all other column in the file.

[FauconSpartiate]

This is a bug in this package. If you're motivated, you can go take a look and try to fix it.
There should be something in Excel.decodeBytes().

[nirranthea]

Hi, I want to report similar issue. The difference is I don't load from an excel file but exporting value from db to excel.

When I'm using version 2.1.0, the saved excel file have correct columns width.
But starting from version 3.0.0 & 4.0.0, the columns width are wrong.

I have set the column width for index 5,6,7 (F, G, H), and for the rest of columns are set by AutoFit.
I have used getColumnWidth for debug, all version resulting similar correct width.

The correct one

The wrong one

Tested using Flutter 3.13.9 & Flutter 3.16.1

[DavidPoetsch]

Hi, as i needed this bug to be fixed i came up with this solution.
As mentioned previously the problem will be in Excel.decodeBytes().
I found no part where the column widths and row heights get parsed.
So i changed the Parser class (parse.dart) to the following.

I created this function:

void _parseTableWidthsAndHeights(XmlElement worksheet, Sheet sheetObject) {
    /* parse default column width and default row height
      example XML content
      <sheetFormatPr baseColWidth="10" defaultColWidth="26.33203125" defaultRowHeight="13" x14ac:dyDescent="0.15" />
    */
    Iterable<XmlElement> results;
    results = worksheet.findAllElements("sheetFormatPr");
    if (results.isNotEmpty) {
      results.forEach((element) {
        double? defaultColWidth;
        double? defaultRowHeight;
        // default column width
        String? widthAttribute = element.getAttribute("defaultColWidth");
        if (widthAttribute != null) {
          defaultColWidth = double.tryParse(widthAttribute);
        }
        // default row height
        String? rowHeightAttribute = element.getAttribute("defaultRowHeight");
        if (rowHeightAttribute != null) {
          defaultRowHeight = double.tryParse(rowHeightAttribute);
        }

        // both values valid ?
        if (defaultColWidth != null && defaultRowHeight != null) {
          print("defaultColWidth $defaultColWidth");
          print("defaultRowHeight $defaultRowHeight");
          sheetObject._defaultColumnWidth = defaultColWidth;
          sheetObject._defaultRowHeight = defaultRowHeight;
        }
      });
    }

    /* parse custom column height
      example XML content
      <col min="2" max="2" width="71.83203125" customWidth="1"/>, 
      <col min="4" max="4" width="26.5" customWidth="1"/>, 
      <col min="6" max="6" width="31.33203125" customWidth="1"/>
    */
    results = worksheet.findAllElements("col");
    if (results.isNotEmpty) {
      results.forEach((element) {
        String? colAttribute = element.getAttribute("min"); // i think min refers to the column
        String? widthAttribute = element.getAttribute("width");
        if (colAttribute != null && widthAttribute != null) {
          int? col = int.tryParse(colAttribute);
          double? width = double.tryParse(widthAttribute);
          print("custom colum width ${col.toString()} ${width.toString()}");
          if (col != null && width != null) {
            col -= 1; // first col in _columnWidths is index 0
            if (col >= 0) {
              sheetObject._columnWidths[col] = width;
            }
          }
        }
      });
    }

    /* parse custom row height
      example XML content
      <row r="1" spans="1:2" ht="44" customHeight="1" x14ac:dyDescent="0.15">
    */
    results = worksheet.findAllElements("row");
    if (results.isNotEmpty) {
      results.forEach((element) {
        String? rowAttribute = element.getAttribute("r"); // i think min refers to the column
        String? heightAttribute = element.getAttribute("ht");
        if (rowAttribute != null && heightAttribute != null) {
          int? row = int.tryParse(rowAttribute);
          double? height = double.tryParse(heightAttribute);
          print("custom row height ${row.toString()} ${height.toString()}");
          if (row != null && height != null) {
            row -= 1; // first col in _rowHeights is index 0
            if (row >= 0) {
              sheetObject._rowHeights[row] = height;
            }
          }
        }
      });
    }
  }

and i called the function in the _parseTable function right after _parseHeaderFooter:

    _parseHeaderFooter(worksheet, sheetObject);
    _parseTableWidthsAndHeights(worksheet, sheetObject); // added this line

[FauconSpartiate]

@DavidPoetsch You are welcome to open a PR with your proposal to get the the change merged.
Don't forget to also add some automated tests.

[DavidPoetsch]

To be honest i have no experience with GitHub.
But i am welcome to help if you could explain this in more detail, as i have no glue what you are talking about. 😄

EDIT: I guess i understood the first part and created a PR
https://github.com/justkawal/excel/pull/347#issue-2255225517