Contact: zeng  @  zegraph.com      Last update: 10 May 2011

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");