Matlab: Saving data in excel then extracting data for calculations by using a date serial as the search parameter.

2 views (last 30 days)
So what I am trying to do is to save data into an excel document sorted by the date with 5 inputs aligned in the columns next to it. I have been able to create the excel file but now I am struggling with how to extract the inputs by searching for the matching date as selected by the user.
If someone can point me in the right direction that would be great. I am pretty sure I need to use a 'if-statement' so the script reads the excel file until it finds a matching date, but I am not to sure how to search an excel document by the date.
I am using the serial date for my code (i.e. 737400 = 12-Dec-2018)
This is the code I currently have:
% ============= DATA ENTRY WINDOW ===============
function cbDataEntry(~, ~)
figure('Position', [30, 30, 400, 400], ...
'Name', 'Data Entry', ...
'MenuBar', 'none', ...
'NumberTitle', 'off', ...
'Resize', 'off');
uicontrol('Style', 'Text', ... % Date Text
'String', 'Date:', ...
'Horizontalalignment', 'left', ...
'Position', [15 370 40 20]);
dateEntry = uicontrol('Style', 'Edit', ... % Date Display
'Position', [45 372 100 20], ...
'BackgroundColor', 'w');
uicontrol('Style', 'Pushbutton', ... % Date Select
'String', 'Select Date', ...
'Position', [160 372 100 20], ...
'callback', @cbCalendar);
uicontrol('Style', 'pushbutton', ... % Save Button
'String', 'Save Data', ...
'Position', [210 20 80 25], ...
'callback', @cbSave);
uicontrol('Style', 'pushbutton', ... % Close Button
'String', 'Close', ...
'Position', [300 20 80 25], ...
'callback', @cbClose);
uicontrol('Style', 'Text', ... % Input1 Text
'String', 'Input1:', ...
'Horizontalalignment', 'left', ...
'Position', [15 300 40 20])
Input1 = uicontrol('Style', 'Edit', ... % Input1 Entry
'callback', @cbInput1, ...
'Position', [50 302 75 20]);
uicontrol('Style', 'Text', ... % Input2 Text
'String', 'Input2:', ...
'Horizontalalignment', 'left', ...
'Position', [15 270 40 20])
Input2 = uicontrol('Style', 'Edit', ... % Input2 Entry
'callback', @cbInput2, ...
'Position', [50 272 75 20]);
uicontrol('Style', 'Text', ... % Input3 Text
'String', 'Input3:', ...
'Horizontalalignment', 'left', ...
'Position', [15 240 40 20])
Input3 = uicontrol('Style', 'Edit', ... % Input3 Entry
'callback', @cbInput3, ...
'Position', [50 242 75 20]);
uicontrol('Style', 'Text', ... % Input4 Text
'String', 'Input4:', ...
'Horizontalalignment', 'left', ...
'Position', [15 210 40 20])
Input4 = uicontrol('Style', 'Edit', ... % Input4 Entry
'callback', @cbInput4, ...
'Position', [50 212 75 20]);
uicontrol('Style', 'Text', ... % Input5 Text
'String', 'Input5:', ...
'Horizontalalignment', 'left', ...
'Position', [15 180 40 20])
Input5 = uicontrol('Style', 'Edit', ... % Input5 Entry
'callback', @cbInput5, ...
'Position', [50 182 75 20]);
function cbCalendar(~, ~) % Calendar
global dates
uicalendar('SelectionType', 1, ...
'DestinationUI', {dateEntry, 'String'});
waitfor(dateEntry, 'String');
d = get(dateEntry, 'String');
dateformat = 'dd-mmm-yyyy';
dates = datenum(d,dateformat);
end
function cbInput1(~, ~) % Input1
global x
x = str2double(Input1.String);
end
function cbInput2(~, ~) % Input2
global y
y = str2double(Input2.String);
end
function cbInput3(~, ~) % Input3
global z
z = str2double(Input3.String);
end
function cbInput4(~, ~) % Input4
global g
g = str2double(Input4.String);
end
function cbInput5(~, ~) % Input5
global h
h = str2double(Input5.String);
end
function cbSave(~, ~) % Save and create excel spreadsheet <<<===== WHERE I CREATE THE SPREADSHEET
global dates x y z g h
filename = 'DataFile.xlsx';
fileExist = exist(filename,'file');
if fileExist==0
title = {'Date ', 'Input1', 'Input2', 'Input3', ...
'Input4', 'Input5'};
xlswrite(filename,title);
else
[~,~,input] = xlsread(filename);
new_data = {dates, x, y, z, g, h};
output = cat(1,input,new_data);
xlswrite(filename,output);
end
end
function cbClose(~, ~) % Close
close
end
end
% ============= DATA VIEWING WINDOW =============
function cbDataLoad(~, ~)
figure('Position', [30, 30, 400, 400], ...
'Name', 'Data Viewer', ...
'MenuBar', 'none', ...
'NumberTitle', 'off', ...
'Resize', 'off');
uicontrol('Style', 'Text', ... % Date Text
'String', 'Date:', ...
'Horizontalalignment', 'left', ...
'Position', [15 370 40 20]);
dateSelect = uicontrol('Style', 'Edit', ... % Date Display
'Position', [45 372 100 20], ...
'BackgroundColor', 'w');
uicontrol('Style', 'Pushbutton', ... % Date Select
'String', 'Select Date', ...
'Position', [160 372 100 20], ...
'callback', @cbCalendar);
uicontrol('Style', 'pushbutton', ... % Load Button
'String', 'Load Data', ...
'Position', [210 20 80 25], ...
'callback', @cbLoad);
uicontrol('Style', 'pushbutton', ... % Close
'String', 'Close', ...
'Position', [300 20 80 25], ...
'callback', @cbClose);
function cbCalendar(~, ~) % Calendar
global dates2
uicalendar('SelectionType', 1, ...
'DestinationUI', {dateSelect, 'String'});
waitfor(dateSelect, 'String');
d = get(dateSelect, 'String');
dateformat = 'dd-mmm-yyyy';
dates2 = datenum(d,dateformat);
end
function cbLoad(~, ~) % Load Data from spreadsheet and make perform calculations <<<==== WHERE I WANT TO GET THE DATA BACK
global dates dates2
filename = 'DataFile.xlsx';
if dates == dates2
end
end
function cbClose(~, ~) % Close
close
end
end
  5 Comments
Wyatt Guggisberg
Wyatt Guggisberg on 6 Dec 2018
Ok that works beautifully! Thank you very much, you just saved me a boat load of time and frustration.
function cbLoad(~, ~)
global dates2
DataTable = readtable('DataFile.xlsx');
index = DataTable.Date == dates2;
Values = DataTable(index,:);
disp(Values)
q = Values.Input1
w = Values.Input2
e = Values.Input3
r = Values.Input4
t = Values.Input5
end

Sign in to comment.

Accepted Answer

Kevin Chng
Kevin Chng on 6 Dec 2018
if you want load data from excel, you should
tableA = readtable('DataFile.xlsx')
Here you may use indexing to get the i th of rows when the dates is match, for example
index = tableA.dates == dates2
newTable = tableA(index,:)

More Answers (0)

Products


Release

R2018b

Community Treasure Hunt

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

Start Hunting!