Copy a sheet from template excel & fill the data to the copied sheet

i have created a small matlab app in which i have an issue with copying data

The issue is i have an excel template & i want to copy the excel template , then fill the data in that template, it works within matlab but once i convert it into desktop app, it only copies the template & it doesn't fill in the data, Kindly help me resolve this

Thanks in advance :)

[filename,folder] = uiputfile("SSE_Report.xlsx");
      
tblRH = table(app.RHFTorque90AngleEditField.Value,app.LHRTorque90AngleEditField.Value,'VariableNames',{'R','L'});
writetable(tblRH,fullfile(folder,filename),'Sheet','Static Steering Effort','Range','F22:G23',AutoFitWidth=false)

tblCentre = table(app.FTorque0AngleEditField.Value,app.RTorque0AngleEditField.Value,'VariableNames',{'R','L'});
writetable(tblCentre,fullfile(folder,filename),'Sheet','Static Steering Effort','Range','F19:G20',AutoFitWidth=false)

tblLH = table(app.RHFTorque100AngleEditField.Value,app.LHRTorque100AngleEditField.Value,'VariableNames',{'R','L'});
writetable(tblLH,fullfile(folder,filename),'Sheet','Static Steering Effort','Range','F25:G26',AutoFitWidth=false)

xlswritefig(app.UIAxes2,fullfile(folder,filename),'Static Steering Effort','C18')

hold("on");

copyfile("Template_Excel.xlsx","SSE_Report.xlsx");

[dpb]

You're writing the updates to whatever is the result of fullfile(folder,filename) but then then you copy over the template file to the equivalent of fullfile(pwd,"SSE_Report.xlsx"). If folder and pwd are the same, you'll have just wiped out everything you just did.

The app almost certainly will start in a different working directory than the one you've been in during development.

In

copyfile("Template_Excel.xlsx","SSE_Report.xlsx");

both filenames should be fully-qualified file names and the copy of the template to the new working file should be before making the updates to not wipe out the changes made.

As one coding stylistic note, I'd suggest to keep with the same input/output paradigm and modern suggested use and replace the xlswrite call with writematrix or writelines.

Also, it would be more efficient to create a single table of the results from F19:G26 and write them once instead of what is now three separate open/write/close cycles. If there's a header in between in the template you could read it on startup and build a cell array of the region and use writecell instead.

[Kavinprasad M]

i understand that and even with copying the template before & writing the data , it doesn't work that way. it only executes writing the data whereas copying the template is missing.

As for creating it in one matrix, i understand but in future it is to be edited & copied in different sheets so i meant to seperate them now to reduce hassle

Is there any other way to make this work?, please let me know

Answer 1

[Image Analyst]

You need to have copy file in advance then write to it.

% Ask use for a filename for our output data
[filename,folder] = uiputfile("SSE_Report.xlsx");
outputFile = fullfile(folder, filename)
% Define the tamplate Excel workbook.
templateFile = fullfile(programFolder, 'Template_Excel.xlsx')
% Copy over template file, if it exists, to output file.
if isfile(templateFile)
    % Get rid of any existing file.  Put it in the recycle bin.
    if isfile(outputFile)
        recycle on;
        delete(outputFile)
    end
    % Create an output file based on the template.
    copyfile(templateFile, outputFile);
else
    % Let user know we didn't find the template.  You will still get an
    % output file though, though not one based on the template.
    warningMessage = sprintf('Warning: template file not found:\n%s', templateFile);
    uiwait(warndlg(warningMessage));
end
% Now create all your data and write it out in one or more writematrix or
% writetable commands.
writetable(yourDataTable, outputFile);

[Kavinprasad M]

Thankyou for your answer, it seems to work very well in MATLAB but once it is converted into standalone app, the template is copied but data is still not updated, i have probably done some mistake, kindly help me resolve ....

app.Result = 'result.xlsx';
              % Ask use for a filename for our output data
              [filename,folder] = uiputfile("SSE_Report.xlsx");
              outputFile = fullfile(folder, filename)
% Define the tamplate Excel workbook.
              templateFile = fullfile(folder, 'Template_Excel.xlsx')
% Copy over template file, if it exists, to output file.
              if isfile(templateFile)
% Get rid of any existing file.  Put it in the recycle bin.
                if isfile(outputFile)
                   recycle on;
                   delete(outputFile)
                end
% Create an output file based on the template.
                copyfile(templateFile, outputFile);
              else 
% Let user know we didn't find the template.  You will still get an
% output file though, though not one based on the template.
              warningMessage = sprintf('Warning: template file not found:\n%s', templateFile);
              uiwait(warndlg(warningMessage));
              end
              
              xlswritefig(app.UIAxes2,fullfile(folder,filename),'Static Steering Effort','C18')
     
              tblRH = table(app.RHFTorque90AngleEditField.Value,app.LHRTorque90AngleEditField.Value,'VariableNames',{'R','L'});
              writetable(tblRH,fullfile(outputFile),'Sheet','Static Steering Effort','Range','F22:G23',AutoFitWidth=false)

              tblCentre = table(app.FTorque0AngleEditField.Value,app.RTorque0AngleEditField.Value,'VariableNames',{'R','L'});
              writetable(tblCentre,fullfile(outputFile),'Sheet','Static Steering Effort','Range','F19:G20',AutoFitWidth=false)

              tblLH = table(app.RHFTorque100AngleEditField.Value,app.LHRTorque100AngleEditField.Value,'VariableNames',{'R','L'});
              writetable(tblLH,fullfile(outputFile),'Sheet','Static Steering Effort','Range','F25:G26',AutoFitWidth=false)

              winopen("SSE_Report.xlsx") 

[dpb]

VERY hard code to read with the indenting as it is and comments way over to the side --

You only remove the old output file it there is a template file; if it isn't found that never happens.

You still are mixing xlswrite with writetable -- I strongly discourage doing that; xlswrite does its own thing about the COM engine knowing nothing about the other instance; I've had cases where trying to write multiple times to the same workbook created issues although generally that was trying to update scatter cells within a workbook by the hundreds, not just a half-dozen, but I'm always leery of repeated immediate access to the same file open/close cycles. SUPPOSED to work but...

You also are still using fullfile(folder,filename) there instead of the defined outputfile variable and, while it shouldn't matter, once you've built it above, there's no point in wrapping it inside fullfille yet again.

writetable(tblLH,fullfile(outputFile),'Sheet','Static Steering Effort','Range','F25:G26',AutoFitWidth=false)

should be

writetable(tblLH,outputFile,'Sheet','Static Steering Effort','Range','F25:G26',AutoFitWidth=false)

BUT, the real problem is probably in

winopen("SSE_Report.xlsx") 

where you're not opening the new outputfile file, but as I pointed out before the equivalent of

winopen(fullfile(pwd,"SSE_Report.xlsx"))

which will NOT be the same between inside MATLAB and the compiled app. Again, that needs be

winopen(outputfile) 

Once you define the output file in the variable, THEN USE IT EXCLUSIVELY, FORSAKING ALL OTHERS!

One more thing to check; if you have ever crashed during debugging, there's a chance there are still "zombie" Excel background processes open; use the Task Manager and verify there are not multiple instances of Excel still running; if so, forcibly kill them (making sure to have closed all open workbooks first, of course).

[Image Analyst]

Yes, this is the common problem of not specifying full path names for files and the uncertainty of exactly where the current working directory is for a compiled stand-alone executable (long explanation, don't want to get into it here). The solution is to just have your template live in the same folder as the executable, and get the template file from there instead of the folder where you want your output to go. Like I said, it's complicated where pwd is - not what you'd expect - so use the attached utility to get it. Then the rest should be mostly the same. See below for improved code:

% Ask user for a filename for their output data workbook.
[filename, outputFolder] = uiputfile("SSE_Report.xlsx");
outputFile = fullfile(outputFolder, filename)

% Define the template Excel workbook.  
% It lives in the same folder as our m-file (if in development mode)
% or the folder of the .exe file (if running a compiled, stand-alone executable).
programFolder = GetExecutableFolder();
templateFile = fullfile(programFolder, 'Template_Excel.xlsx')

% Copy over template file, if it exists, to output file.
if isfile(templateFile)
    % Get rid of any existing file.  Put it in the recycle bin.
    if isfile(outputFile)
        recycle on;
        delete(outputFile)
    end
    % Create an output file based on the template.
    copyfile(templateFile, outputFile);
else
    % Let user know we didn't find the template.  You will still get an
    % output file though, though not one based on the template.
    warningMessage = sprintf('Warning: template file not found:\n%s', templateFile);
    uiwait(warndlg(warningMessage));
end

% Now create all your data and write it out in one or more writematrix or
% writetable commands.
writetable(yourDataTable, outputFile);

[dpb]

The above still only removes/saves an existing output file if a template file also exists...if there's the desire to keep a backup copy around for the one cycle. If there's not that in the program objective, then it's superfluous when there is and inconsistent behavior if isn't (a template, that is).

I don't see that makes the existence of a previous output file dependent upon there being a template file (even though there should be; have gone bypassed/fall through the in case there isn't instead of forcing the user to find/create one in order to go on),

[Image Analyst]

@dpb if he wants to copy any existing file somewhere, he can call copyfile to make a new name and then archive them all to some archive folder(s). I'm not doing that. But I did rework the code so that it sends any existing output file to the recycle bin regardless of whether the template file exists or not. This will make sure the new data does not just blast over existing data and leave the unwanted existing, old data in the workbook.

% Ask user for a filename for their output data workbook.
[filename, outputFolder] = uiputfile("SSE_Report.xlsx");
outputFile = fullfile(outputFolder, filename)

% Get rid of any existing file.  Put it in the recycle bin.
if isfile(outputFile)
    recycle on;
    delete(outputFile)
end

% Define the template Excel workbook.  
% It lives in the same folder as our m-file (if in development mode)
% or the folder of the .exe file (if running a compiled, stand-alone executable).
programFolder = GetExecutableFolder();
templateFile = fullfile(programFolder, 'Template_Excel.xlsx')

 % Copy over template file, if it exists, to output file.
if isfile(templateFile)
    % Create an output file based on the template.
    copyfile(templateFile, outputFile);
else
    % Let user know we didn't find the template.  You will still get an
    % output file though, though not one based on the template.
    warningMessage = sprintf('Warning: template file not found:\n%s', templateFile);
    uiwait(warndlg(warningMessage));
end

% Now create all your data and write it out in one or more writematrix or
% writetable commands.
writetable(yourDataTable, outputFile);

[dpb]

Yes, that is consistent behavior, @Image Analyst ... that's all I was pointing out is that the other was inconsistent... :)

[Kavinprasad M]

Changed as instructed, i don't know what i'm doing wrong here, it works perfect within matlab yet again fails to do so as a standalone app, am i executing the writable & xlswritefig wrong? i appreciate the support T.T

[filename, outputFolder] = uiputfile("SSE_Report.xlsx");
        outputFile = fullfile(outputFolder, filename);

        if isfile(outputFile)
           recycle on;
           delete(outputFile)
        end

        programFolder = GetExecutableFolder();
        templateFile = fullfile(programFolder, 'Template_Excel.xlsx');

        if isfile(templateFile)
           copyfile(templateFile, outputFile);
        else
           warningMessage = sprintf('Warning: template file not found:\n%s', templateFile);
           uiwait(warndlg(warningMessage));
        end
     
        tblRH = table(app.RHFTorque90AngleEditField.Value,app.LHRTorque90AngleEditField.Value,'VariableNames',{'R','L'});
        writetable(tblRH,outputFile,'Sheet','Static Steering Effort','Range','F22:G23',AutoFitWidth=false)
        xlswritefig(app.UIAxes2,outputFile,'Static Steering Effort','C18')

[dpb]

There is no such animal as xlswritefig in base MATLAB product; again I recommend to to move away from deprecated xlswrite to writematrix or writecell although that shouldn't be the cause of an issue.

You don't show a winopen(outputfile) above so can't tell that you opened the correct file.

[Kavinprasad M]

Can writematrix be used to take a plot from UIFigure after edited? If so, I'll replace it right away, also i didn't use winopen but i made sure i opened the right file

I have to use 2 add-on functions outside of base MATLAB (xlswritefig & GetExecutableFolder), if there are alternatives, i would like to add the instead

Thanks again for your support and I'm just new to MATLAB, so i don't know the way around... Please don't mind

[dpb]

Never heard of xlswritefig and never messed with trying to put figures into Excel, but there would be a place to look for a problem; the rest seems straightforward enough (and I'd not doubt anything @Image Analyst thought sufficiently well-enough done to post, he's one of the rock stars).

Will it work without xlswritefig as far as the rest of what you expect?

Have you ensured you have write permission to the outputfile location and that the template file is writeable/editable when created? Seems like I recall there are peculiarities about template files in Excel in that one has to save the template file as a new file; the templates themselves aren't supposed to be written? I'm certainly not expert in all the ins and outs of Excel itself at that usage level; I just create new files from scratch or mung on existing ones.

What if, instead of using a template file the file templatefile is an ordinary Excel file; behavior any different?

Also, back to one of my earlier comments -- if this xlswritefig is a FEX submission or from somewhere else as must be, it's not TMW code, then there's a possibility that during your testing/debugging you've managed to crash and leave "zombie" Excel processes open if it doesn't have a robust cleanup routine installed. Use Task Manager to ensure there's only the one Excel process running (there are typically two processes only); if there are any more, ensure you've closed/saved all open workbooks that are visible and kill all Excel process thread trees - then, when you rerun the next time you'll be sure to have a clean Excel running and no behind-the-scenes links to other copies of the workbook you're trying to mung on.

I'm not sure if there's any way to embed debug info into the compiled app or not other than diagnostic dumps; that's somewhat of a pain since there is no connection to the terminal to dump output to the command window available.

[Image Analyst]

Yes, as @dpb says, I'd use a regular .xlsx file as your template, not a .xlt file. Not sure but if you used an .xlt file it may have quirky things about asking you to save to a different filename than the one it has. And it's also a good suggestion to do control-shift-Esc to see task manager and make sure there are not any zombie Excel instances running before you run your program.

As far as pasting into MS Office apps, see the attached two programs. In ExcelDemo you'll find this:

%==========================================================================================================================
% Create a figure window with a plot (axes control) on it and paste it into the Excel worksheet.
% Adapted from https://www.mathworks.com/matlabcentral/answers/91547-how-do-i-insert-a-matlab-figure-into-an-excel-workbook-through-activex
function PasteFigureIntoExcel(Excel)
try
    % Create sample image from figure	
    hFig = figure; % Bring up a new, separate figure.
    period = pi;
    x = linspace(-2*pi, 2*pi, 400);
    mySineWave = 10 * cos(2 * pi * x / period);
    % Plot it.
    plot(x, mySineWave, 'b-', 'LineWidth', 2);
    xlabel('x', 'FontSize', 20);
    ylabel('y', 'FontSize', 20);
    title('My Sine Wave', 'FontSize', 20);
    grid on;
    
    % Create a filename to save the figure window to disk for temporarily.
    tempImageFullFileName = fullfile(pwd, 'Delete_me.png');	
    print('-dpng', tempImageFullFileName);	% Save it to disk.
    uiwait(msgbox('We saved this plot to disk and will paste it into the workbook.'));
    close(hFig); % Close down figure because we don't need it anymore.
    % Alternative 1 BEGIN.	
    % Get handle to Excel COM Server	
    % 	Excel = actxserver('Excel.Application');	
    % 	% Set it to visible	
    % 	set(Excel,'Visible',1);	
    
    % Optionally, add a Workbook	
    % 	Workbooks = Excel.Workbooks;	
    % 	Workbook = invoke(Workbooks, 'Add');	
    
    % Get a handle to Sheets and select Sheet 1	
    % 	Sheets = Excel.ActiveWorkBook.Sheets;	
    % 	Sheet1 = get(Sheets, 'Item', 1);	
    % 	Sheet1.Activate;
    % Alternative 1 END.
    
    % Alternative 2 BEGIN.	
    % Use whatever sheet is active at the moment.  This should be setup in advance of calling this function.
    currentSheet = Excel.ActiveSheet;
    % Alternative 2 END.	
    
    % Alternative 1 BEGIN.	
    % Get a handle to Shapes for Sheet 1	
    Shapes = currentSheet.Shapes;	
    % Add image	by importing one from an image file on disk.  Place at a certain position.  Last 4 arguments are : x, y, width, height.
    Shapes.AddPicture(tempImageFullFileName, 0, 1, 200, 40, 300, 235);	
    % Alternative 1 END.
    xlMoveAndSize = 1;
    Selection.Placement = xlMoveAndSize;
    % Alternative 2 BEGIN.	
    % Add image	
    % 	Sheet1.invoke('Pictures').Insert(tempImageFullFileName);	
    % Alternative 2 END.	
    
    % Save the workbook and Close Excel	
    % 	invoke(Workbook, 'SaveAs', fullfile(pwd, '\myfile.xls'));	
    % 	invoke(Excel, 'Quit');
    % Delete the temporary image file.
    delete(tempImageFullFileName);
catch ME
    errorMessage = sprintf('Error in function %s() at line %d.\n\nError Message:\n%s', ...
        ME.stack(1).name, ME.stack(1).line, ME.message);
    WarnUser(errorMessage);
end
return;

Of course you'll first have to get the Excel object using ActiveX.

FInally you might take a look at the functions copygraphics and exportgraphics.

[dpb]

"you have write permission to the outputfile location"

I've run into all kinds of OS-set access problems on Win10 that I don't fully understand just what it does and why/how and particularly how to beat it into submission in writing to locations containing executables; it appears by default install on it makes those require Admin privileges. I wonder if the MATLAB distribution of the executables is doing something of that sort as well; I've not tried to deploy a compiled app on this machine; on the uni machine for which I wrote a couple apps I had to have IT log in to be able to even get the executable on the system; it was locked down so tightly couldn't even copy over the testing directory from local machine...

Answer 2

[Kavinprasad M]

Even without the xlswritefig, it seems to have some issues with excel as a standalone app in windows 11. i have template file as .xlsx, i'll change the way i aquire data from table & try it again

Thankyou guys >.<

[Image Analyst]

See the FAQ:

https://matlab.fandom.com/wiki/FAQ#My_standalone_executable_won't_run_on_the_target_computer._What_can_I_try?