C # Excel operations

Recently, I used C # To Operate excel. I mainly read the excel template, copied the sheet page of the template, generated multiple sheet pages, filled the corresponding data, and saved it to the excel file, the knowledge points used are as follows.

1. Add reference and namespace

Add Microsoft. Office. Interop. Excel reference. The default path is C: Program FilesMicrosoft Visual Studio 9.0 Visual Studio Tools for OfficePIAOffice12Microsoft. Office. Interop. Excel. dll.

Add reference using Microsoft. Office. Interop. Excel in the Code;

II. Introduction to Excel

The structure of the Excel class in this namespace is as follows:
ApplicationClass-is our excel application.
Workbook-an excel file that we usually see. It is often operated by the Workbooks class.
Worksheet-a sheet in an excel file.
Worksheet. cells [row, column]-is the cell of a column in a row. Note that the subscript row and column start from 1, it is different from the subscript of an array or set that I usually use.

After learning the basic knowledge above, it is much clearer to use this class to Operate excel.

Iii. Excel operations
Any Excel operation must first use an excel application. First, you must create an ApplicationClass instance and release the instance.

ApplicationClass xlsApp = new ApplicationClass (); // 1. Create an instance of an Excel application object, which is equivalent to opening an Excel application from the Start Menu.
If (xlsApp = null)
{
// Verify the instance. If it is null, the machine running the Code may not have Excel installed.
}

1. Open an existing Excel File

Code

Workbook workbook = xlsApp. workbooks. open (excelFilePath, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing );

Worksheet mySheet = workbook. Sheets [1] as Worksheet; // the first sheet page
MySheet. Name = "testsheet"; // modify the sheet Name here.

2. Copy the sheet page

MySheet. copy (Type. missing, workbook. sheets [1]); // copy mySheet to form a new sheet page. After copying, add a (2) Name After mySheet Page name. Here testsheet (2) is used ), after copying, add one more Worksheet

Note that the two parameters of the Copy method refer to whether the new sheet page is copied before or after the specified sheet page, the preceding example indicates that the copied sheet is behind the first sheet.

3. Delete the sheet page

XlsApp. DisplayAlerts = false; // to delete a sheet, set this item to fasle.
(XlsApp. ActiveWorkbook. Sheets [1] as Worksheet). Delete ();

4. Select the sheet page

(XlsApp. ActiveWorkbook. Sheets [1] as Worksheet). Select (Type. Missing); // Select a sheet

5. save an excel file

Workbook. Saved = true;
Workbook. SaveCopyAs (filepath );

6. Release excel Resources

Workbook. Close (true, Type. Missing, Type. Missing );
Workbook = null;
XlsApp. Quit ();
XlsApp = null;