Vsto: Use workbooks

1. Create a workbook

When creating a workbook programmaticallyMicrosoft. Office. InterOP. Excel. WorkbookObject insteadMicrosoft. Office. Tools. Excel. WorkbookHost item.

1. Click the button in the worksheet to create a workbook.

In the workbookSheet1Create a new button and write the following in the button-clicking event:CodeCreate a workbook:

Using Excel = Microsoft. Office. InterOP. Excel;

Excel. Workbook newworkbook = This. application. workbooks. Add ();

Newworkbook. application. Caption ="Create a workbook";

2. Click the function area button to create a workbook.

InRibbonAdd a button to write the following code to create a new workbook:

Excel. Workbook WB = globals. thisworkbook. application. workbooks. Add ();

WB. application. Caption = "test ";

Ii. Open a workbook

Microsoft Office ExcelInWorkbooksSet allows you to use all open workbooks and open workbooks.

UseWorkbooksSetOpenMethod.

InRibbonTo open a workbook:

Excel. Workbook WB = globals. thisworkbook. application. workbooks. Open (@ "C: \ test.xlsx ");

3. Close the workbook

CallThisworkbook. CloseTo close the workbook associated with the custom item.

Globals. thisworkbook. Close ();

4. Save the workbook

Workbooks can be saved in multiple ways.

If the workbook has not been saved before, you should specify a path to save the workbook. If no explicit path exists,ExcelThe file will be saved in the current folder with the name specified for it when the file is created.

You can also save a copy of The workbook without modifying the workbook opened in the memory.

1. Save the workbook at the current location

CallThisworkbookClassSaveMethod To save the workbook.

2. Save another workbook

CallThisworkbookClassSaveasMethod To save the workbook in the new path.

This. saveas (@ "C: \ book1.xml ")

3. Save a copy of The workbook

CallThisworkbookClassSavecopyasYou can save a copy of The workbook to a file without modifying the workbook opened in the memory. This method is useful when you need to create a backup copy without modifying the workbook location.

When saving a workbook, canceling any save or copy method will cause a running error in the code. For exampleSaveasMethod, but not disabledExcelWhen the user clicks "cancel,ExcelThis will cause a runtime error.Program.

5. Activate a workbook

WorkbooksSetActivateMethod to activateExcelWorkbook and select the first worksheet in the workbook.

1. UseActivateMethod to activate a workbook

UseWorkbooksSetActivateMethod to activate a workbook.

(Microsoft. Office. InterOP. Excel. _ workbook) This. application. workbooks [1]). Activate ();

2. Reference a workbook

You can use an integer (indicating the position in the Set) or a workbook name to compile the indexWorkbooksSet. However, to reference a workbook by name, you must use the name displayed in the title bar. The name does not include the file extension before saving the file.

You can use the workbook location number or name to reference each workbook. Run the following code:

Excel. Workbook WB = This. application. workbooks [1];

// Before book1 is saved:

WB = This. application. workbooks ["book1"];

// After book1 is saved:

WB = This. application. workbooks ["book1.xls"];

 

6. Protect workbooks

ProtectedExcelWorkbooks, So that you cannot add or delete worksheets. You can also program the workbook to cancel the protection. You can specify a password to indicate whether you want to protect the structure (preventing the user from moving the table) and whether you want to protect the workbook window.

1. Protect workbooks

Protecting workbooks does not prevent users from editing cells. To protect data, you must protect the worksheet.

The following code example uses a variable to set a password.

CallProtectMethod and a password.

This. Protect (getpasswordfromuser, missing, missing );

Must be inThisworkbookClass, instead of running in the worksheet class.

2. Disable workbook Protection

CallUnprotectMethod to cancel the workbook protection. If necessary, you need to pass a password.

This. unprotect (getpasswordfromuser );

VII. Set and clear the workbook Password

You can set a password for a workbook to restrict other users from accessing the workbook.

The following example sets the workbook password.

SetThisworkbookThe Password attribute is set to a string provided by the user.

Private void setpassword ()

{

String Password = This. application. inputbox ("Enter the password:",Missing, missing, missing). tostring ();

String confirmpassword = This. application. inputbox ("Please enter the password again:",Missing, missing, missing). tostring ();

If (password! = Confirmpassword)

{

MessageBox. Show ("The two passwords are different!");

Globals. thisworkbook. Password = "";

}

Else

{

Globals. thisworkbook. Password = password;

}

}

To clear the password, you only needPasswordSet the property to an empty string.

8. Obtain and set the default file path of the workbook

Use_ ApplicationObjectDefaultfilepathProperty to obtain the default path. The following code shows the current default path.

System. Windows. Forms. MessageBox. Show (this. application. defaultfilepath );

DirectionApplicationObjectDefaultfilepathAttribute to assign a string value, you can set the default path of the workbook. The following code sets the workbook save pathC: \ Temp.

This. application. defaultfilepath = @ "C: \ Temp ";

IX. recently used workbook files

RecentfilesProperty returns a set that containsExcelAll file names in the recently used file list. The length of the list varies with the number of files to be retained.

The following code traverses the list of recently used files and displays the names in cells relative to the worksheet.

Excel. Range RNG = This. application. get_range ("A1", missing );

For (INT I = 1; I <= This. application. recentfiles. Count; I ++)

{

RNG. get_offset (I-1, 0). value2 = This. application. recentfiles. get_item (I). Name;

}

10. display the open dialog box

The following code prompts youExcelOpen a new workbook. It sets properties to allow multiple selection, clear the list of available filters, and add two new filters. Then, the code will callFiledialogObjectExecuteMethod to open the requested file.

Microsoft. Office. Core. filedialog FD = This. application. get_filedialog(Microsoft. Office. Core. msofiledialogtype. msofiledialogopen );

FD. allowmultiselect = true;

FD. Filters. Clear ();

FD. Filters. Add ("Excel files", "*. xls; *. xlw", missing );

FD. Filters. Add ("all files", "*. *", missing );

If (FD. Show ()! = 0)

{

FD. Execute ();

}