Thread Subject: writing to Excel in a gui

I need to write a large amount of data to an Excel workbook from a GUI. I would like to cut back on the time it takes to open,write,close for each command. I have tried using xlswrite1 and xlswrite2007, but holding the activex server open doesn't work with the GUI. Is there a way I could weite the data to an activex window int he GUI itself and then write the whole created workbook in a single shot? Or am I stuck writing a page at a time?

On Sep 15, 5:17 pm, "Travis " <sinuso...@hotmail.com> wrote:
> I need to write a large amount of data to an Excel workbook from a GUI.  I would like to cut back on the time it takes to open,write,close for each command.  I have tried using xlswrite1 and xlswrite2007, but holding the activex server open doesn't work with the GUI.  Is there a way I could weite the data to an activex window int he GUI itself and then write the whole created workbook in a single shot?  Or am I stuck writing a page at a time?

------------------------------------------------
You're incorrect. I know because I do it. I use xlswrite1 with a
GUI. I open the activeX connection to Excel. Then I call xlswrite1
(), and then I close the connection. It works fine, even in a GUI,
which I am doing. I open Excel, then while it's open I write data to
various ranges in a worksheet, create new worksheets and write more
data to them, etc. Works fine and much, much faster than xlswrite()
which causes a launch/write/closing every time you call it.

Of course you can do it yourself. Just look inside xlswrite1() to see
how it's done, then copy that. ;-)

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <73bf18c9-a8c8-4533-b99f-3483b7fb9b44@j9g2000vbp.googlegroups.com>...
> On Sep 15, 5:17?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > I need to write a large amount of data to an Excel workbook from a GUI. ?I would like to cut back on the time it takes to open,write,close for each command. ?I have tried using xlswrite1 and xlswrite2007, but holding the activex server open doesn't work with the GUI. ?Is there a way I could weite the data to an activex window int he GUI itself and then write the whole created workbook in a single shot? ?Or am I stuck writing a page at a time?
>
> ------------------------------------------------
> You're incorrect. I know because I do it. I use xlswrite1 with a
> GUI. I open the activeX connection to Excel. Then I call xlswrite1
> (), and then I close the connection. It works fine, even in a GUI,
> which I am doing. I open Excel, then while it's open I write data to
> various ranges in a worksheet, create new worksheets and write more
> data to them, etc. Works fine and much, much faster than xlswrite()
> which causes a launch/write/closing every time you call it.
>
> Of course you can do it yourself. Just look inside xlswrite1() to see
> how it's done, then copy that. ;-)

odd, I have tried using xlswrite1, and xlswrite2007 (for an xlsx file) and it always crashes when it goes to write, but if I change it to just xlswrite, it works fine. I am using the following lines...

Excel = actxserver ('Excel.Application');
File=[cd '\' fnme];
if ~exist(fnme,'file')
    ExcelWorkbook = Excel.workbooks.Add;
    ExcelWorkbook.SaveAs(File,1);
    ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);


invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

those are correct aren't they? Or is there something I need to do differently onside a GUI?

I can get it all to work outside of a GUI, but within a GUI is just doesn't write.

On Sep 21, 1:44 pm, "Travis " <sinuso...@hotmail.com> wrote:
> I can get it all to work outside of a GUI, but within a GUI is just doesn't write.

Travis:

Here's some (modified) code I use:

%----- Open Excel for writing.
Excel = actxserver('Excel.Application');
if ~exist(excelFileNameForOneImage, 'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(excelFileNameForOneImage, 1);
ExcelWorkbook.Close(false);
end
if ~exist(excelFileNameForAll, 'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(excelFileNameForAll, 1);
ExcelWorkbook.Close(false);
end
Excel.Visible = false; % Make Excel visible.
% Open up both workbooks.
invoke(Excel.Workbooks, 'Open', excelFileNameForAll);
% Save the reference to this workbook so that we can later activate
it.
wbAll = Excel.ActiveWorkbook;
invoke(Excel.Workbooks, 'Open', excelFileNameForOneImage);
% Save the reference to this workbook so that we can later activate
it.
wbOne = Excel.ActiveWorkbook;
wbOne.Activate; % Activate workbook for this one image.
% Do the actual writing of the data (41 row by 11 column) to the disk
file.
xlswrite1(excelFileNameForOneImage, meanGridGLs, 'Array Means',
'C3');
ca = {'Sum of this array =', sum(sum(meanGridGLs)), 'layers of
aluminum', 'Sum of gray levels = ', sumOfGLs(j)};
xlswrite1(excelFileNameForOneImage, ca, 'Array Means', 'A1');
[similar code snipped]

% Shut down Excel.
Excel.Quit
Excel.delete
clear Excel;

I have two workbooks, wbAll, and wbOne which correspond to two disk
files, the names of which are in string variables
excelFileNameForOneImage and excelFileNameForAllImages. I change
between them several times writing various things (cell arrays and
numerical arrays) to them (most of which code I snipped out).
It's called within a function that gets run as a result of clicking on
a button in the GUI.
Hope this helps, and you can adapt it for your needs.
ImageAnalyst

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <0fae2be9-e203-49ac-9cfb-9e17a2fce1fe@31g2000vbf.googlegroups.com>...
> On Sep 21, 1:44?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > I can get it all to work outside of a GUI, but within a GUI is just doesn't write.
>
> Travis:
>
> Here's some (modified) code I use:
>
> %----- Open Excel for writing.
> Excel = actxserver('Excel.Application');
> if ~exist(excelFileNameForOneImage, 'file')
> ExcelWorkbook = Excel.workbooks.Add;
> ExcelWorkbook.SaveAs(excelFileNameForOneImage, 1);
> ExcelWorkbook.Close(false);
> end
> if ~exist(excelFileNameForAll, 'file')
> ExcelWorkbook = Excel.workbooks.Add;
> ExcelWorkbook.SaveAs(excelFileNameForAll, 1);
> ExcelWorkbook.Close(false);
> end
> Excel.Visible = false; % Make Excel visible.
> % Open up both workbooks.
> invoke(Excel.Workbooks, 'Open', excelFileNameForAll);
> % Save the reference to this workbook so that we can later activate
> it.
> wbAll = Excel.ActiveWorkbook;
> invoke(Excel.Workbooks, 'Open', excelFileNameForOneImage);
> % Save the reference to this workbook so that we can later activate
> it.
> wbOne = Excel.ActiveWorkbook;
> wbOne.Activate; % Activate workbook for this one image.
> % Do the actual writing of the data (41 row by 11 column) to the disk
> file.
> xlswrite1(excelFileNameForOneImage, meanGridGLs, 'Array Means',
> 'C3');
> ca = {'Sum of this array =', sum(sum(meanGridGLs)), 'layers of
> aluminum', 'Sum of gray levels = ', sumOfGLs(j)};
> xlswrite1(excelFileNameForOneImage, ca, 'Array Means', 'A1');
> [similar code snipped]
>
> % Shut down Excel.
> Excel.Quit
> Excel.delete
> clear Excel;
>
> I have two workbooks, wbAll, and wbOne which correspond to two disk
> files, the names of which are in string variables
> excelFileNameForOneImage and excelFileNameForAllImages. I change
> between them several times writing various things (cell arrays and
> numerical arrays) to them (most of which code I snipped out).
> It's called within a function that gets run as a result of clicking on
> a button in the GUI.
> Hope this helps, and you can adapt it for your needs.
> ImageAnalyst

I use that, but I get this error:
Error using ==> evalin
Undefined function or variable 'Excel'.

I have tried making Excel a global, but that didn't work. I am running out of ideas

On Sep 21, 5:49 pm, "Travis " <sinuso...@hotmail.com> wrote:
> I use that, but I get this error:
> Error using ==> evalin
> Undefined function or variable 'Excel'.
>
> I have tried making Excel a global, but that didn't work.  I am running out of ideas- ----------------------------------------------------------------------------------------------------------
I don't see an evalin() in my code I gave you. Where do you get
this? Do you get it at the first place you instantiate the Excel
object:
        Excel = actxserver('Excel.Application');
If so, you do not have Excel installed on that computer.

Try placing an Excel object on your window with Guide. When you
browse for the ActiveX control, do you see "Microsoft Office
Spreadsheet" (or something else that looks like it might refer to
Excel) listed in the list? If not, then you don't have Excel
installed.
Regards,
ImageAnalyst

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <12a9999d-7273-40f2-b2cd-d59acba29fdf@y20g2000vbk.googlegroups.com>...
> On Sep 21, 5:49?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > I use that, but I get this error:
> > Error using ==> evalin
> > Undefined function or variable 'Excel'.
> >
> > I have tried making Excel a global, but that didn't work. ?I am running out of ideas- ----------------------------------------------------------------------------------------------------------
> I don't see an evalin() in my code I gave you. Where do you get
> this? Do you get it at the first place you instantiate the Excel
> object:
> Excel = actxserver('Excel.Application');
> If so, you do not have Excel installed on that computer.
>
> Try placing an Excel object on your window with Guide. When you
> browse for the ActiveX control, do you see "Microsoft Office
> Spreadsheet" (or something else that looks like it might refer to
> Excel) listed in the list? If not, then you don't have Excel
> installed.
> Regards,
> ImageAnalyst

Excel=evalin('base','Excel'); is line 31 in xlswrite1. I have Excel 2003 and Excel 2007. xlswrite1 doesn't work with 07, but I rewrote the new xlswrite to be similar to xlswrite1 that will work with 07.

On Sep 21, 7:30 pm, "Travis " <sinuso...@hotmail.com> wrote:
> Excel=evalin('base','Excel'); is line 31 in xlswrite1.  I have Excel 2003 and Excel 2007.  xlswrite1 doesn't work with 07, but I rewrote the new xlswrite to be similar to xlswrite1 that will work with 07.-

---------------------------------
OK. Well it sounds like you have it solved now.

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <9bfba54c-045d-47fb-bad2-5ec67c226b6d@v2g2000vbb.googlegroups.com>...
> On Sep 21, 7:30?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > Excel=evalin('base','Excel'); is line 31 in xlswrite1. ?I have Excel 2003 and Excel 2007. ?xlswrite1 doesn't work with 07, but I rewrote the new xlswrite to be similar to xlswrite1 that will work with 07.-
>
> ---------------------------------
> OK. Well it sounds like you have it solved now.

I don't have it solved. Outside a GUI things work great, inside not so well. From the code you have provided there is no special trick, but I cannot seem to get mine to work at all.

On Sep 21, 10:08 pm, "Travis " <sinuso...@hotmail.com> wrote:
> ImageAnalyst <imageanal...@mailinator.com> wrote in message <9bfba54c-045d-47fb-bad2-5ec67c226...@v2g2000vbb.googlegroups.com>...
> > On Sep 21, 7:30?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > > Excel=evalin('base','Excel'); is line 31 in xlswrite1. ?I have Excel 2003 and Excel 2007. ?xlswrite1 doesn't work with 07, but I rewrote the new xlswrite to be similar to xlswrite1 that will work with 07.-
>
> > ---------------------------------
> > OK.  Well it sounds like you have it solved now.
>
> I don't have it solved.  Outside a GUI things work great, inside not so well.  From the code you have provided there is no special trick, but I cannot seem to get mine to work at all.

---------------------------------------------------------------
Probably some variable is not in scope. It looks like it expects
Excel to be in the base workspace. Maybe you could declare it as
global so it will be in the base workspace.

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <fe0ea5d7-dfe7-4a6f-a423-371ea422cd6c@j9g2000vbp.googlegroups.com>...
> On Sep 21, 10:08?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > ImageAnalyst <imageanal...@mailinator.com> wrote in message <9bfba54c-045d-47fb-bad2-5ec67c226...@v2g2000vbb.googlegroups.com>...
> > > On Sep 21, 7:30?pm, "Travis " <sinuso...@hotmail.com> wrote:
> > > > Excel=evalin('base','Excel'); is line 31 in xlswrite1. ?I have Excel 2003 and Excel 2007. ?xlswrite1 doesn't work with 07, but I rewrote the new xlswrite to be similar to xlswrite1 that will work with 07.-
> >
> > > ---------------------------------
> > > OK. ?Well it sounds like you have it solved now.
> >
> > I don't have it solved. ?Outside a GUI things work great, inside not so well. ?From the code you have provided there is no special trick, but I cannot seem to get mine to work at all.
>
> ---------------------------------------------------------------
> Probably some variable is not in scope. It looks like it expects
> Excel to be in the base workspace. Maybe you could declare it as
> global so it will be in the base workspace.

OK, moved the Excel=evalin('base','Excel'); outside of the actual command, and it crashed at the same place. I made the GUI using guide, there are no activex controllers in the GUi. I am getting very annoyed that this is not working for me. I am using Student 2008b if that helps at all.

Even with the Excel = evalin('base','Excel') outside the xlswrite2007/xlswrite2001 command I still get the error.

Error using ==> evalin
Undefined function or variable 'Excel'.

This is after...

Excel = actxserver ('Excel.Application');
File = [cd '\' fnme];
if ~exist(File,'file')
    ExcelWorkbook = Excel.workbooks.Add;
    ExcelWorkbook.SaveAs(File)
    ExcelWorkbook.Close(false);
end
Excel.Visible = false; % Make Excel visible.
invoke(Excel.Workbooks, 'Open', File);
WB = Excel.ActiveWorkbook;
WB.Activate

If there is any more help you could provide ImageAnalyst I would greatly appreciate it.

On Oct 21, 9:43 pm, "Travis " <sinuso...@hotmail.com> wrote:
[snip]
> If there is any more help you could provide ImageAnalyst I would greatly appreciate it.
------------------------------------------------------------------------------------------------------------
Travis:
First, go get xlswrite1() from the File Exchange.
http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1

Then copy and paste these 112 lines, fix any line breaks introduced by
the newsreader, and run it in debug mode. Try to go step by step and
understand what it's doing.

If you have to do multiple writes in a single m-file, this will be
much, much, much faster than calling xlswrite() each time. xlswrite()
opens and closes Excel each time which takes a ton of time, whereas
xlswrite1() assumes that you'll do that overhead yourself. It's the
launching of Excel that takes so much time. The actual poking of data
is really fast.
Good luck,
ImageAnalyst



% Demo macro to write numerical arrays and cell arrays
% to two different worksheets in an Excel workbook file.
% By ImageAnalyst
function ExcelDemo
clc;
close all;
clear all;
fullFileName = GetXLFileName();
if isempty(fullFileName)
% User clicked Cancel.
return;
end
Excel = actxserver('Excel.Application');
if ~exist(fullFileName, 'file')
message = sprintf('I am going to create Excel workbook:\n\n%s\n
\nClick OK to continue.\nClick Exit to exit this function',
fullFileName);
button = questdlg(message, 'Creating new workbook', 'OK', 'Exit',
'OK');
drawnow; % Refresh screen to get rid of dialog box remnants.
if strcmpi(button, 'Exit')
return;
end
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(fullFileName,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',fullFileName);

% Create some sample data.
myData = magic(20);
myOtherData = rand(10);

% Then run the new xlswrite1 function as many times as needed or in a
loop
% (for example xlswrite1(fullFileName, yourArrayName,
XL_CellLocation).
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
ca = {'Column Header 1', 'Column Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'B1');
ca = {'Row Header 1'; 'Row Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'A2');
xlswrite1(fullFileName, myOtherData, 'myOtherSheetName', 'B2');

% Delete all empty sheets in the active workbook.
DeleteEmptyExcelSheets(Excel);

% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;
message = sprintf('Done!\nThis Excel workbook has been created:\n%s',
fullFileName);
msgbox(message);
% End of main function: ExcelDemo.m -----------------------------

%--------------------------------------------------------------------
% Gets the name of the workbook from the user.
function fullExcelFileName = GetXLFileName()
fullExcelFileName = []; % Default.
% Ask user for a filename.
FilterSpec = {'*.xls', 'Excel workbooks (*.xls)'; '*.*', 'All Files
(*.*)'};
DialogTitle = 'Save workbook file name';
% Get the default filename. Make sure it's in the folder where this
m-file lives.
% (If they run this file but the cd is another folder then pwd will
show that folder, not this one.
thisFile = mfilename('fullpath');
[thisFolder, baseFileName, ext, version] = fileparts(thisFile);
DefaultName = sprintf('%s/%s.xls', thisFolder, baseFileName);
[fileName, specifiedFolder] = uiputfile(FilterSpec, DialogTitle,
DefaultName);
if fileName == 0
% User clicked Cancel.
return;
end
% Parse what they actually specified.
[folder, baseFileName, ext, version] = fileparts(fileName);
% Create the full filename, making sure it has a xls filename.
fullExcelFileName = fullfile(specifiedFolder, [baseFileName '.xls']);

% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active
workbook.
% This function looped through all sheets and deletes those sheets
that are
% empty. Can be used to clean a newly created xls-file after all
results
% have been saved in it.
function DeleteEmptyExcelSheets(excelObject)
% excelObject = actxserver('Excel.Application');
% excelWorkbook = excelObject.workbooks.Open(fileName);
worksheets = excelObject.sheets;
sheetIdx = 1;
sheetIdx2 = 1;
numSheets = worksheets.Count;
% Prevent beeps from sounding if we try to delete a non-empty
worksheet.
excelObject.EnableSound = false;

% Loop over all sheets
while sheetIdx2 <= numSheets
% Saves the current number of sheets in the workbook
temp = worksheets.count;
% Check whether the current worksheet is the last one. As there
always
% need to be at least one worksheet in an xls-file the last sheet
must
% not be deleted.
if or(sheetIdx>1,numSheets-sheetIdx2>0)
% worksheets.Item(sheetIdx).UsedRange.Count is the number of used
cells.
% This will be 1 for an empty sheet. It may also be one for
certain other
% cases but in those cases, it will beep and not actually delete
the sheet.
if worksheets.Item(sheetIdx).UsedRange.Count == 1
worksheets.Item(sheetIdx).Delete;
end
end
% Check whether the number of sheets has changed. If this is not the
% case the counter "sheetIdx" is increased by one.
if temp == worksheets.count;
sheetIdx = sheetIdx + 1;
end
sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
end
excelObject.EnableSound = true;
return;

OK, so that really narrows it down for me. I get the same error running your code as I do when I run mine...

??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 31
Excel=evalin('base','Excel');

Error in ==> ExcelDemo at 33
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');

meaning there is something in the way MATLAB is set up or the version. I am running student 2008b. I have tried this on two computers, both of which have Excel 2003, and 2007 installed on them.

Any thoughts on this?

Hello,

The problem with excel is that it always recalculates when something new is writen. Excel works with a scheme that keeps track of cells that are linked to one another in some sort of calculation. The recalculting can cause serious overhead when writing because whenever a new cell is written the scheme has to be checked and then calculations are performed. This can be changed in a simple way by telling Excel not to recalculate all cells over again.
Simply set the recalculation to manual before starting your work and when you're finished set it back to automatic.
Like this:

% Excel constants from website:
% http://msdn.microsoft.com/en-us/library/aa221100(office.11).aspx
xlAutomatic = -4105;
xlManual = -4135;

Excel = actxserver('excel.application');
wb = Excel.Workbooks.Open(myExcelFile);
Excel.Calculation = xlManual;
% Write your data, possibly in a loop
xlswrite1(...);
% When done set back to automatic
Excel.Calculation = xlAutomatic;

% Save your workbook and close
wb.Save;
wb.Close;
Excel.Quit;
Excel.delete;

This change caused a great increase in speed for me because i work with big workbooks with multiple sheets that are interconnected to one another.
I hope this goes the same way for you too.

Sander

On Oct 26, 11:29 am, "Travis " <sinuso...@hotmail.com> wrote:
> OK, so that really narrows it down for me.  I get the same error running your code as I do when I run mine...
>
> ??? Error using ==> evalin
> Undefined function or variable 'Excel'.
>
> Error in ==> xlswrite1 at 31
> Excel=evalin('base','Excel');
>
> Error in ==> ExcelDemo at 33
> xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
>
> meaning there is something in the way MATLAB is set up or the version.  I am running student 2008b.  I have tried this on two computers, both of which have Excel 2003, and 2007 installed on them.  

I am also having the same problem. I am using the 2007 student
version. Did you figure out why it won't work yet??

KSullivan <karenannesullivan@gmail.com> wrote in message <e15e605d-5df7-43a3-b547-95e4513b0060@m26g2000yqb.googlegroups.com>...
> On Oct 26, 11:29?am, "Travis " <sinuso...@hotmail.com> wrote:
> > OK, so that really narrows it down for me. ?I get the same error running your code as I do when I run mine...
> >
> > ??? Error using ==> evalin
> > Undefined function or variable 'Excel'.
> >
> > Error in ==> xlswrite1 at 31
> > Excel=evalin('base','Excel');
> >
> > Error in ==> ExcelDemo at 33
> > xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
> >
> > meaning there is something in the way MATLAB is set up or the version. ?I am running student 2008b. ?I have tried this on two computers, both of which have Excel 2003, and 2007 installed on them. ?
>
> I am also having the same problem. I am using the 2007 student
> version. Did you figure out why it won't work yet??

Yes, use this instaead

Note: If you call this from within your own function instead of the MATLAB command line, you will need to change the second line (im xlswrite1 or xlswrite2007) to
   Excel = evalin('caller', 'Excel');
(instead of Excel = evalin('base', 'Excel'); like he has it)

from the xlswrite1 comments.