Reading and writing Excel data with GAUSS

Reading and writing Excel® data with GAUSS

This tutorial will explain how to move data back and forth between GAUSS and Excel® spreadsheets. While these are generally simple commands that can be accomplished with one line of code, our purpose is to provide some understanding of the process. This will allow you to quickly and easily resolve any mistakes that you might make when using these functions.

What GAUSS needs to know

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

  1. The name and location of the Excel® file.
  2. The sheet number.
  3. The cell or range of cells to act upon.
  4. Should the data be interpreted as a matrix or a string.

Let's start with a simple example:

my_var = { 1.1, 2.2, 3.3, 4.4, 5.5 };
file_name = "mydata.xls";
cell_range = "A2:A6";
sheet_num = 1;

spreadSheetWrite(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 spreadSheetWrite.

The Excel® file name

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

  1. The directory, or path.
  2. The file extension.

The directory

In our example above, 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";

The file extension

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
spreadSheetWrite(my_data, "Asia.xlsx", "A2", 2);

//WRONG!
spreadSheetWrite(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 then 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);

will return:

    C2:IU101

String or numeric data

If you are writing data to an Excel® file, GAUSS will detect whether the data is numeric or string and write the data appropriately. However, if you are reading data, GAUSS needs to be told how you would like it interpreted. In most cases, you will want numeric data to be read into a matrix and character data to be read in as a string array. To read data in to a matrix, use either spreadSheetReadM or xlsReadM. The ending M stands for matrix. To read data in as a string array, use either spreadSheetReadSA or xlsReadSA. In this case, as you may have guessed, SA stands for string array.