How To Customize Date Format When Creating Excel Cells Through Javascript (activexobject)?

I'm trying to convert an HTML table to Excel in Javascript using new ActiveXObject('Excel.application'). Bascially I loop through table cells and insert the value to the correspond

Solution 1:

You can avoid Excel's date parsing by entering the data using its native 'serial' date format. e.g '22nd Dec 08' is 39804 as an Excel serial date. (See here for a good explanation of these)

Then format the cell as you did before.

Solution 2:

1. determine what culture-neutral date formats excel supports

2. use javascript to parse your date string and output in the an appropriate format

I don't know what formats excel supports but you'd want something like .net's round trip or sortable formats, where it will always be read consistently.

for #2, if you can trust javascript to construct an appropriate date from whatever string you feed it that's fine. if you're not sure about that you might look at a library like datejs where you can be more specific about what you want to happen.

Solution 3:

Instead of

oSheet.Cells(x,y).NumberFormat = 'dd-mm-yyyy'; 

set this:

oSheet.Cells(x,y).NumberFormat = 14;

Solution 4:

In Vbscript, we use to resolve this by

If IsDate( Cell.Value ) Then
         Cell.Value = DateValue ( Cell.Value )
    End If

Maybe, In java script also you need to play with same approach.

Solution 5:

I've tried your code but at end of the process, I re-applied format to the columns containing dates. It works fine, no matter what local language you have configurated yor machine.

Being my excel object defined as 'template', as soon as I got it data filled, I applied (just for example):

template.ActiveSheet.Range("D10:F99").NumberFormat = "dd/MMM/yyyy;@";

best regards