Converting Excel Calculations to C# (or VB.NET) code and assemblie
 

Hi
I'm not sure whether this is the right forum for this question, but I was
wondering whether anyone knew of a tool that could take an Excel spreadsheet
and export all of its calculations to a source code format (preferrably C#)
so that these calculations could be built into an assembly for use directly
within a Windows Forms application.

I have looked at KDCalc, but I'm not sure whether it does what we need.

Thanks for any help anyone can provide.

Marek

Converting Excel Calculations to C# (or VB.NET) code and assemblie
 

Hello Marek,

Microsoft did not provide such kind of tool to export the calculations to
assemblies.

I think you need to consider some third-party software.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Converting Excel Calculations to C# (or VB.NET) code and assem
 

Hi Wei Lu
Thanks for getting back to me. As an alternative approach, I remember in
the days of the (C/C++) Excel SDK you could hook into each cell calculation
call - is that possible using the current set of Excel tools (VBA or VSTO)?

Cheers

Marek

"Wei Lu [MSFT]" wrote:

Hello Marek,

Microsoft did not provide such kind of tool to export the calculations to
assemblies.

I think you need to consider some third-party software.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Converting Excel Calculations to C# (or VB.NET) code and assem
 

Hello Marek,

I did not have a sample but in VSTO you could use the hook.

And here is a sample which use the hook to sub-classes the Excel window and
handles the Paste event. You may modify it to hook the calculation and use
it in VSTO.

This is the Hook.cs class that sub-classes the main Excel window using
SetWindowLong

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Reflection;

namespace SampleXLS
{
class Hook
{
private const int WM_KEYDOWN = 0x0100;

[DllImport("user32.dll")]
private static extern IntPtr FindWindowEx(IntPtr hParent, IntPtr hChild,
string sClass, string sWindow);

[DllImport("user32.dll")]
private static extern bool GetKeyState(int nVirtKey );

public delegate int WndProcDelegate(IntPtr hwnd, uint msg, uint wParam, int
lParam);
public static WndProcDelegate wpd;
public static IntPtr lpPrevWndProc;
public bool IsHooked;
public IntPtr gHW;

public void SetHook(int hWnd)
{
try
{
if (IsHooked)
MessageBox.Show("Don't hook it twice without unhooking, or you
will be unable to unhook it.");
else
{
gHW = new IntPtr(hWnd);
gHW = FindWindowEx(gHW, IntPtr.Zero, "XLDESK", string.Empty);

gHW = FindWindowEx(gHW, IntPtr.Zero, "EXCEL7",
"SampleXLS.xls");

wpd = new WndProcDelegate(WndProcSub);
lpPrevWndProc = Win32.SetWindowLong(gHW, -4,
Marshal.GetFunctionPointerForDelegate(wpd));
//Marshal.GetFunctionPointerForDelegate(m_delegate)) ;
IsHooked = true;

}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}

}

public void SetUnhook()
{
IntPtr temp;
temp = Win32.SetWindowLong(gHW, -4, lpPrevWndProc);
IsHooked = false;
}

private int WndProcSub(IntPtr hWnd, uint msg, uint wParam, int lParam)
{
uint vkCode = wParam;
if(GetKeyState((int)Win32.VK_CONTROL))
{
if(vkCode == Win32.VK_V)
Debug.Print("Paste Called");
}

return Win32.CallWindowProc(lpPrevWndProc, hWnd, msg, wParam,
lParam);
}
}
}

This is the code in VSTO where I call the SetHook and SetUnHook methods.

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
WorkbookSheetActivate();
oControl = Application.CommandBars["Worksheet Menu
Bar"].Controls["Edit"];
oControls =
(Microsoft.Office.Core.CommandBarControls)oControl .GetType().InvokeMember("C
ontrols"
, System.Reflection.BindingFlags.GetProperty, null, oControl, null);
oButton = (Microsoft.Office.Core.CommandBarButton)oControls["Paste"];
oButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler(oButton_Clic
k);

int i = Application.Hwnd;
objHook.SetHook(i);

}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
objHook.SetUnhook();
}

Hope this helps.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Converting Excel Calculations to C# (or VB.NET) code and assem
 

Whilst I'm familiar with the hooks, what do you (both) mean by "the
calculation" or "cell calculation call" ?
What do you look for in the WndProcSub ?

NickHK

"Wei Lu [MSFT]" wrote in message
...
Hello Marek,

I did not have a sample but in VSTO you could use the hook.

And here is a sample which use the hook to sub-classes the Excel window
and
handles the Paste event. You may modify it to hook the calculation and use
it in VSTO.

This is the Hook.cs class that sub-classes the main Excel window using
SetWindowLong

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Reflection;

namespace SampleXLS
{
class Hook
{
private const int WM_KEYDOWN = 0x0100;

[DllImport("user32.dll")]
private static extern IntPtr FindWindowEx(IntPtr hParent, IntPtr hChild,
string sClass, string sWindow);

[DllImport("user32.dll")]
private static extern bool GetKeyState(int nVirtKey );

public delegate int WndProcDelegate(IntPtr hwnd, uint msg, uint wParam,
int
lParam);
public static WndProcDelegate wpd;
public static IntPtr lpPrevWndProc;
public bool IsHooked;
public IntPtr gHW;

public void SetHook(int hWnd)
{
try
{
if (IsHooked)
MessageBox.Show("Don't hook it twice without unhooking, or you
will be unable to unhook it.");
else
{
gHW = new IntPtr(hWnd);
gHW = FindWindowEx(gHW, IntPtr.Zero, "XLDESK", string.Empty);

gHW = FindWindowEx(gHW, IntPtr.Zero, "EXCEL7",
"SampleXLS.xls");

wpd = new WndProcDelegate(WndProcSub);
lpPrevWndProc = Win32.SetWindowLong(gHW, -4,
Marshal.GetFunctionPointerForDelegate(wpd));
//Marshal.GetFunctionPointerForDelegate(m_delegate)) ;
IsHooked = true;

}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}

}

public void SetUnhook()
{
IntPtr temp;
temp = Win32.SetWindowLong(gHW, -4, lpPrevWndProc);
IsHooked = false;
}

private int WndProcSub(IntPtr hWnd, uint msg, uint wParam, int lParam)
{
uint vkCode = wParam;
if(GetKeyState((int)Win32.VK_CONTROL))
{
if(vkCode == Win32.VK_V)
Debug.Print("Paste Called");
}

return Win32.CallWindowProc(lpPrevWndProc, hWnd, msg, wParam,
lParam);
}
}
}

This is the code in VSTO where I call the SetHook and SetUnHook methods.

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
WorkbookSheetActivate();
oControl = Application.CommandBars["Worksheet Menu
Bar"].Controls["Edit"];
oControls =

(Microsoft.Office.Core.CommandBarControls)oControl .GetType().InvokeMember("C
ontrols"
, System.Reflection.BindingFlags.GetProperty, null, oControl, null);
oButton = (Microsoft.Office.Core.CommandBarButton)oControls["Paste"];
oButton.Click += new

Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler(oButton_Clic
k);

int i = Application.Hwnd;
objHook.SetHook(i);

}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
objHook.SetUnhook();
}

Hope this helps.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Converting Excel Calculations to C# (or VB.NET) code and assem
 

Hi Nick
I'm very much in the exploratory stage of all this, but my end goal is to be
able to take spreadsheets written by our users and to convert the
calculations found in them to C# code that we can then compile and make
available in our main applications.

Unless I can find a third party tool that does this, I need to get Excel to
tell me what the order of the calculations is along with what each
calculation does, so that for example I could convert '=A1*A2' in cell A3
into

double a3 = CalcValue(a1, a2);

where CalcValue looks something like:

public double CalcValue(double a1, double a2)
{
double a3 = a1 * a2;
return a3;
}

I appreaciate that this is a very simple example and that there will be lots
of issues around the use of Excel functions (such as MIN, MAX, FLOOR, IF
etc.) and that is why I was hoping for a third party control. I guess life
is never that simple though!

I hope this makes sense.

Best regards

Marek
"NickHK" wrote:

Whilst I'm familiar with the hooks, what do you (both) mean by "the
calculation" or "cell calculation call" ?
What do you look for in the WndProcSub ?

NickHK

"Wei Lu [MSFT]" wrote in message
...
Hello Marek,

I did not have a sample but in VSTO you could use the hook.

And here is a sample which use the hook to sub-classes the Excel window
and
handles the Paste event. You may modify it to hook the calculation and use
it in VSTO.

This is the Hook.cs class that sub-classes the main Excel window using
SetWindowLong

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Reflection;

namespace SampleXLS
{
class Hook
{
private const int WM_KEYDOWN = 0x0100;

[DllImport("user32.dll")]
private static extern IntPtr FindWindowEx(IntPtr hParent, IntPtr hChild,
string sClass, string sWindow);

[DllImport("user32.dll")]
private static extern bool GetKeyState(int nVirtKey );

public delegate int WndProcDelegate(IntPtr hwnd, uint msg, uint wParam,
int
lParam);
public static WndProcDelegate wpd;
public static IntPtr lpPrevWndProc;
public bool IsHooked;
public IntPtr gHW;

public void SetHook(int hWnd)
{
try
{
if (IsHooked)
MessageBox.Show("Don't hook it twice without unhooking, or you
will be unable to unhook it.");
else
{
gHW = new IntPtr(hWnd);
gHW = FindWindowEx(gHW, IntPtr.Zero, "XLDESK", string.Empty);

gHW = FindWindowEx(gHW, IntPtr.Zero, "EXCEL7",
"SampleXLS.xls");

wpd = new WndProcDelegate(WndProcSub);
lpPrevWndProc = Win32.SetWindowLong(gHW, -4,
Marshal.GetFunctionPointerForDelegate(wpd));
//Marshal.GetFunctionPointerForDelegate(m_delegate)) ;
IsHooked = true;

}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}

}

public void SetUnhook()
{
IntPtr temp;
temp = Win32.SetWindowLong(gHW, -4, lpPrevWndProc);
IsHooked = false;
}

private int WndProcSub(IntPtr hWnd, uint msg, uint wParam, int lParam)
{
uint vkCode = wParam;
if(GetKeyState((int)Win32.VK_CONTROL))
{
if(vkCode == Win32.VK_V)
Debug.Print("Paste Called");
}

return Win32.CallWindowProc(lpPrevWndProc, hWnd, msg, wParam,
lParam);
}
}
}

This is the code in VSTO where I call the SetHook and SetUnHook methods.

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
WorkbookSheetActivate();
oControl = Application.CommandBars["Worksheet Menu
Bar"].Controls["Edit"];
oControls =

(Microsoft.Office.Core.CommandBarControls)oControl .GetType().InvokeMember("C
ontrols"
, System.Reflection.BindingFlags.GetProperty, null, oControl, null);
oButton = (Microsoft.Office.Core.CommandBarButton)oControls["Paste"];
oButton.Click += new

Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler(oButton_Clic
k);

int i = Application.Hwnd;
objHook.SetHook(i);

}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
objHook.SetUnhook();
}

Hope this helps.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Converting Excel Calculations to C# (or VB.NET) code and assem
 

Hi Wei Lu
Thanks very much for that - I will look into it. I've been wanting to get
into VSTO and this looks like my chance.

Best regards

Marek

"Wei Lu [MSFT]" wrote:

Hello Marek,

I did not have a sample but in VSTO you could use the hook.

And here is a sample which use the hook to sub-classes the Excel window and
handles the Paste event. You may modify it to hook the calculation and use
it in VSTO.

This is the Hook.cs class that sub-classes the main Excel window using
SetWindowLong

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Reflection;

namespace SampleXLS
{
class Hook
{
private const int WM_KEYDOWN = 0x0100;

[DllImport("user32.dll")]
private static extern IntPtr FindWindowEx(IntPtr hParent, IntPtr hChild,
string sClass, string sWindow);

[DllImport("user32.dll")]
private static extern bool GetKeyState(int nVirtKey );

public delegate int WndProcDelegate(IntPtr hwnd, uint msg, uint wParam, int
lParam);
public static WndProcDelegate wpd;
public static IntPtr lpPrevWndProc;
public bool IsHooked;
public IntPtr gHW;

public void SetHook(int hWnd)
{
try
{
if (IsHooked)
MessageBox.Show("Don't hook it twice without unhooking, or you
will be unable to unhook it.");
else
{
gHW = new IntPtr(hWnd);
gHW = FindWindowEx(gHW, IntPtr.Zero, "XLDESK", string.Empty);

gHW = FindWindowEx(gHW, IntPtr.Zero, "EXCEL7",
"SampleXLS.xls");

wpd = new WndProcDelegate(WndProcSub);
lpPrevWndProc = Win32.SetWindowLong(gHW, -4,
Marshal.GetFunctionPointerForDelegate(wpd));
//Marshal.GetFunctionPointerForDelegate(m_delegate)) ;
IsHooked = true;

}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}

}

public void SetUnhook()
{
IntPtr temp;
temp = Win32.SetWindowLong(gHW, -4, lpPrevWndProc);
IsHooked = false;
}

private int WndProcSub(IntPtr hWnd, uint msg, uint wParam, int lParam)
{
uint vkCode = wParam;
if(GetKeyState((int)Win32.VK_CONTROL))
{
if(vkCode == Win32.VK_V)
Debug.Print("Paste Called");
}

return Win32.CallWindowProc(lpPrevWndProc, hWnd, msg, wParam,
lParam);
}
}
}

This is the code in VSTO where I call the SetHook and SetUnHook methods.

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
WorkbookSheetActivate();
oControl = Application.CommandBars["Worksheet Menu
Bar"].Controls["Edit"];
oControls =
(Microsoft.Office.Core.CommandBarControls)oControl .GetType().InvokeMember("C
ontrols"
, System.Reflection.BindingFlags.GetProperty, null, oControl, null);
oButton = (Microsoft.Office.Core.CommandBarButton)oControls["Paste"];
oButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler(oButton_Clic
k);

int i = Application.Hwnd;
objHook.SetHook(i);

}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
objHook.SetUnhook();
}

Hope this helps.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.