Extract data from specific cells across multiple Excel sheets

17 次查看（过去 30 天）
Liam
Liam 2023-3-24
评论： 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:
  1. 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)
  2. Assign this data to a new variable (i.e all A1 cells = var1, all B1 cells = B2 etc.)
  3. Each line of the array to be assigned an ID
  4. 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

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

回答（1 个）

drummer
drummer 2023-3-24
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 个评论
Liam
Liam 2023-3-26
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')

请先登录，再进行评论。

类别

在 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