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

Question!

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?

By : jackysee


Answers

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.



Instead of

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

set this:

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


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



This video can help you solving your question :)
By: admin