Date conversion not working properly

Aspose.Cells Product Family
1

Hi,


In Excel, if you put a date as a string into a Cell using VBA as such:

ActiveCell.Value = "07/14/14"
or
ActiveCell.Value2 = "07/14/14"

The cell automatically gets formatted as a Date and you see:
“14/07/2014” (if your region is outside of the US).

In Aspose the same thing does not seem to work:

var workbook = new Workbook();

Worksheet ws = workbook.Worksheets[0];

ws.Cells[“A1”].PutValue( “07/14/14”, true);
ws.Cells[“A2”].PutValue( “07/14/14”, false);

workbook.Save(“Date.xlsx”);

A1 ends up being “41834” (formatted as General instead of Date).
And A2 is, as expected, simply the string “07/14/14”.

The same is true when using ImportTwoDimensionArray:

var array = new[,] { { “07/14/14”} };

var workbook = new Workbook();

Worksheet ws = workbook.Worksheets[0];

ws.Cells.ImportTwoDimensionArray(array, 0, 0, true);

workbook.Save(“Date2.xlsx”);

Best regards,
2

Hi,


Well, Dates are stored in numeric notations in Ms Excel, so you are getting “41834” as value. You may simply add a line to your code segment, it would work fine for your needs.
e.g
Sample code:

var workbook = new Workbook();

Worksheet ws = workbook.Worksheets[0];

ws.Cells[“A1”].PutValue(“07/14/14”, true);
ws.Cells[“A1”].SetStyle( new Style { Custom = “dd/mm/yyyy” });
ws.Cells[“A2”].PutValue(“07/14/14”, false);

ws.AutoFitColumn(0);

workbook.Save(“e:\test2\out1.xlsx”);

Hope, it helps you a bit.

Thank you.
3
Hi,

Setting the style manually is not possible as I am actually using the ImportTwoDimensionArray method with an array that contains many different strings where it is not obvious which string contains a date. I simply used PutValue to illustrate that this problem persists over more than just one method.

It is also not what Excel does in this case, as Excel correctly interprets "41834" as being a Date and correctly formats it. I would expect Aspose to behave in the same way.

Best regards.
4

Hi,


Thanks for providing further details.

Well, I am afraid currently there is no simple way to fit your requirements. We might consider to add some parameters to support more options for converting/formatting numeric values later on.

Currently, if you need to get the expected results, you may try to loop though the worksheet cells and reset the style for those date values only, see the sample code segment below:
e.g
Sample code:

//…
for (int row = startRow; row <= endRow; row++)
{
for (int col = startCol; col <= endCol; col++)
{
Cell cell = cells.CheckCell(row, col);
if (cell != null && cell.Type == CellValueType.IsDateTime)
{
Style style = cell.GetStyle();
style.Number = 14;
cell.SetStyle(style);
}
}
}

Hope, it helps you a bit now.

Thank you.
5

Hi,


I appreciate your help.
The thing is, Aspose is already able to correctly convert to a proper date, it’s just not working properly using the ImportTwoDimensionArray method.

If you create a text file called “test.txt” and have “07/14/14” as the only content and run this code:

var workbook = new Workbook();

Worksheet ws = workbook.Worksheets[0];

var txtLoadOpts = new TxtLoadOptions(LoadFormat.CSV)
{
ConvertDateTimeData = true,
ConvertNumericData = true,
KeepExactFormat = false,
};

ws.Cells.ImportCSV(“test.txt”, txtLoadOpts, 0, 0);

workbook.Save(“Date.xlsx”);

The date gets correctly converted, as intended.
The problem is that it overwrites the destination Worksheet completely and therefore behaves differently from ImportTwoDimensionArray in very crucial ways.
For example if you use an existing Worksheet with A1’s background colour set to yellow, the code above will reset the background colour to white; ImportTwoDimensionArray will keep the yellow colour.

This is therefore sadly only a partial solution.

So basically what I am requesting is a best-of-both-worlds solution that uses the same conversion logic as ImportCSV but without overwriting the destination workbook’s existing formatting. I believe that this should be possible and see the fact that ImportTwoDimensionArray converts a date into a date format but does not format it as date as a bug.

Best regards.

Edit: Also your code above will not work because Aspose interprets the “41834” as numeric and not as a DateTime, which is part of the bug it seems.
6

Hi,


Thanks for providing us further details.

We did evaluate it and we have the following findings:
1) You are rightly saying the importing CSV file to an existing XLSX file will overwrite the existing formatting. I observed the issue as you mentioned. When importing a CSV
file to an Excel XLSX file would overwrite the existing formatting
(background color etc.). I used the following sample code, I used a
simple template Excel file that would import a simple CSV file (which
contains one datetime value), both files are attached here:

e.g


Sample code:


var workbook = new Workbook("e:\\test2\\BkDateConversion1.xlsx");

Worksheet ws = workbook.Worksheets[0];

var txtLoadOpts = new TxtLoadOptions(LoadFormat.CSV)
{

ConvertDateTimeData = true,
ConvertNumericData = true,
KeepExactFormat = false,
};

ws.Cells.ImportCSV("e:\\test2\\test.txt", txtLoadOpts, 0, 0);

workbook.Save("e:\\test2\\out1.xlsx");

I have logged a ticket with an id "CELLSNET-43282" for your issue. we need to investigate whether it can be supported soon (i.e., keep cell's original style when importing data into it).

Once we have any update on it, we will let you know here.

2) Well, you are right, ImportTwoDimensionArray will convert the DateTime values to numeric notations and does not format them into DateTime values, so the solution we provided previously won't work here. We will look into if we got some better way to cope with it. Once we found any good way, we will let you know here immediately.

Thank you.



7

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.3.1.6

We have fixed your issue now.

We
have fixed the formatting issue in importing CSV. For
Cells.ImportTwoDimensionArray, we add an override method
Cells.ImportTwoDimensionArray(object[,] objArray,object[,] styles, int
firstRow, int firstColumn, TxtLoadOptions opts), with TxtLoadOptions, you can specify: ConvertNumericData/ConvertDateTimeData/KeepExactFormat properties by instantiating its object to get the
expected results.


Thank you.

8

The issues you have found earlier (filed as CELLSNET-43298;CELLSNET-43282) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
9

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan