MATLAB: How to save matlab file according to excel sheet name

convertexcelmatMATLABsheetstrcatworkbookworksheetxlsreadxlsx

I am trying to convert an .xlsx file to .mat and save the .mat file by the sheet I refer to while importing. I am able to save the .mat file by the .xlsx workbook name automatically by defining the workbook name as a variable and using the srtcat() function. However, when I define the worksheet name as a variable and use the same srtcat() function (with worksheet name variable) the script does not save.

For the file recognition and saving portion of my script I currently have:

 file_list={'filename','filename'};
 sheet_list= {'sheet1','sheet2'};
 for q = 1:length(file_list)
    s = sheet_list{q};
    c = file_list{q};
    e = '.xlsx';
    xlsx_file = xlsread(strcat(c,e),s);
   % ....imports data.... 
save (strcat(c))

When I substitute 's' in for 'c', the code does not save a file. Conversely, when I have save(strcat(c)), it works fine.

Is there another way to save the .mat file by the excel worksheet name?

Thanks.

Best Answer

Try this:

file_list={'filename1', 'filename2'};
sheet_list= {'sheet1', 'sheet2'};
for q = 1 : length(file_list)
  baseFileName = file_list{q};  
  sheetName = sheet_list{q};
  thisInputFileName = sprintf('%s.xlsx', baseFileName);
  if exist(thisInputFileName, 'file')
    % Import data:
    xlsx_file = xlsread(thisInputFileName, sheetName);
    % Create filename for the output .mat file:
    thisOutputFileName = sprintf('%s_%s.mat', baseFileName, sheetName);
    % Export 'xlsx_file' variable contents to mat file:
    save (thisOutputFileName, 'xlsx_file')
  end
end

Related Solutions

MATLAB: Write to an already opened Excel file

If Excel is already running by the time you want to run this with the target file open, you can't just start a new excel instance and open that workbook; it will just create a new file with the same name, and when it comes time to save, you will get the error that the file is already open (anyway that's where I encountered error when I ran your example).

Instead, you need to fetch the open Excel instance

Excel = actxGetRunningServer('Excel.Application')

If you have multiple workbooks open in the excel instance, you'll need to find the one that you want within the list Workbooks.Item

Excel.Workbooks.Item(k).FullName

Such as by looping through and checking its FullName (full path, I think)

MATLAB: Continue loop on other files if current file is deleted within a loop

excel_xlsx=dir('*.xlsx');  % list of the files
for i=1:length(excel_xlsx)
    [~,file]=xlsfinfo(excel_xlsx(i).name);
    L=length(file);
    [~,txt]=xlsread(excel_xlsx(i).name,L,'B2');   % read the text



    if strcmp(txt, 'Number:')
      delete(excel_xlsx(i).name);
      continue;
    end
    [~,txt]=xlsread(excel_xlsx(i).name,1,'B3');   % read the text
    if strcmp(txt, 'PROGRAM')
      delete(excel_xlsx(i).name);
      continue;
    end
    [~,txt]=xlsread(excel_xlsx(i).name,1,'A10');   % read the text
    if strcmp(txt, 'UPPER')
      delete(excel_xlsx(i).name);
      continue;
    end
    [~,txt]=xlsread(excel_xlsx(i).name,1,'A2');   % read the text
    if strcmp(txt, 'Effect')
      [~,sheets] = xlsfinfo(excel_xlsx(i).name);
      xlsprotect(excel_xlsx(i).name,'unprotect_sheet',sheets{1,1});
      xlswrite(excel_xlsx(i).name,' ',1,'F1');
      xlswrite(excel_xlsx(i).name,' ',1,'F2');
      xlswrite(excel_xlsx(i).name,' ',1,'F3');
      xlswrite(excel_xlsx(i).name,' ',1,'E1');
      xlswrite(excel_xlsx(i).name,' ',1,'E2');
      xlswrite(excel_xlsx(i).name,' ',1,'E3');
      xlswrite(excel_xlsx(i).name,' ',1,'E4');
    end
end

However, having all of those xlswrite is inefficient, especially for .xlsx files, as each one can involve rewriting the entire text of the xml file inside the .xlsx archive.

It would be more efficient to read all of the data at the same time. The way reading .xlsx files is implemented, when you put a restriction on which cells to read, that is handled by reading the entire .xml file and throwing away the unwanted cells, so you would be more efficient to just have one xlsread(), do the appropriate tests, modify the appropriate cells, and write the result out if it changed.

Note by the way that you read in the list of sheets early on, so it is not efficient to do a second xlsinfo() .

Could you confirm that you want to read data from the last sheet, but that it is the first sheet that you want to modify E1:F3 and E4 of ?

Best Answer

Related Solutions

MATLAB: Write to an already opened Excel file

MATLAB: Continue loop on other files if current file is deleted within a loop

Related Question