Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Apr 26, 2022 8:55 am

Hi,

I recently updated Spire.XLS from version 10 to version 12 and experienced a regression concerning the MarkerDesigner.
I am using the most recent release 12.4.1.

I remember the exact same bug to occur in an older version 10 release, which has been fixed later. The fix might even be related to the ticket I opened then (resolved with version 10.4.9):
https://www.e-iceblue.com/forum/populate-using-marker-designer-bug-in-version-10-2-8-t9111.html

The following code reproduces the problem:

Code: Select all
var workbook = new Workbook { Version = ExcelVersion.Version2016 };
var worksheet = workbook.ActiveSheet;

// Create Dummy data Table with values
const string DataTableName = "Data";
const string DataColumnName = "MyColumn1";
var dummyDataTable = new DataTable();
dummyDataTable.Columns.Add(DataColumnName, typeof(int));
for (var i = 1; i <= 5; i++)
{
   var dataRow = dummyDataTable.NewRow();
   dataRow[0] = i;
   dummyDataTable.Rows.Add(dataRow);
}
var expectedSum = 0;
foreach (DataRow row in dummyDataTable.Rows)
{
   expectedSum += (int)row[DataColumnName];
}

// Setup sample formulas using marker desinger syntax in first column and calculated formulas in the other 2, which just reproduce the value from column A via formula (*1)
worksheet.Range[1, 1].Value = "DataColumn";
worksheet.Range[1, 2].Value = "Formula 1";
worksheet.Range[1, 3].Value = "Formula 2";
worksheet.Range[2, 1].Value2 = $"&={DataTableName}.{DataColumnName}(add:styles)";
worksheet.Range[2, 2].Formula = "=A2*1"; // This formula gets copied as it should to each row
worksheet.Range[2, 3].Formula = "=A2*1"; // Expected to be always the same as the formula in column B

worksheet.Range[4, 1].Formula = "=SUM(A2:A3)";
worksheet.Names.Add("SUM_1", worksheet.Range["A4:A4"]);

worksheet.Range[4, 2].Formula = "=SUM(B2:B3)";
worksheet.Names.Add("SUM_2", worksheet.Range["B4:B4"]);

worksheet.Range[4, 3].Formula = "=SUM(C2:C3)";
worksheet.Names.Add("SUM_3", worksheet.Range["C4:C4"]);

// Test marker designer bug
workbook.MarkerDesigner.AddDataTable(DataTableName, dummyDataTable);
workbook.MarkerDesigner.Apply();
workbook.ActiveSheet.ReparseFormula();
workbook.CalculateAllValue();

if (workbook.ActiveSheet.Range[3, 2].Formula != workbook.ActiveSheet.Range[3, 3].Formula) throw new Exception($"Different formulas in copied row");

// Test
foreach (var cellName in new string[] { "SUM_1", "SUM_2", "SUM_3" })
{
   if (worksheet.Range[cellName].CellList[0].FormulaNumberValue != expectedSum) throw new Exception($"Wrong value in '{cellName}'");
}


If you export the resulting excel, all the sum columns (SUM_1, SUM_2, SUM_3) should contain the same sum, but SUM_3 contains a different value, because the formulas in column "C" are wrong (see attached file).
The underlying cause seems to be a bug when populating the additional rows:
* The formulas in the first calculated column "B" are copied as expected.
* The formulas in column "C" are not copied correctly, but somehow shifted by an offset.

This bug forced me to roll back to version 10 and postpone some features which rely on new functionality shipped with version 12. These new features were the primary reason for me to update the license.
As this poses a regression for a working feature of the previous version, I expect the fix to take significantly less time than the 2 months of the previous bug report.

Karl
Attachments
result.zip
Resulting excel from the example code
(7.9 KiB) Downloaded 114 times

karl.pircher@recla.it
 
Posts: 15
Joined: Mon Mar 14, 2016 2:16 pm

Tue Apr 26, 2022 10:26 am

Hello,

Thank you for your inquiry.
I tested your code and reproduced the issue you mentioned. I have logged the issue into our bug tracking system with the ticket number SPIREXLS-3823. Our development team will investigate and fix it. Once it is resolved, I will inform you in time. Sorry for the inconvenience caused.
In addition, I have informed our development team of your situation and asked them to investigate your issue as soon as possible.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Thu May 05, 2022 1:50 am

Hello,

Thanks for your patience.
For issue SPIREXLS-3823, our developers investigated and found that if the code "workbook.ActiveSheet.ReparseFormula()" is removed, the result is correct. Therefore we recommend that you remove this code to avoid the error.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Thu May 05, 2022 9:10 am

Hello Annika,

thank you for the information.
I can confirm that removing the ReparseFormula() statement solves the problem.
With this workaround I can update now to version 12.

However I would still consider this an unintended behavior, which should be fixed in a later version.
I performed some tests using this new information with various versions of Spire and came to the following result:

* OK Version 9.12.20.6046 -> Works with and without ReparseFormula() call as intended
* OK Version 10.9.16 -> Last version from NuGet.org which works in both cases
* BUG 10.11.2.0 -> First version where ReparseFormula() causes the bug.
* BUG 12.4.1.0 -> The bug seems to be present in all 12.* releases up to the currently most recent version 12.4.1

Karl

karl.pircher@recla.it
 
Posts: 15
Joined: Mon Mar 14, 2016 2:16 pm

Thu May 05, 2022 10:09 am

Hello,

Thanks for your feedback.
Please note that removing the code is provided to you as a temporary solution and our development team will address this issue. I'll keep you posted once the new version fixes this.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Thu May 05, 2022 10:14 am

Update: Unfortunately the call to "ActiveSheet.ReparseFormula()" seems to be essential and can not be avoided.
Without it the generated excel file does not contain the expected values initially. Cells containing MarkerDesigner formulas are displayed as "#VALUE" in the generated file until manually triggered, for example by doubleclicking the cell. Only then the values are calculated and rendered correctly.

Thank you for your help. I will wait for the definitive solution.

karl.pircher@recla.it
 
Posts: 15
Joined: Mon Mar 14, 2016 2:16 pm

Mon May 09, 2022 9:55 am

Hello,

Thanks for your patience.
The good news is that the issue SPIREXLS-3823 has been resolved.
You mentioned the removal of the code "ActiveSheet.ReparseFormula()" and the result file shows "#VALUE" issue, which we did not reproduce. If you use the latest version to delete "ActiveSheet.ReparseFormula()", the result file still has the "#VALUE" issue, please provide your test code (which can reproduce your issue) and test environment (such as Windows7, 64bit and region setting (E.g. China, Chinese)) for further investigation. You could attach them here or send them to us via email (support@e-iceblue.com). Thanks in advance.
In addition,I asked our development team, "ActiveSheet.ReparseFormula()" and "workbook.CalculateAllValue()" are both calculation formulas, and using the two together will affect the performance of formula calculation. And the "ActiveSheet.ReparseFormula()" code will be discarded later. Therefore it is recommended that you remove this code.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Thu May 12, 2022 7:09 am

Hi,

I've tested again and can confirm that the result is as expected and doesn't produce any "'#VALUE" problems, if I just call "workbook.CalculateAllValue();", but not " workbook.ActiveSheet.ReparseFormula();".
Previously I deleted both statement, so the values were not calculated when opening the generated excel.

Thank you for the explanation. This solves the issue for me for the moment.
Will the new version be available through the public NuGet feed https://www.nuget.org/packages/Spire.XLS/?

Karl

karl.pircher@recla.it
 
Posts: 15
Joined: Mon Mar 14, 2016 2:16 pm

Thu May 12, 2022 10:13 am

Hello,

Thanks for your feedback.
Once the official version with the fix for this issue is released, you can get it via the link you mentioned. However, the official version has not been released yet. I will notify you as soon as a new version is released. Thanks for your understanding.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon May 16, 2022 6:33 am

Hello,

Thanks for your patience!
Glad to inform you that we just released Spire.XLS Pack(Hotfix) Version:12.5.1 which fixes the issue of SPIREXLS-3823.
Please download the new version from the following links to test.

Website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/12.5.1

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon May 23, 2022 1:25 pm

Hi,

I've tested the new version. The bugfix works as described.

Thank you.
Karl

karl.pircher@recla.it
 
Posts: 15
Joined: Mon Mar 14, 2016 2:16 pm

Tue May 24, 2022 1:07 am

Hello,

Glad to hear that.
If you encounter other issues related to our products in the future, please feel free to contact us.
Wish you all the best!

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.XLS