Excel Add-in Development in C/C++ Applications in Finance phần 7 doc

218 Excel Add-in Development in C/C++

Table 8.14 Selected arguments to xlfGetDocument

ArgNum What the function returns

1 If Name is a sheet name:

• If more than one sheet in the current workbook, returns the name of

the sheet in the form [Book1.xls]Sheet1.

• If only one sheet in the current workbook, but the name of the

workbook is not Name, returns the sheet Name in the form

[Book1.xls]Sheet1

• If only one sheet in the current workbook and the workbook and

sheet are both called Name, returns the name of the workbook in the

form Book1.xls

• If sheet Name does not exist in the current workbook, returns #N/A

If Name is a workbook name:

• If more than one sheet in the given workbook, the name of the first

sheet in the form [Book1.xls]Sheet1

• If one sheet in the given workbook, and the sheet name is not also

Name, the name of that sheet in the form [Book1.xls]Sheet1

• If one sheet with the same name as the given workbook, the name of

the workbook in the form Book1.xls

• If workbook Name is not open, returns #N/A

If Name is omitted:

• If more than one sheet in the active workbook or the sheet name is

not the same as the active workbook name, the name of the active

sheet in the form [Book1.xls]Sheet1.

• If one sheet with the same name as the active workbook, the name of

the workbook in the form Book1.xls

(See also ArgNum 76 and 88 below, which return the names of the

active worksheet and the active workbook respectively.)

2 Path of the directory containing workbook Name if it has already been

saved, else #N/A

3 A number indicating the type of sheet. If given, Name is either a sheet

name or a workbook. If omitted the active sheet is assumed. If Name is

a workbook, the function returns 5 unless the book has only one sheet

with the same name as the book, in which case it returns the sheet type.

1 = Worksheet

2 = Chart

3 = Macro sheet

4 = Info window if active

5 = Reserved

6 = Module

7 = Dialog

Accessing Excel Functionality Using the C API 219

Table 8.14 (continued)

4 True if changes made to the sheet since last saved.

5 True if the sheet is read-only.

6 True if the sheet is password protected.

7 True if cells in the sheet or the series in a chart are protected.

8 True if the workbook windows are protected. (Name can be either a

sheet name or a workbook. If omitted the active sheet is assumed.)

9 The first used row or 0 if the sheet is empty. (Counts from 1.)

10 The last used row or 0 if the sheet is empty. (Counts from 1.)

11 The first used column or 0 if the sheet is empty. (Counts from 1.)

12 The last used column or 0 if the sheet is empty. (Counts from 1.)

13 The number of windows that the sheet is displayed with.

14 The calculation mode:

1 = Automatic

2 = Automatic except tables

3 = Manual

15, 18, 19, 20 Options dialog box, Calculation tab checkbox settings as either true or

false:

15: Returns the Iteration checkbox state

18: Returns the Update Remote References checkbox state

19: Returns the Precision As Displayed checkbox state

20: Returns the 1904 Date System checkbox state

16 Maximum number of iterations.

17 Maximum change between iterations.

33 The state of the Recalculate Before Saving checkbox in the Calculation tab

of the Options dialog box.

34 True if the workbook is read-only recommended.

35 True if the workbook is write-reserved.

36 If the workbook has a write-reservation password and it is opened with

read/write permission, returns the name of the user who originally saved

it with the write-reservation password.

If the workbook is opened as read-only, or if a password has not been

added, returns the name of the current user.

48 The standard column width setting.

(continued overleaf )

220 Excel Add-in Development in C/C++

Table 8.14 (continued)

68 The workbook name without path.

76 The name of the active sheet in the form [Book1.xls]Sheet1

84 The value of the first circular reference on the sheet, or #N/A if none.

87 The position of the given sheet in the workbook. If the workbook name is not given

with the sheet name, operates on the current workbook. (Includes hidden sheets and

counts from 1.)

88 The workbook name in the form Book1

The Excel4() function set-up and call would be as shown in the following C/C++

code example of an exportable function that wraps up the call to xlfGetDocument and

returns whatever is returned from that call.

xloper * __stdcall get_document(int arg_num, char *sheet_name)

{

xloper arg1, arg2;

static xloper ret_xloper;

if(arg_num < 1 || arg_num > 88)

return p_xlErrValue;

arg1.xltype = xltypeInt;

arg1.val.w = arg_num;

if(sheet_name)

{

arg2.xltype = xltypeStr;

arg2.val.str = new_xlstring(sheet_name);

}

else

arg2.xltype = xltypeMissing;

Excel4(xlfGetDocument, &ret_xloper, 2, &arg1, &arg2);

// Tell Excel to free up memory that it might have allocated for

// the return value.

ret_xloper.xltype |= xlbitXLFree;

if(sheet_name)

free(arg2.val.str);

return &ret_xloper;

}

Using the cpp_xloper class, the equivalent code becomes:

xloper * __stdcall get_document(int arg_num, char *sheet_name)

{

cpp_xloper Arg1(arg_num, 1, 88);

Accessing Excel Functionality Using the C API 221

if(!Arg1.IsType(xltypeInt))

return p_xlErrValue;

cpp_xloper Arg2(sheet_name);

cpp_xloper RetVal;

Excel4(xlfGetDocument, &RetVal, 2, &Arg1, &Arg2);

return RetVal.ExtractXloper(true);

}

8.9.7 Getting the formula of a cell: xlfGetFormula

Overview: Returns the formula, as text, of the top left cell in a given

reference. The formula is returned in R1C1 style (see

section 2.2, A1 versus R1C1 cell references for details).

Enumeration value: 106 (x6a)

Callable from: Commands and macro sheet functions.

Return type: Text or error.

Arguments: Ref. A reference xloper.

The Excel4() function set-up and call would be as shown in the following C/C++

code example of an exportable function that wraps up the call to xlfGetFormula. The

function returns the formula as a string.

xloper * __stdcall get_formula(xloper *p_ref)

{

cpp_xloper RetVal;

Excel4(xlfGetFormula, &RetVal, 1, p_ref);

// Extract and return the xloper, using Excel to free memory

return RetVal.ExtractXloper(true);

}

8.9.8 Getting a cell’s comment: xlfGetNote

Overview: Returns the text of the comment attached to the top left cell in

the given reference. If no comment has been added to the cell,

it returns an empty string.

Enumeration value: 191 (xbf)

Callable from: Commands and macro sheet functions.

Return type: Text.

Arguments: Ref. A reference xloper.