xlwrite: Generate XLS(X) files without Excel on Mac/Linux/Win
xlwrite allows to generate '.xls' & '.xlsx' files easily without having Excel installed or using COM-objects. It generates Excel files by using the Apache POI java library. This way Excel files can be generated on any platform supporting Java, including Linux & mac.
xlwrite uses the same syntax as xlswrite, making it easy to integrate in current applications.
xlwrite supports following datatypes:
- string
- numerical
- logical/boolean
The type of data is kept in Excel: for example boolean data in matlab will be saved as boolean in Excel.
xlwrite also supports a combination of above datatypes in a cell array like for example:
xlsData = {1 true 'text' false; 10 -12.5 6 false};
To generate the Excel file without too much overhead, xlwrite interacts directly with the Apache POI library.
The original idea of this script is based on the xlwrite function written by Marin Deresco, however due to some limitations we had to write an improved version.
Although created under Matlab 2012a, xlwrite should also work for earlier versions of Matlab.
UPDATE: if you find any bugs, please contact me directly.
Cite As
Alec de Zegher (2021). xlwrite: Generate XLS(X) files without Excel on Mac/Linux/Win (https://www.mathworks.com/matlabcentral/fileexchange/38591-xlwrite-generate-xls-x-files-without-excel-on-mac-linux-win), MATLAB Central File Exchange. Retrieved .
Comments and Ratings (167)
MATLAB Release Compatibility
Platform Compatibility
Windows macOS LinuxCategories
Tags
Acknowledgements
Inspired by: xlwrite : Export Data to Excel from Matlab on Mac/Win
Inspired: xlRange, number2letter, xlread, gPKPDSimToolbox, Microscopy Image Browser 2 (MIB2), csvimport(filename), csvexport(filename,cellVals), gQSPSim, Parametrize Calcium Signals
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!Discover Live Editor
Create scripts with code, output, and formatted text in a single executable document.
20130227_xlwrite/
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
Select web siteYou can also select a web site from the following list:
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
It has worked perfectly well until I migrated my app to R2020b. When my app runs under the compiler runtime of R2020b Update2, I got the error message below when trying to save an xlsx file. It looks like the same error message from Bo Yuan. I tried to search a solution online and found that uninstalling the Text Analytics toolbox could be a workaround solution. However, I never installed that toolbox. Any idea why this error occurs?
BELOW IS THE ERROR MESSAGE:
No constructor 'org.apache.poi.xssf.usermodel.XSSFWorkbook' with matching signature found.
Error in xlwrite (line 148)
...
I got error "The POI library is not loaded in Matlab."
Update.
If you uninstall the Text Analytics toolbox it works with Matlab 2019b (source: https://github.com/SysBioChalmers/RAVEN/issues/55)
No constructor 'org.apache.poi.xssf.usermodel.XSSFWorkbook' with matching signature found.
Error in xlwrite (line 148)
xlsWorkbook = XSSFWorkbook();
Error in Test_xlWrite (line 28)
xlwrite('test_xlwrite.xlsx', xlsData);
It doens't work with MATLAB 2018a. What should I do?
An error occur, but 1 hrs ago it works well ... don't know what was changed ...
Error using xlwrite (line 137)
Java exception occurred:
org.apache.poi.openxml4j.exceptions.InvalidFormatException: Your InputStream was neither an OLE2 stream, nor an
OOXML stream
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:183)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:140)
/!\/!\/!\/!\ Excel I/O problems using MATLAB 2017b or later /!\/!\/!\/!\
if you are on MATLAB 2017b environment or later, you can have java errors like those :
Java exception occurred:
java.lang.NoClassDefFoundError: org/apache/commons/collections4/ListValuedMap
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:181)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:140)
Caused by: java.lang.ClassNotFoundException:
org.apache.commons.collections4.ListValuedMap
at java.net.URLClassLoader.findClass(URLClassLoader.java:381
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 2 more
Solution : uninstall Text Analytics Toolbox !
see : https://github.com/SysBioChalmers/RAVEN/issues/55#issuecomment-386117111
I'm still having an issue rendering formulas as formulas rather than text.
I've tried using xlsWorkbook.setForceFormulaRecalculation(true); prior to saving the working, however this doesn't work. I'm using excel 2016. Is it possible that this workaround no longer works?
Anyone have luck with this in excel 2016?
Hi Alec de Zegher,
Thanks for making this nice code! I've applied on my own data, however it shows the following error:
??? Java exception occurred:
java.lang.IllegalArgumentException: The workbook already contains a sheet of this name
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:619)
I found someone has the similar error, however, I checked the potential reasons and found that these do not fit my case (i.e., 1. use fullfile; 2. load poi-ooxml-3.8-20120326.jar;poi-ooxml-schemas-3.8-20120326.jar). Could you please help me with that? Thanks a lot!
Best,
Yang
Awesome and handy code, thanks. But I'm wondering if the loop problem is solved? It only saves the last sheet!!!, any help?
works on Mac OS Sierra, MATLAB r2016B !
BTW, the error mentioned by Jon Slater: "No method 'setCellValue' with matching signature found for class 'org.apache.poi.xssf.usermodel.XSSFCell'." occurred when I used an array containing strings ,
changing them to character turned out to be OK though.
Worked perfectly! Thank you very much! Huge time saver!
I can't get it work, when I test it, I get this error:
No constructor 'org.apache.poi.xssf.usermodel.XSSFWorkbook' with matching signature found.
Error in xlwrite (line 148)
xlsWorkbook = XSSFWorkbook();
Error in Test_xlWrite (line 28)
xlwrite(fileName, xlsData, sheetName, startRange);
Pleeease, can someone help me??
anyone getting this to work on Matlab 2017b, mac OS high sierra, Excel 15.39 ? It was working well for me before I updated all these softwares, and it still 'works,' but now when it writes a new tab to my .xlsx, it deletes all the other tabs already in the file! is this a bug with the new versions?
Fantastic! Thank you very much.
wow. thank you.
Thank you very much! It did work on my Mac, Matlab2017a.
Hi,
I can't find these 2 jars:
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
Do you have any idea where I can download them?
Excellent solution. Many Thanks. Works perfectly on Mac, Matlab 2017a.
I can't seem to get the function to write a datetime and I get the following error: "No method 'setCellValue' with matching signature found for class 'org.apache.poi.xssf.usermodel.XSSFCell'." Has anyone else tried to write datetimes?
For those who experiences the bug where you can't add new sheet (it clear everything out).
If you write the full name for the first argument (i.e. append the .xlsx instead of letting the function adding it by itself), it seems to solve the problem
Thanks for the function!
Hi,
if I have empty lines in my matlab cell, then I do not get empty lines in the excel file. Any solution?
thanks,
Iason
This submission was a lifesaver for me as I was trying to get MATLAB to write to an Excel file after being invoked from a scheduled task. It is a known issue that Excel COM automation has difficulties with unattended use. This appears to be an intentional choice by Microsoft (https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office) Anyway, kudos to the author for putting this together.
Was the looping into multiple sheets problem ever resolved?
I have a different number of variables each time I run a program where I am calling this function and I want each variable in it's own sheet. I obviously can't just call the function a set amount of times because the number of times I need to write changes with every run.
I tried:
for a=1:numrois
output = [timepoints; roioutput]
xlwrite(filename, output, a);
end
But all it gives me is a workbook with the final sheet.
@Kip I need to modify the source code of xlwrite to support multiline.
If you need the change now, you could modify xlwrite yourself with the change as listed in
http://poi.apache.org/spreadsheet/quick-guide.html#NewLinesInCells
@Scott Peer any suggestion as to how I could improve the documentation?
The file xlwrite.m should contain all the info (visible in matlab with `doc xlwrite`) or you could run the simple demo under 'Test_xlWrite.m'
Thanks!
I'm so thankful xlwrite exists!
First, a comment to those struggling to operate xlwrite: I found test_xlwrite.m an easy lesson on xlwrite's capability. I did ralize to edit all of the javaaddpath path's to point to the right folder. After that, it worked like a charm.
Now, a question: I am eager to provide linefeeds (alt-enter) in my .xlsx output. I've tried (for hours) on the following:
1) A char() for 'alt-enter'.
2) Various forms of '\r' and/or '\n'.
3) Kludging char(10) and/or char(13).
Only #3 succeeded, but as I'm passing my .xlsx to windows users, I have horribly ugly cells:
="line one"&char(10)&char(13)&"line two"&...
I have ~10 lines in each cell, so it looks horrendous.
(I also have to wake excel up and "replace = with =" to auto-calculate the cells.)
If anyone knows how to get xlwrite to populate a cell with 'line one<alt-enter>line two..., please help me out!
Thank you so much! (R2016b)
If you are not already using Java with MATLAB, don't waste your time, there are no instructions on how to get this to run.
HI, SOMEONE THAN HELP ME TO INSTALL THIS EXCHANGE FILE IN LINUX I AM USING MATLAB 2010, AND DISPLAY THIS MESSAGE "Could not start Excel server for export.XLSWRITE will attempt to write file in CSV format. "I WOULD APPRECIATE YOUR HELP, THANKS
When working with small data size, xlwrite is good, usually 1.5-2x faster than matlab's xlswrite. But when I tried to save a double matrix of size 50x10,000 to an xlsx file, xlwrite choked. It took 133 sec, vs. 4 sec of xlswrite. I have Matlab 2015b.
1. Download the files.
2. Copy them inside the folder that contains your m-file.
3. Run your code, and enjoy!
=====
Thanks a lot for the person who made this code "Alec de Zegher"
PS: if you are using "xlswirte" inside your code, you have to change it to be "xlwirte"
=====
And if you want to open an excel file using mac OS, you can use the function "macopen" it can be found in this website made by Varun Gandhi, or you can google it!
NO NEED TO GO BACK TO WINDOWS GUYS! :)
=====
I tried this file with the current stable release 3.14 of POI.
xlsx files are still created but unfortunately can not be opened with Excel (2013, 15.0.4859.1000).
The context of the xlsx-Files created with with POI 3.8 and 3.14 differ partly, e.g. [Content_Types].XML.
As a Mac user, this is very useful to me. A valuable additional inbuilt capability of XLWRITE is that NANS are written to the xlsx file as blanks. That additional capability alone is worth 5 stars in my opinion. As well as another 5 stars for the main function.
having problems when looping data into it
for i = 1:10
ith = 2+i;
k=3+i;
cellnum= sprintf('A%s', num2str(ith));
xlwrite('file',k,1,cellnum);
the values from 1 - 9 is not saved. only the last one is saved. i think this is the same problem Niklas Brown had 3 years ago. if you don't loop and write 10 xlwrite it works thou
can you help me on how to install this into my matlab? thanks!
Thanks! It works exactly as expected.
When I run this script to works fine until I use it as a function in another script. The issue is that the code skips over the import classes set of lines. when this happens the workbooks in the switchcase are not defined.
If I put a break in xlwrite and step through the code it skips the import section. If I highlight the import lines and F9 thereby forcing the import and click continue the rest of the code executes normally.
What could be causing Matlab to skip the import of the org.apache.poi classes?
Thanks Alec, this is really useful particularly as I regularly change between PC and Mac (R2014b on OS X 10.11).
I found as a couple of others have mentioned, having used xlwrite to write the data, xlsread either fails or only reads one line. This is due to order of parameters in the XML files.
Typically I only saw Ben's solution from 06 Nov 2014 only after debugging it myself. It's also a similar workaround to load in all the rows from the Excel document rather than just the first one.
For convenience and completeness (and for those like me that fail to read below the first few comments!), the workarounds that work for me are:
In folder...
/Applications/MATLAB_R2014b.app/toolbox/matlab/iofun/private/
...edit file xlsreadXLS.m inserting this in at line 200:
if isempty(span.end)
s = regexp(sheetData, '<row r="\d+"><c r="(?<startCol1>[A-Z]+\d+)?" t="n"><v>\d+.\d+</v></c><c r="(?<startCol2>[A-Z]+\d+)?" t="n"><v>\d+.\d+</v></c>', 'names','all');
span.start = s(1).startCol1;
span.end = [':', s(length(s)).startCol2];
end
...edit getSheetNames.m inserting this at line 21:
if isempty(match)
match = regexp(workbook_xml,...
['<sheet name=".*(?<=<sheet name=")(?<sheetName>.+?)(?=" r:id="rId' num2str(sheetIDs(i)),'" sheetId=".*"/>)'], ...
'names' );
end
I could solve the calculation issue by setting xlsWorkbook.setForceFormulaRecalculation(true); before the file is written.
I am having the issue with automated calculation with the written data. The data written to the existing excel file with will not be recognized by the formulas referencing it. The calculation only occurs after editing either the data or the formula by F2 + Enter.
This is an excellent utility. It solves my need for writing data out to an excel file in Linux and Mac environment. However I am wondering if you can also develop xlread for Linux/mac as well. Thanks!
@Ben Oeveren
I am having a similar problem. After writing to an excel file using this function, the built in MATLAB function, xlsread, will only read the first element in the sheet. A workaround is to specify the row/column range in xlsread but there is no way to determine how many row/columns there are beforehand.
It seems like xlsread determines the size of the excel file sheet by reading some metadata value that the xlwrite function does not populate.
Thank you very much!
However, I have one problem you might know how to solve.
I use the function to add a row to an existing excel file. Therefore I first use the build-in xlsread() function and ask for the number of rows. Check my Data. Directly after I use your function xlwrite(filename, DATA) to write the new DATA into the excel file.
It does the writing. When I open the excel file, a new line is added. However, when I read the excel file again using xlsread(), without opening+saving the excel file outside Matlab, it does not recognize just added data. In my case it misjudges the number of rows and overwrites the previous added row.
I'm using osx 10.11.3 and Matlab 2016a.
Do you recognize the problem and do you know how I can solve it?
This is great! Is there a way to directly write Matlab tables (e.g. writetable function?). Thanks!
I'm using xlwrite with Matlab 2015a, and the old poi 3.8 library. I was getting an error:
Undefined function or variable 'HSSFWorkbook'.
I was able to make this go away by modifying xlwrite.m to call the HSSFWorkbook function with the full path like so:
org.apache.poi.hssf.usermodel.HSSFWorkbook()
instead of relying on the import statements. Not sure why this solves the problem, maybe there's been a change in how import works.
Jan Buytaert, I am having the same issues, where the POI files are not being loaded. What was the issue that you found with poi-ooxml-3.12-20150511.jar ?
But because of poi-ooxml-3.12-20150511.jar
it seems no longer possible to write XSLX. Writing XLS still works fine.
Strangely enough poi-oxml-3.8-20120326.jar writes XLSX without a problem.
By the way: I use R2015b and found that the culprit file is: poi-ooxml-3.12-20150511.jar
The other POI 3.12 files are not to blame.
This was a great help, thank you.
I am successfully able to use this code to write the contents of a cell array into a specified cell of an .xlsx file in Excel 2011 for Mac, but how do I get the contents of the cell to keep the previous formatting of the cell? For example, the blank cell I am writing into is set to bold Cambria font size 10, but when I run this code, the contents of the cell change to non-bold Calibri (Body) font size 12. Is there a trick to keep the formatting the same? Thanks for the help!
Well.... with regard to the "xlwrite creates xlsx files that cannot be read by xlsread on a mac" problem: Things have gotten better (2015a) in that an error is no longer generated HOWEVER only one value is read.
After running the 4 lines below:
sFile='/junk/test.xlsx';
sWorksheet='Worksheet Name';
xlwrite(sFile,{'Cell 1','Cell 2';'Cell 3','Cell 4'},sWorksheet);
[fu fu caDataRead]=xlsread(sFile, sWorksheet);
caDataRead only has the contents of one cell:
caDataRead = 'Cell 1'
I figured it out - it was because I didn't have the full filename (including extension). Once I had the extension added it works fine.
When I try to write a second worksheet, it just overwrites the first. When I try this:
testheader={'A' 'B' 'C'};
xlwrite('testfilename',testheader,'sheet 1')
xlwrite('testfilename',testheader,'sheet 2')
I just get a single worksheet with the header text. Am I doing something wrong?
Add the following code to the beginning of xlwrite.m, and keep the poi_library folder in the same directory as xlwrite.m (i.e. if xlwrite.m is in /toolbox, then we have /toolbox/xlwrite.m and /toolbox/poi_library/...
% Check if POI lib is loaded
if exist('org.apache.poi.ss.usermodel.WorkbookFactory', 'class') ~= 8 ...
|| exist('org.apache.poi.hssf.usermodel.HSSFWorkbook', 'class') ~= 8 ...
|| exist('org.apache.poi.xssf.usermodel.XSSFWorkbook', 'class') ~= 8
% load into path
pathname=regexp(which('xlwrite'),'(?<name>.*)\\.*.m','names');
loc=sprintf('%s%cpoi_library%c',pathname.name,filesep,filesep);
jars=strcat(loc,{'poi-3.8-20120326.jar';'poi-ooxml-3.8-20120326.jar';...
'poi-ooxml-schemas-3.8-20120326.jar';'xmlbeans-2.3.0.jar';...
'dom4j-1.6.1.jar';'stax-api-1.0.1.jar'});
javaaddpath(jars);
% make sure it worked
if(exist('org.apache.poi.ss.usermodel.WorkbookFactory', 'class') ~= 8 ...
|| exist('org.apache.poi.hssf.usermodel.HSSFWorkbook', 'class') ~= 8 ...
|| exist('org.apache.poi.xssf.usermodel.XSSFWorkbook', 'class') ~= 8)
error('xlWrite:poiLibsNotLoaded',...
'The POI library is not loaded in Matlab.\nCheck that POI jar files are in Matlab Java path!');
end
end
My remarks below apply to another version of xlwrite at:
http://www.mathworks.com/matlabcentral/fileexchange/37560-xlwrite---export-data-to-excel-from-matlab-on-mac-win?s_tid=srchtitle
The present version works fine, once you figure out how to deal with the Java path, which is neither documented nor obvious.
I put the POI library in a subdirectory of a directory I use during startup, then moved the javaaddpath commands from Test_xlWrite to my startup.m and edited them to point to where I put the POI library, referencing it relative to where I execute the javaaddpath commands during startup.
The javaaddpath commands populate a "dynamic" Java path, and aren't meant for persistent use, but I have not been able to add to the 'static' Java path, I expect because of syntax of path designation. I'll appreciate clear instructions on populating the static path, but for now the dynamic path works.
The maintenance in File Exchange of two programs named xlwrite, with two series of updates to them, is very confusing - I'm surprised this is possible.
I can't get this to go. It errors on "Undefined function or variable 'XSSWorkbook' (line 148). By that point it has already satisfied itself that it has what it needs in the way of poi library. But, perhaps it's checks are invalid. XSSWorkbook is some kind of template or structure from poi. Possibly a path to poi library issue?
Worked perfectly.
OSX 10.10.3, MATLAB 2014a
@Lil shen: To export multiple variables in one sheet: Combine all the variables in one variabele, and use this variable in your xlwrite command.
Do you have the problem when you define a name for the sheet?
@Alec(08 Feb 2015): Hi Alec, it seems to me that calling the xlwrite command twice doesn't work, the latter sheet will overwrite the first sheet. And how can I export multiple variables in one sheet?
Excellent function.
But I had to add one line just at the end of xlwrite in order to force excel to actualize formulas in cells when the created file is opened :
% Force the recalculation
xlsWorkbook.setForceFormulaRecalculation(true);
% Write & close the workbook
fileOut = java.io.FileOutputStream(filename);
xlsWorkbook.write(fileOut);
fileOut.close();
status = 1;
OK I found what was missing.
It seems that I needed a pair of curly brackets around the string:
xlwrite(fullfile(strain,excelFile),{strain},'summary','C10')
It works now!
Works great!
@Alex: I used J's (03 Dec 2014) suggestion and it worked out of the box. Please consider it!
Edit: I'm getting the string strain='Ecoli' spread through the cells from C10 to C14 when using
xlwrite(fullfile(strain,excelFile),strain,'summary','C10')
Why is that?
@Alexandra: this should work normally. Just call the xlwrite command twice:
xlwrite(filename,values1,'sheet1')
xlwrite(filename,values2,'sheet2')
Is there a way to use this function to create 2 sheets in one Excel file? I want to put some data on sheet 1 and some data on sheet 2, but I want it all in the same file
Great Work. Thanks a lot!
Nearly perfect - I would suggest a autoloading of the needed libraries like this:
% Check if POI lib is loaded - try to autoload
if exist('org.apache.poi.ss.usermodel.WorkbookFactory', 'class') ~= 8 ...
|| exist('org.apache.poi.hssf.usermodel.HSSFWorkbook', 'class') ~= 8 ...
|| exist('org.apache.poi.xssf.usermodel.XSSFWorkbook', 'class') ~= 8
try
cpath=fileparts(which(mfilename));
javaaddpath([cpath filesep 'poi_library' filesep 'poi-3.8-20120326.jar']);
javaaddpath([cpath filesep 'poi_library' filesep 'poi-ooxml-3.8-20120326.jar']);
javaaddpath([cpath filesep 'poi_library' filesep 'poi-ooxml-schemas-3.8-20120326.jar']);
javaaddpath([cpath filesep 'poi_library' filesep 'xmlbeans-2.3.0.jar']);
javaaddpath([cpath filesep 'poi_library' filesep 'dom4j-1.6.1.jar']);
catch
error('xlWrite:poiLibsNotLoaded',...
'The POI library is not loaded in Matlab.\nAutoloading failed ...\nCheck that POI jar files are in Matlab Java path!');
end
end
That maintains a high flexibility and increases the flawless usability also for matlab beginners ...
kind regards,
J
Hi Alex,
I'm having the same error some others were having, namely: "The POI library is not loaded in Matlab.
Check that POI jar files are in Matlab Java path!"
I've already added the whole xlwrite folder (with the poi_library folder) into the working directory, and also added a copy into the dynamic java path of MATLAB:
e.g. javaclasspath shows
DYNAMIC JAVA PATH
/Applications/MATLAB_R2013a.app/java/jar/poi_library
Any suggestions?
Thanks!
Ok the problem seems to be two of the tokens appear in reverse order to what getSheetNames was expecting. A fix that worked for me was to insert the following code before line 21 of toolbox/matlab/iofun/private/getSheetNames.m:
if isempty(match)
match = regexp(workbook_xml, ...
['<sheet name=".*(?<=<sheet name=")(?<sheetName>.+?)(?=" r:id="rId' num2str(sheetIDs(i)) '" sheetId=".*"/>)'], ...
'names' );
end
Hi Alec
Thanks for solving most of my problem, I'm just left with one issue. How do I either delete the content of a Cell or overwrite it with Not a number =NA().
Currently trying =NA() I get a string instead of having excel evaluating it to "#N/A" which is not plotted rather than the string being plotted as 0.
Thanks Martin
@Nikos
If you want to place the POI files somewhere else, it is possible, as long as you change the paths correctly in the "javaaddpath" commands.
Hello again,
Problem solved, I had multiple copies of Text.xlWrite.m in different folders.
xlwrite works perfectly as long as Text.xlWrite.m and the folder poi_library are both in the current directory.
Sorry for the bother.
Thanks again, excellent work!
Hi Alec,
This project was much needed, thanks.
I seem to have the same proble as @azizulh khan and @Shawn - I get the error:
"The POI library is not loaded in Matlab.
Check that POI jar files are in Matlab Java path!"
I am certain poi_library is in my workspace and in the matlab path as is Text.xlWrite.m
Please help!
Kind regards, Nikos
@Azizullah Khan
These should be in the zip file. Just extract the whole zip folder to your matlab workspace to make the demo work.
Alec
sir,make me clear about poi library
how can i insert it and from where can i get these files.The jar files?
thanks
Nice job but I believe there is a small problem.
xlwrite is able to create an xlsx file with multiple worksheets which I can open with Excel but I cannot read this file with xlsread. However, if I open the xlsx file with excel and then simply hit save (ostensibly making no changes), I can then open with xlsread.
Any ideas?
Thanks,
Dan
@Gaurav and others facing that bug,
This may be due to some unexpected formatting in some description xml file(s) in the xlsx.
If you have write access to the matlab installation, you can fix it by editing the file
MATLAB/R2014a/toolbox/matlab/iofun/private/getSheetNames.m
replace line 12 with:
'<Relationship Id="rId(\d+?)" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/.*\.xml"\s*/>', ...
and line 19 with:
['<sheet name=".*(?<=<sheet name=")(?<sheetName>.+?)(?=" sheetId=".*" r:id="rId' num2str(sheetIDs(i)) '"\s*/>)'], ...
I have not tested this fix extensively but I think it should not cause problems with other files.
@Alec,
Sorry for hijacking your comment section, but this is the number one page on google at the moment for that issue.
@Gaurav:
It seems like a bug in Matlab itself.
Best would to file a bug submission at the Mathworks.
If it is urgent, you can edit the xlsread.m file directly to remove the bug. I don't have 2014a, so I cannot correct it myself.
Alec
The thing is, I'm writing hundreds of files so I can't open each one in openoffice and hit save to be able to read them with xlsread. Any work around will be appreciated.
Best,
G
@Alec,
Great work, great help, thanks a lot mate!
I'm still having trouble reading the file created by xlwrite through xlsread. I have installed the latest version of of Matlab (2014a). The error I get is:
Error using xlsread (line 247)
No right hand side value for assignment.
But if I open the file in openoffice, hit delete on an empty cell (so not really changing anything), and save it then xlsread can read it. So, I guess they have not really fixed the bug with xlsread.
Best,
G
@Shaun:
It seems the "poi_library" folder is not in the same folder as your Text.xlWrite.m file....
@Volker Schardt
Sorry for the late reply. I will be more than happy to add your code to the zip file. Can you please contact me by Private Message?
Thanks!
Alec
Hi Alec,
Thank you for your great work!
But I have a problem...
I'm using Mac OS X. Matlab 2013a.
i got the following error when run the Text_xlWrite.m.
Error using xlwrite (line 93)
The POI library is not loaded in Matlab.
Check that POI jar files are in Matlab Java path!
Files were loaded with no errors.
I downloaded following jar files.
I've run following codes.
javaaddpath('poi_library/poi-3.8-20120326.jar');
javaaddpath('poi_library/poi-ooxml-3.8.jar');
javaaddpath('poi_library/poi-ooxml-schemas-3.8-beta4-20110826.jar');
javaaddpath('poi_library/xmlbeans-2.3.0.jar');
javaaddpath('poi_library/dom4j-1.6.1-hudson-1.jar');
javaaddpath('poi_library/stax-api-1.0.1.jar');
javaaddpath('poi_library/jxl.jar');
I got errors when the following code was run.
xlwrite(fileName, xlsData, sheetName, startRange);
Nice work indeed!
@ thomas, based on Alec work I did an xlread function. But I haven't done all the cross check work for my input that are needed to provide a out of the box function which can be used by anyone. Maybe I can do this later this week.
@ Alec if you like I would offer you my code to inculde it to your package, instead of opening a new site here on file exchange
@Alec. The function works writing xls-files, but importing the data again (by xlsread) leaves empty data and txt variables.
Hey, just a beautiful and useful function !
the only thing i changed is the auto-cast to cell array for dataset type (will do the same for tables)
if isa(A,'dataset')
A = dataset2cell(A);
end
Only one request : will you provide an xlread which does the same job in the other way ?
EDIT
I also add the auto-import command :
pkg = dir(fullfile(fileparts(mfilename('fullpath')),'lib','*.jar'));
for i=1:length(pkg)
jar{i} = fullfile(fileparts(mfilename('fullpath')),'lib',pkg(i).name);
end
cellfun(@javaaddpath,jar);
I am getting the following error when trying to paste a 28900,14 cell array in an xlsx file. it seems my memory funs out?
Warning: A Java exception occurred trying to load the sprintf class:
Java exception occurred:
java.lang.OutOfMemoryError: GC overhead limit exceeded
at java.util.Arrays.copyOf(Arrays.java:2271)
at java.util.zip.ZipCoder.getBytes(ZipCoder.java:89)
at java.util.zip.ZipFile.getEntry(ZipFile.java:306)
at java.util.jar.JarFile.getEntry(JarFile.java:226)
at java.util.jar.JarFile.getJarEntry(JarFile.java:209)
at sun.misc.URLClassPath$JarLoader.getResource(URLClassPath.java:840)
at sun.misc.URLClassPath.getResource(URLClassPath.java:199)
at java.net.URLClassLoader$1.run(URLClassLoader.java:358)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:270)
at com.mathworks.util.jarloader.SimpleClassLoader.loadClass(SimpleClassLoader.java:233)
at com.mathworks.util.jarloader.SimpleClassLoader.loadClass(SimpleClassLoader.java:201)
at com.mathworks.jmi.OpaqueJavaInterface.findClass(OpaqueJavaInterface.java:213)
> In ExternalException>ExternalException.ExternalException at 35
In JavaException>JavaException.JavaException at 38
In xlwrite at 260
In dikablis_analysis at 445
Error using xlwrite (line 260)
Java exception occurred:
java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:174)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:147)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:39)
Error in dikablis_analysis (line 445)
xlwrite(Xcel_FileNameExt,CELLTIME,sheetName,startRange);
feature request: table support...unfortunately it seems I can only export cells, vectors or matrixes but not tables?
@Dan: I just got a reply back from Mathworks support:
"This bug should be fixed with R2014a. The PreRelease for R2014a is available already and it should also contain the bug fix."
@Dan: XLSREAD has a bug in its implementation to read xlsx files; I have informed Mathworks support regarding this. The Mathworks development team has acknowlodged the bug. Don't know yet if they fixed it.
The workaround is to use xls files if you can.
@Nik: can you send me a demo code so I can reproduce it and fix the bug?
Thanks,
Alec
@Niklas: Try to specify the extension. For me the problem occured when not specifying it:
fileName = 'myfile' does not work with several sheets.
fileName = 'myfile.xlsx' does.
Don't ask me why.
A great submission but there seems to be problems when I use xlsread to read these files. I am unable to read an xlsx workbook with multiple worksheets created with xlwrite (on a Mac). I just get "Worksheet not found".
Hi Alec,
am trying to write a multiple sheet .xls-File. Unfortunately, on every loop is overwrites the worksheet. So at the end I get an xls-File with one worksheet created in the last loop. Sheetname changes on every loop.
If I understand the description correctly, the function should add the worksheet a the end?!
Thanks for help!
Regarding the problem below. There was a corrupted existing myfile.xlsx already in the directory where xlwrite was trying to create the new file.
Hi Alec. I have been using this on Windows and it is great. I have moved m code onto a linux machine and am encountering the following exception:
java.lang.illegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream at ...WorkbookFactory.create(WorkbookFactory.java (line 137 or xlwrite: xlsWorkbook = WorkbookFactory.create(fileIn))
I am trying to create an xlsx file which has been working flawlessly in windows. i have checked all the .jar dependencies and they are identical on my windows install as the linux one. Any suggestions to help fault-finding?
Thanks,
Tom
Hello Guillaume,
Your are right. I will try to send a bug fix as soon as possible.
Best regards,
Alec
Oups my last post has been deleted.
Actually, i've got the following error
Error using xlwrite (line 179)
Java exception occurred:
java.lang.IllegalArgumentException: The workbook already contains a sheet of this name
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:619)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:70)
when i used xlwrite several times on a new workbook with a specific worksheet (e.g. 8th sheet). The function created the workbook with one sheet named "sheet 8". So when i tried to use xlwrite again on the 8th sheet, xlwrite didn't recognized "sheet 8" as the 8th spreadsheet and tried to create a newer with the name "sheet 8". This induced the above error.
replacing line 179 : "xlsSheet = xlsWorkbook.createSheet(['Sheet ' num2str(sheet)]);"
by :
"for sheet_number=1:sheet
xlsSheet = xlsWorkbook.createSheet(['Sheet ' num2str(sheet_number)]);
end"
Works well for me, but is it reproducible and reliable with different cases or is it the best way to do this ...
Thanks again!
@Guillaume:
I highly recommend to use the 'fullfile' command of Matlab to generate paths to directories or files. It allows you to use relative and absolute paths, independent of the operating system.
The error you get is because could not open or store the file in the requested location.
Hope it helps.
Thanks Alec!
You're right, my Poi_Library was not loaded. - -'
But after that, I still got an error :
Error using xlwrite (line 286)
Java exception occurred:
java.io.FileNotFoundException: ~/Documents/Recherche/Etude Course vs Bondissements/Donnees/Fichiers Sujets/Cyril
Schmit2/Analyse Force Cyril Schmit2.xlsx (No such file or directory)
at java.io.FileOutputStream.open(Native Method)
at java.io.FileOutputStream.<init>(FileOutputStream.java:194)
at java.io.FileOutputStream.<init>(FileOutputStream.java:84)
Actually, i had recently changed my laptop to the macbook pro and realized that , with MacOS X, the path filename attribution method is not as easy as Windows .
After multiple trial, i realized that the relative mode didn't work with XlWrite. Therefore, the path filename have to be loaded in absolute mode (starting with /User/username/etc.) with MacOS X
Don't know if it's mentioned in the Help section...
Guillaume
P.S: Sorry for my poor english skills.
@Guillaume: Thanks.
Yes it is possible to write to any file on your drive. We do it all the time.
The error you get seems to be caused by one of the libraries that is not loaded. I think it is poi-ooxml or poi-ooxml-schemas, but not sure.
Very, Very Nice function!!! Thanks for the submission
I would just like to know, is it possible to write on specific path file? I'm working with MACOS X (Mavericks) and when i tried to write in excel spreadsheet out of the matlab path folder the following error appeared :
Error using xlwrite (line 148)
Java exception occurred:
java.lang.NoClassDefFoundError: Could not initialize class org.apache.xmlbeans.XmlBeans
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:290)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:170)
THANK YOU!!!!!
@ S A: Just add the folder to your Matlab path, and then it will always be loaded by default.
You can access the "Set Path" in the ribbon toolbar of Matlab. Hope it helps!
Works great!
I have used Matlab for years on a Windows system, and am just starting on a Mac. How do you add this file to your directory? I copied the poi_library folder to my java folder, and added the .jar files to the classpath.txt file. But how do I add xlwrite to the general matlab directory (so that it may be called in any folder as a built-in matlab function)?
Hi all,
The column index error is due to the Microsoft limitation for *.xls-files (Excel 1997-2003). Just change your specified file extension to *.xlsx to solve the problem.
Thanks Alec for the hint.
Cheers,
Dominik
Hi Alec,
Thanks for the great submission.
However, like Corbin, I am also receiving an error concerning the column size:
"java.lang.IllegalArgumentException: Invalid column index (256). Allowable column
range for BIFF8 is (0..255) or ('A'..'IV')"
Is there a workaround to this?
Thanks,
Dominik
Hi Robert,
It should normally append the data. It will only overwrite data if your column header matrix is too big or if your cell range is not properly defined.
If you cannot solve it, please send me a mail with a demo code, so I can reproduce it, and I will fix it as soon as possible.
Hi Alec,
Thanks for sharing this excellent code. I just tried to write data and then add column headers but found that xlwrite (as opposed to xlswrite) does not append to an already existing file but rather replaces it. Am I missing something?
Thanks, Robert
Hi Alec,
I am receiving this error: "Warning: Could not add cell at RY338 because it exceeds the maximum column limit"
What is the limit on the number of columns xlwrite can make and is there a wordaround for that?
Any info would be great!
Best,
Corbin
@Klever: You get this error, because you are running an old version of Matlab(2009a) which does not support the tilde (~) operator to ignore outputs of a function. This has only been introduced in Matlab 2009b. You can read more about it on http://blogs.mathworks.com/loren/2009/09/11/matlab-release-2009b-best-new-feature-or/
As a workaround, you could try to modify line 140 in xlwrite from
[~,~,fileExt] = fileparts(filename);
to
[unusedA,unusedB,fileExt] = fileparts(filename);
Hi Alec, This script seems like it has great potential for my application. However, it works great on Windows, but on Red Hat and with MATLAB 7.8.0 (2009a) I keep getting the following error when I run your test script:
??? Error: File: xlwrite.m Line: 140 Column: 7
Expression or statement is incorrect--possibly unbalanced (, {, or [.
Error in ==> Test_xlWrite at 28
xlwrite(fileName, xlsData, sheetName, startRange);
Can't seem to solve this error. Any Suggestions?
@Jeremy: This is a bug in the implementation of XLSREAD. When opening an XLSX on a Mac, XLSREAD opens it in 'basic' mode. The 'basic' mode in XLSREAD has a bug when opening XLSX. If you try [~,b,c] = xlsread('myXlsx.xls', 1, '', 'basic') on your windows PC you will see the same bug.
I would recommend to save the file as XLS if possible.
@Barry: There is no plan to create an xlsread version with the POI library. Did you try using the xlsread in 'basic' mode? This should normally avoid the "Could not start Excel server..." error.
I was just wondering if there is a xlread version using the POI library. When using the normal xlsread in a loop I eventually get a "Could not start Excel server...". Thanks.
Update: I tried this on Windows 7 and it worked correctly. On my Mac(10.8) I encountered the problem. Also this was as a .xlsx, it seems the .xls works as stated.
Hi, I've noticed a bug(s) when trying to use xlsread after xlwrite. In my situation it was occurring when the first column of a record was a '1A' instead of a '1a', but solving this next bug may catch that one. I tried to make a sample program to illustrate the bug. The return did not get the second row on the read. Note: If i go into Excel and do something and resave, xlsread then reads the second row:
titles = {'label1', 'label2'};
record = {1, 2};
xlwrite('myXlsx.xlsx', titles, 'A1:B1');
xlwrite('myXlsx.xlsx', record, 'A2:B2');
[~,b,c] = xlsread('myXlsx.xlsx')
b =
'label1'
c =
'label1'
great - a little slow - but still great!
Great submission, thanks.
Why don't you add something like this to beginning:
path = mfilename('fullpath');
name = mfilename;
path = path(1:length(path)-length(name));
javaaddpath([path 'poi_library/poi-3.8-20120326.jar']);
javaaddpath([path 'poi_library/poi-ooxml-3.8-20120326.jar']);
javaaddpath([path 'poi_library/poi-ooxml-schemas-3.8-20120326.jar']);
javaaddpath([path 'poi_library/xmlbeans-2.3.0.jar']);
javaaddpath([path 'poi_library/dom4j-1.6.1.jar']);
@Mario: Thanks!
@Thierry: thanks for the recommendations!
1) The Stax library should indeed also be loaded. This will be fixed in next release.
2) Originally we also wanted to add an auto-load function, but we ran into the issue that the POI folder location is unknown. It is allowed to be anywhere on your computer. For example in our matlab-webapplications, the POI libraries are put next to the other java libraries in a central folder. To keep it as generic as possible we decided to throw an error if it is not loaded.
3)xlwrite replacing NaN with the previous number is indeed a bug. This was solved in the update of 25/01/2013.
Best Regards,
Alec
Hi Alec thanks for this wonderful submission it works great.
1.It seems you also need to javaaddpath the stax*.jar to get it work, right?
(it is omitted in the test_xlwrite).
I would add something like this in the beginning of xlwrite:
if exist('org.apache.poi.ss.usermodel.WorkbookFactory', 'class') ~= 8 ...
% || exist('org.apache.poi.hssf.usermodel.HSSFWorkbook', 'class') ~= 8 ...
% || exist('org.apache.poi.xssf.usermodel.XSSFWorkbook', 'class') ~= 8
% auto-initialize
javadir=fullfile(fileparts(mfilename('fullpath')),'poi_library');
jarfiles=dir(javadir,'*.jar');
javaaddpath(jarfiles);
2. xlwrite replace NaN by previous numeric values
whereas xlswrite replace NaN by empty cell.
Hello Jveer,
It should not be a problem to use javaaddpath together with the compiler. We use it for example with de Matlab JA compiler in our company.
To make it work with the compiler:
- Add/drag&drop the POI folder to 'Shared Resource and Helper Files'-window of the compile project
- make sure the javaaddpath point to the right (relative) location.
I hope this helps
Thank you for the excellent submission.
Is there a way around 'javaaddpath' in order to compile using Matlab compiler?
Very nice, many thanks for sharing this. I've added the javaaddpath-commands to my startup.m file - this way xlwrite is immediately available
Hello Klaus, the bug is fixed & also the documentation was adapted. I uploaded a new version. It should be online once accepted by the Mathworks.
to get xlswrite behavier for NaN's just insert
if isnumeric(data)
if isnan(data)
data=[];
end
end
after
if ~isempty(data)
using cell arrays the NaN to empty conversion is currently not working.
in the help example xlSwrite is copy-pasted
Alec thanks a lot for the immediate feedback! I tried it and it works great, I can't thank you enough :)
Hello Marianna,
When you read the documentation of xl(s)write, you are not allowed to give an empty ([]) sheet. You should use the syntax xlwrite('test.xls',M,'A1:B10') instead of xlwrite('test.xls',M,[],'A1').
Setting the sheet to 1 is also an option, like you said... and the error you are getting is a bug:) I fixed it and uploaded a new version today. It should be online by tomorrow.
Hi and thanks for the much needed submission :)
I have been using the function for some weeks now without problem. Recently I tried to use it in the code of a simple GUI that I implemented and for some reason nothing works..
More specifically, I have o command of the type:
xlwrite('test.xls',M,[],'A1')
and get the following error:
??? No method 'getSheet' with matching signature found for class
'org.apache.poi.hssf.usermodel.HSSFWorkbook'.
Error in ==> xlwrite at 161
xlsSheet = xlsWorkbook.getSheet(0);
If I set the sheet to 1, I get:
??? Java exception occurred:
java.lang.IllegalArgumentException: Sheet index (0) is out of range (0..-1)
Note that everything works fine when I save my variables in the workspace and use the same code from the command line (so it obviously has to do with the GUI).
Any idea if/how I can work around this?
Any help would be much appreciated!
Thanks,
Marianna
@ Dan & Jason: Thanks for the feedback!
I fixed the bug & uploaded a new version. Should be approved by tha Mathworks soon...
Excellent job!
I had the same problem mentioned by Jason. I replaced line 211 by:
currentRow = xlsSheet.getRow(iRow);
if isempty(currentRow)
currentRow = xlsSheet.createRow(iRow);
end
Hope it helps!
When I try to specify a range, it wipes out the entire row. So for example the following code
xlwrite('M:\test.xls',[1,2,3,4],'Data','B5:E5');
writes the data in the proper cells but all of the other cells of row 5 are overwritten to be blank. Is there a way to preserve the data in the other cells?
Hi,
problem solved, xlsread(fileName) works. Great job, thanks a millon.
Omar,
the problem should be solved with this update (for XLS files).
Alec
Hi,
How to read the file again in matlab
xlsread give the following:
Error using xlsread (line 232)
XLS File contains unicode text which is not yet
supported.
Any suggestions?
Thanks in advance
Your code just worked on Linux ubuntu with MATLAB 2011b.Thanks
nice!
Now I got it !!!
thanks a lot
@ Peter
This error is caused because the jxl.jar (JExcelApi) package is not loaded correctly in your workspace.
- Check that you extracted correctly the jxl.jar file from the zip file.
- You should not forget to add the jxl.jar file to your Matlab Java path as explained in test_xlwrite. If jxl.jar is in the same directory as your current workspace: you should execute the "javaaddpath('jxl.jar');" command. If jxl.jar is not in the same directory as your current workspace, you should execute the "javaaddpath('\FULL\PATH\TO_jxl.jar');".
Please let me know if this solves your problem or not.
Alec
Please let me know
??? Undefined variable "jxl" or class "jxl.Workbook.createWorkbook".
I tried to run the test_xlwrite and got the following ?
Error in ==> xlwrite at 87
xlsWorkbook = jxl.Workbook.createWorkbook(xlsFile);
Error in ==> test_XLWRITE at 19
xlwrite(fileName, xlsData, sheetName, startRange);