xlsread
Read Microsoft Excel spreadsheet file
Syntax
num = xlsread(filename)
num = xlsread(filename, -1)
num = xlsread(filename, sheet)
num = xlsread(filename, range)
num = xlsread(filename, sheet, range)
num = xlsread(filename, sheet, range, 'basic')
num = xlsread(filename, ..., functionhandle)
[num, txt]= xlsread(filename, ...)
[num, txt, raw] = xlsread(filename, ...)
[num, txt, raw, X] = xlsread(filename, ..., functionhandle)
xlsread filename sheet range basic
Description
num = xlsread(filename) returns numeric data in double array num from the first sheet in the Microsoft Excel spreadsheet file named filename. The filename argument is a string enclosed in single quotation marks.
xlsread ignores any outer rows or columns of the spreadsheet that contain no numeric data. If there are single or multiple nonnumeric rows at the top or bottom, or single or multiple nonnumeric columns to the left or right, xlsread does not include these rows or columns in the output. For example, xlsread ignores one or more header lines appearing at the top of a spreadsheet.
Any inner rows or columns in which some or all cells contain nonnumeric data are not ignored. Instead, xlsread assigns a value of NaN to the nonnumeric cells.
num = xlsread(filename, -1) opens the file filename in an Excel window, enabling you to interactively select the worksheet to read and the range of data on that worksheet to import.
To import an entire worksheet, first select the sheet in the Excel window, and then click the OK button in the Data Selection Dialog box. To import a certain range of data from the sheet, select the worksheet in the Excel window, drag and drop the mouse over the desired range, and then click OK. (See COM Server Requirements below.)
num = xlsread(filename, sheet) reads the specified worksheet, where sheet is either a positive, double scalar value or a quoted string containing the sheet name. To determine the names of the sheets in a spreadsheet file, use xlsfinfo.
num = xlsread(filename, range) reads data from a specific rectangular region of the default worksheet (Sheet1). (See COM Server Requirements below.)
Specify range using the syntax 'C1:C2', where C1 and C2 are two opposing corners that define the region to be read. For example, 'D2:H4' represents the 3-by-5 rectangular region between the two corners D2 and H4 on the worksheet. The range input is not case sensitive and uses Excel A1 reference style. For more information on this reference style, see Excel help.
    Note   If you specify only two inputs, xlsread must decide whether the second input refers to a sheet or a range. To specify a range (even a range of a single cell), include a colon character in the input string (e.g., 'D2:H4'). If you do not include a colon character (e.g., 'sales' or 'D2'), xlsread interprets the second input as the name or index of a worksheet.
num = xlsread(filename, sheet, range) reads data from a specific rectangular region (range) of the worksheet specified by sheet. If you specify both sheet and range, range can refer to a named range that you defined in the Excel file. (For more information on named ranges, see the Excel help.) See the previous two syntax formats for further explanation of the sheet and range inputs. (Also, see COM Server Requirements below.)
num = xlsread(filename, sheet, range, 'basic') imports data from the spreadsheet in basic import mode. xlsread uses this mode on systems where Excel software is not installed. Import ability is limited. xlsread ignores the value for range and, consequently, imports the whole active range of a sheet. (You can set range to the empty string ('').) Also, in basic mode, sheet is case sensitive and must be a quoted string.
num = xlsread(filename, ..., functionhandle) calls the function associated with functionhandle just prior to obtaining spreadsheet values. This enables you to operate on the spreadsheet data (for example, convert it to a numeric type) before reading it in. (See COM Server Requirements below.)
You can write your own custom function and pass a handle to this function to xlsread. When xlsread executes, it reads from the spreadsheet, executes your function on the data read from the spreadsheet, and returns the final results to you. When xlsread calls your function, it passes a range interface from the Excel application to provide access to the data read from the spreadsheet. Your function must include this interface both as an input and output argument. Example 5 below shows how you might use this syntax.
For more information, see Function Handles in the MATLAB Programming Fundamentals documentation.
[num, txt]= xlsread(filename, ...) returns numeric data in array num and text data in cell array txt. All cells in txt that correspond to numeric data contain the empty string.
[num, txt, raw] = xlsread(filename, ...) returns numeric and text data in num and txt, and unprocessed cell content in cell array raw, which contains both numeric and text data. (See COM Server Requirements below.)
If the Excel file includes cells with undefined values (such as '#N/A'), xlsread returns these values as '#N/A' in the txt output, and as 'ActiveX VT_ERROR:' in the raw output.
[num, txt, raw, X] = xlsread(filename, ..., functionhandle) calls the function associated with functionhandle just prior to reading from the spreadsheet file. This syntax returns one additional output X from the function mapped to by functionhandle. Example 6 below shows how you might use this syntax. (See COM Server Requirements below.)
xlsread filename sheet range basic is an example of the command format for xlsread, showing its usage with all input arguments specified. When using this format, you must specify sheet as a string, (for example, Income or Sheet4) and not a numeric index. If the sheet name contains space characters, then quotation marks are required around the string, (for example, 'Income 2002').
Remarks
COM Server Requirements
The typical installation of Excel for Windows includes the ability to start a COM server. With Excel for Windows installed, you can use xlsread to read any file format recognized by your version of Excel, including XLS, XLSX, XLSB, XLSM, and HTML-based formats. xlsread can read data saved in files that are currently open in Excel for Windows.
If your system does not have Excel for Windows installed, or MATLAB cannot access the COM server, xlsread operates in basic mode. In this mode, xlsread only reads XLS files.
The following five syntax formats are supported only on computer systems able to start a COM server from a MATLAB session. They are not supported in basic mode.
num = xlsread(filename, -1) num = xlsread(filename, 'range')
num = xlsread(filename, sheet, 'range')
num = xlsread(filename, ..., functionhandle)
[num, txt, raw, opt] = xlsread(filename, ..., functionhandle)
Handling Excel Date Values
MATLAB functions import all formatted dates as strings. To import a numeric date, the date field in Excel must have a numeric format.
Both Excel and MATLAB applications represent numeric dates as a number of serial days elapsed from a specific reference date. However, Excel and MATLAB use different reference dates:
Application    Reference Date
MATLAB    January 0, 0000
Excel for Windows    January 1, 1900
Excel for the Macintosh    January 2, 1904
Therefore, you must convert any numeric date that you import before you process it in MATLAB. For more information, see Converting Dates in the MATLAB Data Import and Export documentation.
Consider using the functionhandle parameter for this conversion, discussed in the Syntax Description and in Example 5 and Example 6.
Examples
Example 1— Reading Numeric Data
The Microsoft Excel spreadsheet file testdata1.xls contains this data:
    1    6
    2    7
    3    8
    4    9
    5   10
To read this data into MATLAB, use this command:
A = xlsread('testdata1.xls')
A =
    1    6
    2    7
    3    8
    4    9
    5   10
Example 2 — Handling Text Data
The Microsoft Excel spreadsheet file testdata2.xls contains a mix of numeric and text data:
    1    6
    2    7
    3    8
    4    9
    5   text
xlsread puts a NaN in place of the text data in the result:
A = xlsread('testdata2.xls')
A =
    1    6
    2    7
    3    8
    4    9
    5   NaN
Example 3 — Selecting a Range of Data
To import only rows 4 and 5 from worksheet 1, specify the range as 'A4:B5':
A = xlsread('testdata2.xls', 1, 'A4:B5')
A =
    4    9
    5   NaN
Example 4 — Handling Files with Row or Column Headers
A Microsoft Excel worksheet labeled Temperatures in the file tempdata.xls contains two columns of numeric data with text headers for each column:
Time  Temp
12     98
13     99
14     97
If you want to import only the numeric data, use xlsread with a single return argument. Specify the filename and sheet name as inputs.
xlsread ignores any leading row or column of text in the numeric result.
ndata = xlsread('tempdata.xls', 'Temperatures')
ndata =
    12    98
    13    99
    14    97
To import both the numeric data and the text data, specify two return values for xlsread:
[ndata, headertext] = xlsread('tempdata.xls', 'Temperatures')
ndata =
    12    98
    13    99
    14    97
headertext =
    'Time'    'Temp'
Example 5 — Passing a Function Handle
This example calls xlsread twice, the first time as a simple read from a file, and the second time requesting that xlsread execute some user-defined modifications on the data prior to returning the results of the read. A user-written function, setMinMax, that you pass as a function handle in the call to xlsread, performs these modifications. When xlsread executes, it reads from the spreadsheet, executes the function on the data read from the spreadsheet, and returns the final results to you.
    Note   The function passed to xlsread operates on the copy of the data read from the spreadsheet. It does not modify data in the spreadsheet itself.
Read a 10-by-3 numeric array from Excel spreadsheet testsheet.xls with a simple xlsread statement that does not pass a function handle. The returned values range from -587 to +4,149:
arr = xlsread('testsheet.xls')
arr =
  1.0e+003 *
    1.0020    4.1490    0.2300
    1.0750    0.1220   -0.4550
   -0.0301    3.0560    0.2471
    0.4070    0.1420   -0.2472
    2.1160   -0.0557   -0.5870
    0.4040    2.9280    0.0265
    0.1723    3.4440    0.1112
    4.1180    0.1820    2.8630
    0.9000    0.0573    1.9750
    0.0163    0.2000   -0.0223
In preparation for the second part of this example, write a function setMinMax that restricts the values returned from the read to be in the range of 0 to 2000. You need to pass this function in the call to xlsread, which then executes the function on the data it has read before returning it to you.
When xlsread calls your function, it passes an Excel range interface to provide access to the data read from the spreadsheet. This is shown as DataRange in this example. Your function must include this interface both as an input and output argument. The output argument allows your function to pass modified data back to xlsread:
function [DataRange] = setMinMax(DataRange)
maxval = 2000;  minval = 0;
for k = 1:DataRange.Count
   v = DataRange.Value{k};
   if v > maxval || v < minval if v > maxval
         DataRange.Value{k} = maxval;
      else
         DataRange.Value{k} = minval;
      end
   end
end
Now call xlsread, passing a function handle for the setMinMax function as the final argument, using '' as placeholders for sheet, range, and import mode. After this call, all values are between 0 and 2000:
arr = xlsread('testsheet.xls', '', '', '', @setMinMax)
arr =
  1.0e+003 *
    1.0020    2.0000    0.2300
    1.0750    0.1220         0
         0    2.0000    0.2471
    0.4070    0.1420         0
    2.0000         0         0
    0.4040    2.0000    0.0265
    0.1723    2.0000    0.1112
    2.0000    0.1820    2.0000
    0.9000    0.0573    1.9750
    0.0163    0.2000         0
Example 6 — Passing a Function Handle with Additional Output
This example adds onto the previous one by returning an additional output from the call to setMinMax. Modify the function so that it not only limits the range of values returned, but also returns the indices of the altered elements. Return this information in a new output argument, indices:
function [DataRange, indices] = setMinMax(DataRange)
maxval = 2000;  minval = 0;
indices = [];
for k = 1:DataRange.Count
   v = DataRange.Value{k};
   if v > maxval || v < minval if v > maxval
         DataRange.Value{k} = maxval;
      else
         DataRange.Value{k} = minval;
      end
   indices = [indices k];
   end
end
When you call xlsread this time, account for the three initial outputs, and add a fourth called idx to accept the indices returned from setMinMax:
[arr txt raw idx] = xlsread('testsheet.xls', ...
                            '', '', '', @setMinMax);
idx
idx =
    3   5   8  11  13  15  16  17  22  24  25  28  30
arr
arr =
  1.0e+003 *
    1.0020    2.0000    0.2300
    1.0750    0.1220         0
         0    2.0000    0.2471
    0.4070    0.1420         0
    2.0000         0         0
    0.4040    2.0000    0.0265
    0.1723    2.0000    0.1112
    2.0000    0.1820    2.0000
    0.9000    0.0573    1.9750
    0.0163    0.2000         0

标签: none

评论已关闭