How to customize date format when creating excel cells through javascript (activexobject)?

Question

Hi all,

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 corresponding cell in excel:

//for each table cell
oSheet.Cells(x,y).value = cell.innerText;

The problem is that when the cell is in date format of 'dd-mm-yyyy' (e.g. 10-09-2008), excel would read as 'mm-dd-yyyy' (i.e. 09 Oct 2008). I tried to specify NumberFormat like:

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

But it has no effect. It seems that this only affect how excel display the value, not parse. My only solution now is to swap the date like:

var txt = cell.innerText;
if(/^(\d\d)-(\d\d)-\d\d\d\d$/.test(txt)) txt = txt.replace(/^(\d\d)-(\d\d)/,'$2-$1');

But I'm worrying that it is not generic and a differnt machine setting would fail this.

Is there a way to specific how excel parse the input value?

Answer 1

In Vbscript, we use to resolve this by

If IsDate(Cell.Value) Then

     Cell.Value = DateValue(Cell.Value)

End If

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

Answer 2

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.

Answer 3

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.

Answer 4

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