2506718 - How To: Export to Excel with GridLines enabled using Crystal Reports for Visual Studio .NET

SAP Knowledge Base Article - Public

2506718 - How To: Export to Excel with GridLines enabled using Crystal Reports for Visual Studio .NET

Symptom

Need sample code of setting gridlines on excel export using RAS SDK

Environment

Crystal Reports for Visual Studio

Resolution

private void lstExportFormatType_SelectedIndexChanged(object sender, EventArgs e)
{
    // This gets populated when you click on the export ENUM
    string ExportTypeSelected = lstExportFormatType.SelectedItem.ToString();

    if (ExportTypeSelected == "crReportExportFormatCrystalReports")
    #region RPT
    {
        // This works do not alter
        // this gets the report name and sets the export name to be the same less the extension
        string outputFileName = "";
        string MyRptName = rpt.FileName.ToString();
        outputFileName = MyRptName.Substring(9, rpt.FileName.Length - 9);
        outputFileName = outputFileName.Substring(0, (outputFileName.Length - 4)) + "1.rpt";

        try
        {
            if (File.Exists(outputFileName))
            {
                File.Delete(outputFileName);
            }

            CrystalDecisions.ReportAppServer.ReportDefModel.RPTExportFormatOptions RasRPTExpOpts = new RPTExportFormatOptions();

            try
            {
                RasRPTExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatCrystalReports);
            }
            catch (Exception ex)
            {
                btnSQLStatement.Text = "ERROR: " + ex.Message;
                //return;
            }

            // Set them now:
            //RasPDFExpOpts.CreateBookmarksFromGroupTree = false;
            //RasPDFExpOpts.EndPageNumber = 1;
            //RasPDFExpOpts.StartPageNumber = 1;

            CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions();
            exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatCrystalReports;
            exportOpts1.FormatOptions = RasRPTExpOpts;

            // And Export
            rptClientDoc.PrintOutputController.ExportEx(exportOpts1).Save(outputFileName, true);
            MessageBox.Show("Export to RPT Completed. NOTE: report is *1.RPT", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        catch (Exception ex)
        {
            btnSQLStatement.Text = "ERROR: " + ex.Message;
            return;
        }
        // This works do not alter
    }
    #endregion RPT

    if (ExportTypeSelected == "crReportExportFormatMSExcel")
    #region MSExcel
    {
        // This works do not alter
        // this gets the report name and sets the export name to be the same less the extension
        string outputFileName = "";
        string MyRptName = rpt.FileName.ToString();
        outputFileName = MyRptName.Substring(9, rpt.FileName.Length - 9);
        outputFileName = outputFileName.Substring(0, (outputFileName.Length - 3)) + "xls";

        try
        {
            if (File.Exists(outputFileName))
            {
                File.Delete(outputFileName);
            }

            CrystalDecisions.ReportAppServer.ReportDefModel.ExcelExportFormatOptions RasXLSExpOpts = new ExcelExportFormatOptions();
            RasXLSExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatMSExcel);
            // textBox1 = "Excel - BaseAreaGroupNumber:       " + RasXLSExpOpts.BaseAreaGroupNumber.ToString() + "\n";
            //textBox1 += "Excel - BaseAreaType:              " + RasXLSExpOpts.BaseAreaType.ToString() + "\n";
            //textBox1 += "Excel - FormulaExportPageAreaType: " + RasXLSExpOpts.ExportPageAreaPairType.ToString() + "\n";
            //textBox1 += "Excel - ExportPageBreaks:          " + RasXLSExpOpts.ExportPageBreaks.ToString() + "\n";
            //textBox1 += "Excel - ConstantColWidth:          " + RasXLSExpOpts.ConstantColWidth.ToString() + "\n";
            //textBox1 += "Excel - ConvertDatesToStrings:     " + RasXLSExpOpts.ConvertDatesToStrings.ToString() + "\n";
            //textBox1 += "Excel - StartPageNumber:           " + RasXLSExpOpts.StartPageNumber.ToString() + "\n";
            //textBox1 += "Excel - EndPageNumber:             " + RasXLSExpOpts.EndPageNumber.ToString() + "\n";
            //textBox1 += "Excel - ExportPageBreaks:          " + RasXLSExpOpts.ExportPageBreaks.ToString() + "\n";
            //textBox1 += "Excel - MRelativeObjectPosition:   " + RasXLSExpOpts.MaintainRelativeObjectPosition.ToString() + "\n";
            //textBox1 += "Excel - ShowGridlines:             " + RasXLSExpOpts.ShowGridlines.ToString() + "\n";
            //textBox1 += "Excel - UseConstantColWidth:       " + RasXLSExpOpts.UseConstantColWidth.ToString() + "\n";

            // Set them now:
            //RasXLSExpOpts.BaseAreaType = CrAreaSectionKindEnum.crAreaSectionKindPageHeader;
            //RasXLSExpOpts.UseConstantColWidth = false;
            RasXLSExpOpts.ShowGridlines = True;
            //RasXLSExpOpts.StartPageNumber = 3;
            //RasXLSExpOpts.EndPageNumber = 10;

            // Save the udpated info
            rptClientDoc.set_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatMSExcel, RasXLSExpOpts);

            CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions();
            exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatMSExcel;
            exportOpts1.FormatOptions = RasXLSExpOpts;

            // Show start time
            DateTime dtStart;
            TimeSpan difference;
            dtStart = DateTime.Now;
            btnReportObjects.Text += "Report Export Started: " + dtStart + "\r\n";
            // And Export
            rptClientDoc.PrintOutputController.ExportEx(exportOpts1).Save(outputFileName, true);
            difference = DateTime.Now.Subtract(dtStart);
            btnReportObjects.Text += "Report Export Completed in: " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + "\r\n";
            MessageBox.Show("Export to Excel Completed", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        catch (Exception ex)
        {
            btnSQLStatement.Text = "ERROR: " + ex.Message;
            return;
        }
        // This works do not alter
    }
    #endregion MSExcel

Keywords

.net, export to excel, grid lines, cr for vs , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

SAP Crystal Reports, developer version for Microsoft Visual Studio