Matlab中使用readtable按Excel显示格式导入日期时间数据

问题背景 #

我有多个包含日期时间变量的Excel文件/工作表，部分数据无时间组件，导致单元格格式分为「Date」（dd/MM/yyyy）和「Custom」（dd/MM/yyyy HH:mm）两类。

尝试用readtable结合setvaropts以字符类型导入，结果不符合预期：

opts = detectImportOptions(xls_path);
opts=setvaropts(opts,{'my_datetime_var','excel_format','excel_display'},'Type','char');
data_as_char=readtable(xls_path,opts)

输出结果：

data_as_char =

  2×3 table

          my_datetime_var                  excel_format               excel_display    
    ___________________________    ____________________________    ____________________

    {'2023-03-08 00:00:00.000'}    {'Date'                    }    {'08/03/2023'      }
    {'2023-03-09 12:01:00.000'}    {'Custom: dd/mm/jjjj uu:mm'}    {'09/03/2023 12:01'}

核心问题：my_datetime_var显示格式与excel_display不符，且无时间组件的数据被自动添加00:00:00.000时间部分。

旧函数xlsread能保留显示格式，但已被官方弃用：

[~,txt] = xlsread(xls_path)

输出：

txt =

  3×3 cell array

    {'my_datetime_var'    }    {'excel_format'            }    {'excel_display'     }
    {'08/03/2023'         }    {'Date'                    }    {'"08/03/2023"'      }
    {'09/03/2023 12:01:00'}    {'Custom: dd/mm/jjjj uu:mm'}    {'"09/03/2023 12:01"'}

解决方案 #

方法1：转CSV后读取（最简单直接） #

• 先将Excel文件另存为CSV格式（保存时确保选择保留单元格显示文本）
• 用readtable读取CSV文件，指定变量类型为字符：

opts = detectImportOptions('your_converted_file.csv');
opts = setvaropts(opts, 'my_datetime_var', 'Type', 'char');
data = readtable('your_converted_file.csv', opts);

此方法读取的my_datetime_var会完全保留Excel中的原始显示格式，无多余时间组件。

方法2：通过Excel COM接口读取显示文本 #

直接调用Excel COM对象获取单元格的显示文本，再转换为table：

% 启动Excel后台服务
excel = actxserver('Excel.Application');
excel.Visible = false;
workbook = excel.Workbooks.Open(fullfile(pwd, xls_path));
sheet = workbook.Sheets.Item(1); % 目标工作表索引

% 获取所有单元格的显示文本
range = sheet.UsedRange;
cell_text = range.Value;

% 关闭资源
workbook.Close(false);
excel.Quit;
excel.delete;

% 转换为table
data = cell2table(cell_text(2:end,:), 'VariableNames', cell_text(1,:));

该方法直接读取Excel单元格的原始显示内容，完全匹配需求，且不依赖弃用函数。

方法3：导入datetime后按需格式化 #

如果需要保留datetime类型做后续计算，可导入后根据excel_format列的信息转换为对应格式的字符：

opts = detectImportOptions(xls_path);
opts = setvaropts(opts, 'my_datetime_var', 'Type', 'datetime');
data = readtable(xls_path, opts);

% 根据原始格式转换显示文本
data.formatted_datetime = arrayfun(@(dt, fmt) ...
    contains(fmt, 'HH:mm') ? char(datetime(dt, 'Format', 'dd/MM/yyyy HH:mm')) : char(datetime(dt, 'Format', 'dd/MM/yyyy')), ...
    data.my_datetime_var, data.excel_format, 'UniformOutput', false);

此方法兼顾数据计算需求和显示格式准确性。

内容的提问来源于stack exchange，提问作者mrroy