This site uses cookies. By continuing you accept the use of cookies.I Accept

Last week I started developing a solution for a customer, with the target of creating an Excel Workbook programmatically.

Since we use Microsoft .Net Framework for all our solutions everything is familiar and it’s easy to get started with development against Office applications.

The solution gathers a huge amount of data and fills around 20-30 Worksheets with tables and charts. Everything is based on several filters sent to our application. So the data, number of Worksheets and the design of the charts are designed based on this.

I came across several obstacles in this solution that I want to share.

Debugging

It’s hard to debug an application that is communicating with the Excel Interop. The application simply crashes and the Excel Instance is not killed automatically. So I used one of my screens for the Task Manager, which made it easy for me to kill the instances every time my app crashed.

I still haven’t figured out how to debug this yet.

Performance

When working with the cells in your Worksheet, you must define a Range to work with. The range consists of cells and you can read and write data to them. It didn’t take long before I noticed that every time you put a value in a cell, a call is made to the Excel Interop. Even when putting small amounts of data in a small amount of cells you get slow performance.

The solution is working with Multi Dimensional Arrays, and putting the data into the array. When all data is in its place, you simply write the array to a designated Range in the Worksheet.

image

The example above is an array that works as a matrix. Since I  want to put numbers, strings, dates and formulas in the cells I use object in the array.

After declaring my array I put the data into it:

image

When it’s time to put the data into the Worksheet I do as follow:

image

This performance workaround may seem a little odd, and it’s nothing you’re used to when working with .net. In this case it’s completely necessary.

Using hidden data sources for Charts

In our case we must create several charts based on data that we don’t want to visualize for the person who opens the Excel Workbook. Our solution holds around 150 charts spread over around 25 Worksheets, and the data source for each Chart is unique.

Since (by what I know) you must use cell data as a data source for any chart in Excel we had to create around 150 “tables” containing the data. We felt it would be ugly to display this data in a Worksheet, displayable for any reader.

The solution we used was to create a Worksheet and filling it with data. Then we created the chart and used the data as a Data Source. The last thing we did was to hide the Worksheet that held the data.

You can hide a Worksheet in two ways:

image

This hides the Worksheet, but the user is able to make it visible in the Workbook. So we used the following instead:

image

This completely hides the Worksheet, and the user is unable to make it visible (maybe there is some way but I haven’t found it yet).

Compatibility

This isn’t really an obstacle, other than that a user may use an old version of Office Excel. But when calling the Excel Interop to Save the document to disk, you can provide the version of Excel you want to save for.

image

In our solution, that will be used by any Internet user, we target an older version of Microsoft Excel.

Closing comments

It’s both fun and interesting to work with Visual Studio Tools for Office, and it gives us a great chance to develop better business applications.

The solution we are working on now isn’t made public yet, but I will follow up with more blog posts about this later on.
I will dedicate at least one future blog post to code examples and tips.

As we say in Sweden; Swim Calm!

Post a comment

Post a comment