thisworkbook.cs

强大的.net Excel控件

        (Excel.Worksheet) ThisWorkbook.Sheets["Worksheet Class"];

      // Specify RowLevels and/or ColumnLevels parameters:
      ws.Outline.ShowLevels(3, Type.Missing);

      Excel.Range rng = (Excel.Range) ws.Rows["24:27", Type.Missing];
      rng.Ungroup();

      ThisApplication.get_Range("Data2001", Type.Missing).Ungroup();
      ThisApplication.get_Range("Data2002", Type.Missing).Ungroup();
      ThisApplication.get_Range("AllData", Type.Missing).Ungroup();
    }

    private void ClearNameInformation()
    {
      ThisApplication.get_Range(
        "WorkbookName", Type.Missing).ClearContents();
      ThisApplication.get_Range(
        "WorkbookPath", Type.Missing).ClearContents(); 
      ThisApplication.get_Range(
        "WorkbookFullName", Type.Missing).ClearContents();
    }
	
    private void ClearNames()
    {
      Excel.Range rng;

      try
      {
        rng = ThisApplication.get_Range("Names", Type.Missing);

        ThisApplication.ScreenUpdating = false;

        Excel.Range rngCurrent = rng.CurrentRegion;
        rng = rng.get_Resize(rngCurrent.Rows.Count - 1, rngCurrent.Columns.Count);
        rng.Clear();
        rng.CurrentRegion.Columns.AutoFit();

        rng = ThisApplication.get_Range("NewName", Type.Missing);
        rng.Clear();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message, ThisApplication.Name);
      }
      finally
      {
        ThisApplication.ScreenUpdating = true;
      }
    }

    private void ClearSheetList()
    {
      Excel.Range rng = ThisApplication. 
        get_Range("ListSheets", Type.Missing).get_Offset(1, 0);
      Excel.Range rngEnd = 
        rng.get_End(Excel.XlDirection.xlDown);
      ThisApplication.get_Range(rng.Cells[1, 1], 
        rngEnd.Cells[1, 1]).ClearContents();
    }

    private void ClearStyle()
    {
      // Get the range containing all the document properties, and
      // clear the style
      GetDocPropRange().Style = "Normal";
    }

    private void DemoFind()
    {
      Excel.Range rng = ThisApplication.get_Range("Fruits", Type.Missing);
      Excel.Range rngFound;

      // Keep track of the first range you find.
      Excel.Range rngFoundFirst = null;

      // You should specify all these parameters
      // every time you call this method, since they
      // can be overriden in the user interface.
      rngFound = rng.Find("apples", Type.Missing, 
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, 
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 
        false, Type.Missing, Type.Missing);
      while (rngFound != null)
      {
        if (rngFoundFirst == null ) 
        {
          rngFoundFirst = rngFound;
        }
        else if (GetAddress(rngFound) == GetAddress(rngFoundFirst))
        {
          break;
        }
        rngFound.Font.Color = ColorTranslator.ToOle(Color.Red);
        rngFound.Font.Bold = true;
        rngFound = rng.FindNext(rngFound);
      }
    }

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

      rng.Sort(rng.Columns[1, Type.Missing], Excel.XlSortOrder.xlAscending, 
        rng.Columns[2, 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);
    }

    private void DisplayDocumentProperties()
    {
      Office.DocumentProperty prp = null;
      Office.DocumentProperties prps = 
        (Office.DocumentProperties) ThisWorkbook.BuiltinDocumentProperties;

      Excel.Range rng = ThisApplication.
        get_Range("DocumentProperties", Type.Missing);
      int i = 0;

      try 
      {
        ThisApplication.ScreenUpdating = false;

        try 
        {
          // Set the Revision Number property:
          prp = prps["Revision Number"];
          prp.Value = Convert.ToInt32(prp.Value) + 1;

          // Dump contents of the collection:
          i = DumpPropertyCollection(prps, rng, i);
        } 
        catch (Exception ex) 
        {
          MessageBox.Show(ex.Message, ThisApplication.Name);
        }

        // Work with custom properties:
        try 
        {
          prps = (Office.DocumentProperties)ThisWorkbook.CustomDocumentProperties;
          DumpPropertyCollection(prps, rng, i);
        } 
        catch (Exception ex) 
        {
          MessageBox.Show(ex.Message, ThisApplication.Name);
        }

        // Add a custom property:
        try 
        {
          // Delete the property, if it exists.
          prp = prps["Project Name"];
          prp.Delete();
        } 
        catch
        {
          // Do nothing if you get an exception.
        }
        try 
        {
          // Add a new property.
          prp = prps.Add("Project Name", false, 
            Office.MsoDocProperties.msoPropertyTypeString,         
            "White Papers", Type.Missing);
        } 
        catch (Exception ex) 
        {
          MessageBox.Show(ex.Message, ThisApplication.Name);
        }
      } 
      finally 
      {
        ThisApplication.ScreenUpdating = true;
      }
    }

    private int DumpPropertyCollection(Office.DocumentProperties prps, Excel.Range rng, int i) 
    {
      foreach (Office.DocumentProperty prp in prps)
      {
        rng.get_Offset(i, 0).Value2 = prp.Name;
        try 
        {
          if (prp.Value != null ) 
          {
            rng.get_Offset(i, 1).Value2 = 
              prp.Value.ToString();
          }
        } 
        catch
        {
          // Do nothing at all.
        }
        i += 1;
      }
      return i;
    }

    private String GetAddress(Excel.Range rng)
    {
      // Simple wrapper around get_Address method:
      return rng.get_Address(Type.Missing, Type.Missing, 
        Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
    }

    private Excel.Range GetDocPropRange()
    {
      Excel.Range rng = 
        ThisApplication.get_Range("DocumentProperties", Type.Missing);
      Excel.Range rngStart = 
        (Excel.Range) rng.Cells[1, 1];
      Excel.Range rngEnd = 
        rng.get_End(Excel.XlDirection.xlDown).get_Offset(0, 1);
      return ThisApplication.get_Range(rngStart, rngEnd);
    }

    private void GetNameInformation()
    {
      ThisApplication.get_Range("WorkbookName", Type.Missing).
        Value2 = ThisWorkbook.Name;
      ThisApplication.get_Range("WorkbookPath", Type.Missing).
        Value2 = ThisWorkbook.Path;
      ThisApplication.get_Range("WorkbookFullName", Type.Missing).
        Value2 = ThisWorkbook.FullName;
    }

    private void HideComments()
    {
      ShowOrHideComments(false);
    }

    private void InitializeData() 
    {
      try 
      {
        ThisApplication.ScreenUpdating = false;
        ThisApplication.get_Range("DefaultSaveFormat", Type.Missing).
          Value2 = ThisApplication.DefaultSaveFormat.ToString();
        ThisApplication.get_Range("DefaultFilePath", Type.Missing).
          Value2 = ThisApplication.DefaultFilePath;

        ResetRecentFiles();
        ResetAutoFill();
        ResetBoldSelectedRow();
        ResetFind();
        UnprotectSheet();
        ShowOrHideComments(false);
        ClearDocumentProperties();
        ClearStyle();
        ClearSheetList();
        ClearNameInformation();
        ClearNames();
      } 
      catch (Exception ex) 
      {
        MessageBox.Show(ex.Message, ThisApplication.Name);
      } 
      finally 
      {
        ThisApplication.ScreenUpdating = true;
      }
    }

    private void ListRecentFiles()
    {
      Excel.Range rng = (Excel.Range)ThisApplication.
        get_Range("RecentFiles", Type.Missing).Cells[1, 1];

      for (int i = 1; i <= ThisApplication.RecentFiles.Count; i++)
      {
        rng.get_Offset(i - 1, 0).Value2 = 
          ThisApplication.RecentFiles[i].Name;
      } 
    }

    private void ListSheets()
    {
      int i = 0;

      Excel.Range rng = 
        ThisApplication.get_Range("Sheets", Type.Missing);
      foreach (Excel.Worksheet sh in ThisWorkbook.Sheets)
      {
        rng.get_Offset(i, 0).Value2 = sh.Name;
        i = i + 1;
      }
    } 

    private void ProtectSheet()
    {     
      Excel.Worksheet ws = (Excel.Worksheet)ThisApplication.ActiveSheet;

      Excel.AllowEditRanges ranges = ws.Protection.AllowEditRanges;
      ranges.Add("Information", ThisApplication.get_Range("Information", Type.Missing), Type.Missing);
      ranges.Add("Date", ThisApplication.get_Range("Date", Type.Missing), Type.Missing);
      // This is ugly! We want optional parameters!
      ws.Protect(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 ResetAutoFill()
    {
      ThisApplication.get_Range(
        "AutoFill1", Type.Missing).ClearContents(); 
      ThisApplication.get_Range(
        "AutoFill2", Type.Missing).ClearContents();
    }

    private void ResetBoldSelectedRow()
    {
      Excel.Range rng = ThisApplication.get_Range(
        "BoldSelectedRow", Type.Missing);
      rng.Font.Bold = false;
    }

    private void ResetFind()
    {
      Excel.Range rng = ThisApplication.
        get_Range("Fruits", Type.Missing);
      rng.Font.Color = ColorTranslator.ToOle(Color.Black);
      rng.Font.Bold = false;
    }

    private void ResetRecentFiles()
    {
      ThisApplication.get_Range("RecentFiles", Type.Missing).ClearContents();
    }

    private void ResetSort()
    {
      Excel.Range rng = ThisApplication.
        get_Range("Fruits", Type.Missing);
      rng.Sort(rng.Columns[2, Type.Missing],
        Excel.XlSortOrder.xlAscending, 
        Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending,
        Type.Missing, Excel.XlSortOrder.xlAscending,
        Excel.XlYesNoGuess.xlNo, 6, Type.Missing,
        Excel.XlSortOrientation.xlSortColumns, 
        Excel.XlSortMethod.xlPinYin,
        Excel.XlSortDataOption.xlSortNormal, 
        Excel.XlSortDataOption.xlSortNormal,
        Excel.XlSortDataOption.xlSortNormal);
    }
    
    private void ResetWindows()
    {
      for (int i = ThisApplication.Windows.Count; i >= 2; i--)