. NET Common Skills _ operating Excel Knowledge points

C # manipulating Excel knowledge points

recently in use C # operation Excel, mainly read the Excel template, copy the template sheet page, generate multiple sheet pages to populate the corresponding data and save to Excel file, the knowledge points are as follows.

I. Adding references and namespaces

Add the Microsoft.Office.Interop.Excel reference, whose default path is C:\Program Files\Microsoft Visual Studio 9.0\visual Studio Tools for office\ Pia\office12\microsoft.office.interop.excel.dll

The code adds a reference using the Microsoft.Office.Interop.Excel;

Second, the simple introduction of the Excel class

The structure of the Excel class under this namespace is:

ApplicationClass-is our Excel application.

Workbook-is an Excel file we usually see, often using the workbooks class to manipulate it.

Worksheet-is a sheet page in an Excel file.

Worksheet.cells[row, column]-is the cell of a column in a row, note that the subscript row and column here are starting with 1, which is different from the subscript of the array or collection I normally use.

Once you know the basics, it's a lot clearer to use this class to manipulate Excel.

Iii. operation of Excel

Any action for Excel must first be done with an Excel application, first with a new ApplicationClass instance, and finally releasing this instance.

ApplicationClass Xlsapp = new ApplicationClass (); 1. Creating an instance of an Excel Application object is equivalent to opening an Excel application from the Start menu.

if (Xlsapp = = null)

{

Validate this instance, or null to indicate that the machine running this code may not have Excel installed

}

1. Open an existing Excel file

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; First page of sheet

Mysheet.name = "Testsheet"; Change the sheet name here

2. Copy the sheet page

Mysheet.copy (Type.Missing, workbook. SHEETS[1]); Copy mysheet into a new sheet page, after the name is MySheet page name plus one (2), here is Testsheet (2), after copying, the number of worksheet added a

Note the two parameters of the copy method here, that is, whether to copy the new sheet page before or after the specified sheet page, the above example refers to the copy of the sheet page after the first sheet page.

3. Delete Sheet page

Xlsapp.displayalerts = false; If you want to delete a sheet page, first set this 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 page

5. Save Excel File

Workbook. Saved = true;

Workbook. SaveCopyAs (filepath);

6. Releasing Excel Resources

Workbook. Close (True, Type.Missing, Type.Missing);

workbook = null;

Xlsapp.quit ();

Xlsapp = null;

Added file header :

using System.Reflection;// reference this to use the Missing field

Using Excel;

Excel.applicationclass Excel = new Excel.applicationclass ();

Excel.       Visible = true; activating Excel

Workbook Wbook = Excel. Workbooks.Add (TRUE);

Worksheet Wsheet = (excel._worksheet) wbook.activesheet;

Worksheet Wsheet = (excel.worksheet) wbook.activesheet;

Excel. Cells[3, 5] = " the company phone :" + phone;

Excel. Cells[4, 5] = "Fax of the company :" + zhen;

Excel. Cells[5, 5] = " contact person :" + comname;

Excel. Cells[4, 1] = " customer :" + CustomerName;

Excel. Cells[5, 1] = " contact person :" + Associate;

Excel. Cells[3, 8] = " account name :";

Excel. Cells[3, 9] = AccountName;

Excel. Cells[4, 8] = "Bank of account :";

Excel. Cells[4, 9] = Branchname;

Excel. Cells[5, 8] = " account number :";

Excel. Cells[5, 9] = "'" + AccountID;

                       // set up a query prompt to suppress pop-up saves and overrides

Excel. DisplayAlerts = false;

Excel. Alertbeforeoverwriting = false;

            // Save your work book

Wbook.save ();

            // Save the active table each time so that you can save a different Excel table multiple times, the default save location is in the My Documents

Excel. Cells.Font.Size = 12;

Excel. Cells.Font.Bold = false;

Excel.Range m_objrange = M_objrange.get_range (1, 3);

Wsheet.get_range (Excel. Cells[1, 3], Excel. Cells[1, 3]). Font.Size = 24;

Wsheet.get_range (Excel. Cells[1, 3], Excel. Cells[1, 3]). Font.Bold = true;

Wsheet.get_range (Excel. Cells[3, 1], Excel. Cells[3, 1]). Font.ColorIndex = 3;// Here is set to red, you cannot set the color with font.color

M_objRange.Cells.Font.Size = 24;

M_objRange.Cells.Font.Bold = true;

Excel. Activeworkbook.savecopyas (filename);

Excel. Quit ();

The Code Comment section simply describes the origin of each statement, and the individual is worth the scrutiny.

Statement one Workbook Wbook = Excel. Workbooks.Add (true);

Workbooks.Add parameter is an object type, usually using true or null to indicate that the workbook was created under the default document, or using an enumeration value

Xlwbatemplate.xlwbatworksheet, However, if you pass in an Excel full file name, it is equivalent to opening an existing workbook.

Statement two Worksheet wsheet = (excel.worksheet) wbook.activesheet; This allows you to manipulate multiple worksheets and add them to the wbook.worksheets after instantiation . If you open a workbook that already exists, this statement can also be an error, preferably by calling Wbook.activesheet to get or add some judgment.

Statement three

Excel. Activeworkbook.savecopyas (filename); these two lines of code are important and necessary, otherwise the "Save Sheet1.xls" dialog box pops up when you save it. Determine the currently active table and save the table.

statement four Excel. Quit ();

      This closure has always been questionable, becauseC # operation com quit () excel process does not necessarily end , so killprocess () to handle , I personally think it's not a good idea process. I am currently using app

=

null; power to comfort it. However, it is important to do so, that is, there can be no change before Quit () , or it will pop up the saved dialog box. So make sure that you have executed the Save () method of WorkBook or application before exiting .