xlsWriteM

Purpose

Writes a GAUSS matrix to an Excel® spreadsheet.

Format

ret = xlsWriteM(data, file[, range[, sheet[, vls]]]])

Parameters:

• data (matrix) – data to write.

• file (string) – name of .xls or .xlsx file.

• range (string) – Optional input, the starting point of the write, e.g. “A2”. Default = “A1”

• sheet (scalar) – Optional input, sheet number. Default = 1.

• vls (null string or 9x1 matrix) – Optional input, specifies the conversion of GAUSS values into Excel® empty cells and special types (see Remarks). A null string results in all GAUSS missing values being converted to empty cells. Default = null string.

Returns:

ret (scalar) – 0 if success or a scalar error code.

Portability

Windows, Linux and macOS.

The vls input is currently ignored on macOS and Linux.

Examples

Basic Example

x = { 0 1,
      1 2,
      3 5 };

// Write contents of 'x' to 'myfile.xlsx'
// from cell 'A1' to 'B3'
ret = xlsWriteM(x, "myfile.xlsx");

myfile.xlsx is saved in your current working directory. You can find your current working directory in the main tool bar (in the top of GAUSS) or by using the getGAUSSHome() command.

Write To a Range

// Write 'x' from the previous example to the cells 'C2:D4'
ret = xlsWriteM(x, "myfile.xlsx", "C2");

Specify Path and Sheet Number

// Create a 10x3 matrix of Bernoulli random variables
x = rndBernoulli(10, 3, 0.6);

// Write the data from 'x' to cells 'B4:D13' on sheet 2 of 'myfile.xlsx'
ret = xlsWriteM(x, "C:\\mydata\\myfile.xlsx", "B4", 2);

Remarks

1. To write a dataset with numeric variables and header names to an Excel file, use saved().

2. The vls argument lets users control the export to Excel® empty cells and special types, according to the following table:

   Row Number	Excel® Cell
   1	empty cell
   2	#N/A
   3	#VALUE!
   4	#DIV/0!
   5	#NAME?
   6	#REF!
   7	#NUM!
   8	#NULL!
   9	#ERR

   Use the following to convert all occurrences of 9999.99 to #DIV/0! in Excel® and convert all GAUSS missing values to empty cells in Excel®:

   vls = reshape(error(0),9,1);
   vls[4] = 9999.99;
   

3. If xlsWriteM() 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 scalar error code which can be checked for with scalmiss().

See also

Functions xlsReadSA(), xlsReadM(), xlsWrite(), xlsWriteSA(), xlsGetSheetCount(), xlsGetSheetSize(), xlsGetSheetTypes(), xlsMakeRange()