VS2008环境下C#对Excel的操作 [C#] (收集转载)

Optional Object. Determines how the new workbook is created. If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template. If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet. If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).

也就是说，Workbooks.Add的参数是个可选的object类型，这个参数定义了新的工作簿的创建方式。如果传递的是一个描述Excel文件名的string类型，那么新的工作簿会以你所传递的Excel文件为模板来创建。如果传递的参数是一个常量，那么这个新的工作簿只会包含一个单独的sheet表单。这个常量可以是XlWBATemplate枚举类型的值，如：xlWBSTChart，xlWBATExcel4IntMacroSheet，xlWBATExcel4MacroSheet，或xlWBATWorksheet。如果这个参数被省略了，Excel会按照SheetInNewWorkbook属性的值来创建一个新的工作簿。

通常情况下，使用true或null,表明工作簿在默认文档下创建，或者使用枚举值      XlWBATemplate.xlWBATWorksheet，也可以传入一个已经存在的excel完整文件名。

方法三：打开一个已经存在的Excel文件。

Excel.Workbook workbook=MyExcel.Workbooks.Open(Environment.CurrentDirectory+"/SampleExcel.xls",0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value,true, false, System.Reflection.Missing.Value, false, false, false);    

四、创建工作页sheet表单。

方法一：Excel.Worksheet worksheet =(Worksheet)workbook.Worksheets[1];       //选择sheet1

Workbook.Worksheets属性在MSDN里的定义是：

Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

这是一个只读的sheets集合。

方法二：Excel.Worksheet worksheet = workbook.ActiveSheet as Excel.Worksheet;

           //或者写成Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet; 

在MSDN里对ActiveSheet是接口_Workbook的属性。它的定义如下：

object ActiveSheet {get;}

Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active. Read-only.

这是一个只读属性，它返回一个object类型值，这个值代表了屏幕顶端的活动工作簿的活动sheet表单。

而Workbook类实现了_Workbook接口。

五、设置格式。

这个部分网上资料比较多。略。

六、保存Excel文档。

            //设置禁止弹出保存和覆盖的询问提示框。这两句代码至关重要，而且必不可少，否则，保存时会弹出“是否保存sheet1.xls”的对话框。判断当前激活的表，并保存这个表。
            MyExcel.DisplayAlerts = false;
            MyExcel.AlertBeforeOverwriting = false;

            MyExcel.ActiveWorkbook.SaveCopyAs(filename);

            MyExcel.Quit();

MSDN中对ApplicationClass.DisplayAlerts 属性的解释是：

The default value is True. Set this property to False if you don’t want to be disturbed by prompts and alert messages while a macro is running; any time a message requires a response, Microsoft Excel chooses the default response.

If you set this property to False, Micorosoft Excel sets this property to True when the code is finished, unless you are running cross process code.

When using the SaveAs method for workbooks to overwrite an existing file, the 'Overwrite' alert has a default of 'No', while the 'Yes' response is selected by Excel when the DisplayAlerts property is set equal to True.

MSDN中对ApplicationClass.AlertBeforeOverwriting 属性的解释是：

True if Microsoft Excel displays a message before overwriting nonblank cells during a drag-and-drop editing operation. Read/write Boolean.

ApplicationClass类实现了接口_Application。MSDN中对_Application.ActiveWorkbook 属性的解释是：

Returns a Workbook object that represents the workbook in the active window (the window on top). Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.

Workbook类实现了接口_Workbook。MSDN中对_Workbook.SaveCopyAs 方法的定义是：

void SaveCopyAs([In, Optional] object Filename);

解释如下：

Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.

对Filename的解释如下：

Required. Specifies the file name for the copy.

MSDN中对ApplicationClass.Quit()方法的解释是：

Quits Microsoft Excel.

If unsaved workbooks are open when you use this method, Microsoft Excel displays a dialog box asking whether you want to save the changes. You can prevent this by saving all workbooks before using the Quit method or by setting the DisplayAlerts property to False. When this property is False, Microsoft Excel doesnt display the dialog box when you quit with unsaved workbooks; it quits without saving them.

If you set the Saved property for a workbook to True without saving the workbook to the disk, Microsoft Excel will quit without asking you to save the workbook.