Problem for dates


#1

Hi everybody,

I would like to ask your advice about a problem I'm experiencing with dates.

To cut a long story short I have an xml file like this:

<TABLE>

<ROW>

<CELL format="#,##0.#">15000000000</CELL>

<CELL format="mm/dd/yyyy">5/6/2007 12:00AM</CELL>

</ROW>

<ROW>

<CELL>Value 4</CELL>

<CELL format="mm/dd/yyyy">1/1/08</CELL>

</ROW>

<ROW>

<CELL>Value 3</CELL>

<CELL format="mm/dd/yyyy">4/3/05</CELL>

</ROW>

</TABLE>

this excel is used to generate an Excel file, using the format attribute for the cell Style.Custom property. Everything works fine except for the second cell of the first row: the text is not formatted.

If I delete the "12:00AM" everything works fine also for this line.

I would like also to report the fact that if the format is set to "mm/dd/yyyy hh:mmAM/PM" the AM and PM is displayed as Am or Pm where m is the number of the month.

Thanks a lot.

Massimo


#2

Hi Massimo,

I don't find the first problem. Following is my sample code:

Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;
cells["A1"].PutValue(DateTime.Now);
cells["A1"].Style.Custom = "mm/dd/yyyy";
excel.Save("d:\\test\\book1.xls");

The output file shows correctly.

For this format string: "mm/dd/yyyy hh:mmAM/PM", it's a bug and i will fix it soon.


#3

Hi,

well the code you suggested works perfectly, but it is not definitely for my case: I haven't a datetime object, I have the date expressed as a string.

Here is a sample code to better test my case:

Excel ExcelEngine = new Excel();

ExcelEngine.Worksheets[0].Cells[0, 0].PutValue("12/1/2006 12:00AM");

ExcelEngine.Worksheets[0].Cells[0, 0].Style.Custom = "mm/dd/yyyy";

ExcelEngine.Save("test.xls", SaveType.OpenInBrowser, FileFormatType.Excel2003, Response);

What I would like to achieve is obtaining an excel with the value of the first cell set to "12/01/2006".

Is this possible?

Thanks.

Massimo


#4

Sorry for not replying earlier. Please try:

Excel ExcelEngine = new Excel();

ExcelEngine.Worksheets[0].Cells[0, 0].PutValue("12/1/2006 12:00AM", true);

ExcelEngine.Worksheets[0].Cells[0, 0].Style.Custom = "mm/dd/yyyy";

ExcelEngine.Save("test.xls", SaveType.OpenInBrowser, FileFormatType.Excel2003, Response);


#5

Laurence wrote:

ExcelEngine.Worksheets[0].Cells[0, 0].PutValue("12/1/2006 12:00AM", true);

I've already tried this one but it doesn't solve my problem.

I've installed the new version of the dll but it doesn't solve this problem.

Any other idea?

Massimo


#6

Hi Massimo,

Sorry, my mistake. String with "AM/PM" is not correctly converted. I will fixed it right now.


#7

Thanks a lot. Please let me know when you are done.

Massimo


#8

Hi Massimo,

Please try this attached fix. It's fixed.


#9

Hi Laurence,

this fixes the "mm/dd/yyy hh:mmAM/PM" (and this is very cool) but it doesn't solve my "main" problem.

My problem is that I have dates in string format; some of them have the time expressed and some not; I would like them to be all formatted in the same way (mm/dd/yyyy for example).

It's something like:

Cell1.PutValue("5/6/2007 12:00AM", true);

Cell2.PutValue("1/1/08", true);

Cell3.PutValue("4/3/05", true);

Cell1.Style.Custom = Cell2.Style.Custom = Cell3.Style.Custom = "mm/dd/yyyy";

I would aspect to have three cells containing respectively "05/06/2007", "01/01/2008" and "04/03/2005".

What happens instead is that everything is fine for Cell2 and Cell3 but Cell1 contains "5/6/2007 12:00AM"; the bad thing is that Excel doesn't recognize Cell1 as date so if I try to sort the cells "strange" results are produced (Cell1 is sorted after Cell3 and Cell2 which is obviously not the case).

Let me know.

Thanks a lot.

Massimo


#10

Hi Massimo,

Sorry, I just test the following code in previous version:

Cell1.PutValue("5/6/2007,12:00AM", true);

Please try this attached fix. Now it's ok.


#11

Hi Laurence,

I think we are on the right way to solve the problem: the fix you did works fine expect for a little problem.

Everything works fine in this case:

Cell1.PutValue("10/27/2005 09:15:00 AM", true);

but not in this case:

Cell1.PutValue("10/27/2005 9:15:00 AM", true);

As you can see the only difference is the "0" before "9".

Do you think you can fix this "bug" too?

Thanks a lot for your great and fast support.

Massimo


#12

Hi Massimo,

Please try this attached fix.


#13

Hi Laurence,

it works pefectly!

Thanks once more for your great support!

Massimo