Click here to Skip to main content
15,849,785 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I need some assistance in automating Excel workbooks, in particular, how to work with the Excel ListObject (Table) programmatically. I am using C#, VS 2010, dotNet 4.

I've found and played with various solutions to my problem, but they are all working via Excel addins. I am not building an addin, but a standalone solution that generates Excel workbooks through use of template workbooks.

I developed a custom reporting solution that is used to export result data from SQL databases (user selects DB) in a data analytics environment. First line of reporting happens through Excel workbooks, containing the actual result data within Excel tables (ListObjects), which is then connected to various Pivot charts and tables, and building up to a "dashboard". The whole solution is generic as reporting results are generated across a vast majority of analytics (500+). Results for analytics vary from 1 to 100,000 rows and between 10 to 100 columns. I tried various solutions in automating reporting, including OpenXML and default office dlls (interop etc).

Working with OpenXML I can create the require spreadsheets and results, but have to perform formatting on the cells, rows and columns though code. It also takes too long to export results as the cells has to be generated one at a time (which takes really long when working with results of 50 columns and 50 000 rows for example). I even tried ExtremeML (http://www.extrememl.com/[^]) which works perfectly on smaller results, but not so great on the larger sets. This is where I started using predefined formatted templates with Excel tables that’s already linked to the graphs and pivots etc.

Working through default interop ways, I can also generate the spreadsheets with results and have to perform formatting on the cells, rows and columns though code. Exporting through this is a bit faster than through OpenXML (especially on large results), but I cannot find a way to work with the Excel tables. (Except in addins which uses Global to access the various excel objects etc)

Please can someone point me in the right direction or provide me with a small solution in automating Excel ListObjects (tables). I need to work with Excel tables as this is the easiest way I know to set up proper template workbooks with already linked graphs and pivots etc, especially when working with dynamic (unknown) number or result records etc.
Posted
Comments
Maciej Los 9-Apr-11 7:18am    
How you save your data into Excel sheet? If you use templates, you don't need to format rows and columns.
Steven.Pinto2000 7-May-11 1:27am    
see i would recommend you that to retrieve data from the data base using odbc and then feed it in the template you have created it would be much more easier and faster than using com components
David Combrinck 9-May-11 1:49am    
Hi Steven, Please can you explain a bit more.
If you mean by using Excel itself (via the data menu and connecting to the external source) then this is exactly what I don't want to do since I need to automate this solution. If I have 100+ spreadsheets or templates, then doing each one of the spreadsheets through that method will take forever and unnecesary time for the user.
David Combrinck 9-May-11 2:05am    
Just thought about adding my initial solution i came up with some time ago. Using a app addin with a windows form on top of it. When the addin opens, Excel hides and it is then used like if you open an excel instance in a standalone.

The following link has more information where I posted some sample code etc.
http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/85aa6c06-d61b-450f-aac1-9abeba20726e

However, any other information will still be much appreciated Steven and anyone else.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900