MATLAB Central Newsreader - xlswrite?

xlswrite?

Cooky Monster — Tue, 11 Sep 2007 10:06:34 -0400

Dear All

I have a matrix M of three columns and 100 rows. I like to
export it to Excel. I have to add the lables also.

I am using

Titles = {'Number of Cycles', 'Crack Length','Crack Growth
Rate'}
Labels = {'Cycles', 'a','da/dN'}
Units = {'[Cycles]', '[mm]','[mm/Cycles]'}
[newmatfile, newpath] = uiputfile('*.xls', 'Save As');
filename = [newpath,newmatfile];
if isempty(findstr([newpath,newmatfile],'.xls'))
filename = [filename,'.xls'];
end

xlswrite(filename, Titles, 1, 'A2')
xlswrite(filename, Labels, 1, 'A3')
xlswrite(filename, Units, 1, 'A4')
xlswrite(filename, M,1, 'A6')

I like to ask if I can auto width the columns in Excel
sheet using any matlab command and also to change the font
size.

Thanks in Advance

Regards

Re: xlswrite?

Yair Altman — Tue, 11 Sep 2007 10:44:44 -0400

"Cooky Monster" <sharif.uk@gmail.com> wrote in message
<snip>
> I like to ask if I can auto width the columns in Excel
> sheet using any matlab command and also to change the font
> size.

You cannot do this with xlswrite. However, you can do this
by directly accessing the Excel COM server and running
column-width and font-size VB commands on that server. The
same applies to any other formating you may wish to do on
the data. To see how to do this, you can edit xlswrite.m:
It's a relatively long function, but if you ignore the
error-checking parts, you'll see that the actual skeleton is
pretty small.

An alternative is to use the OfficeDoc submission on the
File Exchange:
http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=15192

Yair Altman
http://ymasoftware.com

Re: xlswrite?

Gina Stuessy — Fri, 06 Aug 2010 15:30:23 -0400

> You cannot do this with xlswrite. However, you can do this
> by directly accessing the Excel COM server and running
> column-width and font-size VB commands on that server. The
> same applies to any other formating you may wish to do on
> the data. To see how to do this, you can edit xlswrite.m:
> It's a relatively long function, but if you ignore the
> error-checking parts, you'll see that the actual skeleton is
> pretty small.
>
> An alternative is to use the OfficeDoc submission on the
> File Exchange:
> http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=15192
>
> Yair Altman
> http://ymasoftware.com
>

Can you tell me specifically how to edit the xlswrite file to set the column widths for each column separately? (Not just 1 width that applies to the whole document.)

Thanks,
Gina

Re: xlswrite?

Andy — Fri, 06 Aug 2010 15:50:08 -0400

% sample code

e=actxserver('excel.application');
eW=e.Workbooks;
eF=eW.Open(filename); % your filename here
eS=eF.ActiveSheet;
eS.Range('A1').EntireColumn.AutoFit; % autofit column A
eS.Range('A1').EntireColumn.Font.Name = 'Times'; % changes font to Times

Re: xlswrite?

Gina Stuessy — Fri, 06 Aug 2010 16:59:06 -0400

"Andy " <myfakeemailaddress@gmail.com> wrote in message <i3havg$gkf$1@fred.mathworks.com>...
> % sample code
>
> e=actxserver('excel.application');
> eW=e.Workbooks;
> eF=eW.Open(filename); % your filename here
> eS=eF.ActiveSheet;
> eS.Range('A1').EntireColumn.AutoFit; % autofit column A
> eS.Range('A1').EntireColumn.Font.Name = 'Times'; % changes font to Times

I'm sorry, I was unclear. I meant I would like to save some data to an excel file through Matlab and save also the widths that the columns should be when the file is opened by someone else who has only Excel. I do not want to open the file through Matlab and then format the columns.

Thanks,
Gina

Re: xlswrite?

Andy — Fri, 06 Aug 2010 17:10:08 -0400

"Gina Stuessy" <gstuessy@gmail.com> wrote in message <i3hf0q$3ve$1@fred.mathworks.com>...
> "Andy " <myfakeemailaddress@gmail.com> wrote in message <i3havg$gkf$1@fred.mathworks.com>...
> > % sample code
> >
> > e=actxserver('excel.application');
> > eW=e.Workbooks;
> > eF=eW.Open(filename); % your filename here
> > eS=eF.ActiveSheet;
> > eS.Range('A1').EntireColumn.AutoFit; % autofit column A
> > eS.Range('A1').EntireColumn.Font.Name = 'Times'; % changes font to Times
>
> I'm sorry, I was unclear. I meant I would like to save some data to an excel file through Matlab and save also the widths that the columns should be when the file is opened by someone else who has only Excel. I do not want to open the file through Matlab and then format the columns.
>
> Thanks,
> Gina

Right, so after writing your data with xlswrite, this code shows you exactly how to autofit the columns (and change the font if you wish) so that when somebody opens the spreadsheet later, the columns will be the correct width. I'm not sure what more you're asking for right now.

Re: xlswrite?

Gina Stuessy — Fri, 06 Aug 2010 17:49:04 -0400

For some reason when I use that script, I get an error message saying the document is already open. If I choose to reopen it, it does have the column width set and the font changed, but then when I close the file and try to open it again, it doesn't. It appears this only works the first time it's opened.

Re: xlswrite?

Andy — Fri, 06 Aug 2010 18:07:04 -0400

"Gina Stuessy" <gstuessy@gmail.com> wrote in message <i3hhug$bnf$1@fred.mathworks.com>...
> For some reason when I use that script, I get an error message saying the document is already open. If I choose to reopen it, it does have the column width set and the font changed, but then when I close the file and try to open it again, it doesn't. It appears this only works the first time it's opened.

Well, if you already have the document open when doing this, then replace the first line with:

e=actxGetRunningServer('excel.application');

Also, if you want to programmatically save and close the file after changing the width, add to the end:

eF.Save;
eF.Close; % close the file
e.Quit; % close Excel entirely

But it is probably a bad idea to use actxGetRunningServer to edit Excel files while they're already open. It is much safer to do this when Excel is closed.

Re: xlswrite?

Gina — Fri, 06 Aug 2010 18:25:27 -0400

This is what I have so far:

[FileName,PathName,FilterIndex] = uiputfile(TableFilename);
xlswrite([PathName, FileName], SliceData);
e=actxserver('excel.application');
eW=e.Workbooks;
eF=eW.Open(FileName); % your filename here
eS=eF.ActiveSheet;
eS.Range('B1').EntireColumn.AutoFit; % autofit column A
eS.Range('A1').EntireColumn.Font.Name = 'Times'; % changes font to Times
eF.Save;
eF.Close; % close the file
e.Quit; % close Excel entirely

I'm getting an error that says the Open function doesn't work because the filename cannot be found, even though the file saves correctly.

Re: xlswrite?

Gina — Fri, 06 Aug 2010 18:29:06 -0400

Nevermind my previous message, that worked when I put the path name and file name in as inputs instead of just the filename.

Thanks for all the help!

Re: xlswrite?

Brad Z — Sat, 12 Mar 2011 16:48:04 -0500

"Andy" wrote in message <i3hflg$f40$1@fred.mathworks.com>...
> "Gina Stuessy" <gstuessy@gmail.com> wrote in message <i3hf0q$3ve$1@fred.mathworks.com>...
> > "Andy " <myfakeemailaddress@gmail.com> wrote in message <i3havg$gkf$1@fred.mathworks.com>...
> > > % sample code
> > >
> > > e=actxserver('excel.application');
> > > eW=e.Workbooks;
> > > eF=eW.Open(filename); % your filename here
> > > eS=eF.ActiveSheet;
> > > eS.Range('A1').EntireColumn.AutoFit; % autofit column A
> > > eS.Range('A1').EntireColumn.Font.Name = 'Times'; % changes font to Times
> >
> > I'm sorry, I was unclear. I meant I would like to save some data to an excel file through Matlab and save also the widths that the columns should be when the file is opened by someone else who has only Excel. I do not want to open the file through Matlab and then format the columns.
> >
> > Thanks,
> > Gina
>
> Right, so after writing your data with xlswrite, this code shows you exactly how to autofit the columns (and change the font if you wish) so that when somebody opens the spreadsheet later, the columns will be the correct width. I'm not sure what more you're asking for right now.

Just to be super clear, it works if you insert this after line 238 in xlswrite:
Excel.selection.EntireColumn.AutoFit;

So, the new code should look like this:
    % Export data to selected region.
    set(Excel.selection,'Value',A);
    Excel.selection.EntireColumn.AutoFit; % This is the new part
    ExcelWorkbook.Save
    ExcelWorkbook.Close(false) % Close Excel workbook.
    Excel.Quit;

You might want to rename it before the save. I has worked for me, but that doesn't mean there isn't some bug lurking around.