How to import specific sections of Excel file and print output

I'm trying to create a GUI that will ask the user for a folder path and do some calculations based on it.

The output should look like

Day TotalTemp(C) TotalCost

---------------------------

Day1 103 203

Day2 93 352

Day3 95 278

I'm not sure about several things, and this is the code I have written so far.

myFolder='C:\Users\Training 1'; %path to excel files placeholder

filePattern = fullfile(myFolder,'i', '*.xlsx');

theFiles = dir(filePattern);

for k=1: length(theFiles)

baseFileName= theFiles(k).name;

fullFileName=fullfile(myFolder, baseFileName);

data1{k} =xlsread(theFiles(k), 'Sheet1','F10:');

totaltemp{k}= sum(data1{k});

data2{k} =xlsread(theFiles(k), 'Sheet2','A9:');

totalcost{k}= sum(data2{k});

end

1. How to extract the "Day#" portion from the file names (EX: Training 1_Day1_East.xslx) and put it in a column.

2. How to only grab data from specific cell sheets (EX:Calculate the sum of Sheet2 F10 to end of row 10).

3. How to print the output to look like the example above.

Answer 1

[Saksham Gupta]

For the 1st query "How to extract the "Day#" portion from the file names (EX: Training 1_Day1_East.xslx) and put it in a column" :

As per my understanding, you wish to find a pattern inside filename string.

You can use the strfind function to find pattern inside string

Below is a sample code

str="Training 1_Day1_East.xslx"; 
a=strfind(str,"Day"); 

The variable ‘a’ in the above code will have indices to all the occurrences of "Day".

If you are sure that Day will always be between underscores and there are only 2 underscores in the name, you may use the below line of code to extract Day# perfectly.

strfind(str,"_") 

For the 2nd query "How to only grab data from specific cell sheets":

As per my understanding, you wish to extract data from few cells only instead of complete file.

To get specific data from excel file, you may check ‘Range’ attribute in readtable.

For the 3rd query "How to print the output to look like the example above":

As per my understanding, you wish to print output in the formatted manner.

sprintf can be used for formatted printing. You use ‘\t’ to give extra tab spaces between column names and ‘\n’ for printing in new line.

Below is a sample code

sprintf("FirstName\t\tLastName\n--------------------------------\nSaksham\t\tGupta")

[Emily]

Thank you for pointing me to the right direction. I was able to get the name problem solved with extractBetween and got the information I needed for the sheet range.

I'm still stuck at printing the output that I want. I thought it'll be easier to put it in a table, but having trouble looping all the files in the folders.

filePattern = fullfile(myFolder, '*.xlsx');
theFiles = dir(filePattern);


for k=1: length(theFiles)
    baseFileName= theFiles(k).name;
    fullFileName=fullfile(myFolder, baseFileName);
    
    Day{k}=extractBetween(fullFileName,"Day","_");
    
    data1{k} =xlsread(theFiles(k), 2,'F10:end10');
    TotalTemp{k}= sum(data1{k}); 
    
    data2{k} =xlsread(theFiles(k), 3,'A10:end10');
    TotalCost{k}= sum(data2{k}); 
    
    T=table(Day, TotalTemp, TotalCost)
    
end

[Saksham Gupta]

As per my understanding of your code, you are looping well but not able to store data in table properly.

Try this code :

for k=1:5
    
    data(k,1).Day=k;
    data(k,1).TotalTemp=k;
    data(k,1).TotalCost=k;
    
end

T = [struct2table(data)];

Change the values as per your wish