Reading and writing Excel data with GAUSS

Introduction

This tutorial will explain how to move data back and forth between GAUSS and Excel® spreadsheets. Variables from tabular Excel® files with string header names in the first row can be read more conveniently with the loadd command. However, if you want to specify cell ranges rather than just columns, you are in the right place.

We will start with a description of the main functions and then discuss the input options in more detail.

xlsWrite

xlsWrite is the main GAUSS function used for writing data to an Excel® file. It has the following inputs:

data: Matrix or string array, containing the data you would like to write to the Excel® file.
file: String, the name of the Excel® file to write to.
cell: Optional input string, containing the starting cell. Default = "A1".
sheet: Optional input scalar, containing the sheet number to write to. Default = 1.
vls: Optional input which controls the handling of empty cells for advanced users.

Example: Write matrix data

Each of the commands below will write the numeric sequence 1.1, 2.2, 3.3 to the cell range A1:A3 of the first sheet of a file named example_data.xlsx in your current working directory.

x = { 1.1, 2.2, 3.3 };

// Write vector to Excel file, using
// default values for 'cell' and 'sheet'
call xlsWrite(x, "example_data.xlsx");

// Write vector to Excel file, specifying the start
// 'cell' as "A1" and using the default 'sheet' number
call xlsWrite(x, "example_data.xlsx", "A1");

// Write vector to Excel file, specifying the start
// 'cell' as "A1" and the 'sheet' number as 1
call xlsWrite(x, "example_data.xlsx", "A1", 1);

xlsReadM

xlsReadM reads numeric data from Excel® files into GAUSS. The 'M' at the end of the function name stands for matrix. It has the following inputs:

file: String, the name of the Excel® file to read from.
cell: Optional input string, containing the cell range to read from, or the starting cell. If only a starting cell is specified, GAUSS will read from the starting cell to the end of the file. Default = "A1".
sheet: Optional input scalar, containing the sheet number to read from. Default = 1.
vls: Optional input which controls the handling of empty cells for advanced users.

Example: Read matrix data

Assuming that we have already run the example above for xlsWrite, all of the lines below will read the sequence 1.1, 2.2, 3.3 from the Excel® file into the matrix new_x.

// Read data from Excel file, using
// default values for 'cell' and 'sheet'
new_x = xlsReadM("example_data.xlsx");

// Read data from Excel file, specifying the start
// 'cell' as "A1" and using the default 'sheet' number
new_x = xlsReadM("example_data.xlsx", "A1");

// Read data from Excel file, specifying the start
// 'cell' as "A1" and the 'sheet' number as 1
new_x = xlsReadM("example_data.xlsx", "A1", 1);

xlsReadSA

xlsReadSA reads string data from Excel® files into GAUSS. The 'SA' at the end of the function name stands for string array. It has the following inputs:

file: String, the name of the Excel® file to read from.
cell: Optional input string, containing the cell range to read from, or the starting cell. If only a starting cell is specified, GAUSS will read from the starting cell to the end of the file. Default = "A1".
sheet: Optional input scalar, containing the sheet number to read from. Default = 1.
vls: Optional input which controls the handling of empty cells for advanced users.

Example: Write then read a string array

// Create 1x3 string array
headers = "Alpha" $~ "Beta" $~ "Gamma";

// Write the 1x3 string array to cells A1:C1
call xlsWrite(headers, "example_data.xlsx");

// Read cells A1:C1 into 'new_headers'
new_headers = xlsReadSA("example_data.xlsx", "A1:C1");

xlsRead/xlsWrite input argument details

While the above commands are generally simple and can be accomplished with one line of code, this section will provide some understanding of the process so that you can quickly and easily resolve any problems that you encounter.

In order for GAUSS to retrieve or write the correct data, it needs to know the following information:

The name and location of the Excel® file.
The sheet number.
The cell or range of cells to act upon.

Let's start with a simple example:

// Create a 5x1 column vector
my_var = { 1.1, 2.2, 3.3, 4.4, 5.5 };

file_name = "mydata.xls";
cell_range = "A2:A6";
sheet_num = 2;

// Write the data from 'my_var' to cells A2:A6 on
// sheet 2 of the file 'mydata.xls' 
xlsWrite(my_var, file_name, cell_range, sheet_num);

This code will write the contents of the GAUSS variable my_var into the cells from A2 to A6 on the first sheet of a file named mydata.xls. Let's look more closely at each of the four inputs to the function xlsWrite.

The Excel® file name

Two important and often overlooked parts of the file name which can trip you up are:

The directory, or path.
The file extension.

The directory. In our example above, we only specified the file name. We did not specify a directory. If the directory is not specified, GAUSS will look for the file ONLY in your GAUSS current working directory. If the file is located in a different directory, you may add the path to the string that contains the file name. Here are a couple of examples:

//Windows: note double backslashes are needed inside strings
file_name =  "C:\\Users\\MyUserName\\MyProject\\mydata.xls";

//Mac
file_name =  "/Users/MyName/MyProject/mydata.xls";

//Linux
file_name =  "/home/myname/myproject/mydata.xls";

If you would prefer GAUSS to look for the data file in the same folder as the program file, regardless of whose computer the files are on, you can use the __FILE_DIR command.

Note: Your GAUSS current working directory will be displayed on the main GAUSS toolbar. You can also use the command cdir(0); to print your current working directory in the GAUSS program input/output window.

The file extension is important. It tells GAUSS which Excel® format the file uses. GAUSS supports reading and writing of both .xls (Excel® 97-2003 Binary file format) and .xlsx (Excel® 2007-2013 XML-based file format) files.

The sheet number

Your Excel® spreadsheets may have text sheet names or simply be numbered. It does not matter to GAUSS whether the sheet has been given a text name or retains the sheet number. However, the GAUSS functions need to be given the sheet index as a number.

For example let's say that you have a spreadsheet named Asia.xlsx in which the first three sheets were named China, Japan, and Korea. In this case, if you wanted to refer to the sheet named Japan, you would set the sheet_num variable to 2, because it is the second sheet. You would not specify the sheet by its actual name.

//Correct
xlsWrite(my_data, "Asia.xlsx", "A2", 2);

//WRONG! Use the sheet index, not the sheet name
xlsWrite(my_data, "Asia.xlsx", "A2", "Japan");

The cell or cell range

The cell range input is a string which should contain a starting cell and may optionally contain an ending cell, which should be separated by a colon. For example:

//Single Cell
cell = "B3";

//Cell range
cell_range = "B2:C93";

When you are writing data to an Excel® file, you only need to specify the starting cell. GAUSS will write the entire contents of your GAUSS variable, beginning with the starting cell and continuing until all of the data has been written.

Note that you cannot use the cell range input to reshape your data. For example, if you had a GAUSS row vector with 9 elements, you might expect that specifying the cell range to equal "A1:A9" would write the data as a column vector. This is not the case. If you would like to reshape your data, this must be done before the call to spreadSheetWrite or xlsWrite.

When you are reading from a spreadsheet, you may also omit the ending cell. If you omit the ending cell, GAUSS will read in all cells of the spreadsheet that contain data, starting from the starting cell that you specify.

Specifying a subset of data to read from a spreadsheet must be done in terms of cell ranges. To convert row and column numbers to a spreadsheet cell range, you can use the GAUSS xlsMakeRange function. xlsMakeRange takes two inputs. The first input specifies the number of rows and the second input specifies the number of columns. They may be either scalars or 2x1 vectors. For example:

row = 2;
col = 3;
print xlsMakeRange(row, col);

will return:

C2

while:

row = { 2,     //starting row
        101 }; //ending row
col = { 3,     //starting column
        255 }; //ending column
print xlsMakeRange(row, col);