Extract data from specific cells across multiple Excel sheets

Question

Liam 2023-3-24

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1934509-extract-data-from-specific-cells-across-multiple-excel-sheets

评论： Liam 2023-3-26

Hi, I have a xlsx file and am looking to extract data from multiple specific cells across multiple spreadsheets in order to then run some stats on it.

What I want to do:

Read the same specific cells containing numetrical data across multiple sheets (i.e A1,B1,C1 in sheet 1, A1,B1,C1 in sheet 2)
Assign this data to a new variable (i.e all A1 cells = var1, all B1 cells = B2 etc.)
Each line of the array to be assigned an ID
Export this to a new excel spreadsheet

There are also some specific text cells where that ideally I would like to read in text also, but this isn't essential.

If anyone could assist in helping with this I would be really grateful

Thanks

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

drummer 2023-3-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1934509-extract-data-from-specific-cells-across-multiple-excel-sheets#answer_1200184

Dear @Liam Jones, could you please share some of what you have worked on so far?

You can guide yourself through this link.

Let us know if you nailed it (or not).

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Liam 2023-3-26

在 MATLAB Online 中打开

Hi @drummer thanks for responding, I was looking at that documentation but just not getting it first time round. I managed to get what I was after digesting everything a bit more. Leaving it here in case it helps anyone

clear, close all, clc
data =  table();
tempData = table();
nSheets = 3;
sheetNames = {'Sheet 1','Sheet 2','Sheet 3'};
file = 'MyFile.xlsx';
for i = 1:nSheets
    name = sheetNames{i};
    tempData.ID = i;
    tempData.Var1 = readtable(file,'Range','A1','Sheet',name,'ReadVariableNames',false);
    tempData.Var2 = readtable(file,'Range','A2','Sheet',name,'ReadVariableNames',false);
    tempData.Var3 = sum(table2array((readtable(file,'Range','D12:D15','Sheet',Name,'ReadVariableNames',false))));
    Data = [data;tempData];
end
Data = splitvars(Data) 
writetable(Data,'Data.xls')