?
Solved

Directlly Export crystal report to an Excel file

Posted on 2006-03-20
10
Medium Priority
?
794 Views
Last Modified: 2008-01-09
Hi,

I am using VB and Crystal Report XI. May I know how can I export crystal report directly to excel once it's generated?

I know if I want it to be print, I can call this
CRViewer1.PrintReport

Is there anything that I can do to export to excel?

Please advice. Thanks.
0
Comment
Question by:cybeh
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 10

Expert Comment

by:Spykair
ID: 16233718
0
 
LVL 8

Accepted Solution

by:
dylanyee earned 1000 total points
ID: 16233759
Spykair's link will help if you are using RDC.
I have an example if you are using .net's ReportDocument class. Below is my code in c#, feel free to convert to VB.
the function will accept parameter from ReportDocument, ExportFormat ("pdf"/"word"/"html"/"excel"), the directory and the filename to export, and will return back the full path and filename that has already been exported.

public string ExportToFile(ReportDocument rptDoc, string ExportFormat, string tempDir, string FileName)
{
      string tempFileName = FileName + ".";
      string tempFileNameUsed = string.Empty;

      switch (ExportFormat)
      {
            case "pdf" :
                  tempFileName += "pdf";
                  rptDoc.ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                  break;
            case "word" :
                  tempFileName+= "doc";
                  rptDoc.ExportOptions.ExportFormatType = ExportFormatType.WordForWindows;
                  break;
            case "excel" :
                  tempFileName+= "xls";
                  rptDoc.ExportOptions.ExportFormatType = ExportFormatType.Excel;
                  break;
            case "html" :
            case "htm" :
                  tempFileName+= "htm";
                  rptDoc.ExportOptions.ExportFormatType = ExportFormatType.HTML40;
                  CrystalDecisions.Shared.HTMLFormatOptions hop = new CrystalDecisions.Shared.HTMLFormatOptions();
                  hop.HTMLBaseFolderName = tempDir;
                  hop.HTMLFileName = tempFileName;
                  rptDoc.ExportOptions.FormatOptions = hop;
                  break;
      }

      try
      {
            CrystalDecisions.Shared.DiskFileDestinationOptions dfo = new CrystalDecisions.Shared.DiskFileDestinationOptions();
            dfo.DiskFileName = tempDir + tempFileName;
            rptDoc.ExportOptions.DestinationOptions = dfo;
            rptDoc.ExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
            rptDoc.PrintOptions.PaperOrientation = OutputOrientation;
            

            rptDoc.Export();
            rptDoc.Close();
            rptDoc.Dispose();

            if (ExportFormat == "html" || ExportFormat == "htm")
            {
                  string[] fp = rptDoc.FilePath.Split("\\".ToCharArray());
                  string leafDir = fp[fp.Length-1];
                  leafDir = leafDir.Substring(0, leafDir.Length - 4);
                  tempFileNameUsed = string.Format("{0}\\{1}", leafDir, tempFileName);
            }
            else
                  tempFileNameUsed = tempFileName;
      }
      catch(Exception e)
      {
            this.Success = false;
            this.LastError = e.Message;
            throw(new Exception(e.Message));
      }

      return tempFileNameUsed;
}


dylan
0
 
LVL 1

Author Comment

by:cybeh
ID: 16233893
Really appreciate if someone can convert dylanyee code into VB. Thanks.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 total points
ID: 16234782
This is close

public ExportToFile( rptDoc as ReportDocument, ExportFormat as string , tempDir as string , FileName as string )
Dim tempFileName as string
Dim tempFileNameUsed as string

     tempFileName = FileName + "."
     tempFileNameUsed = ""

     If (ExportFormat = "pdf") then
               tempFileName = tempFileName  & "pdf"
               rptDoc.ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
     else if (ExportFormat = "word" ) then
               tempFileName = tempFileName  & "doc"
               rptDoc.ExportOptions.ExportFormatType = ExportFormatType.WordForWindows
      else if (ExportFormat = "excel" ) then
               tempFileName = tempFileName  &  "xls"
               rptDoc.ExportOptions.ExportFormatType = ExportFormatType.Excel
      end if

      dim  dfo as  new  CrystalDecisions.Shared.DiskFileDestinationOptions
          dfo.DiskFileName = tempDir + tempFileName
          rptDoc.ExportOptions.DestinationOptions = dfo
          rptDoc.ExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
          rptDoc.PrintOptions.PaperOrientation = OutputOrientation
         

          rptDoc.Export()
          rptDoc.Close()
          rptDoc.Dispose()

          tempFileNameUsed = tempFileName

     ExportToFile = tempFileNameUsed

end

mlmcc
0
 
LVL 1

Author Comment

by:cybeh
ID: 16235348
m_Report.ExportOptions.ExportFormatType = ExportFormatType.Excel

The of "ExportFormatType" of "ExportFormatType.Excel" gave error of variable not defined.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16235695
You may need to include additional dlls or VB may use different constants.  

If you delete Excel and the . then add the . do you get a list?

mlmcc
0
 
LVL 8

Expert Comment

by:dylanyee
ID: 16235933
I think ExportFormatType is reference from CrystalDecision.shared dll, I can't figure it out now since I am away from my office currently.

dylan
0
 
LVL 1

Author Comment

by:cybeh
ID: 16242690
"If you delete Excel and the . then add the . do you get a list?"

NO
0
 
LVL 8

Expert Comment

by:dylanyee
ID: 16243047
ExportFormatType is reference from CrystalDecisions.Shared.
CrystalDecisions.Shared.ExportFormatType
Could you try add a reference to CrystalDecisions.Shared.dll and add Import CrystalDecisions.Shared to your code?

dylan
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16366110
Glad i could help

mlmcc
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question