Behavior of Cell.Value and ExportArray() for referenced date cells using Aspose.Cells for .NET in C#

I have a sheet with a cell reference to another sheet (i.e. Sheet2!A1). The referenced cell contains a date.

When I use Cell.Value, a numeric value is returned. Cell.DateTimeValue returns the correct DateTime. Cell.Type returns IsDateTime, and Excel shows the cell as containing a Date. For these reasons I would expect Cell.Value to return a DateTime.

Similarly, Cell.ExportArray() returns a 2-dimensional array containing the value as a numeric, not a DateTime.

Hi,

Well, Cell.Value returns an object value contained in the cell. Since your cell contained a referenced formula, so you should calculate the formula first to get the value into the cell or later export the value to an array. Or you may also try Cell.StringValue here.

E.g., Suppose I have the ref. formula (=Sheet2!A1) in A1 cell in the first sheet in my template file. In Sheet2 A1 cell I have a datetime value.

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\mybook.xls");
workbook.CalculateFormula(false);
DateTime dtvalue = (DateTime)workbook.Worksheets[0].Cells["A1"].Value;
object [,] arr = workbook.Worksheets[0].Cells.ExportArray(0,0,2,1);
......
Thank you.

Amjad,

OK, I over-simplified the problem.

If you have a cell with a reference to a different file (e.g. ='C:\Test\[test2.xls]Sheet1'!$A$2), then the problem occurs. Try this:

1. Create test2.xls and enter a date into cell A2

2. Create test1.xls and make cell A1 a reference to cell A2 in test2.xls

3. Open test1.xls programmatically as in your sample. You get this:

workbook.Worksheets[0].Cells["A1"].Value: 39212.0

workbook.Worksheets[0].Cells["A1"].DateTimeValue: {10/05/2007 00:00:00}

workbook.Worksheets[0].Cells["A1"].Type: IsDateTime

workbook.Worksheets[0].Cells["A1"].Formula: "=Sheet1!$A$2"

Doing a CalculateFormula() doesn't change anything. It looks like the formula is pointing to the local workbook, not to the referenced workbook. Obviously, Aspose.Cells won't be able to open the remote workbook, I don't expect that. However, since Type is IsDateTime, and the DateTimeValue is correct, shouldn't Value return {10/05/2007 00:00:00} rather than 39212.0?

In short, if Type returns IsDateTime, shouldn't Value return a System.DateTime?

Thanks for you help.

Please try this attached version.

Hi,

Kindly try the attached version which Laurence posted.

Thank you.

Same result: Type=IsDateTime; DateTimeValue={10/05/2007 00:00:00}; Value=39212.0

Any other ideas?

Hi,

Could you replace the line with:

workbook.Worksheets[0].Cells["A1"].StringValue

Thank you.

What I actually want to do is grab a section of a large spreadsheet using ExportArray() and then process the array. This is considerably faster than accessing individual cells.

However, ExportArray() returns an array containing typed values (i.e. the result of calling Cell.Value on each cell). If any of the cells contains a date, as above, then the corresponding array element will contain a numeric value, which is no good to me.

Sure, if I wanted to access a single cell I could use DateTimeValue or StringValue (or whatever) and get the correct result - but I can't do that.

My expectation is that if Type returns IsDateTime then Value should return a System.DateTime. Is this a correct expectation?

Hi,

What I actually want to do is grab a section of a large spreadsheet using ExportArray() and then process the array. This is considerably faster than accessing individual cells.

However, ExportArray() returns an array containing typed values (i.e. the result of calling Cell.Value on each cell). If any of the cells contains a date, as above, then the corresponding array element will contain a numeric value, which is no good to me.

Sure the array contains datatime values not numeric values according to your need. Well, I tried your scenario, I used a template excel file which contains a recursive reference formula into Sheet1 A1 cell i.e. ='D:\test\[tsttbook2.xls]Sheet1'!$A$1. And in the testbook2, I filled the Sheet1 A1 cell with date value. Then, using code snippet, I export the data of the Sheet1 from template file to an object array. I confirmed the array got datetime values. Following is my testing code:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\templatebook.xls");
object [,] arr = workbook.Worksheets[0].Cells.ExportArray(0,0,2,1);
MessageBox.Show(arr[0,0].ToString()); //It will show the datetime values not numeric values.
MessageBox.Show(arr[1,0].ToString());

Did you try the latest hotfix posted by Laurence?

And Related Cell.Value should return datetime, we will figure it out soon.

Thank you.

Hi,

Related Cell.Value......

Well, actually MS Excel saves datetime value as numeric data in files. It shows them according to number format when you import or export data in files. And related ExportArray method, We checked the format and make it to return DateTime value.

Cell.Value returns native data. So, you can try to do i.e.:

if(cell.Type == CellValueType.IsDateTime)
workbook.Worksheets[0].Cells["A1"].DateTimeValue.........

Thank you.

Thanks for this, ExportArray() does work with the the version posted by Laurence. However, my license gives an error saying that I’m not entitled to updates after Feb 2007, so it only works in evaluation mode. How can I get this functionality to work?

Hi,
I think your license is expired and you have to upgrade your subscription for using the latest releases / hotfixes. You may post your query
Aspose.Cells Forum

Thank you.

I am also facing same issues while exporting data into object [] [] array using exportArray java.

When printing the value of object array I got this date value : 2013-06-30T00:00:00 and the original excel value is : 6/30/2013 in the excel.

But while inserting arrays value in another excel, I am getting some other numeric value : 41455.
not the original date.

I am doing all the processing with .xlsx files.

Using aspose-cells-7.1.2.jar with jdk 1.5

Can you please help me on this ?

Thanks
Sachin Dagar

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Please download and try the latest version Aspose.Cells for Java (Latest Version) and see if it fixes your issue.

If your problem still occurs, then please provide us your source Excel files and sample code replicating this issue with the latest version. We will look into it and help you asap.

Please also see the following documentation for importing and exporting data from worksheets into different data sources for your reference.