Can Matlab create an Excel object and write table to it without saving it to a location?

I just want Matlab to create a temp Excel file, open it, write Matlab table data to it, let it hang on the screen, but without saving it to some location. Then user could manually save this Excel file to the desired location.

This is a question related to the base Matlab. But eventually I would like to realize this on Matlab App Server - when users click download data from the server, an Excel object is opened with the data on it, and users can then manually save the file to some folder.

Answer 1

[Umar]

Hi @Yiling,

Let me address your query regarding, “I just want Matlab to create a temp Excel file, open it, write Matlab table data to it, let it hang on the screen, but without saving it to some location. Then user could manually save this Excel file to the desired location.This is a question related to the base Matlab. But eventually I would like to realize this on Matlab App Server - when users click download data from the server, an Excel object is opened with the data on it, and users can then manually save the file to some folder.”

Follow the steps mentioned below.

Step#1: I will start by preparing the generic data to export to Excel.

% Sample data
data = table([1; 2; 3], {'A'; 'B'; 'C'}, 'VariableNames', {'ID', 'Name'});

Step#2: Then, use actxserver to create an instance of Excel that will allow you to manipulate the application programmatically.

     % Create an ActiveX server for Excel
     excelApp = actxserver('Excel.Application');

For more information on actxserver function, please refer to

https://www.mathworks.com/help/matlab/ref/actxserver.html

Step#3: Generate a new Excel workbook where you will write the data.

% Add a new workbook
 workbook = excelApp.Workbooks.Add();

Step#4: Transfer the MATLAB table data into the active worksheet.

   % Access the first worksheet
   sheet = workbook.Sheets.Item(1);

   % Write the table data to the Excel sheet
   % Convert the table to a cell array for easier writing
   dataCell = [data.Properties.VariableNames; table2cell(data)];
   sheet.Range('A1').Resize(size(dataCell, 1), size(dataCell, 2)).Value =          dataCell;

Step#5: Now, make sure that the Excel application is visible to the user, allowing them to interact with it.

    % Make Excel visible to the user
     excelApp.Visible = true;

At this point, the temporary file will remain open, so users can save it wherever they choose. To guide users on how to save the file, you can use a message box before opening Excel:

    % Display instruction message
    msgbox('The data is now open in Excel. To save the file, go to File > Save                 As,and choose your desired location.', 'Instructions', 'modal');

If you're implementing this in a MATLAB App Server context, make sure that the server environment has the necessary permissions to launch Excel and that the users have Excel installed on their local machines. If you have any further questions, please let me know.

[Yiling]

I ran each step as mentioned in Matlab and I got error "Invoke Error, Dispatch Exception: Exception occurred." at Step#4 when running "sheet.Range('A1').Resize(size(dataCell, 1), size(dataCell, 2)).Value = dataCell;"

[Umar]

Hi @ Yiling,

Could you please share your code in order to assist you better.

[Umar]

Hi @Yiling,

The error message "Invoke Error, Dispatch Exception: Exception occurred." at Step#4 suggests that there is an issue with the way the data is being written to the Excel sheet. This typically occurs when the dimensions of the data being written do not match the expected format or when there is an issue with the Excel application itself.The most likely cause of this error is related to the dataCell variable. If dataCell is not properly initialized or if it contains data types that are incompatible with Excel (such as complex numbers or unsupported data types), the Resize method may fail. Additionally, if the Excel application is not properly instantiated or if the specified range is invalid, this could also lead to the error. To resolve this issue, follow these steps:

Check Initialization of dataCell: make sure that dataCell is properly initialized and contains only compatible data types (e.g., numeric or string).

Verify Excel Application State: Make sure that the Excel application is open and that the specified sheet is accessible.

Modify the Code: Here is a revised version of the code that includes checks for the dataCell variable and ensures that the Excel application is properly referenced:

% Assuming 'dataCell' is already defined and contains valid data
if isempty(dataCell)
  error('dataCell is empty. Please provide valid data.');
end

% Create an Excel application instance
excelApp = actxserver('Excel.Application');
excelApp.Visible = true; % Optional: Make Excel visible

% Add a new workbook or reference an existing one
workbook = excelApp.Workbooks.Add; % or   excelApp.Workbooks.Open('your_file.xlsx');

% Reference the desired sheet
sheet = workbook.Sheets.Item(1); % Adjust the index as necessary

% Write data to the specified range
try
  sheet.Range('A1').Resize(size(dataCell, 1), size(dataCell, 
  2)).Value = dataCell;
catch ME
  disp('An error occurred while writing to Excel:');
  disp(ME.message);
end

% Clean up
% workbook.SaveAs('your_file.xlsx'); % Uncomment to save
% excelApp.Quit;
% delete(excelApp);

Please let me know if this helps resolve your issue. Also, @Image Analyst provided solution to your problem as well. I would advise looking into his attachments to see if thief help resolve your problem.

Answer 2

[Image Analyst]

If you're using Windows I know how to do it. You can use ActiveX programming to open Excel as a server then send commands to Excel to do things like export data to it, format cells, save and open workbooks, etc. I'm attaching a full demo, plus a static class where you can call functions to do various things.