.net – How to create Template based User Customisable reports in Excel
This will be a very challenging project, mostly because Excel isnt really a reporting tool the way that Crystal Reports is. Ive created a great many reports in Excel, and every one of them has been a custom solution, tailor-fit to the users needs. Creating a generic reporting tool in Excel will require a lot of gap-filling in Excels feature-set.
How to define report parameters in
There really arent report parameters in Excel, at least not in the same way there are for Crystal Reports. If you plan on using C# and VSTO, then you can create a form (or use the task pane) for the user to input parameters. You could also try using VBA/VSTO and use the native Excel data objects (QueryTables, ListObjects and PivotTables), which may provide you with some pre-built options for parameters. If you want the user to be able to create their own reports, then youll need to create some kind of report wizard to help them set up and manage these parameters.
How to generate multi page report
(when database returns dataset of more
than one record) ?
How to paginate
report like crystal report does when
results are to be displayed on
multiple page ?
Both of these are similar problems, if I understand them correctly. All of the pagination features you see in Crystal Reports (e.g., keep section together, repeat headings, etc.) will need to be coded in Excel, because they just dont exist. Because Excel doesnt really have anything analogous to Crystal Reports groupings, this would be a challenging issue to tackle, I would think. How do you define a region in Excel as something you would keep together when printing on a new page? How do you add blank spaces at the end of the previous page (Excel will typically try to scale the size)? The object model is there, and it certainly seems doable, just really time consuming and hard.
how to define Report header & footer
area in Excel to be displayed on
mulitple page report ?
This isnt actually too hard, as there are page header and footer objects you could use. You would have to manually update the values however, as you typically cant have dynamic information there (no formulas, no data objects, etc.). You could also use the Rows to repeat at top if you needed more flexibility.
In general, Excel is really the wrong tool for this kind of work. Excel can be a powerful reporting tool, but generally takes someone pretty knowledgeable in Excel, and some of the features are just not there even for the power users (without writing some hefty VBA).
Take a look at this:
It enables you to create an Excel template file according to your needs. The end user in the extreme case would only need to customize the template. Thats an easy thing to do. Customize to include or delete columns, labels, formulas, etc.
This solution appears to be perfect for end users who have knowledge of Excel & database query writing.