Spreadsheets: Excel and CSV

MS Excel spreadsheets can be accessed directly from a Mosel model with the help of the excel or xls/xlsx I/O drivers. The use of these drivers is similar to what we have seen above for the odbc driver.

Yet another method for accessing spreadsheet data consists in using the CSV driver csv with spreadsheets that have been saved in CSV format. All the dedicated spreadsheet drivers are defined by the module msheet. The main differences between the functionality and usage of the various spreadsheet drivers are summarized in the following table.

Data input using the excel or xls I/O drivers

We shall work with a spreadsheet that has the following contents:

Assuming that we have named 'MyDataTable2' the cell range B3:D6 (that is, selecting just the data, excluding the header row) we can then read in these data with the following model duoexc.mos.

model "Duo input (Excel)"
 uses "mmsheet"

 declarations
  A4: dynamic array(range,range) of real
 end-declarations

! Use the excel driver for reading the data
 initializations from "mmsheet.excel:data.xls"
  A4 as 'MyDataTable2'
 end-initializations

! Print out the data we have read
 writeln('A4 is: ', A4)

end-model 

It is also possible to read the data from a range 'MyDataTable' that inludes the header row (that is, selecting the area B2:D6). In this case we need to specify the driver option skiph to skip the header line of the selected data range:

 initializations from "mmsheet.excel:data.xls"
  A4 as 'skiph;MyDataTable'
 end-initializations 

Yet another possibility is to use directly the worksheet and cell references instead of defining a named range (NB: the first sheet of the workbook is selected if no worksheet name is specified and the used cells of the selected sheet are assumed if no cell range selection is provided):

 initializations from "mmsheet.excel:data.xls"
  A4 as '[Sheet1$B3:D6]'
 end-initializations 

Working with named ranges has the advantage over this explicit form that modifications to the spreadsheet layout repositioning the data range will not make it necessary to modify the model.

Instead of the excel driver that can only be used with an existing MS Excel installation, we can switch to the generic xls driver by modifying the file name to

 "mmsheet.xls:data.xls" 

All else, including the driver options and the usage of named ranges, remains unchanged. Note: with a spreadsheet saved in XLSX format (files with the extension .xlsx) we would have to use the driver xlsx.

Data output using excel or xls

The following model duoexc_out.mos writes out the array A into the spreadsheet range F3:H3 that we have called 'MyOutTable3'. These cells denote the first row of the rectangular area into which we wish to write.

model "Duo output (Excel)"
 uses "mmsheet"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the excel driver for writing data
 initializations to "mmsheet.excel:data.xls"
  A as "grow;MyOutTable3"
 end-initializations

end-model 

In this model we have used the option grow of the excel driver to indicate that the actual output area may grow (= add more rows, the number of selected columns must be sufficient) beyond the specified output range as required by the data. Alternatively, we may also specify the complete output range such as in

 initializations to "mmsheet.excel:data.xls"
  A as '[Sheet1$F3:H11]'
 end-initializations 

or more dynamically:

 initializations to "mmsheet.excel:data.xls"
  A as '[Sheet1$F3:H' + (3+A.size-1) + ']'
 end-initializations 

If the output range has been defined to include the header row (ODBC-compatible format) we need to use again the option skiph.

 initializations to "mmsheet.excel:data.xls"
  A as 'skiph;grow;MyOutTable1'
 end-initializations 

When using the excel or xls/xlsx drivers the definition of the output range in the spreadsheet remains unchanged even if the actual output area exceeds its length. As a consequence, the output from a second model run will start at exactly the same place as the first, overwriting any previous results in the same location (but not deleting any lines if the output from the second run uses fewer rows than the first).

A specific feature of the excel driver is that the Excel spreadsheet file may remain open while writing to it from Mosel. In this case the data written to the spreadsheet does not get saved, enabling the user thus to experiment with model runs without causing any unwanted lasting effects to the output file. However, when using the xls/xlsx drivers, output data is saved directly into the spreadsheet file—the output file needs to be closed if the application used for displaying it locks write access to the file by other programs.

With the excel driver, the output file must exist prior to writing to it from Mosel. The xls/xlsx drivers will create a new spreadsheet file of the corresponding format if the specified file is not found, in which case it is obviously not possible to work with predefined ranges, but the option skiph+ can be used to output a header line, for example as in the following version that writes output into the columns F to H of the first sheet of a new spreadsheet file 'anewfile.xls':

 initializations to "mmsheet.xls:anewfile.xls"
  A as 'skiph+;[1$F:H](Index1,Index2,Value_of_A)'
 end-initializations 

Data input using the csv I/O driver

Now assume that the spreadsheet from Section Data input using odbc has been saved in CSV format into the file data.csv. We can then read in these data with the following model duosheet.mos.

model "Duo input (CSV)"
 uses "mmsheet"

 declarations
  A6: dynamic array(range,range) of real
 end-declarations

! Use the csv driver for reading the data
 initializations from "mmsheet.csv:data.csv"
  A6 as '[B3:D6]'
 end-initializations

! Print out the data we have read
 writeln('A6 is: ', A6)

end-model 

The CSV format does not support the definition of named ranges and there is no notion of 'worksheet' (when saving an Excel spreadsheet in CSV format the user selects the sheet to be saved). We therefore need to address cell ranges explicitly by indicating their position using the (letter,number) notation as shown above or alternatively, with RC (row-column) notation:

 initializations from "mmsheet.csv:data.csv"
  A6 as '[R3C2:R6C4]'
 end-initializations 

Driver options such as skiph to skip the range header line apply as before

 initializations from "mmsheet.csv:data.xls"
  A4 as 'skiph;[B2:D6]'
 end-initializations 

As for the spreadsheet drivers, it is also possible to use an empty range definition, in which case all contents of the CSV file is considered as the selected area.

 initializations from "mmsheet.csv:data.csv"
   A6 as '[]'
 end-initializations 

If we want to read all the content of columns B to D, skipping the first line, we can use the following shorthand notation (NB: this notation without row numbers, just like the row-column notation works with all mmsheet drivers):

 initializations from "mmsheet.csv:data.xls"
  A4 as 'skiph;[B:D]'
 end-initializations 

The main advantage of CSV over Excel format is its greater portability (CSV format is supported on all Xpress platforms) and the possibility to combine the csv driver freely it with other drivers, such as shmem or rmt that are not compatible with the other spreadsheet drivers.

Data output using csv

The following model duosheet_out.mos writes out the array A into the spreadsheet range F3:H3. These cells denote the first row of the rectangular area into which we wish to write.

model "Duo output (Excel)"
 uses "mmodbc"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the csv driver for writing data
 initializations to "mmsheet.csv:data.csv"
  A as "grow;[F3:H3]"
 end-initializations

end-model 

In this model we have used the option grow of the excel driver to indicate that the actual output area may grow (= add more rows, the number of selected columns must be sufficient) beyond the specified output range as required by the data. Alternatively, we may also specify the complete output range such as in

 initializations to "mmsheet.csv:data.csv"
  A as '[F3:H11]'
 end-initializations 

Yet another option that is available with the csv driver is the possibility to output the names of column headers via the option skiph+ (note also that just like xls/xlsx, the csv driver will create the output file if it does not exist). The following version of our output example writes output into the columns F to H of a newly created file 'anewfile.csv' where the first row of each column contains the indicated header text (note that if the file and column headers exist already, the header text is expected to match the existing).

 initializations to "mmsheet.csv:anewfile.csv"
  A as 'skiph+;[F:H](Index1,Index2,Value_of_A)'
 end-initializations