mmsheet

The Mosel module mmsheet implements several I/O drivers for accessing and modifying spreadsheet files in different formats from `initializations' blocks. The I/O drivers rely on different technologies for accessing spreadsheets.

I/O drivers

The I/O drivers provided by mmsheet are all designed to be used in `initializations' blocks and expect the same type of information regarding file names and record references. The common form of a file specification for all the mmsheet drivers is:

mmsheet.*:[noindex|partndx|autondx[=#];][grow;][skiph;][emptyndx;][bufsize=#;]filename

The spreadsheet file name must be a physical file (with its extension), except for the "csv:" driver that accepts extended file names. The driver options (stated before the file name) shared by all mmsheet drivers are:

noindex

Indicates that only data (no indices) are transferred between the spreadsheet and Mosel. By default, the first columns of each table are interpreted as index values for the array to be transferred. This behaviour is changed by this option.

partndx

Indicates that the first nbdim-1 columns are interpreted as indices ( nbdim being the number of dimensions of the array to process) and remaining ones are used as data for the last dimension.

autondx[=st]

Indices are not read or written but automatically generated from the line number (this option only applies to 1-dimension arrays indiced by ranges). By default the first index has value 1 but a different value st may be stated.

grow

When writing data, the driver uses the provided range ignoring the end of the data if there is not enough space. When this option is specified, the driver extends the range by adding lines if necessary.

skiph

With this option, the driver skips the first line (or header) of the provided range. If the range contains only one line, the following line is selected.

emptyndx

When reading array indices an empty cell causes a failure. With this option empty cells are replaced by the default value of the corresponding type ( e.g. 0 for a numerical value)

bufsize=c

To set the size of the data buffer in kilobytes (default c=2).

The driver-specific options are documented separately for each driver in the following sections.

In the initializations block, each label entry is understood as a range in the workbook: named ranges are represented by their name (e.g. "MyRange") and explicit ranges are noted using square brackets (e.g. "[sheet1$a1:c2]"). For explicit ranges, the sheet is identified by its name or number and separated from the cell selection with the $ sign. The first sheet of the workbook is selected if no indication is given. Similarly, the used cells of the selected sheet are assumed if no selection is provided. The cell selection can be stated either using the usual format with a letter to select the column followed by a line number (e.g. "a1:c1") or by specifying row and column numbers by prefixing the row number by the letter "R" and the column number by the letter "C" (e.g. "R1C1:R1C3"). It is also possible to select some of the columns from the specified range: this can be done either with a list of names or a list of column numbers (relative to the beginning of the range) noted in parentheses after the range description. To use names, the option skiph must be used and the column names are taken from the header row that is skipped through this option. When using skiph, column numbers need to be stated by prefixing the column number by #. Note that, before the range selection, one can add options as for the file opening. For instance, "skiph;grow;" can be used for writing data to a named range formatted for an ODBC connection.

In addition to the above options a label may consist in the string "rangesize;" followed by a range specification (e.g. "rangesize;[]"), this special label can only be used to populate a list of integers that receives the size of the range in the form of 2 integers (number of lines and number of columns).

Example:

initializations from "mmsheet.excel:skiph;auction.xls"
 NWeeks as "[b1:d12]"           ! Initialize `NWeeks' with data in b2:d12
 BPROF as "noindex;BPROFILE"    ! Initialize `BPROF' with named range `BPROFILE'
                                ! all columns being data (no indices)
 mycols as "[b1:h12](#3,#5,#7)" ! Initialize `mycols' with columns d2:d12,
                                ! f2:f12 and h2:h12
 mycol2 as "[b1:h12](nam1,#5,nam3)"
                                ! Initialize `mycol2' with the column named
                                ! 'nam1', the column f2:f12 and the column
                                ! named 'nam3'
end-initializations 

The mapping between the selected cells of the workbook and the Mosel data structures is similar to the one used for databases (options noindex and partndx correspond to setting parameter mmodbc.SQLndxcol to false): refer to the section Data transfer between Mosel and the database of the mmodbc chapter for further explanation.

Although direct read and write operations are not supported by these drivers, a spreadsheet may be open using fopen: this allows to keep the document open across several 'initializations' blocks and avoid the cost of loading and unloading the file (that may be expensive particularly with the "excel:" driver).

Cells of a spreadsheet are implicitly typed as either numbers, booleans or text strings. When getting the value of a cell the driver may have to perform a type conversion: the conversion from a number to its textual representation relies on the real format "realfmt" (see setparam) that may have to be changed when using a driver of this module. For instance the number 1234567 will be converted to the text string 1.23457e+06 with the default real format ("%g"). To preserve the integer representation of such a cell it is required to use "%.10g" as the real format.

For further examples of working with databases and spreadsheets, the reader is referred to the Xpress whitepaper Using ODBC and other database interfaces with Mosel.

Driver excel

mmsheet.excel:[noindex|partndx;][grow;][skiph;][emptyndx;][newxl;][bufsize=#;]filename

This driver uses directly the application Excel for accessing the file (relying on COM/OLE as the communication channel): as a consequence it is available only under the Windows platform and requires Excel to be installed on the host executing the Mosel model. All file formats handled by the version of Excel can be used but this driver does not support creation of new files (i.e. it can only modify existing files). In addition to the options described in the introductory section, the option newxl may be used: by default the driver does not open the file if it can find a running instance of Excel having the required file open: it works directly with the application and modifications made to the workbook are not saved when the file is closed in Mosel. If this option is specified a new instance of Excel is started in all cases and the workbook is saved before quitting the application when the file is closed in Mosel.

Driver xls/xlsx

mmsheet.xls:[noindex|partndx|autondx[=#]|dataframe;][grow;][skiph[+];][emptyndx;]
            [skiperrcell;][nan=s;][inf=s;][minf=s;][bufsize=#;]filename

mmsheet.xlsx:[noindex|partndx|autondx[=#]|dataframe;][grow;][skiph[+];][emptyndx;]
             [skiperrcell;][nan=s;][inf=s;][minf=s;][bufsize=#;]filename

These two drivers rely on the libxl library to access the spreadsheet file: they are available on the Windows, Linux and MacOS platforms and do not require any additional software. The first driver handles xls files while the second deals with xlsx and xlsm format Excel files. These drivers can be used to create new files: when used for writing (through an `initializations to' block) non-existing sheets are automatically added to the workbook and the file is created if necessary. When the option skiph+ is used instead of skiph when writing to a file, the necessary header row is created if this row is empty (this option behaves like skiph when reading a file and when no column name is provided).

The option dataframe only applies to a 2-dimension array with first index as a range (for the line numbers). If combined with skiph the array to be processed must be indiced by a range and a set of strings. The second index is automatically populated with the column names of the header row when reading (the same line is generated when writing).

The option skiperrcell makes it possible to input spreadsheets including error cells (e.g. #NUM!): they are handled as empty cells. When used for writing, this option replaces special values NAN and INFINITY by empty cells.

The options nan (not a number), inf (infinity) and minf (-infinity) specify how to represent the corresponding special values. The provided character sequence can be either some text (inf=INF) or a numerical value (inf=1e99). If the definition of minf is not provided, it will be deduced from the definition of inf by prefixing a textual string by the symbol '-' or change the sign of a numerical value. The conversion applies for both writing and reading, note that when trying to identify special values text comparisons are case insensitive.

Driver csv

mmsheet.csv:[noindex|partndx|autondx[=#]|dataframe;][alltxt;][grow;][skiph[+];][emptyndx;]
            [bufsize=#;][fsep=c;][dsep=c;][true=s;][false=s;]filename

This driver works on spreadsheets saved in ascii CSV format (Comma Separated Values). It is available on all platforms that are supported by Mosel and can open or create files using extended format file names (i.e. combining several I/O drivers). A CSV file contains a single sheet (number 1 identified as "Sheet1") and does not support named ranges, that is, cell references must use the explicit notation. When the option skiph+ is used instead of skiph when writing to a file, the necessary header row is created if this row is empty (this option behaves like skiph when reading a file and when no column name is provided).

The option dataframe only applies to a 2-dimension array with first index as a range (for the line numbers). If combined with skiph the array to be processed must be indiced by a range and a set of strings. The second index is automatically populated with the column names of the header row when reading (the same line is generated when writing).

The following driver-specific options may be used to specify the properties of the format to handle:

alltxt

by default the driver tries to guess the type of the cells while reading the document (cells can be either numbers, booleans or text strings). When this option is used all cells are recorded as text strings

dsep=c

character used as decimal separator (default: ".")

fsep=c

character used to separate fields. The default value is ","; tabulation or ";" are also often employed

true=s

text representing the true value of a Boolean (default: "TRUE")

false=s

text representing the false value of a Boolean (default: "FALSE")

For example, the following statements will read data from a file formatted for the French language and that has been compressed with gzip:

initializations from "mmsheet.csv:fsep=;;dsep=,;true=vrai;false=faux;zlib.gzip:mydata.csv.gz"
 A as "[a1:c12]"
end-initializations 

The csv driver supports the getfsize function applied to a file already loaded into memory: it reports the amount of memory currently allocated for the corresponding document. For instance the following displays the memory used by "mydata.csv":

fopen("mmsheet.csv:mydata.csv",F_INPUT)
writeln(getfsize("mmsheet.csv:mydata.csv"))
fclose(F_INPUT)