Excel file generated with Cells doesn't properly recognize and sort date fields

Aspose.Cells Product Family
1

I generated the attached excel file with the most recent version of ASPose Cells for .net.



The data gets into the sheet via the Cells.ImportFromDataReader method.



I then go through each column and apply various styles.



The “First Contact Date” and “Last Contact Date” fields are formatted as follows:



cells.Columns(i).Style.Number = 14



I expect Style.Number 14 to be a date type recognized by Excel. However, if you open the attached excel spreadsheet, you will notice that Excel is treating the two columns “First Contact Date” and “Last Contact Date” as strings and they don’t sort properly. 1/1/2006 is followed by 1/10/2006 which shows it is being sorted as a string. Furthermore, if you doubleclick into a cell and then out of it, excel recognizes that it is a date and treats it properly.



I need to be able to format these columns as dates and have excel treat them as dates.



Please advise,



James

2

Hi James,

Apparently the issue is not with sorting of records. It is formatting of date columns. Please use the code segment specified under to format your date columns.

Style style;

StyleFlag flag = new StyleFlag();

style = worksheet.Cells.Columns[0].Style;

style.Number = 14;

flag.NumberFormat = true;

worksheet.Cells.Columns[0].ApplyStyle(style, flag);

This code segment works fine for applying the format style for the whole columns. I hope it will help in formating and then sorting of data properly.

Regards;

Nausherwan Aslam

3

Hi James,

I would like to add more. Actually, with the release of Aspose.Cells for .NET 4.4.2 we did enhance some formatting API and also incorporated some new API for performance reasons. So, in the new versions of Aspose.Cells for .NET (e.g.., 4.4.2, 4.4.3, 4.5.0 and so on), the Column.Style is a read-only property, so, you should use either Cells.ApplyColumnStyle() method or Column.ApplyStyle() method for your task. The process is simple:

Create a Style object, set your desired formatting attributes, create the StyleFlag struct (The struct is used which formatting options you want to apply to a column, row or a range), specify which formatting attributes you want to apply, now use either of the methods (Cells.ApplyColumnStyle() / Column.ApplyStyle() ) to apply the formatting style and perform your task.

It is to be noted here, this technique would also enhance the performance to certain extent in the long run.

Thank you.

4

Dear Nausherwan,



Thank you for your attention to this issue.



- I’ve generated the attached Excel file with the latest version of Aspose.Cells - 4.5

- The data gets into the sheet via the Cells.ImportFromDataReader method.

- I then loop through each column and apply various styles based on the column header name.



The “First Contact Date” and “Last Contact Date” columns are formatted as follows (modeling the code excerpt you gave me):



Dim aspStyle1 As Style = cells.Columns(i).Style

aspStyle1.Number = 14



Dim aspStyleFlag1 As New StyleFlag

aspStyleFlag1.NumberFormat = True



cells.Columns(i).ApplyStyle(aspStyle1, aspStyleFlag1)



As you can see in the attached Excel spreadsheet, Excel still does not recognize the columns as date columns. Please let me know if there is a solution.



Thanks,



James Vitale

5

Hi James,

We have found this issue. It's caused by that we get the string value from the DataReader although the type of the field is date. We will add a new method Cells.ImportFromDataReader(OleDbDataReader reader, bool isFieldNameShown,
int firstRow, int firstColumn, bool insertRows, string dateFormatString,
bool convertStringToNumber) method to convert the string value to numberic value and set the date number format.

6

Hi James,

Hope you will be fine. I have gone through this formatting issue again and I agree with Simon’s reply that a new function will be required to convert the string or text type to date. This Format conversion process works fine if your database field type is DateTime. But if your database fields are string or text, in that case the format style will not take effect and you will see all the dates as strings in your generated XLS file.

So I think currently your database fields “First Contact Date” and “Last Contact Date” are of string or text format that is why the format style is not applying on them. Hopefully new fix of Aspose.Cells (as informed by Simon) will fix this problem for you

Best Regards,

7

Hi Jams,

Please try the fix in

http://www.aspose.com/community/forums/145257/index-was-out-of-range.must-be-non-negative-and-less-than-the-size-of-the-collection.r-nparameter/showthread.aspx#145257

We have supported Cells.ImportFromDataReader(reader, bool isFieldNameShown,
int firstRow, int firstColumn, bool insertRows, string dateFormatString,
bool convertStringToNumber) method.