VSTO中开发Excle 常用的操作

1. 当前工作簿：workBook = Globals.ThisAddIn.Application.ActiveWorkbook;

2. 当前工作表：workSheet = (Excel.Worksheet)workBook.ActiveSheet;

3.工作簿名：    workBookName = workBook.Name;

4.工作表名：    workSheetName = workSheet.Name;

5.排序：

                Range myRange = (Excel.Range)workSheet.Cells[1, 1];

                workSheet.Sort.SortFields.Add(myRange, Excel.XlSortOn.xlSortOnValues,    Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal);

                myRange = workSheet.get_Range("A1", "A10");

                workSheet.Sort.SetRange(myRange);

                workSheet.Sort.Header = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo;

                workSheet.Sort.MatchCase = false;

                workSheet.Sort.Orientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns;

                workSheet.Sort.SortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin;

                workSheet.Sort.Apply();

6.设置单元格格式：

      //显示格式

      setRange.NumberFormatLocal = "$#,##0_);[红色]($#,##0)";  

      //背景色

      setRange.Interior.ColorIndex = 3;

      //边框

      setRange.Cells.Borders.ColorIndex = 1;

      //设置单元格中不同字符为不同颜色，这个功能只有到07后才有的  

      Range rangeStyle = (Range)mgrSummary.UsedRange[changeStart, 1];

         if (rangeStyle != null)

         {

               object styleValue = rangeStyle.Value2;

               if (styleValue != null)

               {

                    Characters changeStyle = rangeStyle.get_Characters(0, rangeStyle.Value2.ToString().Length);

                     changeStyle.Font.Color = Color.Red.ToArgb();

                }

         }

7.查找单元格内容：

      usedRange.Find(string, miss, XlFindLookIn.xlFormulas, XlLookAt.xlWhole, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false, false, miss);

8.单元格区域拷贝：

                worksheetRange = worksheet.get_Range(columnName, miss);

                newWorksheetRange = newWorkSheet.get_Range(columnName, miss);

                worksheetRange.Copy(newWorksheetRange);

9.产生数据透视表

        private Worksheet GeneratetPivot(Worksheet worksheet, PivotCaches pivotCaches)

        {

            Worksheet worksheetPivot = (Worksheet)worksheets.Add(miss, worksheet, 1, miss);

            worksheetPivot.Name = PivotName;

            PivotCache pivotCache = pivotCaches.Add(XlPivotTableSourceType.xlDatabase, worksheetLoan.UsedRange);

            Range range = worksheetPivot.get_Range("A1", miss);

            //创建数据透视表

            PivotTable pivotTable = pivotCache.CreatePivotTable(range, tPivotName, true, XlPivotTableVersionList.xlPivotTableVersionCurrent);

            //Adds row, column, and page fields to a PivotTable report or PivotChart report

            pivotTable.AddFields("Name", miss, miss, true);

            pivotTable.AddFields("Amount", miss, miss, true);

            PivotField pivotField = (PivotField)pivotTable.PivotFields("Name");

            pivotField.Orientation = XlPivotFieldOrientation.xlRowField;

            pivotField = (PivotField)pivotTable.PivotFields("Amount");

            pivotField.Orientation = XlPivotFieldOrientation.xlDataField;

            pivotField.Function = XlConsolidationFunction.xlSum;

            return worksheetPivot;

        }

9、增加新的数据表

    Microsoft.Office.Interop.Excel.worksheet mynewsheet=new Microsoft.Office.Interop.Excel();  

    mynewSheet=(Microsoft.Office.Interop.Excel.worksheet)workbook.worksheets.add(type.missing,type.missing,1,type.missing);

  关于Add方法的介绍：

  Add 方法应用于Sheets和Worksheets对象时新建工作表、图表或宏表，语法如下：

expression.Add(Before, After, Count, Type)

参数Before是可选的，指定工作表对象，新建的工作表将置于此工作表之前。

参数After是可选的，指定工作表对象，新建的工作表将置于此工作表之后。

如果Before和 After两者均省略，则新建的工作表将插入到活动工作表之前。

参数Count可选，要新建的工作表的数目。默认值为 1。

参数Type可选，指定新建的工作表类型。