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.
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;][grow;][skiph;][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:
- 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.
- 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.
- 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.
- 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.
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
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).
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.
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.
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. The following driver-specific options may be used to specify the properties of the format to handle:
- character used to separate fields. The default value is ","; tabulation or ";" are also often employed
- character used as decimal separator (default: ".")
- text representing the true value of a Boolean (default: "true")
- 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
If you have any comments or suggestions about these pages, please send mail to firstname.lastname@example.org.
© Copyright 2001-2013 Fair Isaac Corporation. All rights reserved.