thisworkbook.cs

强大的.net Excel控件

        ThisApplication.Windows[i].Close(
          false, Type.Missing, Type.Missing);
      ThisApplication.Windows[1].WindowState = 
        Excel.XlWindowState.xlMaximized;
    }
    	
    private void SetDefaultFilePath()
    {
      ThisApplication.DefaultFilePath = 
        ThisApplication.get_Range("DefaultFilePath", Type.Missing).
        Value2.ToString();
    }

    private void SetDefaultSaveFormat()
    {
      // Retrieve the name of the new save format,
      // as a string:
      string strSaveFormat = ThisApplication.
        get_Range("DefaultSaveFormat", Type.Missing).
        Value2.ToString();

      // Look up the corresponding enumerated value, in the 
      // column next to the column containing the list of 
      // enumerated value names:
      if (strSaveFormat != String.Empty ) 
      {
        Excel.Range rng = ThisApplication.get_Range("xlFileFormat", Type.Missing);
        Excel.Range rngFind = rng.Find(strSaveFormat, 
          Type.Missing, Type.Missing, Type.Missing, 
          Type.Missing, Excel.XlSearchDirection.xlNext, 
          Type.Missing, Type.Missing, Type.Missing);
        int intSaveFormat = Convert.ToInt32(rngFind.get_Offset(0, 1).Value2);
        ThisApplication.DefaultSaveFormat = (Excel.XlFileFormat) intSaveFormat;
      }
    }

    private void SetPassword()
    {
      Password frm = new Password();

      if (frm.ShowDialog() == DialogResult.OK)
        ThisWorkbook.Password = frm.Value;
      frm.Dispose();
    }

    private void ShowOrHideComments(bool show)
    {
      // Show or hide all the comments:
      Excel.Worksheet ws = 
        (Excel.Worksheet) ThisApplication.Sheets["Worksheet Class"];

      for (int i = 1; i <= ws.Comments.Count; i++)
      {
        ws.Comments[i].Visible = show;
      }
    }

    private void TestEvaluate()
    {
      Excel.Range rng = ThisApplication.
        get_Range("Evaluate", Type.Missing);

      try 
      {
        Excel.Range rngNew = 
          (Excel.Range) ThisApplication.Evaluate(
          rng.get_Offset(0, 1).Value2);
        rngNew.Value2 = "Hello, World!";
      } 
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message, ThisApplication.Name);
      }
    }

    private void TestEmail()
    {
      if (ThisApplication.MailSystem == 
        Excel.XlMailSystem.xlMAPI ) 
      {
        if ( ThisApplication.MailSession == null ) 
        {
          SendMail frm = new SendMail();
          if (frm.ShowDialog() == DialogResult.OK )
          {
            ThisApplication.MailLogon(frm.EmailName, 
              frm.EmailPassword, frm.DownloadNewMail);
          }
        }
        string  strEmail = ThisApplication.
          get_Range("SendMail", Type.Missing).
          get_Offset(0, 1).Value2.ToString();
        ThisWorkbook.SendMail(strEmail, 
          "Sample Excel Email", Type.Missing);
        ThisApplication.MailLogoff();
      } 
      else 
      {
        MessageBox.Show("This demonstration works only if MAPI is installed.");
      }
    }

    private void TestFileDialog(String DialogName)
    {
      Office.FileDialog dlg;
      switch (DialogName)
      {
        case "FilePicker":
          dlg = ThisApplication.get_FileDialog(
            Office.MsoFileDialogType.msoFileDialogFilePicker);
          dlg.AllowMultiSelect = false;
          dlg.Filters.Clear();
          dlg.Filters.Add("Excel Files", "*.xls;*.xlw", Type.Missing);
          dlg.Filters.Add("All Files", "*.*", Type.Missing);
          if(dlg.Show() != 0)
          {
            ThisApplication.get_Range("FilePickerResults", Type.Missing).
              Value2 = dlg.SelectedItems.Item(1);
          }
          break;

        case "FolderPicker":
          dlg = ThisApplication.get_FileDialog(
            Office.MsoFileDialogType.msoFileDialogFolderPicker);
          if (dlg.Show() != 0)
          {
            ThisApplication.get_Range("FolderPickerResults", Type.Missing).
              Value2 = dlg.SelectedItems.Item(1);
          }
          break;
					
        case "Open":
          dlg = ThisApplication.get_FileDialog(
            Office.MsoFileDialogType.msoFileDialogOpen);
          dlg.Filters.Clear();
          dlg.Filters.Add("Excel Files", "*.xls;*.xlw", Type.Missing);
          dlg.Filters.Add("All Files", "*.*", Type.Missing);
          if(dlg.Show() != 0)
            dlg.Execute();
          break;

        case "SaveAs":
          dlg = ThisApplication.get_FileDialog(
            Office.MsoFileDialogType.msoFileDialogSaveAs);
          if (dlg.Show() != 0)
            dlg.Execute();
          break;
      }
    }

    private void TestNames()
    {
      Excel.Name nm;
      try 
      {
        // Turn off screen updating.
        ThisApplication.ScreenUpdating = false;

        Excel.Range rng = ThisApplication.get_Range("Names", Type.Missing);
        for ( int i = 0 ; i <= ThisApplication.Names.Count - 1; i++)
        {
          nm = ThisApplication.Names.Item(i + 1, Type.Missing, Type.Missing);
          rng.get_Offset(i, 0).Value2 = nm.Name;
          // Without the leading "'", these references
          // get evaluated, rather than displayed directly.
          rng.get_Offset(i, 1).Value2 = "'" + nm.RefersTo.ToString();
          rng.get_Offset(i, 2).Value2 = "'" + nm.RefersToR1C1.ToString();
          rng.get_Offset(i, 3).Value2 = nm.Value;
        }
        rng.CurrentRegion.Columns.AutoFit();

        nm = ThisApplication.Names.Add(
          "NewName", @"='Other Application Members'!$A$6", 
          Type.Missing, Type.Missing, Type.Missing, 
          Type.Missing, Type.Missing, Type.Missing, 
          Type.Missing, Type.Missing, Type.Missing);
        ThisApplication.get_Range(
          "NewName", Type.Missing).Value2 = "Hello, World!";
      } 
      catch (Exception ex) 
      {
        MessageBox.Show(ex.Message, ThisApplication.Name);
      } 
      finally 
      {
        // Turn on screen updating.
        ThisApplication.ScreenUpdating = true;
      }

    }

    private void TestPrecisionAsDisplayed(bool IsPrecisionAsDisplayedOn)
    {
      ThisWorkbook.PrecisionAsDisplayed = 
        IsPrecisionAsDisplayedOn;
    }
	
    private void TestSpelling()
    {
      Excel.Range rng = ThisApplication.
        get_Range("CheckSpelling", Type.Missing);

      rng.get_Offset(0, 2).Value2 = 
        (ThisApplication.CheckSpelling(
        rng.get_Offset(0, 1).Value2.ToString(), Type.Missing, Type.Missing) 
        ? "Spelled correctly" 
        : "Spelled incorrectly");
    }

    private void TestWindows()
    {
      ThisWorkbook.NewWindow();
		
      Excel.Window wnd = ThisWorkbook.NewWindow();
      wnd.Caption = "New Window";
      wnd.Activate();

      ThisApplication.Windows.Arrange( 
        Excel.XlArrangeStyle.xlArrangeStyleTiled, 
        Type.Missing, Type.Missing, Type.Missing);
      wnd = ThisApplication.Windows[3];
      wnd.GridlineColor = ColorTranslator.ToOle(Color.Red);
      wnd.Caption = "A New Window";
      wnd.DisplayHeadings = false;
      wnd.DisplayFormulas = false;
      wnd.DisplayWorkbookTabs = false;
      wnd.SplitColumn = 1;
    }

    private void TestWorksheetFunction() 
    {
      Excel.Worksheet ws = (Excel.Worksheet) ThisWorkbook.ActiveSheet;
      Excel.Range rng = ws.get_Range("RandomNumbers", Type.Missing);
      System.Random rnd = new System.Random();

      for ( int i = 1 ; i <= 20; i++)
        ws.Cells[i, 2] = rnd.Next(100);
    
      rng.Sort(rng, Excel.XlSortOrder.xlAscending, 
        Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, 
        Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlNo, 
        Type.Missing,Type.Missing, Excel.XlSortOrientation.xlSortColumns, 
        Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, 
        Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);

      Excel.WorksheetFunction wsf = ThisApplication.WorksheetFunction;
      ws.get_Range("Min", Type.Missing).Value2 = wsf.Min(rng, 
        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);
      ws.get_Range("Max", Type.Missing).Value2 = wsf.Max(rng, 
        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);
      ws.get_Range("Median", Type.Missing).Value2 = wsf.Median(rng,
        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);
      ws.get_Range("Average", Type.Missing).Value2 = wsf.Average(rng, 
        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);
      ws.get_Range("StDev", Type.Missing).Value2 = wsf.StDev(rng, 
        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);
    }

    private void UnprotectSheet()
    {
      Excel.Worksheet ws = 
        (Excel.Worksheet) ThisApplication.Sheets["Worksheet Class"];
      ws.Unprotect(Type.Missing);

      // Delete all protection ranges, just to clean up.
      // You must loop through this using the index, 
      // backwards. This collection doesn't provide 
      // an enumeration method, and it doesn't handle
      // being resized as you're looping in a nice way.
      Excel.AllowEditRanges ranges = ws.Protection.AllowEditRanges;

      for (int i = ranges.Count; i >= 1; i--)
      {
        ranges[i].Delete();
      }
    }

    private void WorkWithComments()
    {
      // Add a new comment:
      Excel.Range rng = ThisApplication.get_Range("Date", Type.Missing);
      if (rng.Comment != null ) 
      {
        rng.Comment.Delete();
      }
      rng.AddComment("Comment added " + DateTime.Now);

      // Display all the comments:
      ShowOrHideComments(true);
    }
    private void WorkWithGroups()
    {
      Excel.Worksheet ws = 
        (Excel.Worksheet) ThisApplication.ActiveSheet;

      // Set worksheet-level features for the outline.
      // In this case, summary rows are below
      // the data rows (so Excel knows where to put
      // the summary rows), and we don't want Excel
      // to format the summary rows--that's already been done.
      ws.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryBelow;
      ws.Outline.AutomaticStyles = false;

      // Group the two named ranges. Each of these
      // ranges extends across entire rows.
      ThisApplication.get_Range("Data2001", Type.Missing).
        Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      ThisApplication.get_Range("Data2002", Type.Missing).
        Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      ThisApplication.get_Range("AllData", Type.Missing).
        Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

      // The range of rows from 24 to 27 doesn't have 
      // a named range, so you can work with that 
      // range directly.
      Excel.Range rng = (Excel.Range)ws.Rows["24:27", Type.Missing];
      rng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

      // Collapse to the second group level.
      ws.Outline.ShowLevels(2, Type.Missing);
    }

  }
}