<< xlinfo xlreadwrite xlread_data >>

xlreadwrite >> xlreadwrite > xlread

xlread

Read Microsoft Excel spreadsheet file using Java

Syntax

value = xlread(file_path)
value = xlread(file_path,sheet)
value = xlread(file_path,range)
value = xlread(file_path,sheet,range)
[value,text,data] = xlread(...)
[value,text,data,custom] = xlread(file_path,sheet,range,processFun)

Arguments

file_path

a character string giving the path of the Excel file.

sheet

an integer giving the sheet position or a character string giving the sheet name.

range

a character string giving the rectangular range to be read in the sheet using Excel syntax.

processFun

Scilab function used to process the sheet data.

value

a matrix of numbers containing the numerical data found in the sheet at specfied range. The cells without numerical data are represented by NaN values. The value of cells with date type is the corresponding Excel serial date number (origin is 1900). To convert to a Scilab serial date number (day 1 is 1-jan-0000) add 693960. The formated date string is in the text output.

text

a matrix of strings containing the textual data found in the sheet at specfied range. The cells without text data are represented by empty strings. The string for cells with date type is the original formated date string. The Excel serial date number is in the value output.

data

a cell array containing the raw data found in the sheet at specfied range. Empty cells are represented by NaN values.

custom

contains the eventual second output from processFun

Description

This function reads a given sheet of an Excel workbook by eventually specifying the range to be read using the Excel syntax e.g. A1:B3. It returns the numerical data and the strings contained in the sheet cells.

When the sheet argument is not given, then the first sheet of the workbook is read. In this case, the range argument must include a colon, otherwise it will be interpreted as a sheet name.

When the range argument is not given, then the whole sheet is read and the value,text matrices tightly enclose the region where data of the corresponding type is found. When no data of a given type is found, the corresponding matrix is empty. If the resulting matrices have a non-homogeneous type, the corresponding cells are set to %NaN in value or the empty string "" in text.

Otherwise it can be of the form A1 to read a single cell, A1:B3, A1:, :B3, where the two latter specify the start or the end cell of the range. In that case (and when not empty) value,text,data have the same size as the given range and type non-homogeneous matrices are padded as described before.

When the processFun parameter is given, the function is called with a single argument Data where Data.Value contains the raw data of the sheet, Data.Count is the number of cells and Data.WorkSheet is the sheet as a Java object. The first output of processFun replaces the raw sheet data before further analysis and the eventual second output is returned in the custom output argument.

Only .xls and .xlsx files are handled (EXCEL97 xlWorkbookNormal and EXCEL2007 xlOpenXMLWorkbook). Binary .xlsb files are not supported.

Examples

// read first sheet
filename = fullfile(SCI,'modules/spreadsheet/demos/xls/Test1.xls');
[numbers,text,raw] = xlread(filename)

[numbers,text,raw] = xlread(filename,"A1:D4")

// read specified range of second sheet of xlsx file
filename = fullpath(atomsGetLoadedPath("xlreadwrite")+'/demos/xlsx/Testbig.xlsx');
numbers = xlread(filename,2,"B3:D6")

// read specified range of sheet named "Sheet 2" of xlsx file
numbers = xlread(filename,'Sheet 2','B4:D14')

// apply function to data
function Data=convert(Data)
    for k = 1:Data.Count
        Data.Value{k} = floor(100*Data.Value{k});
    end
end

numbers = xlread(filename,'Sheet 2','B4:D14',convert)

See also

Bibliography

This function is based on POI library (http://poi.apache.org/).


Report an issue
<< xlinfo xlreadwrite xlread_data >>