Parallel Toolbox to write many Excel files: How to assign specific job to Workers?
1 view (last 30 days)
Show older comments
Muhammad Robith
on 4 Apr 2022
Commented: Muhammad Robith
on 6 Apr 2022
Hello! I have question on using Parallel Toolbox to write many Excel files. Structure-wise, here is the code.
clc, clear
count_day = 1; % row for date and data
for i = 1
for j = 1
for k = 1:30
count_day = count_day+1; % +1, row 1 for time
tanggal_file = [num2str(i) num2str(j) num2str(k)]; % date
% char for excel range
count_day_str = num2str(count_day);
excel_range_str = ['B' count_day_str ':Y' count_day_str];
excel_tanggal = ['A' count_day_str];
% start parfor
parfor L = 1:60
vars_data = rand(1,24,'single'); % data
nama_file = ['paralel' num2str(L) '.xlsx']; % filename
sheet_name = 'Values'; %sheet name
% % write date
xlswrite(nama_file,{tanggal_file},sheet_name,excel_tanggal);
% % write series
xlswrite(nama_file,vars_data,sheet_name,excel_range_str);
end
end
end
end
My goal is to create many excel files (60 in this case), where each files has 30 rows x 25 columns (day+value at each hour). I have no problem (for now) for these rows and columns, but this code always give error: sometimes this code produce 60 excel files (sometimes don't), then I got error with this message
% Error using xlswrite (line 219)
% Invoke Error, Dispatch Exception:
% Source: Microsoft Excel
% Description: Microsoft Excel cannot access the file '-:\-----\------\06AC4800'. There are several possible reasons:
%
% • The file name or path does not exist.
% • The file is being used by another program.
% • The workbook you are trying to save has the same name as a currently open workbook.
% Help File: xlmain11.chm
% Help Context ID: 0
%
% Error in Untitled_tesparfor (line 13)
% parfor L = 1:60
I do believe that either 2nd or 3rd reason are the problems here, where 2 workers works on same L value. So, I have this idea: On a parallel pool with 3 workers, Worker 1 only use 1:20, Worker 2 only use 21:40, the rest is taken by Worker 3.
How can I code that to my 2016a MATLAB? Or do you have any suggestions on what should I do?
Thank You.
2 Comments
Raymond Norris
on 5 Apr 2022
Does this happens consistently? parfor will only divy out unique L to each worker.
I was able to recreate your error once, though I believe I had one of the xlsx files open while the code was running. Do you get the error by letting it run without opening any of the files while the code is running? Though other times MATLAB would leave rows blank in the xlsx file I had open (again, I only saw the error once).
Accepted Answer
Edric Ellis
on 5 Apr 2022
The function xlswrite is no longer recommended, and you should use writematrix instead. The problem you're seeing is because several Excel processes are trying to write to temporary files at the same time, and they are clashing. This problem does not occur when you use writematrix because it (by default) does not use Excel behind the scenes. Here's how you should adapt your parfor loop:
parfor L = 1:60
vars_data = rand(1,24,'single'); % data
nama_file = ['paralel' num2str(L) '.xlsx']; % filename
sheet_name = 'Values'; %sheet name
% % write date
writematrix(vars_data, nama_file, "Sheet", sheet_name, "Range", excel_tanggal);
% % write series
writematrix(vars_data, nama_file, "Sheet", sheet_name, "Range", excel_range_str);
end
3 Comments
Edric Ellis
on 5 Apr 2022
If you can't upgrade yet, then another possible workaround is to make a unique directory each time you want to write a file, and then use movefile to move the resulting file later. I think this will work, but I'm not sure. Ideally the best option is to upgrade!
More Answers (0)
See Also
Categories
Find more on Startup and Shutdown 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!