We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Cell reference from another worksheet not working within a formula

When I refer a cell from another worksheet (worksheet name contain spaces) within a formula, it will not work if there is any character (except '=') in front of it.

How can I refer a cell/range from worksheet name like 'Sheet 1' within a formula?

Sample code and screenshots below for your reference, please help. Thank you.


Version: syncfusion_flutter_xlsio: ^20.4.38-beta


  @override
Widget build(BuildContext context) {
return Scaffold(body: Center(child: ElevatedButton(onPressed: _createExcel, child: Text('Create Excel'))));
}


Future _createExcel() async {
// Create a new Excel Document.
final Workbook workbook = Workbook(2);


// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];
sheet.name = "Sheet 1";
sheet.getRangeByName('A1').setNumber(100);
sheet.getRangeByName('B1').setFormula("='Sheet 2'!A1");


final Worksheet sheet2 = workbook.worksheets[1];
sheet2.name = "Sheet 2";
sheet2.getRangeByName('A1').setNumber(50);
sheet2.getRangeByName('A2').setFormula("=A1+'Sheet 1'!A1");


final List bytes = workbook.saveAsStream();
workbook.dispose();
//Download the output file in web.
AnchorElement(rel='nofollow' href: "data:application/octet-stream;charset=utf-16le;base64,${base64.encode(bytes)}")
..setAttribute("download", "test.xlsx")
..click();
}

Sheet 1: '=' in front of cell reference, the formula is working as expected.

Sheet 1.jpg

Sheet 2: expect to have formula =A1+'Sheet 1'!A1 , but the formula was removed by Excel because it contains error.

Using other third party software to open that .xlsx file, the cell A2 contain =A1+"Sheet 1"!A1 (single quote became double quote, and the reference is not working as well)

Sheet 2.jpg


14 Replies

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 3, 2023 09:53 AM UTC

Hi Oscar,


We are able to reproduce the reported issue at our end and validating it currently. We will share the validation details in 2 business days, on February 7th,2023.


Regards,

Keerthi.



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 7, 2023 01:43 PM UTC

We have confirmed the issue as Excel document is corrupted while applying formula contains sheet reference and logged a defect report. We will include the fix for this issue in our weekly NuGet release scheduled for February 21st, 2023.


You can track the status of this defect report through below feedback link.

https://www.syncfusion.com/feedback/40911/excel-document-is-corrupted-while-applying-formula-contains-sheet-reference


Disclaimer: Inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 21, 2023 11:41 AM UTC

Oscar, we have included the fix for Excel document is corrupted while applying formula contains sheet reference in our weekly NuGet release version 20.4.51. Kindly upgrade your Syncfusion packages to this latest 20.4.51 version and let us know if the reported issue is resolved.


Latest NuGet Package: https://pub.dev/packages/syncfusion_flutter_xlsio/versions/20.4.51-beta 



OA Oscar Au Yeung February 22, 2023 03:58 AM UTC

I am not able to run my project in debug mode after upgraded to the latest version 20.4.51. Also tried other version, and here is my test result.


My config:

Flutter SDK: 3.3.3

Dart SDK: 2.18.2


syncfusion_flutter_xlsio version:

20.4.51: Unable to run debug mode.

20.4.50: Unable to run debug mode.

20.4.49: Able to run debug mode. As expected, the issue in this post is not fixed.

20.4.38: Able to run debug mode. As expected, the issue in this post is not fixed.


Debug Console shows the following error messages:

Launching lib\main.dart on Chrome in debug mode...
main.dart:1
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:419:54: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
            builder.attribute('algorithmName', sheet._algorithmName);
                                                     ^
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/worksheet/worksheet.dart:1786:11: Context: '_algorithmName' refers to a property so it couldn't be promoted.
See http://dart.dev/go/non-promo-property
  String? _algorithmName;
            ^


/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:902:51: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
                builder.attribute('tooltip', link.screenTip);
                                                  ^
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/hyperlinks/hyperlink.dart:63:11: Context: 'screenTip' refers to a property so it couldn't be promoted.
See http://dart.dev/go/non-promo-property


  String? screenTip;
          ^
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:905:51: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
                builder.attribute('display', link.textToDisplay);


                                                  ^
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/hyperlinks/hyperlink.dart:85:11: Context: 'textToDisplay' refers to a property so it could
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/hyperlinks/hyperlink.dart:85:11: Context: 'textToDisplay' refers to a property so it couldn't be promoted.
See http://dart.dev/go/non-promo-property


  String? textToDisplay;
          ^
/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:991:59: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
                            'tooltip', picture.hyperlink!.screenTip);
                                                          ^


/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:1666:62: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
                'ContentType', _workbook._defaultContentTypes[key]);
                                                             ^


/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:2636:45: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
        builder.attribute('text', condition.text);
                                    ^


/C:/src/flutter/.pub-cache/hosted/pub.dartlang.org/syncfusion_flutter_xlsio-20.4.51-beta/lib/src/xlsio/general/serialize_workbook.dart:3323:57: Error: The argument type 'String?' can't be assigned to the parameter type 'Object' because 'String?' is nullable and 'Object' isn't.
 - 'Object' is from 'dart:core'.
                    builder.attribute('id', dataBarImpl._stGUID);
                                                        ^


Failed to compile application.
Exited (sigterm)


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 23, 2023 11:45 AM UTC

We request you to upgrade your flutter SDK version and check if the issue is resolved. If the issue still persists, kindly share us that SDK version to investigate the query further.



OA Oscar Au Yeung February 24, 2023 01:53 AM UTC

The error still exists after I run "flutter upgrade" to upgrade my flutter SDK to 3.7.5 and Dart SDK to 2.19.2.



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 24, 2023 12:40 PM UTC

Oscar, we will resolve this issue and include the fix in our next weekly NuGet release scheduled for February 28th,2023.



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 28, 2023 12:13 PM UTC

Oscar, we have resolved the issue in our latest version 20.4.52. Kindly upgrade to this latest version and let us know if this helps.


Latest Package: https://pub.dev/packages/syncfusion_flutter_xlsio/versions/20.4.52-beta 



OA Oscar Au Yeung March 1, 2023 03:33 AM UTC

Thanks for the quick update, the latest version fixed the issue mentioned in this thread. However, the sheet reference is not work when I apply it in an Excel function. Similar to the original issue, Excel remove the formula as it contains error.


syncfusion_flutter_xlsio: 20.4.52-beta

Flutter SDK: 3.7.5

Dart SDK: 2.19.2


Sample code below for your reference, please help. Thank you.


Future<void> _createExcel() async {
    // Create a new Excel Document.
    final Workbook workbook = Workbook(2);


    // Accessing sheet via index.
    final Worksheet sheet = workbook.worksheets[0];
    sheet.name = "Sheet 1";
    sheet.getRangeByName('A1').setNumber(1);
    sheet.getRangeByName('B1').setNumber(50);
    sheet.getRangeByName('A2').setNumber(2);
    sheet.getRangeByName('B2').setNumber(60);
    sheet.getRangeByName('A3').setNumber(3);
    sheet.getRangeByName('B3').setNumber(70);


    final Worksheet sheet2 = workbook.worksheets[1];
    sheet2.name = "Sheet 2";
    sheet2.getRangeByName('A1').setNumber(1);
    sheet2.getRangeByName('A2').setFormula("=VLOOKUP(A1, 'Sheet 1'!A1:B3, 2, 0)");


    final List<int> bytes = workbook.saveAsStream();
    workbook.dispose();
    //Download the output file in web.
    AnchorElement(rel='nofollow' href: "data:application/octet-stream;charset=utf-16le;base64,${base64.encode(bytes)}")
      ..setAttribute("download", "test.xlsx")
      ..click();
  }


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team March 1, 2023 12:06 PM UTC

Sorry, Oscar. We are able to reproduce the reported issue and validating it currently. We will share the validation details on March 3rd, 2023.



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team March 3, 2023 01:26 PM UTC

Oscar, we have confirmed the issue as Excel document is corrupted while applying VLOOKUP formula containing a sheet reference and logged a defect report. We will include the fix for this issue in our weekly NuGet release scheduled for March 21st, 2023.


You can track the status of this defect report through below feedback link.

https://www.syncfusion.com/feedback/41745/excel-document-is-corrupted-while-applying-vlookup-formula-containing-a-sheet


Disclaimer: Inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.



RS Ramya Sivakumar Syncfusion Team March 22, 2023 10:34 AM UTC

Oscar, we have planned for the 2023 Volume 1 Main release this week, so there is no Weekly NuGet release this week. We will be having the weekly NuGet release on April first week. We will update you once our weekly NuGet release is published.



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team April 4, 2023 11:20 AM UTC

We deeply regret for the inconvenience, Oscar.

We are facing testing issues for these cases and working on them with high priority. We will include the fix for both cases in our next weekly NuGet release scheduled for April 11th, 2023.

We appreciate your patience.


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team April 11, 2023 12:54 PM UTC

Oscar, we have included the fix for both cases in our latest weekly NuGet release version 21.1.39. Kindly upgrade to this latest version of Syncfusion and let us know if the reported issue is resolved.


Loader.
Up arrow icon