Improved Custom ‘Export Button’ on Crystal Reports for Visual Studio

Crystal report works great when the export feature is used to export reports to PDF and word, but the one real drawback is the ‘Export to Excel’ feature. No matter how well you in designing the crystal report, the export to excel output is always skewed. It is the way Crystal Report runtime engine maps the objects and places them in excel cells based on the (x,y) position of the original Crystal Objects in the .RPT file.

I did not like how the default export feature was working on crystal reports and also wanted new features like:

1. More uniform export to excel feature with less dummy rows and columns and more visually appealing output.   
2. Restrict the number of export features to only Excel, PDF and Microsoft word and remove all the unnecessary list of export options that the system provides.
3.  Control over Export file fonts, regardless of what font the Crystal report was developed on.
4. A friendly option in the ‘Export Successful ‘message box for users to click on a link which opens the export file for them without going to the folder where the report was exported.
5. Fixed width of rows for uniformity.

So let’s begin the process on steps:

Step 1: Assumptions

• You are using Visual Studio 2005 or higher.
• Crystal Report viewer is hosted in a windows form ‘ReportViewer.cs’
• The example is for the report being exported to excel but it can be used for export to other formats

Step 2: Load data to the report via XML or Memory Stream

 private ReportDocument myReport = null;  
 private void ReportViewer_Load(object sender, EventArgs e)   
 {  
    myReport = new ReportDocument();  
    myData = new DataSet();  
    try   
    {  
      switch (_inputType)   
      {  
         case DataInputType.ByteArray:  
            myData.ReadXml(new System.IO.MemoryStream(_reportData));  
            break;  
         case DataInputType.MemoryStream:  
            stream.Seek(0, SeekOrigin.Begin);  
            myData.ReadXml(_stream);  
            break;  
      }  
      //Load RPT file  
      myReport.Load(reportPath + _RPTName);  
      myReport.SetDataSource(myData);  
      crystalReportViewer1.ShowFirstPage();  
      crystalReportViewer1.ShowGroupTreeButton = true;  
      crystalReportViewer1.ReportSource = myReport;  
    }  
   catch (Exception ex)   
    {  
        ErrorHandler.PublishError(false, "Error displaying report.", ex, true, true);  
    }  
 }  

Step 3: Hide the default export button and add our custom clone on the same place

 //Loop through all the controls in the crystal viewer, Strip the default export button and put a custom export button, Items [0] being the first button which happens to be the export button.  
       
 foreach (Control control in crystalReportViewer1.Controls)   
 {  
    if (control is System.Windows.Forms.ToolStrip)  
    {  
      //Remove Default Export Button, items[0]  
      ToolStripItem tsOriginalItem = ((ToolStrip) control).Items [0];  
      ((ToolStrip) control).Items.RemoveAt(0);  
   
       //Add a clone of Custom Button  
       ToolStripItem tsNewItem = ((ToolStrip) control).Items.Add("");  
        
      //Give this button the same tag,image and tooltip text so it looks and smells like the default export button  
      tsNewItem.ToolTipText = tsOriginalItem.ToolTipText;  
      tsNewItem.Tag = tsOriginalItem.Tag;  
      tsNewItem.Image = tsOriginalItem.Image;  
   
      ((ToolStrip) control).Items.Insert(0, tsNewItem);  
          
      //Write new click event for the new export button  
      tsNewItem.Click += new EventHandler(tsNewItem_Click);  
    }  
 }  
   

Step 4: Define your own custom save file dialogue window when the export button in step 3 is clicked.

Cheat Sheet for Crystal Report Formulas

Conversion Functions:

Conversion Function	Description
CBool(number), CBool(currency)	Convert to Boolean.
CCur(number), CCur(string)	Convert to Currency.
CDbl(currency), CDbl(string), CDbl(boolean)	Convert to Number. Equivalent to ToNumber().
CStr()	Convert to String. Equivalent to ToText().
CDate(string), CDate(year, month, day), CDate(DateTime)	Convert to Date.
CTime(string), CTime(hour, min, sec), CDate(DateTime)	Convert to Time.
CDateTime(string), CDateTime(date), CDateTime(date, time), CDateTime(year, month, day)	Convert to DateTime.
CDateTime(year, month, day, hour, min, sec)	Convert to DateTime.
ToNumber(string), ToNumber(boolean)	Convert to a Number.
ToText()	Convert to String. Same as CStr().
IsDate(string), IsTIme(), IsDateTime()	Test a string for being a valid date/time.
IsNumber(string)	Test a string for being a valid number.
ToWords(number), ToWords(number, decimals)	Convert a number to its word equivalent.

·         Formula = ToWords(123.45) Result is “one hundred twenty-three 45 / 100”

 Math Functions:

Function Name	Description
Abs(number)	Return the absolute value.
Fix(number, decimals)	Return a number with a specified number of significant digits.
Int(number),� numerator \ denominator	Return the integer portion of a fractional number.
Remainder(numerator, denominator),	Return the remainder of dividing the numerator by the denominator.
numerator Mod denominator	Return the remainder of dividing the numerator by the denominator.
Round(number, decimals)	Round up a number with a specified number of significant digits.
Sgn(number)	Return a number's sign.
Sqr(number), Exp(number), Log(number)	The standard arithmetic functions.
Cos(number), Sin(number), Tan(number), Atn(number)	The standard scientific functions.

Convert display string to cost: 

CStr (CCur(ToNumber({Data.SupplyCost})/100))

New Line in formula : ChrW(13)

New Line in XML     :  

Alternate Row Color:

if RecordNumber mod 2 = 0 then crSilver else crNoColor

3-Formula Trick:     Init/Calc/Display:

Trick to create separate .cs file for global.asax file and using JavaScript to redirect to custom error page for unauthorized users (code 401)

There is no way to create a global.asax.cs page in vs 2008 or older versions of Visual studio. So we have a work around.

1. Add Global.asax file to your project, notice it won’t let you put the code in separate .cs file.

2. Add a new code file to your project "Global.cs". It will ask you to put the file in App_Code folder (Important step).You click "Yes" which will put the file in App_code folder.

3. Now open the global.cs file you just created and add

using System.Web.Configration;

4. Inherit the Global class from System.Web.HttpApplication like

public class Global : System.Web.HttpApplication

5. Now go back to your old Global.asax file and copy everything inside <Script> tag to this Global.cs file.
6. In Global.asax add Inherit property in Application tag to point to this new code file, remove everything from Global.asax file but only one line is left

<%@ Application Language="C#"  Inherits="Global" %> 

7. Inherits tag in the above line points to the .cs file we are using "Global"
8. You should be good to go and use the Global.cs file without any problem.


Now to create a custom error page for Unauthorized users (code 401/ code 401.2) simply add Application_EndRequest() function in Global.cs page and use JavaScript code to redirect self to custom error page by trapping the 401 Unauthorized code.
1. Create a custom error Page ( E.g. CustomError.cs)
2. Create authorization tag in web.config file to deny or allow users.
3. If the user’s access is denied the Application_EndRequest() function will try to capture the Context Response. We can all kinds of response like error code 401, 402, 404 for my example I am using 401.
4. If the code is indeed 401 (we only look for first 3 letters of the Response status ignoring all the other detail) we redirect the user to the custom error page with this line of code.

context.Response.Write("<script language=javascript>self.location='CustomError.aspx';</script>");

5. So the whole function will look like this:

protected void Application_EndRequest(Object sender, EventArgs e)
{
   HttpContext context = HttpContext.Current;
   if (context.Response.Status.Substring(0, 3).Equals("401"))
   {
      context.Response.ClearContent();
      context.Response.Write("<script  
      language=javascript>self.location='CustomError.aspx'; </script>");
    }
 }

6. You have successfully created a custom code file for Global.asax as well as created a custom error page for unauthorized users.