Memory Issue with workbook.saveAsStream() in syncfusion_flutter_xlsio for Excel Files

Question

Hi everyone,I have shared a sample code for you reference. I'm generating large Excel files using the Syncfusion XlsIO package in Flutter. In my code, the generateListData function creates random data with 200 columns and 49,000 rows.

I also tried generating 100 columns, but it severely hung my windows machine.  when I call:

final List<int> finalBytes = workbook.saveAsStream();

it consumes a huge amount of memory and causes my system to hang. Interestingly, I was able to successfully export 100,000 rows with 50 columns, but increasing the number of columns significantly raises memory consumption.

Is there any workaround or optimization to reduce memory usage when saving large Excel files? I'd appreciate any suggestions or best practices to handle datasets more efficiently. This is my flutter doctor summaryDoctor summary (to see all details, run flutter doctor -v):[√] Flutter (Channel stable, 3.24.3, on Microsoft Windows [Version 10.0.19045.5487], locale en-IN)[√] Windows Version (Installed version of Windows is version 10 or higher)[X] Android toolchain - develop for Android devices    X Unable to locate Android SDK.      Install Android Studio from: https://developer.android.com/studio/index.html       On first launch it will assist you in installing the Android SDK components.      (or visit https://flutter.dev/to/windows-android-setup for detailed instructions).      If the Android SDK has been installed to a custom location, please use      `flutter config --android-sdk` to update to that location.[√] Chrome - develop for the web[√] Visual Studio - develop Windows apps (Visual Studio Community 2022 17.10.5)[!] Android Studio (not installed)[√] VS Code (version 1.97.2)[√] Connected device (3 available)[√] Network resourcesThanks in advance!SrimathiAttachment: lib_3b188e6e.zip

Karthika Santhana Krishnan · Answer

Hi Srimathi,We are validating the scenario and we will provide the details on February 25, 2025.Regards,Karthika S.

srimathi · Answer

Thank you for the update. I appreciate you letting me know. I look forward to receiving the details.

Surah Al Waqiah · Answer

Generating large Excel files can be memory-intensive. Try using workbook.dispose() after saving to free up memory. Also, consider writing data in chunks instead of loading everything at once. Using CSV for extremely large datasets might be a more efficient alternative. Hope this helps!

srimathi · Answer

Thanks for the reply Surah. If you see my code, I've already implemented workbook.dispose() at the end of my process. Here's my sample code:

void _createExcel(Map<String, dynamic> args) async {

  debugPrint('Started Excel isolation ${DateTime.now()}');
  List<dynamic> headers = args['headers'];
  List<List<dynamic>> rowsData = args['rowsData'];

  SendPort sendPort = args['sendPort'];

  final excel.Workbook workbook = excel.Workbook();
  final excel.Worksheet sheet = workbook.worksheets[0];

  sheet.name = 'Data';

  // Write headers
  for (int i = 0; i < headers.length; i++) {
    sheet.getRangeByIndex(1, i + 1).setText(headers[i]);
  }

  const int chunkSize = 50000; // Define chunk size

  for (int startRow = 0; startRow < rowsData.length; startRow += chunkSize) {
    int endRow = (startRow + chunkSize).clamp(0, rowsData.length);
    for (int i = startRow; i < endRow; i++) {
      for (int j = 0; j < rowsData[i].length; j++) {
        sheet.getRangeByIndex(i + 2, j + 1).setText(rowsData[i][j].toString());
      }
    }
    debugPrint('Chunk from row $startRow to $endRow written ${DateTime.now()}');
    // Convert estimated size to megabytes
  }

  // Force garbage collection
  await Future.delayed(Duration(milliseconds: 100));

  debugPrint('Save the final workbook ${DateTime.now()}');
  // Save the final workbook

  final List<int> finalBytes = workbook.saveAsStream();

  debugPrint('finalBytes ${DateTime.now()}');

  workbook.dispose();

  debugPrint('disposed ${DateTime.now()}');
  // Send combined bytes to the main thread
  sendPort.send(finalBytes);

  //sendPort.send('TERMINATE');
  debugPrint('Bytes sent to main thread ${DateTime.now()}');
}

How can I write the data in chunks for this code: final List<int> finalBytes = workbook.saveAsStream()? Could you guide me, please?

Mohan Chandran · Answer

Hi Srimathi,Flutter XlsIO maintains cell data in a collection, so memory usage increases with the number of cells. Since you're exporting 100,000 rows × 50 columns (5 million cells), XlsIO requires approximately 6 GB of RAM and 3.5 minutes to save the Excel document in a Windows application.To optimize performance, we recommend splitting the data and exporting it across multiple Excel documents. Writing data in chunks to a single document is not feasible, as XlsIO does not support modifying an existing file—it only supports creating new Excel documents.Let us know if you need further assistance.Best regards,Mohan

srimathi · Answer

Hi Mohan,Thank you for the detailed explanation.