Crystal Reports Excel export with worksheets

问题:

Is there a way to convince Crystal Reports to export a page / group / whatever to separate worksheets when exporting to Excel (Data Only)? I'm using the CR that came with VS2008 (version 10.5)

Thanks.

回答1:

According to the documentation you cannot export a report directly to multiple worksheets in a single Excel workbook.

When the limit of 65536 rows in Excel is reached though, the exporter does create a new worksheet, but you are not in control :)

update
To create your own Excel merger:

PRE: Make sure you have the Office (Excel) SDK libraries installed.
PRE: Place the files that need to be merged in a single directory.

In a VS2008 solution:

• Create a new empty Excel Workbook (variable: objNewWorkbook)
• Loop through the files in the directory (where you placed the Excel files) and for each item:
  1. Load the file as a Excel Workbook (variable: objWorkbookLoop)
     • Create a new Worksheet in objNewWorkbook (optionally: with the filename of objWorkbookLoop) (variable: objNewWorksheetLoop)
     • Copy the data from (probably sheet1 in) objWorkbookLoop to objNewWorksheetLoop
• Finally save objNewWorkbook to a file.

回答2:

One of the things everybody ignores is that excel automation is not an acceptable solution. Yes it works ( almost always) , but even Microsoft recommends to not use it for unattended execution : http://support.microsoft.com/kb/257757 The only safe way I know to export a crystal report to multiple worksheets is by creating a grouped report and burst it using R-Tag report manager. This tool is not using Excel automation so you can run your reports at any time and on the server but if you are currently using another software to run your reports you will need to switch to this one (it is not an extension).

I know this thread is an old one, but I can see links to it without a real answer. Hopefully this will help somebody.