通过javascript（activexobject）创建excel单元格时如何自定义日期格式？ [英] How to customize date format when creating excel cells through javascript (activexobject)?

问题描述

我正在使用新的 ActiveXObject（Excel.application）在JavaScript中将HTML表格转换为Excel。勉强地，我循环遍历表单元格，并将值插入excel中的相应单元格中：

  //为每个表单元格
 oSheet.Cells（x，y）.value = cell.innerText; 
  

问题是当单元格的日期格式为dd-mm-yyyy（例如10-09-2008），excel将读作mm-dd-yyyy（即2008年10月09日）。我试图指定 NumberFormat 如下：

  oSheet.Cells（x， y）.NumberFormat ='dd-mm-yyyy'; 
  

但它没有任何效果。似乎这只会影响excel 显示值，而不是解析。我现在唯一的解决办法就是换掉这个日期：

  var txt = cell.innerText; 
 if（/ ^（\d\d） - （\d\d）-\d\d\d\d $ /。test（txt））txt = txt。替换（/ ^（\d\d） - （\d\d）/，'$ 2- $ 1'）; 
  

但我担心这不是通用的，不同的机器设置会失败。 p>

有没有办法具体说明如何excel 解析输入值？

解决方案

在Vbscript中，我们用

 解决这个问题如果IsDate（Cell.Value）Then 
 Cell.Value = DateValue（Cell.Value）
如果
  

也许，在java脚本中也需要使用相同的方法。

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?

解决方案

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.

这篇关于通过javascript（activexobject）创建excel单元格时如何自定义日期格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！