• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 798
  • Last Modified:

Directlly Export crystal report to an Excel file

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
cybeh
Asked:
cybeh
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
SpykairCommented:
0
 
dylanyeeCommented:
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
 
cybehAuthor Commented:
Really appreciate if someone can convert dylanyee code into VB. Thanks.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
mlmccCommented:
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
 
cybehAuthor Commented:
m_Report.ExportOptions.ExportFormatType = ExportFormatType.Excel

The of "ExportFormatType" of "ExportFormatType.Excel" gave error of variable not defined.
0
 
mlmccCommented:
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
 
dylanyeeCommented:
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
 
cybehAuthor Commented:
"If you delete Excel and the . then add the . do you get a list?"

NO
0
 
dylanyeeCommented:
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
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now