Package TolExcel

Tools for reading an writing Excel workbooks from TOL.

Writing is already an experimental issue and there are known bugs as #1145.

Note: In this version just binary files with extension .xls of Excel 97 are allowed.

/* API for Class TolExcel::@WorkBook */
Class TolExcel::@WorkBook{

/* Methods */
/* 
Non static method defined at TolExcel::@WorkBook
Activates the worksheet with specified name
*/ 
Real ActivateNamedWS(Text workSheetName, Real readRange, Real
headerIndexedByName);

/* 
Non static method defined at TolExcel::@WorkBook
Activates the n-th worksheet.
If readRange is true then calls to GetSheetMaxRange with argument
headerIndexedByName
*/ 
Real ActivateWS(Real workSheetNumber, Real readRange, Real headerIndexedByName);

/* 
Non static method defined at TolExcel::@WorkBook
Closes the workbook if it is has been open. If you doesn't close it, the
workbook will be closed at instance destruction time.
*/ 
Real Close(Real void);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadMatrix(firstRow, col, _.maxRow-firstRow+1, 1)
Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a Matrix with the numeric values of a rectangle of cells in the current
work sheet. Non numeric or empty cells will be interpreted as unknown values.
*/ 
Matrix GetFullColumn(Real col, Real firstRow);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadMatrix(firstRow, col, _.maxRow-firstRow+1, 1)
where col is the index of the column which first row has the specified
name.Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a Matrix with the numeric values of a rectangle of cells in the current
work sheet. Non numeric or empty cells will be interpreted as unknown values.
*/ 
Matrix GetFullColumnByName(Text name, Real firstRow);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadMatrix(minRow=1, minCol=1, _.numRow, _.numCol)
Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a Matrix with the numeric values of a rectangle of cells in the current
work sheet. Non numeric or empty cells will be interpreted as unknown values.
*/ 
Matrix GetFullMatrix(Real void);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet GetRange(minRow=1, minCol=1, _.numRow, _.numCol,
columnDefaults)
Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a Set of Sets with the values of a rectangle of cells in the current
work sheet. Each cell will have any of allowed types : Text, Real, Date.
When argument columnDefaults is not the empty set, then if a cell hasn't the
specified type for its column in argument columnDefaults, then the unknown
value of the type will be returned.
*/ 
Set GetFullRange(Set columnDefaults);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadSeries(minRow=1, minCol=1, _.numRow, _.numCol, dating)
Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a Set of series with specified dating. The first columne in the range
is considered as the dates supporting the data of the Series. Every column from
the column 2 on is considered as the data of one Serie. If dating is W then it
will readed from the cell at left top corner. If this cell is empty or it's not
a valid TimeSet then the default dating C will be used.
*/ 
Set GetFullSeries(TimeSet dating, Text dateFormat);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadVMatrix(firstRow, col, _.maxRow-firstRow+1, 1)
Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a VMatrix with the numeric values of a rectangle of cells in the
current work sheet. Non numeric or empty cells will be interpreted as unknown
values.
*/ 
VMatrix GetFullVColumn(Real col, Real firstRow);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadVMatrix(firstRow, col, _.maxRow-firstRow+1, 1)
where col is the index of the column which first row has the specified
name.Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a VMatrix with the numeric values of a rectangle of cells in the
current work sheet. Non numeric or empty cells will be interpreted as unknown
values.
*/ 
VMatrix GetFullVColumnByName(Text name, Real firstRow);

/* 
Non static method defined at TolExcel::@WorkBook
Call to methodSet ReadVMatrix(minRow=1, minCol=1, _.numRow, _.numCol)
Method GetSheetMaxRange must be called before.
You must set minRow and minCol members before to calling GetSheetMaxRange if
there are empty rows or columns before the data cells
Returns a VMatrix with the numeric values of a rectangle of cells in the
current work sheet. Non numeric or empty cells will be interpreted as unknown
values.
*/ 
Matrix GetFullVMatrix(Real void);

/* 
Non static method defined at TolExcel::@WorkBook
Scans the current work sheet in order to stablish a rectangle of interesting
cells, starting from cell [minRow,minCol], wich default value is [1,1]. Reads
along first column until an empty cell is found to stablish maxCol. Then, reads
along first row until an empty cell is found to stablish maxRow.
If argument headerIndexedByName is true stores the cells of first row in member
_.headers and indexes it.
Note that cells in first row cannot be repeated and must be of type Text.
*/ 
Real GetSheetMaxRange(Real headerIndexedByName);

/* 
Non static method defined at TolExcel::@WorkBook
Returns true if the specified cell is empty
*/ 
Real IsEmptyCell(Real row, Real col);

/* 
Non static method defined at TolExcel::@WorkBook
Reads from current active work sheet the content of specified cell returning
one of these types: Text, Date, Real
*/ 
Anything ReadCell(Real row, Real col);

/* 
Non static method defined at TolExcel::@WorkBook
Reads from current active work sheet the content of specified cell returning a
Date
*/ 
Date ReadDate(Real row, Real col);

/* 
Non static method defined at TolExcel::@WorkBook
Returns a Matrix with the numeric values of a rectangle of cells in the current
work sheet. Non numeric or empty cells will be interpreted as unknown values.
*/ 
Matrix ReadMatrix(Real row_ini, Real col_ini, Real row_num, Real col_num);

/* 
Non static method defined at TolExcel::@WorkBook
Returns a Set of Sets with the values of a rectangle of cells in the current
work sheet. Each cell will have any of allowed types : Text, Real, Date.
When argument columnDefaults is not the empty set, then if a cell hasn't the
specified type for its column in argument columnDefaults, then the unknown
value of the type will be returned.
*/ 
Set ReadRange(Real row_ini_, Real col_ini_, Real row_num, Real col_num, Set
columnDefaults);

/* 
Non static method defined at TolExcel::@WorkBook
Reads from current active work sheet the content of specified cell returning a
Real
*/ 
Real ReadReal(Real row, Real col);

/* 
Non static method defined at TolExcel::@WorkBook
Returns a Set of series with specified dating. The first columne in the range
is considered as the dates supporting the data of the Series. Every column from
the column 2 on is considered as the data of one Serie. If dating is W then it
will readed from the cell at left top corner. If this cell is empty or it's not
a valid TimeSet then the default dating C will be used.
*/ 
Set ReadSeries(Real row_ini, Real col_ini, Real row_num, Real col_num, TimeSet
dating, Text dateFormat);

/* 
Non static method defined at TolExcel::@WorkBook
Reads from current active work sheet the content of specified cell returning a
Text
*/ 
Text ReadText(Real row, Real col);

/* 
Non static method defined at TolExcel::@WorkBook
Returns a VMatrix with the numeric values of a rectangle of cells in the
current work sheet. Non numeric or empty cells will be interpreted as unknown
values.
*/ 
VMatrix ReadVMatrix(Real row_ini, Real col_ini, Real row_num, Real col_num);

/* 
Non static method defined at TolExcel::@WorkBook
Saves changes in workbook
*/ 
Real Save(Real void);

/* 
Non static method defined at TolExcel::@WorkBook
Saves changes in workbook in the specified path
*/ 
Real SaveAs(Text path);

/* 
Non static method defined at TolExcel::@WorkBook
Writes into the specified cell of current active work sheet the specified value
with on of these types: Text, Date, Real
*/ 
Anything WriteCell(Real row, Real col, Anything value);

/* 
Non static method defined at TolExcel::@WorkBook
Writes a Matrix in the range of cells starting with the specified cell of
current active work sheet.
Returns the number of modified cells.
*/ 
Real WriteMatrix(Real row, Real col, Matrix values);

/* 
Non static method defined at TolExcel::@WorkBook
Writes a range of cells starting with the specified cell of current active work
sheet. Set cellValues must be a set of sets which elements are of one of these
types: Text, Real, Date.
Returns the number of modified cells.
*/ 
Real WriteRange(Real row0, Real col0, Set cellValues);

/* 
Non static method defined at TolExcel::@WorkBook
Writes a range of cells starting with the specified cell of current active work
sheet. Set series must be a set of series with the same dating.
Returns the number of modified cells.
*/ 
Real WriteSeries(Real row, Real col, Set series, Text dateFormat);

/* 
Non static method defined at TolExcel::@WorkBook
Writes a VMatrix in the range of cells starting with the specified cell of
current active work sheet.
Returns the number of modified cells.
*/ 
Real WriteVMatrix(Real row, Real col, Matrix values);

/* 
Non static method defined at TolExcel::@WorkBook
Returns de internal identifier of excel workbook handler.
*/ 
Real get_id(Real void);

/* 
Static method defined at TolExcel::@WorkBook
Creates an empty workbook.
*/ 
Static @WorkBook New(Text path);

/* 
Static method defined at TolExcel::@WorkBook
Opens an existent workbook.
*/ 
Static @WorkBook Open(Text path)/* Members */;

/* 
Non static member defined at TolExcel::@WorkBook
Column headers. Used in method GetSheetMaxRange when argument
headerIndexedByName is true.
*/ 
Set _.header = Copy(Empty);

/* 
Non static member defined at TolExcel::@WorkBook
If true, the first row was interpreted as column headers and will be stored in
_.header. Used in method GetSheetMaxRange.
*/ 
Real _.headerIndexedByName = False;

/* 
Non static member defined at TolExcel::@WorkBook
Last columns that was readed in method GetSheetMaxRange.
*/ 
Real _.maxCol = ?;

/* 
Non static member defined at TolExcel::@WorkBook
Last row that was readed in method GetSheetMaxRange.
*/ 
Real _.maxRow = ?;

/* 
Non static member defined at TolExcel::@WorkBook
Number of columns that were readed in method GetSheetMaxRange.
*/ 
Real _.numCol = ?;

/* 
Non static member defined at TolExcel::@WorkBook
Number of rows that were readed in method GetSheetMaxRange.
*/ 
Real _.numRow = ?;

/* 
Non static member defined at TolExcel::@WorkBook
First column to be readed in method GetSheetMaxRange. Must be set by user if
it's not the fiersdt one before to calling GetSheetMaxRange
*/ 
Real minCol = 1;

/* 
Non static member defined at TolExcel::@WorkBook
First row to be readed in method GetSheetMaxRange. Must be set by user if it's
not the fiersdt one before to calling GetSheetMaxRange
*/ 
Real minRow = 1;

/* 
Non static member defined at TolExcel::@WorkBook

*/ 
Real verbose = True;

/* 
Non static member defined at TolExcel::@WorkBook
The path of the workbook archive used in Open or New methods
*/ 
Text _.path;

/* 
Static member defined at TolExcel::@WorkBook
Default date format.
*/ 
Static Text defaultDateFormat = "%Y-%m-%d";

/* 
Static member defined at TolExcel::@WorkBook
Maximum number of columns that a work sheet can store. Used in method
GetSheetMaxRange.
*/ 
Static Real maxAllowedCol = 256;

/* 
Static member defined at TolExcel::@WorkBook
Maximum number of rows that a work sheet can store. Used in method
GetSheetMaxRange.
*/ 
Static Real maxAllowedRow = 256^2;

/* 
Static member defined at TolExcel::@WorkBook
Controls warnings verbosity.
*/ 
Static Real showMassiveWarning = False
};