Hello,
we haven an Excel with a Validation on a specific cell.
i.e.: the cell-value in “B1” must be greater than an other cell-value in “A1” and ignoring blank-values.
When we open the excel-file and write a value in “B1” and leave “A1” free, it is working fine.
But when we open the excel-file with Aspose with C# and call the function Aspose.Cells.Cell.GetValidationValue()
for “B1” then we get “false” as the return-value but we expect true like in pure excel.
Why differs the behavior from excel and Aspose?
Thanks in advance
Matthias
@Matthias_Winzer,
Please zip your template Excel file to attach it and paste your sample code here to evaluate your issue precisely, we will check it soon.
Hello Amjad_Sahi,
here is the Excel-file.
MW_FB01_ElektrErzeuger_Speicher_2020_V2_ohne Schutz.zip (232.0 KB)
In the Excel-file:
Sheet: “2.1 Bewegungsdaten”
Cell: “C92” depend on Cell “E26”
If we let Cell “E26” free and put a value in Cell “C92” in the excel -> no problem.
When we do that with the following code the function “GetValidationValue()” returns false but should return true:
Specific code: p_cell.GetValidationValue()
More code:
private bool ValidateAndSetCellValue(ref Cell p_cell, string p_newCellValue, string p_newFormel, ref int p_notChangedCount)
{
bool err = false;
if (p_cell != null && !p_cell.StringValue.Equals(p_newCellValue) && string.IsNullOrEmpty(p_newFormel))
{
string oldCellValue = p_cell.StringValue;
// Zelle ist vorhanden und Werte unterscheiden sich
var val = p_cell.GetValidation();
double tempDouble;
if (val != null && val.Type == ValidationType.Decimal && double.TryParse(p_newCellValue, out tempDouble))
{
double tmpDbl = Convert.ToDouble(p_newCellValue);
p_newCellValue = tmpDbl.ToString("G");
}
Aspose.Cells.Style cellStyle = p_cell.GetStyle();
const int cellStyleText = 49;
p_newCellValue = EntferneFakeExcelDatumFallsNotwendig(cellStyle, p_newCellValue);
if (cellStyle.Number == cellStyleText)
{
p_cell.PutValue(HttpUtility.HtmlDecode(p_newCellValue), false, false);
}
else
{
p_cell.PutValue(HttpUtility.HtmlDecode(p_newCellValue), true, false);
}
bool isKorrektAberListenbug = false;
if (val != null && val.Type == ValidationType.List)
{
if (val.Value1 as object[] != null)
{
var listenEintraege = (object[])val.Value1;
for (int k = 0; k <= listenEintraege.Length - 1; k++)
{
if (listenEintraege[k].ToString().Trim() == p_newCellValue)
{
isKorrektAberListenbug = true;
break;
}
}
}
}
if (!(p_cell.GetValidationValue() || string.IsNullOrEmpty(p_cell.Value.ToString()) || isKorrektAberListenbug))
{
// validierung fehl geschlagen
// Zellwert zurücksetzen
p_cell.PutValue(oldCellValue, true, false);
err = true;
}
}
else if (!string.IsNullOrEmpty(p_newFormel) && p_cell.Formula != p_newFormel)
{
// die Formel wurde geändert und muss gespeichert werden
p_cell.Formula = p_newFormel;
}
else
{
// wenn nichts geändert wurde (z.B. bei einer berechneten Zelle ändert sich das Ergebnis der Berechnung aber nicht die Formel) müssen wir uns das merken,
// damit wir die korrekte Anzahl der tatsächlich geänderten Zellen haben
p_notChangedCount += 1;
}
return !err;
}
We are using Aspose.Cells with the version: 8.6.2
Our Application is a Webform-App in C# with .NET Framework 4.7.1
Thanks in advance
Matthias
@Matthias_Winzer,
Please try using our latest version/fix, i.e., Aspose.Cells for .NET v20.6.x (as you are using some older version of the product). I have tested using the simplest lines of code and it works ok. See the sample code for your reference:
e.g
Sample code:
var book = new Workbook("e:\\test2\\MW_FB01_ElektrErzeuger_Speicher_2020_V2_ohne Schutz.xlsx");
book.CalculateFormula();
Worksheet sheetBla = book.Worksheets["2.1 Bewegungsdaten"];
Cell cell1 = sheetBla.Cells["E26"];
Cell cell2 = sheetBla.Cells["C92"];
cell1.PutValue(12);
cell2.PutValue(11);
book.CalculateFormula();
bool isValid = cell2.GetValidationValue(); // isValid is true
If you still find the issue with latest version/fix, kindly do paste runnable sample code (same as above) to show the issue, we will check it soon.
Hello Amjad_Sahi,
thanks for your quick answere.
The problem is, that the cell “E26” is empty.
When I open the Excel in real and put a Value in “C92” there is no validation error.
When I do it in code, like my sample code below, there will be a validation error.
My Sample code:
var book = new Workbook("C:\\Projekte\\MW_FB01_ElektrErzeuger_Speicher_2020_V2_ohne Schutz.xlsx");
book.CalculateFormula();
Worksheet sheetBla = book.Worksheets["2.1 Bewegungsdaten"];
//Cell cell1 = sheetBla.Cells["E26"];
Cell cell2 = sheetBla.Cells["C92"];
//cell1.PutValue(12);
cell2.PutValue(11);
book.CalculateFormula();
bool isValid = cell2.GetValidationValue(); // isValid is false!
TestContext.WriteLine("Ergebnis: " + isValid);
Thanks in advance
Matthias
@Matthias_Winzer,
Thanks for further details.
Please notice, apparently we reproduced the issue as you mentioned using your template file but we need to investigate if this is expected behavior or it is a bug. Logically it should return as false. Anyways, I have logged a ticket with an id “CELLSNET-47456” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
@Matthias_Winzer,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47456”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
@Matthias_Winzer,
Please try our latest version/fix: Aspose.Cells for .NET v20.6.5 (attached)
Aspose.Cells20.6.5 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.6.5 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.6.5 For .NetStandard20.Zip (5.3 MB)
Your issue should be fixed in it.
Let us know your feedback.
Hello Amjad_Sahi,
thanks for the quick bugfix.
The one issue seemed to be solved.
But we have another Problem, see example-code.
The first validation is working -> OK, the second not.
Is it possible to have the same behavior like in Excel in context of validation?
My Sample code:
var book = new Workbook("C:\\Projekte\\MW_FB01_ElektrErzeuger_Speicher_2020_V2_ohne Schutz.xlsx");
book.CalculateFormula();
Worksheet sheetBla = book.Worksheets["2.1 Bewegungsdaten"];
Cell cell2 = sheetBla.Cells["C92"];
cell2.PutValue(131384.39);
book.CalculateFormula();
bool isValid = cell2.GetValidationValue(); // isValid is true!
TestContext.WriteLine("Ergebnis: " + isValid);
Cell cell3 = sheetBla.Cells["E92"];
Cell cell4 = sheetBla.Cells["F92"];
cell3.PutValue(47459.26);
cell4.PutValue(47459.26);
book.CalculateFormula();
isValid = cell4.GetValidationValue(); // isValid is false!
TestContext.WriteLine("Ergebnis: " + isValid);
Thanks in advance
Matthias
@Matthias_Winzer,
Thanks for providing us new scenario/ case.
You are right, the issue is still there using your newer case (sample code). I have reopened the issue and we will look into it to figure it out soon.
Sorry for any inconvenience caused!
@Matthias_Winzer,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47456”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
@Matthias_Winzer,
Please try our latest version/fix: Aspose.Cells for .NET v20.6.6 (attached)
Your issue should be fixed in it.
Let us know your feedback.
Aspose.Cells20.6.6 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.6.6 For .Net4.0.Zip (5.4 MB)
Hello Amjad_Sahi,
thanks for the bugfix.
The issue seemed to be solved now.
Matthias
@Matthias_Winzer,
Good to know that your issue is sorted out by the new fix/version. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.
The issues you have found earlier (filed as CELLSNET-47456) have been fixed in Aspose.Cells for .NET v20.7. This message was posted using Bugs notification tool by ahsaniqbalsidiqui