Parallel Toolbox to write many Excel files: How to assign specific job to Workers?

4 views (last 30 days)
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
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).
Muhammad Robith
Muhammad Robith on 5 Apr 2022
Edited: Muhammad Robith on 5 Apr 2022
Yes, this is happens consistently, even when no excel files being open.
EDIT: or maybe this is related to '-:\-----\------\06AC4800' kind of files? L divy out unique L to each worker, but 2 of them generate/need access to this kind of file, which have same name.

Sign in to comment.

Accepted Answer

Edric Ellis
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
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!

Sign in to comment.

More Answers (0)

Products


Release

R2016a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!