Category: Office Development

private bool MoveRow(Excel.Range rngRowToMove, int moveRowNum)
{
    // get reference to the sheet
    Excel.Application xlApp = rngRowToMove.Application;
    Excel.Worksheet ws = (Excel.Worksheet)rngRowToMove.Application.ActiveSheet;
    int rowToDelete = rngRowToMove.Row; // and the original row number

    // verify that the moveRowNum is further down (below/higher number) than
    // the rngRowToMove location. If it is not then we fail…
    if (moveRowNum <= rowToDelete)
        return false;

    try
    {
        // now grab the row where we want to move to
        Excel.Range rngMoveTo = ((Excel.Range)rngRowToMove.Application.Cells[moveRowNum, 1]).EntireRow;
        // insert a row so as to not delete the data already there
        // but only do it if it is not the last row in the sheet
        if(moveRowNum < ws.UsedRange.Rows.Count)
            rngMoveTo.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
        // and move…
        rngRowToMove.EntireRow.Cut(rngMoveTo);
        // now go to delete the empty row
        ((Excel.Range)xlApp.Cells[rowToDelete, 1]).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        return true;
    }
    catch
    {
        return false;
    }
}

MoveRow(xlApp.ActiveCell, ((Excel.Worksheet)xlApp.ActiveSheet).UsedRange.Rows.Count + 1)

void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook Wb)
{
    // check to see if the document is part of documet management system
    if (isManagedDocument())
        Wb.EnableAutoRecover = false; // disable AutoRecovery just for this wb
}

if (Wb.IsInplace)
{
    // do something here
}

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    // hook to events
    Application.WorkbookOpen += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(Application_WorkbookOpen);
    Application.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
}

string wbOpenCaption = "";
void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook Wb)
{
    // get the captions name because iof this is an embedded workbook
    // the name will have changed by the time we get to the Activate
    // event and that is a sure sign of an embdedded state
    wbOpenCaption = Wb.Windows[1].Caption.ToString();
}

void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
{
    // on activate we see if the OPEN document caption has changed.
    // When the user has opened a workbook enbedded anywhere, the intial
    // caption for the window will be something like "Book2" in the
    // Workbook_Open event. However, by the time we get the Activate
    // event the caption will be updated to "Workbook in Document.docx"
    // So here we are looking for that condition along with the
    // condition where the workbook has no path. This assures us we
    // have an embedded document.
    //
    // In scenarios where a new workbook is just now embeeded into a
    // document the Caption for the window will be "Object" also in
    // those scenarios the Open event is not called.
    string wbActivateCaption = Wb.Windows[1].Caption.ToString();
    if (wbOpenCaption.Length > 0 &&
        wbOpenCaption != wbActivateCaption &&
       Wb.Path.Length == 0)
    {
        MessageBox.Show("This is an embedded workbook.");
    }
    else if (wbOpenCaption.Length == 0 &&
            wbActivateCaption.ToLower() == "object")
    {
        MessageBox.Show("New embedded workbook.");
    }
    wbOpenCaption = ""; // reset
}

[GuidAttribute(“D94AF1AD-7E2A-4611-AA6F-47351FF46ACD”)]
public interface IFunctions
{
string UDF_Replace(object a);   // new interface
string UDF(object a, object b); // legacy interface
}///<summary>
/// This is how COM will see the class for the functions
/// using the interface defined above. This has to use
/// the IDTExtensibility interface as well. You cannot
/// use VSTO as it short-circuts the extensibility for
/// you automatically and there is no way to register
/// it to be seen in this way…
///</summary>
[GuidAttribute(“1A43DEAA-EE1D-4e0d-8CC1-79B3998B7CEB”),
ProgId(“ExcelFunctionsExample.Connect”),
ClassInterface(ClassInterfaceType.AutoDual)]
[ComDefaultInterface(typeof(IFunctions))]
public class Connect : Object, Extensibility.IDTExtensibility2, IFunctions
{
public Connect() { } // constructor
// These 6 methods are required for the IDTExtensibility2 interface
public void OnBeginShutdown(ref System.Array a) { }
public void OnStartupComplete(ref System.Array a) { }
public void OnAddInsUpdate(ref System.Array a) { }
public void OnDisconnection(Extensibility.ext_DisconnectMode e,
ref System.Array a) { }
public void OnConnection(object application,
Extensibility.ext_ConnectMode e,
object oo, ref System.Array a) { }
// These next three methods are registering the DLL with COM
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);
}
private static string GetSubKeyName(Type type)
{
string s = @”CLSID\{“ + type.GUID.ToString().ToUpper() +
@”}\Programmable”;
return s;
}

///<summary>
/// This function is the new user defined function
///</summary>
///<param name=”a”></param>
///<returns></returns>
public string UDF_Replace(object a)
{
// >> Do your work here <<
return a.ToString();
}

///<summary>
/// This function is the legacy user defined function
///</summary>
///<param name=”a”></param>
///<param name=”b”></param>
///<returns></returns>
public string UDF(object a, object b)
{
// get reference to the cell where the function
// is being evaluated
Excel.Range thisRange = (Excel.Range)
((Excel.Range)a).Application.get_Caller(1);
// get the value in the cell…
string ret = thisRange.Value2.ToString();
// You cannot change a formula while you are inside the
// evaluation of that formula. But you can spawn off a
// thread so that this will get processed after this
// function exits…
new Thread(new ThreadStart(delegate
{
// replace the formula
thisRange.FormulaR1C1Local = “=UDF_Replace(“ + ret + “)”;
})).Start();
return ret; // return
}
}

// get reference to the Excel application from the ThisAddIn
Excel.Application xlApp = Globals.ThisAddIn.Application;
Tools.Workbook wb = xlApp.ActiveWorkbook.GetVstoObject();
// get the VSTO tools worksheet object
Tools.Worksheet ws = ((Excel.Worksheet)wb.ActiveSheet).GetVstoObject();

// Create a button
System.Windows.Forms.Button btn = new System.Windows.Forms.Button();
btn.Text = "X";
// create an event handler for click
// NOTE: This is an inline delegate
btn.Click += ((pSender, pE) => MessageBox.Show("Hello World"));
// create an event handler for the mouse enter event that
// will pop up a ToolTip.
btn.MouseEnter += ((pSender, pE) =>
{
    // create a tooltip on the fly and attach it to the
    // button so that when the user hovers over it, it shows
    System.Windows.Forms.ToolTip tt = new ToolTip();
    tt.ToolTipIcon = ToolTipIcon.Info;
    tt.SetToolTip(btn, "Hello World!");
});
// Now build a user control, add the button to it and then
// add the control to the form
UserControl uc = new UserControl();
btn.Dock = DockStyle.Fill;
uc.Controls.Add(btn);
// NOTE: When we add the control to the form we need to specify
// the name of the control and to prevent a conflict we name
// the control as a GUID
ws.Controls.AddControl(uc, (Excel.Range)xlApp.Selection,
    Guid.NewGuid().ToString());

btn.Click += ((pSender, pE) => MessageBox.Show("Hello World"));

btn.Click += new EventHandler(btn_Click);
btn.Click += delegate(object pSender, EventArgs pE) { MessageBox.Show("Hellow World!"); };
btn.Click += ((pSender, pE) => MessageBox.Show("Hello World"));

private void ThisAddIn_Startup(object sender, EventArgs e)
{
    // hook to the Before Save Event
    Application.WorkbookBeforeClose += new
        Excel.AppEvents_WorkbookBeforeCloseEventHandler(
            Application_WorkbookBeforeClose);
}

/// <summary>
/// The BeforeClose event we attach to. The only event
/// exposed by Excel to us to determin that the file is
/// about to close
/// </summary>
/// <param name="Wb">The Workbook object being closed</param>
/// <param name="Cancel">If true, closing is stopped</param>
void Application_WorkbookBeforeClose(Excel.Workbook Wb,
                                     ref bool Cancel)
{
    // because this event fires before the close we will
    // do whatever work we need to do here, such as
    // prompting the user, pre-work or other items.

    // <– your code here –>
    // be sure if needed to set Cancel=true here if
    // needed…
    // <– your code here –>

    // now we check to see if cancelled…
    if (Cancel == false)
    {
        // ok – not cancelled – so we spawn a thread
        // but first…
        // we cannot use the Wb.FullName inside the delegate
        // because it will be destroyed by the time the thread
        // fires off. So we put it into a variable and pass
        // that value with the delegate.
        string wbFullPath = Wb.FullName;
        // start new inline thread
        new Thread(new ThreadStart(delegate()
        {
            // we will call back to the Handle_AfterClose
            // method and pass it the name of the workbook.
            Handle_AfterClose(wbFullPath);
            // NOTE: Because Excel takes the next action to
            // close the file, this threads immediate call
            // back into the add-in will not occur until
            // Excel has compelted the operation.
        })).Start();
    }
}

/// <summary>
/// This is the callback method ("event") we use to determine
/// the point after which our file has been closed.
/// </summary>
/// <param name="fn">The full path and name of the file
///                  closed</param>
private void Handle_AfterClose(string fn)
{
    // display a simple message at this point
    MessageBox.Show(fn + " has been closed!");
}

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Diagnostics;

private Excel.Workbook openFile(string fn)
{
    // bind to the path… if the document is not open
    // then the Marshal will open an new instance of Excel or
    // open in the current active instance of Excel
    Excel.Workbook wb = (Excel.Workbook)Marshal.BindToMoniker(fn);
    if (wb == null)
    {
        // we need to start a new instance of Excel
        // with the filename passed to it
        Process p = new Process();
        p.StartInfo.FileName = "excel.exe";
        p.StartInfo.Arguments = fn; // send the filename to excel
        p.Start(); // start an instance of Excel first
        p.WaitForInputIdle();  // wait for the process to be ready
        // now bind to minker again to connect to the instance
        // of the file that we just opened
        wb = (Excel.Workbook)Marshal.BindToMoniker(fn);
    }

    // show the application – just in case
    wb.Application.Visible = true;
    // verify the window is shown
    wb.Windows[1].Visible = true;
    // if minimized – restore the window
    if (wb.Application.WindowState == Excel.XlWindowState.xlMinimized)
        wb.Application.WindowState = Excel.XlWindowState.xlNormal;
    // set the applcation to the foreground
    SetForegroundWindow(wb.Application.Hwnd);
    // then activate the workbook
    wb.Activate();
    return wb; // done
}

private void openAnExcelFile()
{
    string fn = @"c:\users\davidcr\desktop\test.xlsx";
    if (File.Exists(fn))
    {
        Excel.Workbook wb = openFile(fn);
        MessageBox.Show(wb.Name + " has been opened.");
    }
}