DateTime function gives zero result in C#

Aspose.Cells Product Family
1

I am writing this code in C#.

When I open the .xls file it appears the function is calculated correctly by my code is always getting zero back!

Here is the issue:

I have a Aspose.Cells.Workbook that I create and from there I create a Worksheet. I then have a program that allows users to add data to my cells. The data can be of type String, Integer or DateTime. When they are done adding data they may create a formula and execute that formula against the cell data they have added. I calculate the formula for them by taking the formula they give me and setting it as the formula for one of my cells. I then call workbook.CalculateFormula() and get the value from that cell and return it to them as the result of their formula.The problem seems to be when the user adds a DateTime data to a cell. When they add data of type “int” the function resulting value is returned correctly. When they add DateTime data and I call workbook.CalculateFormula() I get zero as my result every time! Here are my code snippets:

add the data:

else if (data.GetValue(2).ToString().ToLower().Contains(“date”))
{
DateTime myDate = Convert.ToDateTime(data.GetValue(1).ToString());
colBcell.PutValue(myDate);
Aspose.Cells.Style cellStyle = colBcell.Style;
cellStyle.Number = 14;
formulaCell.Style = cellStyle;
}


calculate the formula:
workbook.CalculateFormula();


get the resulting data:

//Variables to store values of different data types
string stringValue = null;
double doubleValue = 0.0;
bool boolValue = false;
DateTime dateTimeValue = DateTime.Now;

//Passing the type of the data contained in the cell for evaluation
switch(formulaCell.Type)
{
//Evaluating the data type of the cell data for string value
case Aspose.Cells.CellValueType.IsString:
stringValue = formulaCell.StringValue;
break;


//Evaluating the data type of the cell data for double value
case Aspose.Cells.CellValueType.IsNumeric:
doubleValue = formulaCell.DoubleValue;
break;


//Evaluating the data type of the cell data for boolean value
case Aspose.Cells.CellValueType.IsBool:
boolValue = formulaCell.BoolValue;
break;


//Evaluating the data type of the cell data for date/time value
case Aspose.Cells.CellValueType.IsDateTime:
dateTimeValue = formulaCell.DateTimeValue;
break;


//Evaluating the unknown data type of the cell data
case Aspose.Cells.CellValueType.IsUnknown:
stringValue = formulaCell.StringValue;
break;


//Terminating the type checking of type of the cell data is null
case Aspose.Cells.CellValueType.IsNull:
break;
}

Console.WriteLine("String: " + stringValue);
Console.WriteLine("DateTime: " + dateTimeValue);
Console.WriteLine("douoble: " + doubleValue);
Console.WriteLine("bool: " + boolValue);

Console output:

String:
DateTime: 12/31/1899 12:00:00 AM
douoble: 0
bool: False

What am I doing wrong?

Thanks!

2

Hi,

Please try this fix. We have supported to get the date value of the formula.

3

I tried the new Aspose.Cells.dll from the previous post with the same results. I removed the old dll, then checked to make sure my project would not run because it was missing, then added the new one and the project ran again with the same results. I still need a fix for this problem.

Thanks!

4

Hi,

Thanks for the info, We will check and get back to you soon.

Thank you.

5

Hi,

And could you post your excel file here with sample code. It will help us to reproduce the issue.

Thank you.

6

I have attached the C# .CS file that is what the unit test calls. The below code creates a new object of the file I have attached by calling new AsposeCreator(). Please rename the attachment to .cs from .cs.txt. My unit test adds three rows of data that constists of a variable name in col1 (one, two, three), the variable data in col2 (as a date) and then col3 has a cell with the function =MAX( {one},{two},{three} ). As you will see in the code I look for the data type “Date/Time” and do a cast before I call cell.PutValue(date). I will add the resulting .xls file in my next post.

Unit test:

[Test]
public void TestFormulaMaxDateResult()
{
AsposeCreator aspose = new AsposeCreator();
aspose.addRow(“one”, “12-18-1970”, “Date/Time”);
aspose.addRow(“two”, “10/26/1975”, “Date/Time”);
aspose.addRow(“three”, “10/16/2007”, “Date/Time”);
aspose.setFileLocation(“C:\temp\function_results.xls”);
aspose.setFormula("=MAX( {one},{two},{three} )");
aspose.buildSpreadSheet();
Assert.AreEqual(“10/16/2007”, aspose.getFormulaResult());
}


Unit test result:

String:
DateTime: 12/31/1899 12:00:00 AM
douoble: 0
bool: False
TestCase 'com.emc.gsca.util.AsposeTestClass.TestFormulaMaxDateResult’
failed:
String lengths are both 10. Strings differ at index 1.
Expected: “10/16/2007”
But was: “12/31/1899”
------------^


Thanks!

7

And here is the resulting Excel file from the unit test in the previous post. Please reanme it to .xls from .xls.txt.

Thanks!

8

Hi,

Thanks for your file and codes.

Please try this fix.

9

That works! Thanks for your quick response and help on this!