Hi,
I have a document with ZIP CODE column and say HIRE_DATE which using custom formula mm/dd/yyyy.
How can I differentiate between 2 columns when reading data?
if I use DateTimevalue that ZIP code is getting some date and if I use DoubleValue than HIRE_DATE comes out as some double number totally unrelated to the value in a cell.
I am sending an e-mail with the doc to demonstrate the problem.
I am using 1.6.3.3 version.
Thanks
Mark
Dear Mark,
Thanks for your consideration.
If ZIP CODE and HIRE_DATE are saved in a designer file, they are all numeric values. Since it’s hard to detect customer numeric format, so currently you have to know the type of the cell values and differentiate them by your own method.
Lawrence,
Your answer means to me that there is no solution to the problem.
Aspose.Excel does not tell me how to differentiate one field from another.
In one of the cases I have a custom formula assigned to a cell, Aspose does not tell me that, leaving me with 2 identicalk columns and no way of knowing which one is what.
The type of the cell is the same for both cases IsDouble.
Am I stuck at this point?
Mark
here a few more details regarding the problem:
below is an info details on Numeric value representing ZIPCODE:
+ InCells {Aspose.Excel.Cells} Aspose.Excel.Cells
IsDateTime False Boolean
DateTimeValue #12/5/2168# Date
Row 4 Integer
Column 7 Byte
Type IsNumeric Aspose.Excel.CellValueType
CellType dh
SSTIndex -1 Integer
Name “H5” String
StringValue “98226” String
IntValue 98226 Integer
DoubleValue 98226.0 Double
FloatValue 98226.0 Single
BoolValue <error: an exception of type: {System.Exception} occurred> Boolean
Style Nothing Aspose.Excel.Style
XFIndex -1 Integer
XFIndexRead 21 Integer
Formula “” String
IsStringFormula False Boolean
FormulaRecord Nothing a1
ArrayRecord Nothing c9
SharedFormula Nothing ba
StringRecord Nothing a6
cellval.DoubleValue 98226.0 Double
cellval.StringValue “98226” String
iFld 7 Integer
below is an info details on Numeric value representing DATE with custom formula mm/dd/yyyy
NOTICE that nowhere i can find out that field has a formula assigned to it, so how can I make a decision what field it is?
+ InCells {Aspose.Excel.Cells} Aspose.Excel.Cells
IsDateTime False Boolean
DateTimeValue #9/30/2003# Date
Row 4 Integer
Column 0 Byte
Type IsNumeric Aspose.Excel.CellValueType
CellType dh
SSTIndex -1 Integer
Name “A5” String
StringValue “37894” String
IntValue 37894 Integer
DoubleValue 37894.0 Double
FloatValue 37894.0 Single
BoolValue <error: an exception of type: {System.Exception} occurred> Boolean
Style Nothing Aspose.Excel.Style
XFIndex -1 Integer
XFIndexRead 23 Integer
Formula “” String
IsStringFormula False Boolean
FormulaRecord Nothing a1
ArrayRecord Nothing c9
SharedFormula Nothing ba
StringRecord Nothing a6
cellval.DoubleValue 37894.0 Double
cellval.StringValue “37894” String
iFld 0 Integer
Please help me resolve this issue, it is a critical problem for us at the moment.
Thanks
Mark
Dear Mark,
I didn’t mean there is no solution, but it’s hard.
If you only use “mm/dd/yyyy” to label a cell as date value, it’s simple to know it’s type. But since custom number format is various, it’s hard to give a general solution.
Could you differentiate the cell type by the column header, such as “HIRE_DATE”?
If you really need this feature, I can release a fix at the start of next week to meet your specific needs.
Laurence,
It is not possible for me to know what is the field in Excel represents, since we have different formats excel files coming to us from thouthands of customers.
All I need is a custom formula to be exposed if there is one, than I can decide on my own what to do with the field.
We really need this feature ASAP, it is critical to us.
Thanks
Mark
Dear Mark,
How do you think about this solution?
If the cell number format is standard format in number format description, you can get DateTime type when the number format index is 14-22 or 45-47.
If the cell number format is custom format, you can use Cell.Style.Custom to get custom number format string.
If this solution can meet your need, I will do some modification to Aspose.Excel and release you the new fix no later than the start of next week.
Laurence,
This is fine with me as long as i can differentiate between 2 numeric fields - 1 with custom formula and 1 w/o.
Will I know in your proposed solution (part 1) when to use DateTimeValue and when StringValue etc…?
Thanks
Mark
Dear Mark,
All type of cell data can be converted to StringValue. If it’s a double/integer or datetime type, you can use DateTimeValue.
Dear Mark,
When you set number format to datetime, cell type will be datetime if this format is standard format. If the number format is custom, the cell type will be double. But you can use Cell.Style.Custom to get the custom format string. Thus you can differentiate if it’s a datetime value.
The question is: will I be able to differentiate when to use double and datetime?
say i have double, I also need to know that the custom formula assigned to the field.
I have no way og knowing which column is plain double (say ZIP) and which one is double with format rules assigned.
Does it make sense?
Thanks
Mark
Dear Mark,
If one cell is ZIP code, you can get a double type and Cell.Format.Custom is null or “”.
If one cell is HIRE_DATE with standard datetime number format, you can get a datetime type.
If one cell is HIRE_DATE with custom datetime number format, such as “mm/dd/yyyy”, you can get a double type and Cell.Format.Custom is “mm/dd/yyyy”.
Will this help you to differentiate when to use double or datetime?
Hi Laurence,
I was just wondering how it is going with a requested feature?
Thanks, we appreaciate your help
Mark