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