Excel Scripts实现单列日期时间拆分至两列的技术求助

问题原因 #

你用getValue()获取到的是Excel内部的日期序列号（比如返回的45231），不是单元格显示的「11/4/2023 11:00 am」文本，直接转字符串拆分自然会出错。

解决方案1：基于显示文本拆分（适合固定显示格式） #

这种方法直接读取单元格显示的文本，按空格拆分日期和时间：

function main(workbook: ExcelScript.Workbook) {
  const mainSheet = workbook.getActiveWorksheet();
  const usedRange = mainSheet.getUsedRange();
  const lastRow = usedRange.getRowCount();
  const dateTimeRange = mainSheet.getRange(`A1:A${lastRow}`);
  
  for (let i = 0; i < lastRow; i++) {
    const cell = dateTimeRange.getCell(i, 0);
    const displayText = cell.getDisplayValue();
    const [datePart, timePart, period] = displayText.split(" ");
    const fullTime = `${timePart} ${period}`;
    
    // 写入日期并设置格式
    cell.setValue(datePart);
    cell.setNumberFormatLocal("m/d/yyyy");
    // 写入时间并设置格式
    const timeCell = mainSheet.getCell(i, 1);
    timeCell.setValue(fullTime);
    timeCell.setNumberFormatLocal("h:mm AM/PM");
  }
}

解决方案2：基于日期对象处理（更稳定，不受显示格式影响） #

直接操作日期对象提取日期和时间，不管单元格显示格式如何都能正常工作：

function main(workbook: ExcelScript.Workbook) {
  const mainSheet = workbook.getActiveWorksheet();
  const usedRange = mainSheet.getUsedRange();
  const lastRow = usedRange.getRowCount();
  const dateTimeRange = mainSheet.getRange(`A1:A${lastRow}`);
  
  for (let i = 0; i < lastRow; i++) {
    const cell = dateTimeRange.getCell(i, 0);
    const dateValue = cell.getValue() as Date;
    if (!(dateValue instanceof Date)) continue; // 跳过非日期单元格
    
    // 提取纯日期（时间置为0）
    const dateOnly = new Date(dateValue.getFullYear(), dateValue.getMonth(), dateValue.getDate());
    // 提取纯时间（日期置为Excel基准日期1900-01-01）
    const timeOnly = new Date(1900, 0, 1, dateValue.getHours(), dateValue.getMinutes(), dateValue.getSeconds());
    
    cell.setValue(dateOnly);
    cell.setNumberFormatLocal("m/d/yyyy");
    mainSheet.getCell(i, 1).setValue(timeOnly);
    mainSheet.getCell(i, 1).setNumberFormatLocal("h:mm AM/PM");
  }
}

内容的提问来源于stack exchange，提问作者Ponoco-nuchi