xlswrite

Easily create a Microsoft Excel format spreadsheet from MATLAB®.

Description

NOTE: The MathWorks added similar functionality to MATLAB 7. You may want to check out the built in xlswrite before trying mine. - Scott

Creates a Microsoft Excel format spreadsheet using the MATLAB ActiveX interface. This can also open the spreadsheet directly without ever saving to a file.

Nothing too fancy:
 - Supports arbitrary number of header lines
 - Supports column headers
 - Writes a single matrix
 - Can write to specific sheets (thanks Fahad!)

Example:
m = rand(100,4);
header = 'This is some introductory information at the top of my spreadsheet.';
colnames = {'Ch1','Ch2','Ch3','Ch4'};
filename = 'myfile.xls';
xlswrite(m,header,colnames,filename);

Will create a spreadsheet named myfile.xls

• Acknowledgements: This submission has inspired the following:; xlschart, XLSWriteMex, xlsheets, xlswrite, table2word, dat2xls, xlswrite_mod, xlcolumn, Example of how to save or read data to an Excel archive.
• MATLAB release: MATLAB 6.5 (R13)
• Other requirements: Microsoft Excel spreadsheet software

[Saurabh Singal]

this is brilliant.
I was thinking of something along these lines, and just because Scott was profiled as a free t-shirt winner, I happened to click on his profile and found this utility.

[B Donley]

Nice, but you aren't really using ActiveX to it's full potential which will let you format the cells, shade, border and so forth. You can do the exact same thing without purchasing ActiveX by just giving a tab-delimited (tab spaced) ASCII file the extension .xls and calling "excel filename" through a command prompt (type help !). Nice that you can open without saving though...that is definitely useful.

[M. S.]

helpful

[Hein Hardy]

Thank you very much, you just saved us an increadible amount of time

[J S]

thanks, useful

[ken stessens]

Very helpful indeed. The option that is still missing in my opinion, is to add new data in an existing file for which you did already make a layout in excel. Let's say, not only create a file, but also update the cells in an existing excel layout, and this without opening the excel file or even the excel program... Thanks for letting me know if the code has been added ;-)

[Femi Omitaomu]

This is very good. Thanks for sharing it.

[Billot adrien]

Thanks for sharing it, it's really helpful. It can be easily modified to add a 'file update' option...just one thing : Excel ActiveX server creation sometimes bugs if the function is applied several times. In that case, it's better to open only one ActiveX server and to close it outside of the function, after computation !

[Chris Patscheck]

Very good add in. Can be a bit slow, but who really needs fast data transfer to excel. I am getting a lot of use out of this, and it kept me from writing my own function to take care of this.

Thanks, Chris

[Moti Huber]

Great !
Thanks

[rajneesh agrawal]

this add in really helped me a lot. thanks for it

[Evaristo Hernández]

 

[John Muller]

This function is really hot -- it's simple, well written, easy to use and fairly robust. The author has included generous comments in the function, which allow the user to make simple changes easily, in order to tailor the function to your specific needs. WELL DONE!

[pong tor]

so cool!

[M LG]

 

[Eric Altshuler]

Worked like a charm, it's great. Thanks a lot

Eric

[Fahad Al Mahmood]

It's a great function indeed and I'm glade it is going to be a part of (MATLAB 7.0)! My only suggestion is to add an option for writing to a selected sheet.

[Rob Morse]

Nice one.. would be nice to be able to set an option to ask for a filename with a dialog? Thanks!

[Uday Desai]

Very Smart. Great Job. It works.

[terry wu]

Great. It really help me a lot!

[mathieu jerome]

 

[Omri Barak]

great, here's the code to add sheet in case it doesn't exist:

% Make the specified sheet active.
try
    Sheets = Excel.ActiveWorkBook.Sheets;
    target_sheet = get(Sheets, 'Item', sheetname);
catch
    try
        target_sheet = invoke( Sheets, 'add' );
        set( target_sheet, 'name', sheetname );
    catch
        error(['Sheet ' sheetname ' does not exist!, and could not be created']);
    end
end;

[Ted Maddess]

Bloody marvelous

[Anadi Mahajan]

be careful about the kind of filenames you pass. by default the results are stored in the working directory. To use an absolute path modify the lines 99 and 104 to avoid using the pwd and filesep.
Replace
invoke(op, 'SaveAs', [pwd filesep filename]);
with
invoke(op, 'SaveAs', filename);

And also
Replace
op = invoke(Excel.Workbooks, 'open', [pwd filesep filename]);
With
op = invoke(Excel.Workbooks, 'open', filename);

This should allow you to pass any arbitrary pathname as long as the file can be created in the path you specify, the function will work fine.

Great job!!

[Brad Hicks]

Just what I needed.....Thanks!

[Michael Kleder]

Oh yes.

[Dimitri Shvorob]

On my PC, with Excel 2002, spreadsheets generated by the function have '65535' whenever a NaN was; in addition, to make numbers show in a reasonable format (e.g. with two decimal places), one needs to go through the steps of selecting the range and formatting cells as 'numeric'. Would it be possible to let user avoid this small but unnecessary hassle?
Thank you.

[Dimitri Shvorob]

The function works great (except for that irritating 65535-means-NaN thing) on my computer; however, testing my program on a different PC, with Excel 2003, I received error message

??? Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: Microsoft Office Excel cannot access the file 'N:\stats\MATLAB7\work'. 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: C:\Program Files\Microsoft Office\OFFICE11\1033\xlmain11.chm
Help Context ID: 0

No disks are write-protected, no Excel files are open. Could the authors please test the m-file's compatibility with Excel 2003?

[tiago duperray]

 

[zmeb olivier]

I am using windows 2000 and when I save data into the sheet2, data that were on the sheet1 disappeard.

[Santosh Pandey]

In order to create new worksheets if the sheetname does not exist, modify the section "% Make the specified sheet active." from the code "xlswrite.m" as shown below.

% Make the specified sheet active.
try
    Sheets = Excel.ActiveWorkBook.Sheets;
    target_sheet = get(Sheets, 'Item', sheetname);
catch
    % Error if the sheet doesn't exist. It would be nice to create it, but
    % I'm too lazy.
    % The alternative to try/catch is to call xlsfinfo to see if the sheet exists, but
    % that's really slow.
    % error(['Sheet ' sheetname ' does not exist!']);
    
    % Creating new sheet if it does not exist
    hWSs=op.worksheets;
    target_sheet = invoke(hWSs,'Add');
    target_sheet.Name=sheetname;
end;

[vijay krishna]

 

[Mark Hayworth]

This functionality is now built in to the core MATLAB product. There are now built-in functions like xlswrite, xlsread, xlsinfo, etc. (See the help entry on Excel.) So you most likely won't need to download this code.

[Moji Muda]

 

[Mohammed JADOUI]

Hello,
Could you please let me know if there is any version of xlswrite which handles Column and Row headers? If yes, may I have the '.m' code?
Thanks a lot
Mohammed

[LAKISHA GUINN]

Hi,
Could you please let me know if there is a way to use xlswrite to save to the next empty row. I am trying to use the built-in xlswrite function to save data to an existing excel file. Is there code that will detect the location of the next empty row and save the data there? I tried the following code but it gives me an error: [num txt DataRange]=xlsread(fileName);
[z v]=size(DataRange)
z=z+1%gives the number of the next empty row
range=sprintf('A \b%d',z)%range should start in column A of row
xlswrite(fileName,data,1, range);

Thanks

[Matthieu Baudry]

 

[Laura Woodworth]

Hi,
Is there any way to get xlswrite to either write to a file name that has been date/time stamped, or write to a generic file name with a worksheet that has been date/time stamped?

The code I am working on is meant to be run multiple times, to generate sets of answers, and I am trying to prevent having to alter the code each time for a unique file name.

Thanks for any help!

Laura

[Chandra B K]

Works very well but probably an addition to the functionality could be writing row headers (titles) ?
Maybe there is a way to do it using the current version, but I was unable to do it!

[D R]

Does not work as written

[djr djr]

 

[Xavier]

Hi, I want to know if there are some way to write in excel 2007 format (*.xlsx)

[sophie neve]

well, that is sad you do not accept any number in the titles of the columns... for example, if you want to have
frequency1 = 10 as a title
...

[Scott Hirsch]

Just a note to requests for new features - the xlswrite that has shipped with MATLAB since R14 (MATLAB 7) offers much more flexibility than mine, so I encourage you to check it out if this one doesn't address your needs.

[mohamed eltahan]

I am a starter in Matlab and I have faced a small problem.
I want to write the program so that some variable values can be writen into excel. eg. (A=12, B=14,C=15...)
However, since this program will be run many times and update each of the variables into excel. So Everytime, I need to writen in a new row to ensure there is no overwriting. for instant:

xlswrite('Z:\junk\new.xls',ProbeName,'Gain','A3');
xlswrite('Z:\junk\new.xls',GainPX(1),'Gain','B3');
xlswrite('Z:\junk\new.xls',GainNX(1),'Gain','C3');

However, since new.xls contain lots of those information (It will update each experiment result). Every time I run this program, I want it automatically find the correct address under the last update. The problem is I don't know how many update were in the excel so I cannot determine which row i will begin to write.

Is there any command so that I can find whats the last row of an excel document so that I can ensure where should I update my data to this excel file. Many Thanks.