readtable, excel, date base

5 次查看（过去 30 天）
Bruce
Bruce 2021-3-3
评论： Bruce 2021-3-4
How can I tell readtable what the appropriate excel base date is?
  3 个评论
显示 1更早的评论
Bruce
Bruce 2021-3-3
R2020a, and I'll get you the file when I'm back at work tomorrow.
Bruce
Bruce 2021-3-4
Here is sample.xlsx and sample.mat. sample.mat contains the results of
data = readtable('sample.xlsx','sheet','Results','PreserveVariableNames',true);

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Cris LaPierre
Cris LaPierre 2021-3-3
编辑：Cris LaPierre 2021-3-3
Perhaps with the DatetimeType name value pair. You can find details in the Name-Value Pair Arguments section of the documentation under Text and Spreadsheet files.
One of the options is exceldatenum.
Otherwise, you might consider using import options. You can use setvarops to set parameters for how datetimes are interpretted. See this example. The dateType property might provide you the setting you need.
  3 个评论
显示 1更早的评论
Cris LaPierre
Cris LaPierre 2021-3-3
If the original question is still how to tell readtable what base date to use, then look into the dateType property I linked to above.
If the question is instead how to determine what date system the workbook is using, I'm afraid I don't know of a way to do that programmatically.
Cris LaPierre
Cris LaPierre 2021-3-3
编辑：Cris LaPierre 2021-3-3
Ok, I may have found a way (idea source). It relies on creating an activex connection to the workbook. Once you've done that, you can query the property. I first recorded a macro in Excel of me toggling the 1904 date system setting on then off. The macro contained the following
ActiveWorkbook.Date1904 = True
ActiveWorkbook.Date1904 = False
That gives me a hint at what property name I should try to query.
Looking on the forum, I found this post about how to create an ActiveX connection to an Excel Workbook in MATLAB. I modified the code from that post to the following.
% Specify file name
file = 'D:\Applications\MATLAB701\work\example.xls'; % This must be full path name
% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
% Open Excel file
Workbook=Workbooks.Open(file);
% Determine if 1904 date system was used
is1904 = Workbook.Date1904
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;
The result is a logical value (1 or 0) for is1904 indicating whether the 1904 date system is used in the indicated workbook or not. That can be used to select which dateType is used for importing the data.
Just note that the workbook must not be open in Excel when this code runs.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by