Producing more complex excel worksheets using c#

Question

I am required to produce a somewhat complicated excel workbook containing over 30 worksheets, each using information from several database tables to dynamically draw several tables per sheet, with formating. I have used c# in the past although I am unfamilar with its use in Excel. Any recomendations regarding to how to format and posistion cells dynamically from a database would be a great help.

Thanks in advance.

Answer 1

Visual Studio Tools for Office (VSTO) is your friend :) I use this all the time to build complex worksheets. This is way, way better than importing from CSV or using some kind of wacky DB connectivity.

Here are some links

• MSDN
• Blog
• Wikipedia

This is very easy to do, though there are a few oddities. I find it very effective to use the Excell MACRO recorder to record actions, then look at the source and re-impliment in C#. The object model is exactly the same between VB and C#.

VSTO is part of Visual Studio 2008. I looked on the product page, but I cannot determine if it comes with VS2008 C# Express Edition. The easiest thin to do is just download it and see :)

Answer 2

Your question sounds like you already understand the technology involved, so this answer is restricted to formatting & layout.

I did something very similar 10 years ago. It's been a while, but here's what I figured out.

1. Put all calculated & summary info on top. (including 'bottom line' totals)
2. Base charts on the totaled cells at the top of the sheets since they don't move.
3. Put all charts, summaries, etc on their own sheet.
4. If there are calculations involved, make each step of the process available so the user can step through them. This will prevent you spending countless hours proving your code calculates according to spec, if you don't do this, your 'black box' calculations will be questioned constantly.
5. If they don't want to see the raw data, only the calculations, then consider pushing it out to a hidden sheet.
6. Store a template workbook with your app, then just copy it so you can do all formatting in XL, not code.

One thing you really want to avoid is keeping track of different cell locations. You want to push values into cells which are always in the same location.

Answer 3

I find a very effective and often underused feature in Excel is the Web Query.

If you can present your data in a website, you can pre-build your Excel workbook with web queries set up to gather the data from the website on demand, which is presented in the form of tables in appropriate web pages.

You can add query string parameters for passing arguments to the web page and you can set auto-refresh, formatting preservation, etc. PivotTables/Charts can refer to the data and can made up to update upon data refresh with a few lines of VBA, for example.

This is often a much simpler solution than VSTO if it can meet the requirements.

Another option would be to use a third party Excel file generation assembly.