VSTO Power Tools

Andrew Whitechapel announced yesterday that a new toolkit for VSTO and VSTA now is available for download. 

I will test most of the tools and review some of them here. For me these kinds of tools are very important as they can both save time during the development cycle and when troubleshooting targeting computers.

What should be noted is the following:

• The toolkit is available “as is” meaning MSFT does not give any officially support for it.
• The toolkit explicit target Visual Studio 2008 and Office 2007. I will test the VSTO Troubleshooter tool with Excel 2003 to find out if it is compatible or not.

Those of us who use VSTO should take the time to test and to give feedback in order to improve the toolkit. Does anyone know how we can communicate the feedback?

For me it’s too early to implement any of the class libraries ‘Office Custom UI’ manager and ‘Office Interop API extensions’ in production solutions. Nevertheless, it looks very promising!

Kind regards,
Dennis

Advertisement

Export data from DataGridView to Excel

A common task is to view filtered data from databases in Windows applications where the users then can select records to be imported into Excel. In this context the main issue is usually how to achieve an acceptable performance when writing the selected data to Excel.

When it comes to automation of Excel the communication between Excel and for instance a .NET application is said to be an Out-of-Process communication as Windows allocate separated memory areas to them. In other words, the applications do not share the same memory area which has a negative impact on the overall performance.

When it comes to .NET automation of Excel there are two additional aspects we need to be aware of:

• The COM interop calls themselves create an overhead and
• The more calls to Excel Object Model the slower it gets.

The best approach is therefore to reduce the number of calls to Excel Object Model and before calling Excel prepare the data as much as possible. In VBA we have the data type ‘Variant’ which is good to use when working with cells’s data. In .NET we have a similar data type by the name of ‘Object’. The final key to increase the speed is to use as much as possible 2D arrays (which are 0-based) and when necessary declare them as ‘Object’.

The Case

For the case I use the Northwind database which is connected to a SQL Server 2005 where the data is presented for the users in the following Windows form:

 

The screenshot below shows all the code in use for the case. The ‘Export’ procedure shows the use of 2D arrays where they in the first step are populated with data in managed code. In the next step the data is ‘dumped’ in one go into the targeting COM worksheet:

The above code can be downloaded from here. Of course, in the above case we could have reduced the number of arrays to only use one.

When I have managed to fully port myself to VS 2008 and when SQL Server 2008 is out I will make some posts about LINQ to SQL.

Kind regards,
Dennis