locked
How to export crystal report data in excel format or in excel sheet .

    Question

  • Hi Sir..

    I am working in desktop application using C#.net.In which i am using crystal report,dataset and crystalreportviewer .i want to know how convert crystal report data in excel format or in a new excel worksheet.

    Randeep Chauhan

    Wednesday, July 13, 2011 12:44 PM

Answers

  • Hello,

    Here's a few examples to get you going. It will only export one type so comment/uncomment out which ever one you want to keep and then use the Teli-Sense to see the various options for each type:

     

          CrystalDecisions.Shared.PdfRtfWordFormatOptions pdfOpts = CrystalDecisions.Shared.ExportOptions.CreatePdfRtfWordFormatOptions();
          CrystalDecisions.Shared.ExcelDataOnlyFormatOptions excelOptsDataOnly = CrystalDecisions.Shared.ExportOptions.CreateDataOnlyExcelFormatOptions();
          CrystalDecisions.Shared.ExcelFormatOptions excelOpts = CrystalDecisions.Shared.ExportOptions.CreateExcelFormatOptions();
          CrystalDecisions.Shared.MicrosoftMailDestinationOptions mailOpts = CrystalDecisions.Shared.ExportOptions.CreateMicrosoftMailDestinationOptions();
          CrystalDecisions.Shared.DiskFileDestinationOptions diskOpts = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions();
          CrystalDecisions.Shared.ExportOptions exportOpts = new CrystalDecisions.Shared.ExportOptions();
    
          rpt.Load(@"c:\reports\World Sales Report.rpt");
    
          pdfOpts.UsePageRange = false;
          exportOpts.ExportFormatOptions = pdfOpts;
    
          excelOptsDataOnly.UseWorksheetFunctionsForSummaries = true;
          excelOptsDataOnly.MaintainColumnAlignment = true;
    
    
          string MyRptName = rpt.FileName.ToString();
          MyRptName = MyRptName.Substring(MyRptName.LastIndexOf(@"\") + 1, (rpt.FileName.Length - 3) - (MyRptName.LastIndexOf(@"\") + 1)) + "pdf";
          MyRptName = MyRptName.Substring(MyRptName.LastIndexOf(@"\") + 1, (rpt.FileName.Length - 3) - (MyRptName.LastIndexOf(@"\") + 1)) + "xls";
    
          //diskOpts.DiskFileName = "World Sales Report.pdf";
          diskOpts.DiskFileName = MyRptName;
    
          mailOpts.MailMessage = "See attached Action Plan";
          mailOpts.MailSubject = "Action Plan";
          mailOpts.MailToList = "yourmailaddress@msn.com";
          mailOpts.UserName = "yourmailaddress@msn.com";
          mailOpts.Password = "yourmailaddressPassword";
          exportOpts.ExportDestinationOptions = mailOpts;
    
          //exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.MicrosoftMail;
          exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
    
          //exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
          exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.ExcelRecord;
    
          rpt.Export(exportOpts);

    Don


    Don Williams
    Wednesday, July 13, 2011 5:53 PM
  • You can check following code

    1. Add following namespace

     

    using CrystalDecisions.CrystalReports.Engine;
    

    Then use following code to Convert it Excel

     

     

    using (ReportClass rptH = new ReportClass())
       {
        rptH.FileName = @"C:/Report/crJournal.rpt"; //Your rpt file path 
            // if you put your rpt file on Bin then you need to write only rpt file name
        rptH.Load();
        rptH.SetDataSource( ds );// Provide Dataset for report : Ds is DataSet
        rptH.ExportToDisk(ExportFormatType.Excel, "Give Output file path");
    
       }
    

    Hope it will help you.

     

     

     


    Hasibul Haque,MCC,MCPD blog.e-rains.com
    Thursday, July 14, 2011 6:19 AM
  • Hi Randeep,

    Try to study this code:

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using CrystalDecisions.CrystalReports.Engine;
    using CrystalDecisions.Shared;
    using System.Web.Mail;
    
    namespace CrystalReportExportToExcel
    {
     public partial class Form1 : Form
     {
      ReportDocument reportDocu;
    
      public Form1()
      {
       InitializeComponent();
      }
    
      private void btnExport_Click(object sender, EventArgs e)
      {
       try
       {
        ExportOptions exportOptions;
    
        DiskFileDestinationOptions diskFileDestinationOptions = new DiskFileDestinationOptions();
        ExcelFormatOptions formatTypeOptions = new ExcelFormatOptions();
    
        //destination folder...
        diskFileDestinationOptions.DiskFileName = "C:\\Crystal export\\to excel\\CrystalToExcel.xls";
        exportOptions = reportDocu.ExportOptions;
        exportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
        exportOptions.ExportFormatType = ExportFormatType.Excel;
        exportOptions.DestinationOptions = diskFileDestinationOptions;
        exportOptions.FormatOptions = formatTypeOptions;
        reportDocu.Export();
        MessageBox.Show("Successfully Exported...");
       }
       catch (Exception ex)
       {
        MessageBox.Show(ex.ToString());
       }
    
      }
    
      private void btnLoad_Click(object sender, EventArgs e)
      {
       reportDocu = new ReportDocument();
    
       TableLogOnInfo tableLogoninfo = new TableLogOnInfo();
       ConnectionInfo connectionInfo = new ConnectionInfo();
       Tables CrTables;
    
       //location of crystalreport1.rpt
       reportDocu.Load("C:\\Documents and Settings\\Hardz\\My Documents\\Visual Studio 2008\\Projects\\CrystalReportExportToExcel\\CrystalReportExportToExcel\\CrystalReport1.rpt");
    
       connectionInfo.ServerName = "IT-Hardz";
       connectionInfo.DatabaseName = "Northwind";
       //crConnectionInfo.UserID = "Hardz";
       //crConnectionInfo.Password = "Password";
    
       CrTables = reportDocu.Database.Tables;
       foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
       {
        tableLogoninfo = CrTable.LogOnInfo;
        tableLogoninfo.ConnectionInfo = connectionInfo;
        CrTable.ApplyLogOnInfo(tableLogoninfo);
       }
    
       crystalReportViewer1.ReportSource = reportDocu;
       crystalReportViewer1.Refresh();
      }
     }
    }
    
    

     


    Hope it helps.

    Thanks!

    Hardz


    Thursday, July 14, 2011 8:05 AM

All replies

  • On 7/13/2011 8:44 AM, Randeep Chauhan wrote:
    > Hi Sir..
    >
    > I am workingin  desktop applicationusing  C#.net.In which i amusing  crystal report,dataset and crystalreportviewer
     > .i want to know how convert crystal report datain  excel format orin
     anew  excel worksheet.
    >
     
    Doesn't the Crystal Report have an export to Excel option when viewing
    the report?
     
    • Proposed as answer by Hardz T L Friday, July 15, 2011 9:01 AM
    Wednesday, July 13, 2011 1:10 PM
  • Hello,

    Here's a few examples to get you going. It will only export one type so comment/uncomment out which ever one you want to keep and then use the Teli-Sense to see the various options for each type:

     

          CrystalDecisions.Shared.PdfRtfWordFormatOptions pdfOpts = CrystalDecisions.Shared.ExportOptions.CreatePdfRtfWordFormatOptions();
          CrystalDecisions.Shared.ExcelDataOnlyFormatOptions excelOptsDataOnly = CrystalDecisions.Shared.ExportOptions.CreateDataOnlyExcelFormatOptions();
          CrystalDecisions.Shared.ExcelFormatOptions excelOpts = CrystalDecisions.Shared.ExportOptions.CreateExcelFormatOptions();
          CrystalDecisions.Shared.MicrosoftMailDestinationOptions mailOpts = CrystalDecisions.Shared.ExportOptions.CreateMicrosoftMailDestinationOptions();
          CrystalDecisions.Shared.DiskFileDestinationOptions diskOpts = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions();
          CrystalDecisions.Shared.ExportOptions exportOpts = new CrystalDecisions.Shared.ExportOptions();
    
          rpt.Load(@"c:\reports\World Sales Report.rpt");
    
          pdfOpts.UsePageRange = false;
          exportOpts.ExportFormatOptions = pdfOpts;
    
          excelOptsDataOnly.UseWorksheetFunctionsForSummaries = true;
          excelOptsDataOnly.MaintainColumnAlignment = true;
    
    
          string MyRptName = rpt.FileName.ToString();
          MyRptName = MyRptName.Substring(MyRptName.LastIndexOf(@"\") + 1, (rpt.FileName.Length - 3) - (MyRptName.LastIndexOf(@"\") + 1)) + "pdf";
          MyRptName = MyRptName.Substring(MyRptName.LastIndexOf(@"\") + 1, (rpt.FileName.Length - 3) - (MyRptName.LastIndexOf(@"\") + 1)) + "xls";
    
          //diskOpts.DiskFileName = "World Sales Report.pdf";
          diskOpts.DiskFileName = MyRptName;
    
          mailOpts.MailMessage = "See attached Action Plan";
          mailOpts.MailSubject = "Action Plan";
          mailOpts.MailToList = "yourmailaddress@msn.com";
          mailOpts.UserName = "yourmailaddress@msn.com";
          mailOpts.Password = "yourmailaddressPassword";
          exportOpts.ExportDestinationOptions = mailOpts;
    
          //exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.MicrosoftMail;
          exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
    
          //exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
          exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.ExcelRecord;
    
          rpt.Export(exportOpts);

    Don


    Don Williams
    Wednesday, July 13, 2011 5:53 PM
  • You can check following code

    1. Add following namespace

     

    using CrystalDecisions.CrystalReports.Engine;
    

    Then use following code to Convert it Excel

     

     

    using (ReportClass rptH = new ReportClass())
       {
        rptH.FileName = @"C:/Report/crJournal.rpt"; //Your rpt file path 
            // if you put your rpt file on Bin then you need to write only rpt file name
        rptH.Load();
        rptH.SetDataSource( ds );// Provide Dataset for report : Ds is DataSet
        rptH.ExportToDisk(ExportFormatType.Excel, "Give Output file path");
    
       }
    

    Hope it will help you.

     

     

     


    Hasibul Haque,MCC,MCPD blog.e-rains.com
    Thursday, July 14, 2011 6:19 AM
  • Hi Randeep,

    Try to study this code:

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using CrystalDecisions.CrystalReports.Engine;
    using CrystalDecisions.Shared;
    using System.Web.Mail;
    
    namespace CrystalReportExportToExcel
    {
     public partial class Form1 : Form
     {
      ReportDocument reportDocu;
    
      public Form1()
      {
       InitializeComponent();
      }
    
      private void btnExport_Click(object sender, EventArgs e)
      {
       try
       {
        ExportOptions exportOptions;
    
        DiskFileDestinationOptions diskFileDestinationOptions = new DiskFileDestinationOptions();
        ExcelFormatOptions formatTypeOptions = new ExcelFormatOptions();
    
        //destination folder...
        diskFileDestinationOptions.DiskFileName = "C:\\Crystal export\\to excel\\CrystalToExcel.xls";
        exportOptions = reportDocu.ExportOptions;
        exportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
        exportOptions.ExportFormatType = ExportFormatType.Excel;
        exportOptions.DestinationOptions = diskFileDestinationOptions;
        exportOptions.FormatOptions = formatTypeOptions;
        reportDocu.Export();
        MessageBox.Show("Successfully Exported...");
       }
       catch (Exception ex)
       {
        MessageBox.Show(ex.ToString());
       }
    
      }
    
      private void btnLoad_Click(object sender, EventArgs e)
      {
       reportDocu = new ReportDocument();
    
       TableLogOnInfo tableLogoninfo = new TableLogOnInfo();
       ConnectionInfo connectionInfo = new ConnectionInfo();
       Tables CrTables;
    
       //location of crystalreport1.rpt
       reportDocu.Load("C:\\Documents and Settings\\Hardz\\My Documents\\Visual Studio 2008\\Projects\\CrystalReportExportToExcel\\CrystalReportExportToExcel\\CrystalReport1.rpt");
    
       connectionInfo.ServerName = "IT-Hardz";
       connectionInfo.DatabaseName = "Northwind";
       //crConnectionInfo.UserID = "Hardz";
       //crConnectionInfo.Password = "Password";
    
       CrTables = reportDocu.Database.Tables;
       foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
       {
        tableLogoninfo = CrTable.LogOnInfo;
        tableLogoninfo.ConnectionInfo = connectionInfo;
        CrTable.ApplyLogOnInfo(tableLogoninfo);
       }
    
       crystalReportViewer1.ReportSource = reportDocu;
       crystalReportViewer1.Refresh();
      }
     }
    }
    
    

     


    Hope it helps.

    Thanks!

    Hardz


    Thursday, July 14, 2011 8:05 AM