Excel writing Problem: find correct row, write to empty cell
11 views (last 30 days)
Show older comments
Hello Community,
I have a fairly advanced excel writing task. I have 2 types filenames that I need to write to specific cells in a spreadsheet. I acquire these names from user selection in a GUI. The files are named in this fashion: Label_SLR_LL_S1_0006_v1
Segment_SCH_RL_S3_0008_v1
So the files with "Label" are written to the Label and column and so forth.
First problem is I need to write to the next empty row instead of overwriting existing data. Ideally, I would like to check to see if the data already exist in the column, if it does, overwrite, if not find empty cell and write.
The second problem is the sheet contains data that is organized by row in this fashion:
2009-10-06 09-26-15_SHC_RL_S1_0002FinalData.mat
2009-10-06 09-28-01_SHC_RL_S2_0002FinalData.mat
2009-10-06 09-37-31_SHC_LL_S1_0002FinalData.mat
2009-10-06 09-39-18_SHC_LL_S2_0002FinalData.mat
2009-10-06 09-48-43_RHA_RL_S2_0002FinalData.mat
2009-10-06 09-50-06_RHA_RL_S3_0002FinalData.mat
2009-10-06 10-09-46_SLR_RL_S1_0002FinalData.mat
So the keywords are
SLR_LL_S1
SHC_LL_S2
RHA_RL_S3
and so forth Each row in the the sheet contains data specific to that identifer. I need to be able to write my filenames:
Label_SLR_LL_S1_0006_v1
Segment_SCH_RL_S3_0008_v1
into the correct row based on the keywords.
The second part of my problem might not be necessary if a simple sort in excel can put my filenames in the correct order? I dont think it can because of how they are named.
I realize this a hefty question and any suggestion are greatly appreciated. I litteraly spent over 3 hours searching the Answers and newsgroup but couldt find a solution to this problem.
5 Comments
Fangjun Jiang
on 3 Aug 2011
So, to summarize, you have nx3 strings in an Excel file, you want to insert new strings into the right cell. The right cell means that the strings with the same keywords are grouped together. I assume you want to insert at least 3 strings at a time, with one for each of the "Header", "Label" and "Segment" column. These 3 strings shall have the same keywords.
Accepted Answer
Fangjun Jiang
on 3 Aug 2011
There are still things you didn't clarify. The data in the column "Header", are there multiple lines of file name that contain the same key? If not,, the following code should be good enough.
NewFileName='Label_SLR_LL_S1_0006_v1';
if strfind(NewFileName,'Label_')
Column=2;
Key=NewFileName(7:16);
elseif strfind(NewFileName,'Segment_');
Column=3;
Key=NewFileName(9:18);
else
error('Wrong File Name.');
end
ExcelFile='MyData.xls';
[Dummy,Dummy,Raw]=xlsread(ExcelFile);
Index=strfind(Raw(:,1),Key);
Index=find(~cellfun('isempty',Index));
Raw{Index,Column}=NewFileName;
xslwrite(ExcelFile,Raw);
9 Comments
Fangjun Jiang
on 3 Aug 2011
If the data in your first column could have duplicated key word, how do you decide which row to put if the new file name has that key word? This is a very important question you have to be clear at the beginning.
More Answers (1)
Fangjun Jiang
on 3 Aug 2011
You can't expect an exact code to solve your problem with so much details.
7 Comments
Fangjun Jiang
on 4 Aug 2011
Doug Hull provided lots of tutorial video over there, you can watch them whenever you like. Not every line can be put in a break point. You don't need to actually. Have a few break point and then you can press F10 to run line by line.
See Also
Categories
Find more on Get Started with MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!