CODEWITHSUNDEEP
javascriptexceldateactivexformat
jackysee
jackysee

Reputation: 2061

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 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?

Upvotes: 1

Views: 11900

Answers (5)

lakshmanaraj
lakshmanaraj

Reputation: 4175

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.

Upvotes: 0

user302084
user302084

Reputation: 176

Instead of 

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

set this:

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

Upvotes: 0

Oscar Goldman
Oscar Goldman

Reputation: 1

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

Upvotes: -1

lincolnk
lincolnk

Reputation: 11238

  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.

Upvotes: 0

Hobbo
Hobbo

Reputation: 612

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.

Upvotes: 0

Related Questions