C# Interop.Excel类代码示例

        /// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
                CurSheet = null;
                CurBook.Close(false, mValue, mValue);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
                CurBook = null;

                CurExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
                CurExcel = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch (System.Exception)
            {
                // Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
            }
            finally
            {
                foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
                    //if (pro.StartTime < DateTime.Now)
                    pro.Kill();
            }
            System.GC.SuppressFinalize(this);
        }

 private void MakeBoldText(Excel.Worksheet sheet, int firstCell, int lastCell, int row)
 {
     for (int i = firstCell; i < lastCell; i++)
     {
         mySheet.Cells[row, i].Font.Bold = true;
     }
 }

 // closes Excel
 public static void CloseExcel(Excel.Application xlApp, Excel.Workbook wb)
 {
     // Close Excel
     object misValue = System.Reflection.Missing.Value;
     wb.Close(false, misValue, misValue);
     xlApp.Quit();
 }

    /// <summary>
    /// Class constructor does all the work
    /// </summary>
    /// <param name="excelApp"></param>
    /// <param name="fileName"></param>
    public StudentWorkbook( Excel.Application excelApp, string fileName )
    {
      try  // to open the student's spreadsheet
      {
        excelWorkbook = excelApp.Workbooks.Open( fileName, 0,
            true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,  // read only
            false, 0, true, false, false );
      }
      catch ( Exception e )
      {
        Console.WriteLine( "error: " + e.Message );
        Console.WriteLine( "Could not open spreadsheet " + fileName );
      }

      Excel.Sheets excelSheets = excelWorkbook.Worksheets;  // get the Worksheets collection
      Excel.Worksheet excelWorksheet = excelSheets[ 1 ];    // get the first one

      // get the Team Number cell
      Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" );

      // try to convert this cell to an integer
      if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 )
      {
        Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" );
      }

      // get the scores cells
      scores = excelWorksheet.get_Range( "B7", "B15" );

      // get the Additional Comments cell
      comments = excelWorksheet.get_Range( "B18", "B18" );

    }  // end of StudentWorkbook()

        internal Excel.PivotTable CreateSalesPivotTable(Excel.Range range, String filePath)
        {
            string fileDirectory = System.IO.Path.GetDirectoryName(filePath);
            string fileName = System.IO.Path.GetFileName(filePath);

            string pivotTableName = Properties.Resources.SalesAndProfitPivotTableName;
            Excel.PivotCache cache = this.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);

            cache.Connection = String.Format(
                CultureInfo.CurrentUICulture,
                salesPivotTableConnectionTemplate,
                fileDirectory);
            cache.CommandType = Excel.XlCmdType.xlCmdSql;
            cache.CommandText = String.Format(
                CultureInfo.CurrentUICulture,
                salesPivotTableQueryTemplate,
                fileName);

            Excel.PivotTable pivotTable = cache.CreatePivotTable(range, pivotTableName, missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);

            // 将新的数据透视表的属性调整为
            // 所需方式的格式信息。
            pivotTable.ErrorString = " -- ";
            pivotTable.DisplayErrorString = true;
            pivotTable.NullString = " -- ";

            return pivotTable;
        }

        public ReportGenerator(Excel.Worksheet src_worksheet, Excel.Worksheet dest_worksheet)
        {
            _src_worksheet = src_worksheet;
            _dest_worksheet = dest_worksheet;

            writeProjectNamesToDestSheet();
        }

        /// <summary/>
        public PluginManager( MSExcel.Application application )
        {
            myApplication = application;

            myPluginContext = new PluginContext( myApplication );
            myPlugins = new List<AbstractPlugin>();
        }

        public static void InsertColumn(Excel.Worksheet xlSht, int ColumnNo)
        {
            Excel.Range range = (Excel.Range)xlSht.Cells[1, ColumnNo];
            Excel.Range column = range.EntireColumn;

            column.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Type.Missing);
        }

 private void protectWS(Excel.Worksheet pWS, String pPwdWrite) {
   if((pWS != null) && (pPwdWrite != null)) {
     Object vWritePwd = Type.Missing;
     if(pPwdWrite != null)
       vWritePwd = pPwdWrite;
     pWS.Protect(
       vWritePwd,
       true,
       true,
       true,
       false,
       true,
       true,
       true,
       false,
       false,
       false,
       false,
       false,
       true,
       true,
       true
     );
   }
 }

        protected void ReleaseExcel(ref MSOffice.Application excelApp, ref MSOffice.Workbook excelBook, ref MSOffice.Worksheet excelSheet)
        {
            try
            {
                excelBook.Close(false, null, null);
                excelApp.Quit();
                GC.Collect();

                IntPtr ptr = new IntPtr(excelApp.Hwnd);
                int pid = 0;
                GetWindowThreadProcessId(ptr, out pid);
                System.Diagnostics.Process proc = System.Diagnostics.Process.GetProcessById(pid);

                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelSheet);
                excelApp = null;
                excelBook = null;
                excelSheet = null;

                //最后尝试结束进程，出错表示已销毁
                try
                { proc.Kill(); }
                catch (Exception) { }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void WriteDataToWorkSheet(IScriptWorker CurrentScriptWorker, Excel.Worksheet xlWorkSheet, out int RowCounter, out int ColumnCounterForNamesOfTests)
        {
            xlWorkSheet.Cells[1, 1] = "";
            dynamic DatesOfTests = CurrentScriptWorker.GetDatesOfTests();

            RowCounter = 2;
            foreach (dynamic CurrentDate in DatesOfTests)
            {
                xlWorkSheet.Cells[RowCounter, 1] = CurrentDate;
                ++RowCounter;
            }

            dynamic TestsForSave = CurrentScriptWorker.GetListOfTests();
            ColumnCounterForNamesOfTests = 2;         
            foreach (dynamic CurrentTest in TestsForSave)
            {
                xlWorkSheet.Cells[1, ColumnCounterForNamesOfTests] = (string)CurrentTest.GetTestName();
                dynamic CurrentTestResults = CurrentTest.GetValues();
                int RowCounterForTestValues = 2;
                foreach (dynamic CurrentValue in CurrentTestResults)
                {
                    xlWorkSheet.Cells[RowCounterForTestValues, ColumnCounterForNamesOfTests] = CurrentValue;
                    ++RowCounterForTestValues;
                }
                ++ColumnCounterForNamesOfTests;
            }
        }

 protected void OnCompleted(Excel.Workbook xlBook)
 {
     if (Completed != null)
     {
         Completed(this, xlBook);
     }
 }

 protected void OnSaving(Excel.Workbook xlBook)
 {
     if (Saving != null)
     {
         Saving(this, xlBook);
     }
 }

 public Sheet(Excel.Worksheet _worksheet)
 {
     this.Base = _worksheet;
     this.CurrentRow = 1;
     this.CurrentCol = 1;
     this.Valid = ReadValues();
 }

 public FrmImport(Excel.Application excelapp, CultureInfo culture)
     : base(excelapp)
 {
     _excelapp = excelapp;
     InitializeComponent();
     _ctrlAddress = new[] { ctrlAddress1, ctrlAddress2, ctrlAddress3, ctrlAddress4, ctrlAddress5, ctrlAddress6 };
 }