VSTO & .NET & Excel

January 1, 2014

Syncfusion’s Free Offers

Filed under: .NET & Excel, Apps for Office, COM Add-ins, Excel, SQL Server, SQLite, Tools, UI Design, Valentina DB, VSTO & Excel — Dennis M Wallentin @ 8:37 pm

During 2013 Syncfusion have been releasing additional books in the series of Succintly. They cover a lot of development areas and are all free (PDFs).

Not only books are shipped free. Syncfusion offers also for Hobbyist the Essential Studio for JavaScript.

Per se it’s not free as You need to pay $ 1 for 1 license which is also valid for next offer.

Another offer for Hobbyist  is the Essential Studio for WinRT (Windows 8.1)

The JavaScript Studio should attract Excel developers who develop so called Office Apps.

Anyway, I find it nice when a company like Syncfusion actually see the group of hobbyists.

Yes, I’m a full member of the Hobbyists group ūüėČ

Happy Coding!

Dennis

July 20, 2013

Ribbon Control – Xojo – J√©r√©mie Leroy

Filed under: SQL Server, SQLite, Tools, UI Design, Valentina DB, Xojo — Dennis M Wallentin @ 2:57 pm

This is the first post of a series that will present controls and tools by Jérémie Leroy.

Why I pick these controls is quite simple to explain:

  • All controls and tools are of high quality
  • They are easy to work with, even for a newbie like me
  • They all support the RAD concept
  • The prices are reasonable given what we get for the money

First out is the tool for creating Ribbon UI in an easy way. It’s a visual designer tool that generates the required code to show and use the Ribbon UIs.

Interesting, when creating the Ribbon UI and the code for it we actually run in Debug mode.

The elements of the control are as the following picture shows:

RibbonElements

It’s easy to keep these three elements in mind. Next, let us have a look on the control itself. The following screen shot views the Ribbon UI tool:

RibbonStart
To start to add Tabs, Sections and Buttons we simple click on the Reorder Ribbon button. When adding new elements we can at the same time manipulate the added item. The following shows tries to show it:

RibbonUI1

When all the elements have been added to the Ribbon it can look as the following screen shot:

RibbonUIFinish

The last step to do is to generate the code to create the Ribbon UI. Click the button To XML/Xojo and it will very fast generate the required code to build the customized Ribbon UI.

RibbonUIFinal

The next and final step is simple to cut & paste the generated Xojo code to the Open event of  the RibbonCanvas objectet.

 Dim T As RibbonTab
 Dim S As RibbonSection
 Dim B As RibbonButton

 //Tab 1
 T = New RibbonTab("Home")
 me.Tabs.Append T

 S = New RibbonSection("Help")
 T.Sections.Append S

 B = New RibbonButton("btnDBHelp", "Database", Ico("database-help"), Nil)
 B.IconName = "database-help"
 S.Buttons.Append B
 B = New RibbonButton("btnQHelp", "Query", Ico("query-help"), Nil)
 B.IconName = "query-help"
 S.Buttons.Append B
 B = New RibbonButton("btnRHelp", "Report", Ico("report-help"), Nil)
 B.IconName = "report-help"
 S.Buttons.Append B

 S = New RibbonSection("Record")
 T.Sections.Append S

 B = New RibbonButton("btnAdd", "Add", Ico("button_green_add"), Nil)
 B.IconName = "button_green_add"
 S.Buttons.Append B
 B = New RibbonButton("btnUpdate", "Update", Ico("button_green_down"), Nil)
 B.IconName = "button_green_down"
 S.Buttons.Append B
 B = New RibbonButton("btnDelete", "Delete", Ico("button_red_delete"), Nil)
 B.IconName = "button_red_delete"
 S.Buttons.Append B

 S = New RibbonSection("Maintenance", "Maintenance")
 T.Sections.Append S

 B = New RibbonButton("btnDBCompress", "Compress Database", Ico("compress_database"), Nil)
 B.IconName = "compress_database"
 S.Buttons.Append B
 B = New RibbonButton("btnTableCompress", "Compress Table", Ico("compress_table"), Nil)
 B.IconName = "compress_table"
 S.Buttons.Append B
 B = New RibbonButton("btnDBBackup", "Backup Database", Ico("database_backup"), Nil)
 B.IconName = "database_backup"
 S.Buttons.Append B

 S = New RibbonSection("Query")
 T.Sections.Append S

 B = New RibbonButton("btnQuery", "Query", Ico("query"), Nil)
 B.IconName = "query"
 S.Buttons.Append B
 B = New RibbonButton("btnLookup", "Lookup", Ico("query_lookup"), Nil)
 B.IconName = "query_lookup"
 S.Buttons.Append B
 B = New RibbonButton("btnQueryDB", "Query Database", Ico("query-database"), Nil)
 B.IconName = "query-database"
 S.Buttons.Append B
 B = New RibbonButton("btnExecute", "Execute", Ico("query-execute"), Nil)
 B.IconName = "query-execute"
 S.Buttons.Append B

 S = New RibbonSection("Report")
 T.Sections.Append S

 B = New RibbonButton("btnReport", "Report", Ico("report"), Nil)
 B.IconName = "report"
 S.Buttons.Append B
 B = New RibbonButton("btnOpenReport", "Open Report", Ico("report_open-add"), Nil)
 B.IconName = "report_open-add"
 S.Buttons.Append B
 B = New RibbonButton("btnMailReport", "Mail Report", Ico("report_open-mail"), Nil)
 B.IconName = "report_open-mail"
 S.Buttons.Append B

me.Height = me.BestHeight

As part of the solution there is also a support function to work with the added icons:


Ico(Name As String, Debug As Boolean = False) As Picture

If IconList.HasKey(Name) then
 Return IconList.Value(Name)
 elseif Debug then
 #if DebugBuild
 break
 #endif
 End If

 Return New Picture(1, 1, 32)
 Return Nil

As we can see, it’s a quite straight forward code. It simple to follow and to maintenance it.

Here are the interesting links to J√©r√©mie’s site and productpage:

Enjoy!

Kind regards,

Dennis

Edit Note: Of course, I should also mention that the professional icons in use are made by Axialis.

June 8, 2013

Xojo: An Old New Kid on The Block

Until 4th June this year the company had the name Real Software Inc and their product, a cross-platform developing kit, had the name Real Studio. However, the company name is now Xojo Inc and the product’s name is Xojo.

Bob Keeney, founder of BKeeney Software, has written a great review about Xojo 2013r1. I refer to that articles then write one myself.

Here I will bring up two important subjects, Xojo in Windows and Missing Controls – Expensive to buy.

Xojo in Windows

When I started to hang around and lurk around I got the impression that the major group of developer are on the Apple platform. Since then it has been confirmed in many ways and with the transformation of Real Studio UI to Xojo UI it’s quite clear that Xojo Inc favor the Apple platform. Have a look on the below screen shot, is it Windows or?

Xojo in Windows

Missing Controls – Expensive to buy

If I buy a development tool like Xojo I expect to have included a bunch of basic controls. Xojo Inc do not follow the main stream of development tools. They have excluded two important controls, a Data Grid and a Chart control.

In fact, they have placed themselves as hostage to 3rd party controls by some vendors. For developers it gets more expensive and by using the 3rd party controls the developers are also in the hand of the 3rd party vendors.

OK, we start first with buying a license of Xojo Desktop for US$ 300 (excluding VAT).

Suppose we need a descent Data Grid control we then must buy a collection of plug-ins. The collection has a price of US$ 199 (excluding VAT) with a subscription of one year.

Next, we realize we need a chart control. It exist a free chart control but it has not been updated for the last years. Beside that, it lack a lot of feature. Our real option is to buy a commercial Chart control. It cost US$ 26o (excluding VAT).

Let us summarize it:

  • We pay US$ 300 for one Xojo Desktop License
  • We pay US$ 560 for two basic controls!
  • All in all, we pay US$ 860 (excluding VAT)

We actually pay more for two basic controls then what one license of Xojo Desktop cost! That sucks.

Badwill can be created in many ways. Badwill created this way can easily be avoided; provide a Data Grid control and a Chart control as part of Xojo packages.

Is it only me who find it unacceptable?

Xojo is an interesting development too and I find the job done by Xojo Inc to be good. However, given the present feedback Xojo need one or two releases before all the teething are gone.

Finally, the subject for this article is related to the fact that Xojo is based on Real Studio so in one way it’s new in another way it’s old.

Kind regards,
Dennis

February 22, 2013

POEditor: An Excellent Translation Tool!

It’s very¬†exceptionable¬†that I get thrilled over a software. The last time must be the first version of Excel 2.1d, that’s nearly 30 ago, I got in my hand!

I can only say that it’s an excellent software and it’s also free. It’s so good that I say its speak for itself.

The URL to POEditor is: http://poeditor.com/

Enjoy!

Kind regards,
Dennis

November 10, 2012

Axialis Pure Flat 2013 Toolbar Stock Icons Rock!

Filed under: .NET & Excel, COM Add-ins, Excel, Real Basic Developing, Tools, UI Design, VSTO & Excel, XLLs — Dennis M Wallentin @ 2:00 am

A software program’s success is usually depended on its functionality, i.e. it does the work and it has a user-friendly UI. In fact, the UI is the most important part of applications as that is what the end-users see and work via. This is something we all known (or at least should know).

Among the components that make up the UIs are icons. Icons should give the users an understanding about the functions that are hidden behind the icons. When Microsoft changes the UI for its Windows operating system it also affect the design on icons.

With Windows 8 we have got a new UI style, the Metro style. The new style of icons can simple be named as flat.

Axialis, the vendor of the tool IconWorkshop, offers also excellent icons. At least that’s my opinion, therefore I would like to push for their new series of Pure Flat icons.

The below Ribbon UI shows some of the flat icons in Axialis first series of icons:

For more information and to see more icons examples please visit:

Kind regards,
Dennis

August 23, 2012

Index of UX guidelines for Metro style apps

Filed under: .NET & Excel, Apps for Office, Excel, UI Design, VSTO & Excel — Dennis M Wallentin @ 2:44 pm

Microsoft have recently released its guidelines to create GUI for Metro style apps. It can be downloaded from

Kind regards,
Dennis

October 27, 2011

Axialis Stock Icons For Toolbar & Ribbon

Filed under: .NET & Excel, Excel, UI Design — Dennis M Wallentin @ 5:43 pm

I’m the first to admit that I have moved forward and backward when it comes to stock icons and which icons to use. The UI plays a critical role in all kind of softwares which means that we also need to consider what kind of icons to be used. With the introduction of Ribbon UI I would say that the design has become even more important. It requires a larger area of the hosted Windows.

When I design UIs I try to put myself in the role of an end user who spends a considerable time with my solutions in Excel. Some cool UI design is no longer cool after two weeks use where the user spends perhaps 10-15 hours during that period using the solution.

However I cannot say that I’m well educated when it comes to design. I try to follow Microsoft’s concept for designing Ribbon UIs.

The following screen shot shows examples of the new stock icons from Axialis who has recently started out to offer complete icons sets. Yes, it’s the same company who also offers the great icon creator tool, IconWorkshop.


Personally I prefer light-weighted colored icons with a soft tone and that is also what the new icon set, Axialis Ribbon & Toolbar Stock Icons, offers. What we get is an improved professional UI.

It’s easy to forget that in VS.NET we cannot use resource files that have names like “aaa bbb.ping”. So if we use let say 16 icon files we need to rename them all before using them. Axialis has managed to keep that in mind and therefore all icon files have names like “aaa_bbb.ping”.

This is what Axialis writes about the new released icons:/
Icons are available in sizes 16×16, 24×24, 32×32, 48×48 and normal, hot & disabled states. Provided file formats are PNG, ICO and BMP. Colors are coded in RGB with alpha channel transparency in PNG and ICO icons. BMP icons are coded in RGB with magenta areas to define transparency.

For more information please visit one of the links. I also recommend visiting their homepage on a more regular basis as they plan to release more stock icons sets.

Kind regards,
Dennis

December 8, 2010

Ribbon UI: Syncfusion’s Essential Tools & Professional-Icons.com

Filed under: .NET & Excel, Excel, Tools, UI Design, VSTO & Excel — Dennis M Wallentin @ 8:14 pm

From time to time I come to think of how important it is with a user-friendly and with a professional Graphical User Interface (GUI). Of course, this is something that every developer sooner or later finds out themselves. What matters and what clients usually see is the GUI that the technical solutions are wrapped in.

In this article I will try to discuss my tools I use to create a GUI that can be competitive. I will do it by using the Ribbon UI, aka Office UI, and see which tools that is necessary in order to create the UI. I will not cover any guidelines to create the UI only which tools that are necessary in order to build the GUI.

Ever since Microsoft released its Office UI they have had a strong desire to protect the intellectual property (ip) of it. From what I know it appears to be the first time Microsoft actually has taken this action. To protect their ip Microsoft request every vendor, that wish to implement the Ribbon UI in their software solutions, to apply for a license in order to use the UI. It should be noted that it exist some exceptions from this request.

To apply for a license to use the Office UI is rather easy and is also free. To find out more about it please see the following; Office UI Licensing. What is notable is that if we have got a license to use Office UI 2007 we need to update the license to also include the Office UI 2010.

Due to my lack of knowledge about the law I don’t know to which extend we must apply for licenses when developing add-ins and standalone workbooks with customized Office UI in Excel.

So far I have only applied for licenses for my free and commercial add-ins that have customized Office UI but not for any clients’ solutions.

VS 2010 is shipped with some basic controls together with some simple icons. I say basic because the controls do what they are supposed to do but provide no flexibility and no extra that actually can light up the solutions. If we want to use the Office UI we must buy the required controls from a third-party since Microsoft does not provide us with such controls. On the market it exist a great number of vendors that offer a great number of powerful controls.

As for the icons we face the same situation as with the controls for the Ribbon UI. However it exist a larger groups of free icons with various quality on the Internet and without any support at all. An advantage with icons, compared with the controls, is that we can use the built-in icons shipped with Office. These icons are quite common which may be a limitation if we want to create more relevant and unique UIs. Third-party provides us with icons that reflect specific tasks and activities and the vendors usually provide us with support.

Personally I try to always get commercial tools as I then get good products with support and an access to larger knowledge base. Through the years I have used several vendors but previously this year I changed my strategy. Instead of using many vendors I decided to only have a very few vendors. Actually, I ended up with two vendors; Syncfusion for all the Windows Form Controls and Professional-Icons.com for all the icons.

I decided to use Syncfusion for a various reasons including very good and powerful controls together with an excellent support. Syncfusion’s packages are not among the cheapest on the market but I think they offer a high ROI, especially if we learn how to leverage them. At least that’s my experience. An expression I picked up for many years says: quality goes never out of style. Syncfusion has that kind of quality and their products are robust and reliable for all kind of software development.

When building the Ribbon UI (see below) I used several controls from the Syncfusion’s Essential Tools also for creating the framework for the Ribbon UI. It’s straightforward and quite easy to build this UI, especially if You have done it a couple of times. The Ribbon UI control can, in addition to work with Windows Form Controls, also work well with other .NET controls. For a good introduction about the main control RibbonControl Adv please click here.

So far I have only been talking about Windows Form controls but nothing about the icons. The icons from Professional-Icons.com come with a great number of file formats including ICO, PNG, GIF, JPEG and BMP. The icons shipped come also in various sizes from 16 x 16 to 256 x 256. They ship the icons in various packages such as Applications, Accounting, Business, Medical and many more. Professional-Icons.com also offers some bundles of their packages that give better prices then buying the packages one by one. In my experience I rarely can only use one package of icons. It’s more common that I need icons from at least from two packages like Business and Network or Database. Again, Professional-Icons.com’s products are not the cheapest on the market but by selecting them You get great icons with high quality and also a very good support.

The following screen shot shows Syncfusion’s Windows Form controls and Professional-Icons.com’s icons in use in a Ribbon UI. This is actually a stripped version of a client‚Äôs solution.

Personally I find the screen shot to be very nice and reflect the quality I want to achieve when building UI in my clients’ solutions.

What do You think and how do You work to create user-friendly UI?

Kind regards,
Dennis

PS: Syncfusion offers also great controls for ASP.NET/ASP.NET MVC, WPF and Silverlight

August 9, 2010

Working with Folders

Filed under: .NET & Excel, UI Design, VSTO & Excel — Dennis M Wallentin @ 10:48 pm

Introduction

It’s quite often that we need to work with folders in various Excel solutions. It involves creating, deleting, moving, copying and update folders with or without any user interactions.

In this article I will discuss and show the code in order to work with folders. In the first part I will cover the basic processes to handle folders and in the second part of the article I will discuss folders and some user interactions.

Before we start up we should make sure that we have a reference to the System.IO namespace in the project and that we have made an import statement at the top of the class module. We will be using classes and methods that reside in that namespace.

Basic work with Folders
In the first example we will create a new folder named “Code” within a user’s folder system. That is done as the following code snippet shows:

Const NewFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Not Directory.Exists(NewFolder) Then
'If it not already exist we create the folder.
Directory.CreateDirectory(NewFolder)
End If

To delete an empty folder is also quite easy to do which the below code snippet also shows:

Const DeleteFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Directory.Exists(DeleteFolder) Then
'If it exist we delete the folder.
Directory.Delete(DeleteFolder)
End If

But what if we want to delete folders that contain files? The above code will throw an exception if the folder is not empty. By setting the second argument explicit to “True” in the Delete method a recursive deleting of all files is executed. That includes also all the subfolders in the targeting folder. The following code snippet shows it:

Const DeleteFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Directory.Exists(DeleteFolder) Then
'If it exist we delete all the files and the folder.
Directory.Delete(DeleteFolder, True)
End If

To move files and subfolders from one location to another is also an easy task to achieve. What should be noted is that the old folder is always deleted and that the new folder cannot exist when the code is being executed. If the folder already exists the code will throw an exception. The code snippet below shows the details.

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Code"
Const Target As String = "c:\Test\Dennis"

If Directory.Exists(Folder) Then
'The new folder cannot exist.
If Not Directory.Exists(Target) Then
'Move file and subdirectories to
'the new created folder and delete
'the old folder.
Directory.Move(Folder, Target)
End If
End If

To copy a folder’s content to a new location requires another strategy then what we so far have been used. Instead we must use the method My.Computer.FileSystem.CopyDirectory which also the following code shows. The last parameter controls if any existing files should be overwritten or not.

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Dennis"
Const Target As String = "c:\Test\Backup"

If Directory.Exists(Folder) Then
'If the destination folder does not exist it
'will automatically be created.
My.Computer.FileSystem.CopyDirectory(sourceDirectoryName:=Folder,
destinationDirectoryName:=Target, overwrite:=True)
End If

The CopyDirectory method has two additional parameters that we can use whereof one can be rather useful here. If we know that we will be handling large files or a great number of files that will take some time to copy then we can let the users know about it. To do so we add the parameter ShowUI to the code as the below code also shows.

'On top of the CLass module
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
'...

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Dennis"
Const Target As String = "c:\Test\Backup"

If Directory.Exists(Folder) Then
'If the destination folder does not exist it
'will automatically be created.
My.Computer.FileSystem.CopyDirectory(sourceDirectoryName:=Folder,
destinationDirectoryName:=Target, showUI:=UIOption.AllDialogs)
End If

When executing the code the following Progress Dialog in Windows 7 is showed (the option UIOption.AllDialog makes it possible):


And as we also can see from the screen shot the user can cancel the operation if necessary. Since the other methods of My.Computer.FileSystem are focused on single items they are of little interest in this context.

If You are used to use the SHFileOperation Function then it has been replaced with the IFileOperation Interface. For more information about the later please see IFileOperation Interface.

Some words about Special Folders
It’s beyond the scope for this article to discuss SpecialFolders but I would like to show a basic example on how we can work with Special Folders.

In the following example we will retrieve the path to My Documents folder for the current user:

Dim MyDocuments As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

MessageBox.Show(MyDocuments.ToString)

As we can see, to get access to the special folders we use the Environment.SpecialFolder.MyDocuments to access to MyDocuments Special Folder. When executing the above code we get the following information:


Folders & User Interactions
The UI design for the two cases, with some user interactions involved, is showed in the below screen shot.

In the first case the user click on a button in order to populate the ListBox control with names of Excel files. To get the file names from the pre-decided location we use the following code in the Click event for the button:

Private Sub btnFixedFolder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnFixedFolder.Click

Const Folder As String = "C:\Users\Dennis Wallentin\Documents\Fixed Folder"

Dim dirInfo As New DirectoryInfo(Folder)
Dim ArrayFiles As FileInfo() = dirInfo.GetFiles("*.xlsx")

Me.lbFixedFolder.DataSource = ArrayFiles
End Sub

When executing the code the outcome is what the below screen shot shows:

If we need to iterate through the collection of files in the array it’s also quite easy to do. The following code shows an example where we extract the files names and add them to the ListBox:

For Each item In ArrayFiles
strBuilder = New StringBuilder(item.Name)
strBuilder.Replace(".xlsx", String.Empty)
Me.lbFixedFolder.Items.Add(strBuilder)
Next

In the next case the users must select a source folder. To let the users choose folder we add the FolderBrowserDialog control to the project. The following code makes the Folder Browser Dialog available to the users and populates the ListBox control with the Excel files from the selected folder:

Private Sub btnUserFolder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnUserFolder.Click

Const Title As String = "Select a folder for the documents:"
Me.FolderBrowserDialog1.Description = Title

If Me.FolderBrowserDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

Dim Folder As String = Me.FolderBrowserDialog1.SelectedPath
Dim dirInfo As New DirectoryInfo(Folder)
Dim ArrayFiles As FileInfo() = dirInfo.GetFiles("*.xlsx")

Me.lbUserFolder.DataSource = ArrayFiles

End If

End Sub

When the code is executed the Folder Browser Dialog is launched as the following screen shot also shows:

After the selection of a source folder the Listbox gets populated as the following and final screen shot also shows:

I hope that the above has given some insight on how to work with folders in the VB.NET language.

Kind regards,
Dennis

September 9, 2009

Goodbye Radio Buttons!

Filed under: .NET & Excel, COM Add-ins, UI Design, VSTO & Excel — Tags: — Dennis M Wallentin @ 12:20 pm

For years I have been using the Radio Button control in many of my solutions (where it has been appropriated). When using the control it was usually together with the Group Box control. The following screen shot shows a common approach when using these two controls together and where users are supposed to select one of the three options. Typically one of the Radio Buttons is also pre-checked when the Windows Form is loaded.

RadioButtons

When designing the UI and consider to use Radio Buttons the question we should raise is; when is the number of items relevant to the end users? My answer is when the users are about to make a selection, not before and not after that decision point. After the decision has been made the selected item should be viewed. 

This lead to a new standpoint, at least to me, when designing UI; instead of a group of Radio Button controls we replace them with a Combo Box control as the following screen shot show:

Comboboxes

When the Windows Form is loaded we have the same option as with Radio Buttons; to have one (or more) item(s) selected or to have no no item pre-selected. When the users have made their decisions the selected item is showed in the control. Consequently,  the UI gets cleaner and the Windows form may even be down sized. Of course, the same discussion can be applied to the List Box control.

All in all, for all my future projects I will simple drop the Radion Button control and instead use the Combo Box control. Do You agree?

Kind regards,
Dennis

Create a free website or blog at WordPress.com.