How to import specific sections of Excel file and print output

2 次查看（过去 30 天）
Emily
Emily 2022-6-22
编辑： Saksham Gupta 2022-6-23
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.

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

采纳的回答

Saksham Gupta
Saksham Gupta 2022-6-22
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")
  2 个评论
Emily
Emily 2022-6-22
编辑：Emily 2022-6-22
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
Saksham Gupta 2022-6-23
编辑：Saksham Gupta 2022-6-23
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

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by