Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting EasyLanguage code to Excel | Excel Discussion (Misc queries) | |||
Preserving VB code when converting from Excel | Excel Programming | |||
Deploying a application which uses Excel primary interop assemblie | Excel Programming | |||
Converting date to numerical and doing calculations | Excel Worksheet Functions | |||
Converting VB Code for Excel 2000 to Excel 2003 | Excel Programming |