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.
LVL 1
cybehAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SpykairCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cybehAuthor Commented:
Really appreciate if someone can convert dylanyee code into VB. Thanks.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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

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

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

mlmcc
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.