Tutorial 14 – Reading Microsoft Excel Files
Scilab provides a set of functions to read Microsoft Excel files. Scilab
version 5.1.1 can read Excel files up to version 2003 (.xls), but not Microsoft
Excel 2007 (.xlsx) files. The steps to read an Excel file are: (i) open the file with
xls_open() function, (ii) read the file contents with the xls_read() function
and (iii) close the file with the mclose() function. Before opening the file, check
the current working directory and change it if necessary. Alternatively, note the
complete path to the file and use it to define the full path to the file location.
Assuming the cells A1:C3 in
Sheet1 contain data as shown in
the adjoining figure, Value is a
3x4 matrix containing, TextInd is
a 3x4 matrix and SST is a 1x3
matrix as shown in the output
above.
Following is a simple example of how you can open and read an Excel file,
assuming that the file is named test.xls and is located in the current working
directory:
-->[fd, sst, sheetnames, sheetpos] = xls_open('test.xls');
-->[value, text] = xls_read(fd, sheetpos(1));
-->mclose(fd);
-->disp(value)
1. 2. 3. Nan
4. 5. 6. Nan
7. 8. 9. Nan
-->disp(text)
0. 0. 0. 1.
0. 0. 0. 2.
0. 0. 0. 3.
-->disp(sst)
! One Two Three !
The function xls_open() returns four values, fd is the file descriptor that
is required during subsequent operations on the file, SST is the vector of all
strings in the file, Sheetnames is the vector of names of all sheets in the file and
Sheetpos is a vector of numbers indicating the beginning of different sheets
within the file. The function xls_read() requires the file descriptor fd and the
sheetpos of the sheet to be read, previously obtained from xls_open() function
call. To read the contents of the first sheet, use sheetpos(1). This function
returns two values, Value is a matrix of containing all numerical values in the
sheet with Nan (Not a number) in the cell positions containing non-numeric
data and text is a matrix containing zeros corresponding to cells containing
numeric data and non-zero values corresponding to string data. The non-zero
integers in text point to the corresponding string value stored in the variable
sst returned by the xls_open() function call.
Tutorial 14 – Reading Microsoft Excel Files | 40
There is another function readxls() that can read Excel files. It is simpler
to use compared to the xls_open(), xls_read() and mclose() combination.
-->sheets = readxls('test.xls');
-->s1 = sheets(1);
-->a = s1.value
a =
! 1. 2. 3. One !
! 4. 5. 6. Two !
! 7. 8. 9. Three !
-->s1.text
-->ans =
! One !
! Two !
! Three !
The Scilab variable editor editvar can show the values read by readxls()
function and also allow editing and updating of values to Scilab workspace.
-->editvar s1
ไม่มีความคิดเห็น:
แสดงความคิดเห็น