Contents

Previous

Next

Subchapters

Current Chapters-> nrows ncols read read3 read4 readh read5 read6 read7 write write3 writesep close eof omwrite omread IOformat xlread xlwrite xlsheets spread bread bwrite readwav h5read h5write diary matfiles netio urlget ftp

Parent Chapters-> Omatrix6 io xlread

Search Tools-> contents reference index search

Reading Excel Data

Syntax	xlread(file) xlread(file, sheetName)
See Also	xlwrite , xlsheets , read4 , bread , spread

Overview
Reads data from Microsoft Excel files, (.XLS files). The argument file must be a character row vector that specifies the name of the file to load. By default xlread loads the first worksheet in the given file. If all data in the given worksheet are numeric-valued, then the value returned by xlread is an NR by NC double matrix where NR is the number of rows in the worksheet, and NC is the number of columns. The first non-empty row in the worksheet is assumed to be column headers used by Excel and are not loaded by the xlread function. If present, the second argument sheetName must be a character row vector that specifies the name of the worksheet within the given file to load.

Examples

Loading Numeric Data
If you have an Excel file xlio.xls, that contains 2 columns and 5 rows of numeric data that looks like

and at the O-Matrix prompt you enter,
     xlread("xlio.xls")
O-Matrix will respond
{
[ 1 , 1 ]
[ 2 , 1 ]
[ 3 , 2 ]
[ 4 , 4 ]
[ 5 , 8 ]
}

Loading Numeric Data from a Named Sheet
If you continue the above example by renaming the second sheet of xlio.xls to MySheet and enter the following data

and then enter the following at the command prompt
     xlread("xlio.xls","MySheet")
O-Matrix will respond
{
[ 1 , 1 ]
[ 2 , 4 ]
[ 3 , 9 ]
}

Loading Character Data
If you have an Excel file xlascii.xls that contains two non-numeric columns,

and you enter
     T = xlread("xlascii.xls")
O-Matrix will create the character matrix T. Excel returns the default number of columns for each column in the spread sheet, so the result T will contain 2 rows and 510 columns since each column A, and B contain 255 characters. You can use the align function to reduce the result. If at the O-Matrix prompt you enter
     align(T," ",[10,10])
O-Matrix will respond

oneone    onetwo    
twoone    twotwo    
threeone  threetwo  

Alternatively, if you know that each column of your Excel file contains the default number of characters, (255), you can use the following function to trim the blank columns from the result
function xlclip(T) begin
     nc = coldim(T)/255
     nr = rowdim(T)
     iCol = fill("",nr,1)
     for j = 0 to nc-1 begin
          iCol = [iCol, strclip(T.blk(1,j*255+1,nr,255))]
     end
     return iCol
end

Notes
The xlread function can only load Excel files that contain 30000 rows or less. You must have a copy of Excel installed on your machine to use the xlread function. The xlread function is compatible with Excel versions prior to Excel 2007. See the file omwin\function\xlread.oms for instructions on using xlread with Excel 2007.