Spreadsheet drivers

The model mmsheet provides a set of spreadsheet I/O drivers that implement all basic data access tasks similarly to the functionality provided by ODBC (but none of the advanced SQL statements).

• mmsheet.excel is a software-specific I/O driver that accesses directly Excel spreadsheets. If the spreadsheet file is kept opened while running the Mosel model the output is written to the spreadsheet without saving it. This driver cannot be used remotely.
• mmsheet.xls and mmsheet.xlsx are I/O drivers for accessing spreadsheets in the Excel formats XLS and XLSX/XLSM respectively. They do not depend on Excel and can be used for reading and writing to spreadsheets on non-Windows platforms (Linux, Mac). They take the same options as mmsheet.excel and their use is analogous to this driver, with the exception that they save data immediately into the spreadsheet file, that is, an output file should not be open in another application while writing to it with these drivers.
• mmsheet.csv provides access to spreadsheet files in CSV format (text files). It is available for all platforms running Mosel and the file name is not restricted to physical files, it may be an extended file name. As with xls/xlsx output data is saved directly into the file.

With all mmsheet spreadsheet drivers output always starts at the same place (that is, previous output gets overwritten).

model "Burglar2 (Excel)"
 uses "mmxprs"

 parameters
  CNCTEXC = 'mmsheet.excel:burglar.xls'
 end-parameters
 ...

! Spreadsheet range includes header line -> option 'skiph' to skip header
 initializations from CNCTEXC
  [VALUE,WEIGHT] as "skiph;BurgData"
 end-initializations
 ...

! Insert solutions into spreadsheet: results from previous runs
! are overwritten by new output
! Only first line of output range is specified -> option 'grow'
 initializations to CNCTEXC
  SOLTAKE as "skiph;grow;SolTake"
 end-initializations
end-model

The input and output ranges in the spreadsheet used with the excel driver contain just the data, no column headers. To work with the ODBC format of ranges the option skiph needs to be used as shown in the example. Another option employed when outputting data is grow, this indicates that the output range is specified by a single row and may grow on demand.

Instead of naming the ranges in the spreadsheet it is equally possible to work directly with the cell references for the input and output ranges (including the worksheet name, which is `burglar' in our case):

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "[burglar$B4:D11]"
 end-initializations
 ...

! Insert solutions into spreadsheet: results from previous runs
! are overwritten by new output
! Only first line of output range is specified -> option 'grow'
 initializations to CNCTEXC
  SOLTAKE as "grow;[burglar$F4:G4]"
 end-initializations

Or alternatively, using the row-column notation:

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "[burglar$R4C2:R11C4]"
 end-initializations
 ...

 initializations to CNCTEXC
  SOLTAKE as "grow;[burglar$R4C6:R4C7]"
 end-initializations

Other formatting options for the specification of spreadsheet ranges include the selection of columns via their relative position within the cell range:

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "[burglar$A4:F11](#2,#3,#4)"
 end-initializations

and the possibility to omit row numbers, thereby indicating that we want to read the contents of the entire column, typically used in combination with option skiph to skip the first row (assuming it contains the header text):

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "skiph;[burglar$F:G]"
 end-initializations

Switching to the more generic driver mmsheet.xls in the examples above simply means changing the driver name in the filename string CNCTEXC. For CSV format, besides changing the driver name, we need to save the spreadsheet with CSV format. CSV files do not contain worksheets or range names, so ranges always need to be defined via cell references when using mmsheet.csv.

For further detail on the spreadsheet drivers the reader is refered to the section `mmsheet' of the `Mosel Language Reference Manual' and the examples discussed in the Xpress Whitepaper `Using ODBC and other database interfaces with Mosel'.