Jiro's pick this week is Excel Column Conversion by Mike Sheppard.
Oftentimes, I work with Excel files. I read from and write to them using xlsread and xlswrite, respectively. Both of these functions have an option to specify the range of the worksheet to access, and this is especially useful when I am trying to load in one section at a time or append data to the same file. However, to do that I need to specify the columns using Excel's column naming convention. For example, 5th column is "E" and 28th column is "AB".
Mike's function ExcelCol makes this conversion very simple:
ExcelCol(5) ExcelCol(28)
ans =
'E'
ans =
'AB'
Now, my code for reading in the first 50 rows from specific columns (columns 120 to 150) of an Excel file looks like this:
colNames = ExcelCol([120, 150]) rng = sprintf('%s1:%s50', colNames{:}) data = xlsread('datafile.xlsx', rng);
colNames =
'DP'
'ET'
rng =
DP1:ET50
This function works the other way as well, going from a column label to a number:
ExcelCol('ABC')ans = 731
Comments
Give this a try and tell us what you think, or leave a comment for Mike on the submission page.
Get
the MATLAB code
Published with MATLAB® 7.10
First of all, this is indeed very usefull, but on the topic of reading and writing to excel there are still a few issues that can be adressed (and have been by the file exchange community).
The problem with the standard xlsread and xlswrite is that they both start an excel COM server, open the workbook, do their thing (save if necessary), close the workbook and finally close the server. This obviously causes a lot of overhead when reading and writing in bulk is needed. The file exchange therefor offers xlsread1 and xlswrite1. This code allows you to start your own excel server:
global Excel Excel = actxserver('excel.application'); Excel.Workbooks.Open('your workbook here.xls'); [num, txt, raw] = xlsread1([], 'sheet'); % [] for file because it's already loaded. %do stuff with the data ... Excel.ActiveWorkbook.Save: Excel.ActiveWorkbook.Close; Excel.Quit; Excel.delete;This might seem as a lot of work, and not straightforward for people who don’t want this much, so perhaps it would be nice to introduce an option in the standard xlsread like:
Another hint for speeding up the xlswrite is to set the calculation mode in Excel to manual when writing. Standard (for most Excel users) this is set to automatic, causing excel to do a lot of recalculations when a cell is written. Image a sheet with a great deal of dependencies and a lot of data to write… this little trick sped up my algorithm with a factor somewhere in between 1e6 and 1e9.
On the topic itself (the column conversion): there are two usefull files to be found:
Both files can take array-like (cell for the column to number) arguments making both very usefull.
Hope this has been usefull.
Sander
Excellent comment, Sander. Thanks!
I like those FEX entries you mentioned for improving reading and writing to Excel files. It would be nice if those setup code before and after were separate functions, like
fopenExcel % connect to Excel
% do stuff in Excel
fcloseExcel % close connection with Excel
For your enhancements to XLSREAD, please submit it through http://www.mathworks.com/support/. It sounds pretty useful.
Thanks for pointing out the two functions for converting column labels. I saw another entry, but I must’ve missed that one.
Many people pointed out the overhead of the xlsfunctions. There are several fast excel submissions but I agree that the best solution would be a fopenExcel and a fcloseExcel.