I have a non-standard date format - YY/MM/DD. I set the style.Custom=“yy/mm/dd”, and I set the style on the cell, but the cell still does not use the format for conversion when I call e.g. cell.PutValue("22/03/24, true). How do I pass the correct format to cell.PutValue method?
You may insert Date values in a standard and locale oriented way in code, it will be converted to your desired DateTime format automatically. Anyways, we will look into it further soon.
Like the situation that inputting value for a cell in ms excel, generally the cell’s number format setting will be determined by the input value and not vice versa the value can be parsed according to the format setting. We may consider the possibility of support such a feature for user’s convenience later. Currently, now that you know the exact format of the input value, we think you may parse it with the format by yourself and then put the parsed date time value to the cell.
Thanks for the prompt response. My problem actually runs a little deeper than in my explanation above.
I do not know when to call PutValue with convert and when without.
Let me explain with a little more detail - please find the DemoData spreadsheet attached. DemoData.zip (10.0 KB).
It is a very simple spread sheet with two columns.
The important one is the first - date column in format “YY/MM/DD” - the format that maintains the date sorting also if the date is read as a string (alphanumeric order is the same as the date order).
The excel and Aspose.Cells figure out that those values are dates (the Style.IsDateTime is set to true), but do not convert them to the Dates - the Cell.IsString - stays true and the Cell.DateTimeValue throws an exception. The Style.Custom, btw is set to (apparently date default “mmm d, yyyy”)
Now I am working on a simple Excel editor which would allow entering the dates. Somehow I need to know that the dates in this column should not be converted. I am pretty sure that Aspose.Cells know it (since it does not convert them upon loading) and Excel knows it but I do not see any flag or anything that would tell me that I should not be converting the date string if the user enters it via my custom Excel editor.
BTW at this point, in order to get around this problem, I simply do not do the conversion for the dates, but my Excel Editor has to handle any spreadsheet so I can easily imagine that dates in some other spreadsheets will be in a more usual format, e.g. “mm/dd/yy” and they’ll have to be converted for proper sorting and formulas relying on the Date order.
Please, help!
Thank you for the details. For your special requirement, how about parsing the DateTime value by yourself according to the cell’s number format setting? The code example:
if (cell.NumberCategoryType == NumberCategoryType.Date)
{
Style style = cell.GetStyle(false);
cell.PutValue(DateTime.ParseExact("22/10/24",
style.InvariantCustom.Replace('D', 'd').Replace('m', 'M').Replace('Y','y'), CultureInfo.InvariantCulture));
}
The thing is that my Excel Editor should process any Worksheet not only that one. Yes I could create some special treatment for this particular case but how would I distinguish it from other cases in other spreadsheets (which can be coming from completely different clients).
Aspose.Cells seem to understand that this is a special case and should not be converted to the date because it does not convert it at the loading stage. I am wondering how, so that I’d be able to tap into that Aspose.Cells functionality.
Excel also understands that this is a special case - otherwise the client’s formulas would be all screwed.
@npolyak,
As @johnson.shi previously replied, we are currently aligned with MS Excel.
Do you need to support parsing the values set by the cell based on the format it has already set?
When you calling Cell.PutValue(“22/03/24”, true), we do convert the value to a numeric value(same with the data model of ms excel, in our component date time values are kept as numeric values too, whether it will be taken as date time or numeric depends on the number format of corresponding cell). If you have set date time formula for the cell, then the value will be represented as a date time value.
The uncertain part for the result is which part(22 or 24) should be taken as the year. It is determined by the region currently used by your workbook. In the default date time format of corresponding region, if the year is prior to month and day, then 22 will be taken as year and 24 will be taken as day. If the year is behind month and day, then 22 will be taken as day and 24 will be taken as year. For example, if your used region is en-US, the parsed date will be 03/22/2024. If your used region is zh-CN, the parsed date will be 03/24/2022. This automatic way should be the most preferred one if it can meet your requirement.
If your situation is more complicated and the automatic detection cannot work, from your first post we think you want us to parse the input value according to the cell’s number format which you have set before calling Cell.PutValue. It is similar to the code I gave(using DateTime.ParseExact) but may be more complicated, such as you also need to parse a double value in some kind of complicated formatting. Please correct me if I misunderstood your requirement, because I also doubt whether it is feasible for an editor to require user to set cell’s formatting firstly, and then user must input the correctly formatted string value for a number.
@johnson.shi
Somehow, when the workbook is loaded into Aspose.Cell - this column is not converted to the DateTime (even though it recognizes that this is the Date format, but the cell keeps the data as a string). The other columns are converted. So I need to understand how Aspose.Cell figured out that this Date column should not be converted after loading the workbook. Then I can use the same algorithm for the user input setting the convert boolean flag to false within PutValue method for this case.
If I had Aspose.Cell source code, I could have figured it out easily, but we do not have it.
I do not think we should require the user to set the date format since Excel does not require it.
Thanks
I think now I finally get what the problem you are talking about. I was focusing on the description in your first post and misunderstood your requirement. In fact you need to know the logic used by ms excel to determine whether some kind of input should be parsed as date time or not, such as the data in column A in your DemoData.xlsx.
Unfortunately, we do not use the logic to parse data while loading the data from template file. In fact we read the data in column A as string just because they are defined as string value in your template file. If they are saved as date time(as we have said, date time is also saved as numeric value), then we will load them as date time too.
The possible number formatting in ms excel is vary and very complicated. We are afraid it is hard and even impossible for us to know all the rules and perform them exactly same with ms excel always. For example, as we have said, your input “22/10/24” cannot be recognized as date time in ms excel, but it will be parsed as date time value by our component.
So, although our component provides behavior and results as similar as possible to MS Excel, it is still impossible to make the behavior of your editor completely consistent with MS Excel. Maybe you need to make some special implementations according to your special needs and use them together with our components to achieve the desired effect.
Aspose.Cells handles this correctly during the LOADING process. So I only need to know how Aspose.Cell handles it at the LOADING stage in order to do something similar during the User Editing. I do not need to know how MS Excel does it. Aspose.Cells marks the cells as dates in the Style, but leaves them as strings in the cell. I need to know how it figures it out - Aspose.Cells, not Excel.
Thanks
As we have said, those cells are kept as string just because they are defined as string in your template file. In your file the cell such as A4 is defined as:
<c r="A4" s="8" t="s"><v>2</v></c>
Here the type of its value is string, so we keep it as string. For a string value, the formatting for a number will take no effect.
@npolyak
There is no datetime type stored in the file. Numberic values are stored in the file .
Excel will display numbers in different formats.
You can get datetime from numberic as MS Excel with
@johnson.shi This is already good. This is not my file - this is our client’s file (or rather these two columns were copied from our client’s file).
How does Aspose.Cells’ API expose the fact that it is a string? For example if I get the Column A from Aspose.Cells - will it have some property to indicate that this is a string column?
We are afraid you can only detect the value type for every cell separately(such as by Cell.Type, or by IsNumericValue/ IsErrorValue for some special types). For cells in one column, there is no constraint that all cells must be the same type. If your business constraint can make sure that all cells in one specific column are of same type, then you can check any one cell in this column to get the type.
No, I do not have such constraint. But the cell will also be a string if e.g. it has null or some non-date entry. Then the cell’s Type will change dynamically to a string.
Therefore I need to be able to distinguish between this two cases - taking the hint from the column, that even if this string is passed in the date format it still should remain the string.
I do not mind parsing the excel file, if you tell me how to do it.
Thanks
Please see all possible types(the enum CellValueType) you can get by Cell.Type property. If one cell is empty without any content, then its type will be CellValueType.IsNull.
If you get one type value other than CellValueType.IsDateTime, then this cell’s value must not be a DateTime. For such kind of situation, either the cell’s value is not a numeric value(including the value is a string which seems representing a valid date time), or this cell has not been set with a date time formatting.
For your words “Then the cell’s Type will change dynamically to a string.”, we are not sure about your requirement. Do you need to force those cells whose value is not a date time to be string type? If so, we think you may try below code:
CellValueType t = cell.Type;
if (t != CellValueType.IsDateTime && t != CellValueType.IsString)
{
cell.PutValue(cell.StringValue);
}