Problem for dates

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

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.

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

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);

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

Hi Massimo,

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

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

Massimo

Hi Massimo,

Please try this attached fix. It's fixed.

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

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.

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

Hi Massimo,

Please try this attached fix.

Hi Laurence,

it works pefectly!

Thanks once more for your great support!

Massimo