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

A week ago I blogged about Excel Interop in .Net. Today we released the customer solution that uses Excel Interop to create Excel files. First I want to present some new issues that we learned from when deploying this solution.

Excel Interop in a Web Application

A web application doesn’t by default use valid security credentials when running. So if you want to create some sort of Office document programmatically, you must provide a valid users Username and Password in the Web.Config. We also found other issues by doing this, and from what I’ve read it’s not recommended to do this in a Web Application. In our case we hit a dead end and started building a different solution for creating the Excel files.

Task Scheduler and Excel Interop

Our solution which is web based takes a user’s input and based on that creates an Excel file that contains an extensive report. Our “workaround” for the above issue was to create a log where every request is stored. Then we built a Windows Console Application that reads the log, creates the Excel file and sends it as an attachment in an e-mail to the person who requested the file. The Console Application is triggered every 3 minutes by the Task Scheduler.

Now we learned something new. Excel which is instantiated by the Console Application, can only run if the calling user has a Desktop folder. Since the Task Manager runs this application unattended, we have no Desktop folder.

After some digging in various forums I found the answer, and fortunately the solution.

If you run Windows Server 2008/2008 R2 in x64:
Create a folder named “Desktop” in C:\Windows\SysWOW64\config\systemprofile\

If you run Windows Server 2008/2008 R2 in x86:
Create a folder named “Desktop” in C:\Windows\System32\config\systemprofile\

The user who is declared as the running user in the Task, must have write permissions in the Desktop-folder

The final Application

The solution I’ve been writing about is Nationella Viltolycksrådet; www.viltolycka.se. In this web site all Swedish accidents with wild life are reported. Over 7 000 persons work daily, reporting these accidents into the system. Therefor we have access to a great amount of statistical data.

In this case the visitor can make a selection of the data he or she wants statistics for, then enter the name and e-mail address. In a few minutes the Excel file containing the reports will be sent to the e-mail address.

Try it here: http://www.viltolycka.se/statistik/excelrapport.aspx

The web site is only in Swedish.

Skärmklipp2

Skärmklipp3

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!