Excel Library

This library uses the DispHelper COM Helper Library to manipulate data in MS Excel worksheet through Excel's COM interface. The functions for accessing data are registered as __get and __set respectively so that array access expression, e.g., a = xls[i,j] or xls[i,j] = a, may be used to get and set values in cells of a worksheet.

Function	Parameter Type	Remark
excel([flag])	Boolean	Creates and returns an Excel object. If the optional flag is set to true, errors may be shown in a message window (good for debugging).
.open(fname)	string	Opens an Excel file. Return 0 if successful.
.visible(flag)	boolean	Makes Excel visible or invisible (default).
.close()		Closes the Excel program. Return 0 if successful.
.save([fanme])	string	Save Excel workbook; or save as another file if the optional fname is given. Return 0 if successful.
.activate(index)	integer	Activates the specified worksheet. Return zero if successful.
.add([name])	string	Adds one worksheet and renames it if the optional name is given.
.delete()		Deletes the active worksheet.
.ncol(i, j)	integers	Counts the number of non-empty columns starting from cell(i,j) and return an integer.
.nrow(i, j)	integers	Counts the number of non-empty rows starting from cell(i,j) and return an integer.
.__get(row, col)	integers or arrays	Gets the value in cell(row, col) in the active worksheet. Returns a real, a string, or a null depending on the content in the cell. If row and col are arrays, e.g., 1:10 and 5:30, it returns a pointer of double containing data in the range.
.__set(row, col, value)	integer or array, integer or array, number or string or user	Sets the value to cell(row, col) in the active worksheet. If row and col are arrays and value is a user object, it assumes the pointer of the user object points to exact number of double values.

Example

load("excel.dll", "matrix.dll");

xls = excel(true);
xls.open("e:\\Book1.xls");
xls.visible(true);
csv(xls[1,1]);
xls[1,1] = 100.0;
csv(xls[1,1]);
xls[1,1] = "Hi!";
csv(xls[1,1]);

ptr = xls[1:5,3:7];
d = double(5, 5);
d.import(ptr);
d.print();
d.fill(0, 1);
[ptr, n] = d.ptr();
xls[1:5,3:7] = ptr; 

xls.save();
xls.close();

// Because a dialog box may show up in closing Excel
// it is recommended to wait a while and then
// send the ENTER key to the foreground window.

sleep(1000);
sendkey("Enter");