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 Listbytes = 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 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)
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.
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.
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.
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
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)
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.
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.
Oscar, we will resolve this issue and include the fix in our next weekly NuGet release scheduled for February 28th,2023.
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
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();
}
Sorry, Oscar. We are able to reproduce the reported issue and validating it currently. We will share the validation details on March 3rd, 2023.
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.
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.
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.