Reading Excel Data

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.

Back to O-Matrix Data Access and Manipulation Page.