OADate 问题汇总

关于 OADate 常见的问题汇总

相信有用过日期并导出过 JSON 的小伙伴应该不难发现，当单元格的值是一个日期时，我们会将其存储为 OADate。

这是为了解决日期的序列化以及时区问题，所以我们用了这样一个特殊的方式保存日期。

Excel 也是对于日期格式的保存策略也是如此。在此做一个 OADate 格式造成的各种困扰做一个总结

问题 1：OADate 和 1900 年的关系

首先，关于 OADate。在我们取日期格式的值的时候发现拿到的值是个类似/OADate(42065)/这样的格式。

这个是 UTC 时间 1970/1/1 日到该日期的毫秒数。为了解决日期的序列化以及时区问题，所以我们用了这样一个特殊的方式保存日期。

SpreadJS 的处理方式是相当于把 OADate 当做一种“媒介”，在 SpreadJS 中我们会正常处理显示日期，存到数据库用的是 OADate，从数据库读取出来，然后拿到 SpreadJS 展示也还是正常的日期。

如果需要将 OADate 从数据库读取，做一些其他的数据处理，可以搜索相关语言对 OADate 的处理方法。

关于 1900 年这个时间问题，这是 Excel 和 js 日期计算的差异。在 Excel 中会把 1900 年当做闰年计算，而实际上 1900 年是平年，而闰年比平年多一天，所以 Excel 在 1900 年 2 月 29 前会多一天，也就是与 js 差一天。而在 1900 年 3 月 1 日后，js 的时间是与 Excel 一致了。

如果需要与 Excel 保持一致，则需要判断单元格的值是否在 1900/1/0 ~ 1900/2/29 这个区间，如果在，则就在 SpreadJS 的日期中减一。

顺带一说，OADate 是微软的时间格式，也就是后台 C#传到前端就是一个 OADate 的格式。

问题 2：为什么通过 getDataSource 方法获取到的日期数据是 OADate 格式的

问题代码：

function initSpreadJs(spread) {
  var spreadNS = GC.Spread.Sheets;

  var sheet = spread.getActiveSheet();

  var colInfos = [
    { name: "date", displayName: "日期", formatter: "yyyy-MM-dd" },
  ];

  sheet.bindColumns(colInfos);

  sheet.setDataSource([{ data: "1" }]);
}

原因：代码中 formatter 会改变数据类型，SpreadJS 会自动将之识别为日期格式，因此存储在 SpreadJS 中的值就是 OADate 类型了，且 autoFormatter 的机制触发也会发生该情况。如果希望避免这种问题，建议将 formatter 这一步操作放到后端处理，前端直接拿取后端返回的字符串即可

问题 3：如何将输入的日期传输到后台是字符串类型，从后台拿到的也是字符串类型

方式如下：

1. 通过代码：setValue 时给文本，比如 sheet.setValue(0,0,"2011-11-11");

2. 通过单元格格式代码：sheet.setFormatter(-1,3, "@");

如果是想让整个工作表都设置为单元格样式，那么可以通过 defaultStyle 来实现

var defaultStyle = sheet.getDefaultStyle();
defaultStyle.formatter = "@";
sheet.setDefaultStyle(defaultStyle);

3. 通过 UI 界面：先设置单元格格式为文本单元格,然后输入日期字符串值。

上述操作后，单元格记录的是文本值，你获取值拿到的也是字符串。且后续你可以继续设置日期单元格格式，来定义此日期字符串以需要的日期格式来显示

问题 4：日常日期格式在不同环境下转换为 OADate 格式

C#环境

DateTime date = DateTime.Now;
double oadate = date.ToOADate();

Python 环境

from datetime import datetime

date = datetime.now()
oadate = (date - datetime(1899, 12, 30)).total_seconds() / (24 * 60 * 60)

JS 环境

var date = new Date();
var oadate =
  (date.getTime() - Date.parse("1899-12-30")) / (24 * 60 * 60 * 1000);

Java 环境

import java.util.Date;

Date date = new Date();
double oadate = (date.getTime() - new Date(1899, 11, 30).getTime()) / (24 * 60 * 60 * 1000.0);

如果使用的是葡萄城的 GCExcel 产品，仅需一个属性设置便可以自动将日期字符串转换为 OADate 类型

// 例如日期为："日期": "2011-11-11 11:11:22",
workbook.setAutoParse(true);// 在调用数据绑定前，设置该属性即可自动转换类型

问题 5：OADate 格式在不同环境下转换为日常日期格式

JS 环境

// OADate类型转日常使用日期格式
function fromOADate(date) {
  // 正则表达式
  var oaDateReg = new RegExp(
    "^/OADate\\(([-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?)\\)/\\s*"
  );

  if (typeof date === "string" && oaDateReg.test(date)) {
    var oadate = parseFloat(date.match(oaDateReg)[1]);
    var ms =
      (oadate * 86400000 * 1440 -
        25569 * 86400000 * 1440 +
        new Date((oadate - 25569) * 86400000).getTimezoneOffset() * 86400000) /
      1440;
    return new Date(ms);
  } else {
    return date;
  }
}

另外，还有一个取巧的办法就是通过 tag 进行一个转换，SpreadJS 的 tag 会将存入的值按照给定的内容进行类型转换

sheet.tag("/OADate(44542)/");
var date = sheet.tag();

如果想要结合数据绑定实现自定义的格式转换，可以这样做

var sheet = spread.getSheet(0);
sheet.autoGenerateColumns = false;
var colInfos = [
  { name: "id", displayName: "ID" },
  { name: "name", displayName: "Name", size: 100 },
  {
    name: "date",
    displayName: "Date",
    size: 80,
    formatter: "yyyy/mm/dd",
    value: function (item, value) {
      if (arguments.length == 2) {
        // 将oaDate类型转换成Date格式
        sheet.tag(value);
        let tempDate = sheet.tag();
        console.log(tempDate);
        if (tempDate instanceof Date) {
          item.date = tempDate.getTime();
        } else {
          item.date = value;
        }
      } else {
        // 时间戳
        if (item.date && item.date > 631123200000) {
          return new Date(item.date);
        } else {
          return item.date;
        }
      }
    },
  },
  { name: "date", displayName: "DateValue", size: 80 },
];
sheet.bindColumns(colInfos);
sheet.setDataSource([{ id: 1, date: 1817740800000 }, {}, {}]);

上述方法也可以通过自定义 convert 方法来实现，代码如下

// { name: "birthday", displayName: "birthday", size: 100, value: birthdayConverter },

function birthdayConverter(item, value) {
  if (arguments.length === 1) {
    return item["birthday"]();
  } else {
    item["birthday"](tryConvertOADateToDate(value));
  }
}
function tryConvertOADateToDate(t) {
  var _jsonOADateRegExp = new RegExp(
    "^/OADate\\(([-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?)\\)/\\s*$ "
  ); // match /OADate(9999.999)/
  if (typeof t === "string" && t.charAt(0) === "/") {
    //Date string is startwith '/'
    if (_jsonOADateRegExp.test(t)) {
      var x = t.match(_jsonOADateRegExp);
      t = fromOADate(parseFloat(x[1]));
    }
  }
  return t;
}

function fromOADate(oadate) {
  var offsetDay = oadate - 25569;
  var date = new Date(offsetDay * 86400000); // multiply 86400000 first then do divide. it will cause some float precision error if the order is not. // 2014/10/17 ben.yin here is a "+1" or "-1", is for javascript divide low precision, it will loss last digit precision.So here add 1, for loss, for result right. // add 1 when after 1987, sub 1 when before 1987
  var adjustValue = offsetDay >= 0 ? 1 : -1;
  return new Date(
    (oadate * 86400000 * 1440 +
      adjustValue -
      25569 * 86400000 * 1440 +
      date.getTimezoneOffset() * 86400000) /
      1440
  );
}

Java 环境

public class Test {
    public static void main(String[] args) throws ParseException {
        long d = 44542;
        double mantissa = d - (long) d;
        double hour = mantissa * 24;
        double min = (hour - (long) hour) * 60;
        double sec = (min - (long) min) * 60;

        SimpleDateFormat myFormat = new SimpleDateFormat("dd MM yyyy");
        Date baseDate = myFormat.parse("30 12 1899");
        Calendar c = Calendar.getInstance();
        c.setTime(baseDate);
        c.add(Calendar.DATE, (int) d);
        c.add(Calendar.HOUR, (int) hour);
        c.add(Calendar.MINUTE, (int) min);
        c.add(Calendar.SECOND, (int) sec);

        System.out.println(c.getTime());
    }
}

.NET 环境

System.DateTime.FromOADate(44542)

问题 6：数据绑定时，如果将日期设置为字符串格式，会导致 Spread 展示时无法自动 format

通过数据绑定中的 value 方法，来设置相互转换。value 本身有一个作用，就是在数据绑定时候，充当一个转换器，在数据源与真实显示的值之间做转换。

通过 value 方法，我们可以自由定义如何进行转换。

核心代码如下：

tableColumn1.value(function (item, value) {
  if (arguments.length > 1) {
    var oaTest = new RegExp(
      "^/OADate\\(([-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?)\\)/\\s*$"
    );
    if (oaTest.test(value)) {
      x = value.match(oaTest);
      var formatter = new GC.Spread.Formatter.GeneralFormatter("yyyy-mm-dd");
      item["date"] = formatter.format(format.parse(x[1]));
    } else {
      item["date"] = value;
    }
  } else {
    return new Date(item.date);
  }
});

这里需要一个判断 arguments.length > 1，通过判断代表双向绑定数据的来和去。

当 arguments.length > 1，里面的逻辑会在页面的数据同步给数据源时走的逻辑，反之则是数据同步给页面时走的逻辑。

完整 demo 请见论坛精华帖：数据绑定日期格式转换

问题 7：想要在 Spread 中嵌入一个日期事件选择器

作者: GrapeCity China | 审核：AlexZ | 更新时间：2023.09.26