xlsGetSheetSize#

Purpose#

Gets the size (rows and columns) of a specified sheet in an Excel® spreadsheet.

Format#

{ r, c } = xlsGetSheetSize(file[, sheet])#

Parameters:

file (string) – name of .xls or .xlsx file.
sheet (scalar) – Optional input, sheet index (1-based). Default = 1.

Returns:

r (scalar) – number of rows.
c (scalar) – number of columns.

Portability#

Windows, Linux and macOS

Examples#

Example 1: Pass in sheet number#

Find the number of rows and columns in the first sheet of the example file, yarn.xlsx with the following code:

// File name with full path
fname = getGAUSShome() $+ "examples/yarn.xlsx";

sheet = 1;

// Pass in optional sheet number
{ r, c } = xlsGetSheetSize(fname, sheet);

After the above code:

r = 28
c =  4

The call to xlsGetSheetSize() could also be made without passing in the sheet number:

Example 2: Use default sheet index#

// File name with full path
fname = getGAUSShome() $+ "examples/yarn.xlsx";

// Leave out optional sheet number
{ r, c } = xlsGetSheetSize(fname);

Since the default sheet index is 1, this code will assign r and c to the same values as the previous example.

Example 3: Use trap for custom error handling#

If you do not want your program to terminate in the case of an error in this function, you can set the trap state as in the example below.

sheet = 1;

// Retain the old trap value so it can
// be reset to its previous state
oldtrap = trapchk(1);

// Set trap
trap 1;

{ r, c } = xlsGetSheetSize(fname, sheet);

// Check to see if return value is an error code
if scalmiss(r);
   // User error handling code here
   print "xlsGetSheetSize failed";
endif;

Remarks#

If xlsGetSheetSize() fails, it will either terminate and print an error message or return a scalar error code, which can be decoded with scalerr, depending on the state of the trap flag.

`trap 0`	Print error message and terminate program.
`trap 1`	Return a scalar error code which can be checked for with `scalmiss()`.

If a scalar error code is returned, both return values will be set with the error code.