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.
Submit an article or tip