WPF-based Custom Task Pane (looking for C# example..had issues trying to build my own)

[BabaDaHut]

On day 1 using Excel DNA, I got the sample Distribution\Samples\CustomTaskPane.dna working with no problems.

I tweaked it (as shown in the first attached dna file) to show the current cell's formula...and still works OK, but occassionally I would need to reload the xll file.

 

On day 2, I thought I would try my luck parsing the current cell formula into a WPF based FlowDocument (using some pre-existing C# code I got working in PowerShell). Here's where things got interesting. The only example of loading WPF XAML into a CTP I could find was in F# (but that mentioned this VSTO C# blog:  http://www.clear-lines.com/blog/post/Docking-WPF-controls-in-the-VSTO-Task-Pane.aspx). Since I don't have access to VSTO (that's why I'm here checking out DNA), I tried experimenting. Then everything blew up and Excel crashed when I tried the second attached DNA file <<FirstAddIn - Copy (13).dna>>. Lucky 13 :( ...After this crash, none of the CTP examples worked, neither did the Ribbon examples. I got them working again by removing a totally different addin : the Power Pivot COM addin.

 

Does anybody know why this would occur?

(BTW, I using Vista, with Excel = (Microsoft Office Professional Plus 2010)

 

More importantly, does anyone have some sample DNA file that shows how to load WPF/XAML into a CTP in C#?

 

Thanks.

[BabaDaHut]

In case it helps explain what I'm trying to do, here's a cleaned up version of a formula parser in a CTP that might be of some use deciphering long formulas as is....but it is just a text box...I'd really like to do more with this using WPF. For example replacing addresses with field names (in cases where you have the same formula copied down a column) perhaps inserting in-line clickable buttons to scroll to precedents. Does anyone have any sample c# DNA file that loads WPF/XAML into CTP (or even a new window for that matter)?.

[Patrick O'Beirne]

That's interesting ... have you looked at existing parsers like RefTreeAnalyzer

Aaron Blood's Explode addin at XL-Logic.com
http://www.xl-logic.com/

Jan Karel Pieterse has an addin for formula formatting: RefTreeAnalyser for Excel:
http://www.jkp-ads.com/RefTreeAnalyser.asp

http://www.dailydoseofexcel.com/archives/2005/04/01/excel-formula-formatter/
leads us to Rob Van Gelder's AudXL
http://vangelder.orconhosting.net.nz/excel/audxl.html

Arixcel Explorer from www.arixcel.com

Tade Oyebode's Formula Parser

Lyquidity formula analyser, http://www.lyquidity.com/formulaviewer

Rainbow Analyst also has a simple formula parsing tool (in the “cell inspector” function).
www.themodelanswer.com

Jocelyn Paine has a formula Parser written in Prolog.

Ray Payette has pointed to
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

There's Fishbrain http://homepages.ecs.vuw.ac.nz/~elvis/db/Excel.shtml
Invesitgation into Excel Syntax and a Formula Grammar

Maybe something at http://dirkgorissen.com ?

On 21/01/2014 19:50, BabaDaHut wrote:

In case it helps explain what I'm trying to do, here's a cleaned up version of a formula parser in a CTP that might be of some use deciphering long formulas as is....but it is just a text box...I'd really like to do more with this using WPF. For example replacing addresses with field names (in cases where you have the same formula copied down a column) perhaps inserting in-line clickable buttons to scroll to precedents. Does anyone have any sample c# DNA file that loads WPF/XAML into CTP (or even a new window for that matter)?.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

[BabaDaHut]

Sysmod, thanks for the links. I see some of these parsers may be more than I need, but might not be easily customized. A big advantage of ExcelDNA (besides being free) is that it doesn't require installing anything; a non-administrator can simply modify the dna file as needed and open the xll file.* Same goes for VBA, but VBA doesn't easily do Custom Task Panes (or WPF FlowDocuments) without intervention by ExcelDNA, or VSTO, etc.

 

I'm interested in more than just formula parsers...for example another idea would be to dump a list of column headers along with the current row's values into a transposed ListView in the CTP. The ability to do this with WPF/XAML is what I hope someone will be able to show me with a C# example.

 

  

* I am also curious about what sort of residual temporary files or registry changes, or other footprint comes with using Excel DNA.

 

P.S. A couple weeks ago, I recall seeing a VSTO based addin that seemed to take formula parsing to the next level...but I can't find it anymore...It might have been an Office 365 based thing with an online example...I think it was a commercial offering from some group in Australia. If I find it, I'll post back in case it helps anyone.

[BabaDaHut]

I'm still hoping someone can provide some sample DNA file that shows how to load WPF/XAML into a CTP in C#...or tell me I can't do it...but in case anyone is more interested in Excel formula parsing/auditing:

 

 Here is a link with more suggestions: http://www.linkedin.com/groups/Available-free-download-AddIns-track-1724487.S.95809725

 Of the free ones mentioned, http://www.spreadsheetstudio.com/tools/navigation-tools/  seems best suited for what I do. I just installed on my home computer and works fine (but unfortunately, my employer's IT dept. won't allow installing anything until it goes through the channels...and by that time it will be obsolete). Alternatively, http://www.bestpracticemodelling.com/software/bpmtraverse, is just a xlam file that does alot of the same thing (but doesn't appear to allow you to leave the dialog box and pick other cells, or do the nice "Bracket Forests" and "Interim Results" like spreadsheetstudio's "Formula Explorer"). Perhaps both of these could benefit by optionally applying the attached code for new line breaks and aligning matching parentheses.

[Sergey Zhilyakov]

Hi,

I'm still hoping someone can provide some sample DNA file that shows how to load WPF/XAML into a CTP in C#...or tell me I can't do it

You can do it and it is quite simple. To do so

1. Create a WPF user control and customize it as you wish;
   
2. After you build the project, the newly created control is available in the toolbox;
   
3. Create a WinForms user control and drag & drop your WPF control into it. The WPF control is embedded as ElementHost. That's it.
   

There is no anything special about it.

Best regards,

Sergey

--

[BabaDaHut]

Sergey, thanks for the encouragement; but I am not able to follow your suggestion. Is this something that can be done by simply editing a text file (similar to running raw c# code including WPF/XAML with Powershell)? Bear in mind that I do not have VSTO (or any access to Visual Studio on my work computer). I do have the free VC# 2010 Express on my home computer, and am willing to download any free extensions, but was hoping to avoid packing extra DLL's into the XLL (like would be needed with the F# example). Please advise and/or provide sample code if possible. I do appreciate your help. 

[DC]

Here is a C# example based on Govert's sample included with Excel-DNA. (http://exceldna.codeplex.com/SourceControl/latest#Distribution/Samples/CustomTaskPane.dna)

It is more or less equivalent to the F# version that you referenced. (written by me)

https://github.com/mndrake/ExcelCustomTaskPaneCS

Dave

[BabaDaHut]

DC, very coooool....thank you very much :).

 

I was able to build your C# solution with VC# 2010 Express and that gives me the dll which incorporates the wpf/xaml stuff that is then referenced in the dna file.

 

Is there any way to have the dna file reference (or include) the XAML file directly (rather than via a dll) and build it at run time (like is possible with PowerShell)? That would save me from having go to my home computer just to tweak the xaml and recreate a dll.

 

Thanks again.