thisworkbook.cs

强大的.net Excel控件

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;
using VB = Microsoft.VisualBasic;
using Office = Microsoft.Office.Core;

// Office integration attribute. Identifies the startup class for the workbook. Do not modify.
[assembly:System.ComponentModel.DescriptionAttribute("OfficeStartupClass, Version=1.0, Class=ExcelObjectModelCS.OfficeCodeBehind")]

namespace ExcelObjectModelCS
{
  /// <summary>
  /// Contains managed code extensions for the workbook.
  /// </summary>
  public class OfficeCodeBehind
  {
        
    /// <summary>
    /// Application object.
    /// </summary>
    internal Excel.Application ThisApplication
    {
      get { return thisApplication;}
    }

    /// <summary>
    /// Workbook object.
    /// </summary>
    internal Excel.Workbook ThisWorkbook
    {
      get { return thisWorkbook;}
    }

    private Excel.Application thisApplication = null;
    private Excel.Workbook thisWorkbook = null;

    private Excel.WorkbookEvents_OpenEventHandler openEvent;
    private Excel.WorkbookEvents_BeforeCloseEventHandler beforeCloseEvent;
    private Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler followHyperlinksEvent;
    private Excel.WorkbookEvents_SheetSelectionChangeEventHandler selectionChangeEvent;

    #region Generated initialization code

    /// <summary>
    /// Default constructor.
    /// </summary>
    public OfficeCodeBehind()
    {
    }

    /// <summary>
    /// Required procedure. Do not modify.
    /// </summary>
    /// <param name="application">Application object.</param>
    /// <param name="workbook">Workbook object.</param>
    public void _Startup(object application, object workbook)
    {
      this.thisApplication = application as Excel.Application;
      this.thisWorkbook = workbook as Excel.Workbook;

      openEvent= new Excel.WorkbookEvents_OpenEventHandler (ThisWorkbook_Open);
      thisWorkbook.Open += openEvent;
            
      beforeCloseEvent = new Excel.WorkbookEvents_BeforeCloseEventHandler(ThisWorkbook_BeforeClose);
      thisWorkbook.BeforeClose += beforeCloseEvent;

      followHyperlinksEvent = new Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler(ThisWorkbook_SheetFollowHyperlink);			
      thisWorkbook.SheetFollowHyperlink +=	followHyperlinksEvent;

      selectionChangeEvent = new Excel.WorkbookEvents_SheetSelectionChangeEventHandler(ThisWorkbook_SheetSelectionChange);
      thisWorkbook.SheetSelectionChange += selectionChangeEvent;
    }

    /// <summary>
    /// Required procedure. Do not modify.
    /// </summary>
    public void _Shutdown()
    {
      thisApplication = null;
      thisWorkbook = null;
    }

    /// <summary>
    /// Finds the control with the specified name in the active worksheet.
    /// </summary>
    /// <param name='name'>Name of the control to find.</param>
    /// <returns>
    /// Returns the specified control, or null if it is not found.
    /// </returns>
    object FindControl(string name )
    {
      return FindControl(name, (Excel.Worksheet) ThisWorkbook.ActiveSheet);
    }

    /// <summary>
    /// Returns the control with the specified name in the specified worksheet.
    /// </summary>
    /// <param name='name'>Name of the control to find.</param>
    /// <param name='sheet'>Worksheet object that contains the control.</param>
    /// <returns>
    /// Returns the specified control, or null if it is not found.
    /// </returns>
    object FindControl(string name, Excel.Worksheet sheet ) 
    {
      Excel.OLEObject theObject;
      try
      {
        theObject = (Excel.OLEObject) sheet.OLEObjects(name);
        return theObject.Object;
      }
      catch
      {
        // Returns null if the control is not found.
      }
      return null;
    }

    #endregion

    /// <summary>
    /// Called when the workbook is opened.
    /// </summary>
    protected void ThisWorkbook_Open()
    {
      InitializeData();
    }

    /// <summary>
    /// Called before the workbook is closed.
    /// The Cancel parameter has no effect. The workbook will close 
    /// whether Cancel is set to True or False, unless it is kept open 
    /// by user intervention or by code such as a COM add-in or VBA.
    /// </summary>
    /// <param name="Cancel">Has no effect.</param>
    protected void ThisWorkbook_BeforeClose(ref bool Cancel)
    {

    }

    protected void ThisWorkbook_SheetFollowHyperlink(System.Object Sh , Excel.Hyperlink Target)
    {
      switch (Target.SubAddress)
      {
        case "ApplyStyle":
          ApplyStyle();
          break;
        case "AutoFill":
          AutoFill();
          break;
        case "CheckSpelling":
          TestSpelling();
          break;
        case "ClearDocumentProperties":
          ClearDocumentProperties();
          break;
        case "ClearGroups":
          ClearGroups();
          break;
        case "ClearNameInformation":
          ClearNameInformation();
          break;
        case "ClearNames":
          ClearNames();
          break;
        case "ClearSheetList":
          ClearSheetList();
          break;
        case "ClearStyle":
          ClearStyle();
          break;
        case "Evaluate":
          TestEvaluate();
          break;
        case "FilePicker":
        case "FolderPicker":
        case "SaveAs":
        case "Open":
          TestFileDialog(Target.SubAddress);
          break;
        case "Find":
          DemoFind();
          break;
        case "HideComments":
          HideComments();
          break;
        case "ListRecentFiles":
          ListRecentFiles();
          break;
        case "ListSheets":
          ListSheets();
          break;
        case "NameInformation":
          GetNameInformation();
          break;
        case "PADFalse":
          TestPrecisionAsDisplayed(false);
          break;
        case "PADTrue":
          TestPrecisionAsDisplayed(true);
          break;
        case "ProtectSheet":
          ProtectSheet();
          break;
        case "ResetAutoFill":
          ResetAutoFill();
          break;
        case "ResetBoldSelectedRow":
          ResetBoldSelectedRow();
          break;
        case "ResetFind":
          ResetFind();
          break;
        case "ResetRecentFiles":
          ResetRecentFiles();
          break;
        case "ResetSort":
          ResetSort();
          break;
        case "ResetWindows":
          ResetWindows();
          break;
        case "SendMail":
          TestEmail();
          break;
        case "SetDefaultFilePath":
          SetDefaultFilePath();
          break;
        case "SetDefaultSaveFormat":
          SetDefaultSaveFormat();
          break;
        case "SetDocumentProperties":
          DisplayDocumentProperties();
          break;
        case "SetPassword":
          SetPassword();
          break;
        case "Sort":
          DemoSort();
          break;
        case "UnprotectSheet":
          UnprotectSheet();
          break;
        case "WorkWithComments":
          WorkWithComments();
          break;
        case "WorkWithGroups":
          WorkWithGroups();
          break;
        case "WorkWithNames":
          TestNames();
          break;
        case "WorkWithWindows":
          TestWindows();
          break;
        case "WorksheetFunctions":
          TestWorksheetFunction();
          break;
      }
    }

    protected void ThisWorkbook_SheetSelectionChange(System.Object sh, Excel.Range Target)
    {
      if (ThisApplication.Intersect(
        Target, ThisApplication.get_Range("BoldSelectedRow", 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, 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, Type.Missing) != null)
      {
        // The selection is within the range where you're making
        //the selected row bold.
        BoldCurrentRow((Excel.Worksheet) sh);
      }
    }
		
    private void ApplyStyle()
    {
      const String STYLE_NAME = "PropertyBorder";
      // Get the range containing all the document properties.
      Excel.Range rng = GetDocPropRange();
      Excel.Style sty;
      try
      {
        sty = ThisWorkbook.Styles[STYLE_NAME];
      }
      catch
      {
        sty = ThisWorkbook.Styles.Add(STYLE_NAME, Type.Missing);
      }

      sty.Font.Name = "Verdana";
      sty.Font.Size = 12;
      sty.Font.Color = ColorTranslator.ToOle(Color.Blue);
      sty.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
      sty.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
      rng.Style = STYLE_NAME;
      rng.Columns.AutoFit();
    }

    private void AutoFill()
    {
      Excel.Range rng = ThisApplication.get_Range("B1", Type.Missing);
      rng.AutoFill(ThisApplication.get_Range("B1:B5", Type.Missing), Excel.XlAutoFillType.xlFillDays);

      rng = ThisApplication.get_Range("C1", Type.Missing);
      rng.AutoFill(ThisApplication.get_Range("C1:C5", Type.Missing), Excel.XlAutoFillType.xlFillMonths);

      rng = ThisApplication.get_Range("D1", Type.Missing);
      rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing), Excel.XlAutoFillType.xlFillYears);

      rng = ThisApplication.get_Range("E1:E2", Type.Missing);
      rng.AutoFill(ThisApplication.get_Range("E1:E5", Type.Missing), Excel.XlAutoFillType.xlFillSeries);
    }

    private int LastBoldedRow = 0;
    private void BoldCurrentRow(Excel.Worksheet ws) 
    {
      // Keep track of the previously bolded row.

      // Work with the current active cell.
      Excel.Range rngCell = ThisApplication.ActiveCell;

      // Bold the current row.
      rngCell.EntireRow.Font.Bold = true;

      // Make sure intRow isn't 0 (meaning that 
      // this is your first pass through here).
      if (LastBoldedRow != 0) 
      {
        // If you're on a different
        // row than the last time through here,
        // make the old row not bold.
        if (rngCell.Row != LastBoldedRow) 
        {
          Excel.Range rng = 
            (Excel.Range)ws.Rows[LastBoldedRow, Type.Missing];
          rng.Font.Bold = false;
        }
      }
      // Store away the new row number 
      // for next time.
      LastBoldedRow = rngCell.Row;
    }


    private void ClearDocumentProperties()
    {
      GetDocPropRange().ClearContents();
    }

    private void ClearGroups()
    {
      Excel.Worksheet ws =