Archive for the ‘Reports’ Category

Excel Repot From Data Table

February 23, 2010
Code for coverting Data table Execl Report

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Reflection;

namespace Common
{
 /// <summary>
 /// Download Excel
 /// </summary>
 public class DownloadExcel : System.Web.UI.Page
 {

 #region Methods (4)

 // Public Methods (3)

 /// <summary>
 /// Generics the list to data table.
 /// </summary>
 /// <param name="list">The list.</param>
 /// <returns></returns>
 public static DataTable GenericListToDataTable(object list)
 {
 DataTable dt = null;
 Type listType = list.GetType();
 if (listType.IsGenericType)
 {
 //determine the underlying type the List<> contains
 Type elementType = listType.GetGenericArguments()[0];

 //create empty table -- give it a name in case
 //it needs to be serialized
 dt = new DataTable(elementType.Name + "List");

 //define the table -- add a column for each public
 //property or field
 MemberInfo[] miArray = elementType.GetMembers(
 BindingFlags.Public | BindingFlags.Instance);
 foreach (MemberInfo mi in miArray)
 {
 if (mi.MemberType == MemberTypes.Property)
 {
 PropertyInfo pi = mi as PropertyInfo;
 dt.Columns.Add(pi.Name);
 }
 else if (mi.MemberType == MemberTypes.Field)
 {
 FieldInfo fi = mi as FieldInfo;
 dt.Columns.Add(fi.Name, fi.FieldType);
 }
 }

 //populate the table
 IList il = list as IList;
 foreach (object record in il)
 {
 int i = 0;
 object[] fieldValues = new object[dt.Columns.Count];
 foreach (DataColumn c in dt.Columns)
 {
 MemberInfo mi = elementType.GetMember(c.ColumnName)[0];
 if (mi.MemberType == MemberTypes.Property)
 {
 PropertyInfo pi = mi as PropertyInfo;
 fieldValues[i] = pi.GetValue(record, null);
 }
 else if (mi.MemberType == MemberTypes.Field)
 {
 FieldInfo fi = mi as FieldInfo;
 fieldValues[i] = fi.GetValue(record);
 }
 i++;
 }
 dt.Rows.Add(fieldValues);
 }
 }
 return dt;
 }

 /// <summary>
 /// Gets the date from string.
 /// </summary>
 /// <param name="strDate">The STR date.</param>
 /// <returns></returns>
 public static DateTime GetDateFromString(string strDate)
 {
 DateTime dt;
 try
 {
 Int32 intOADate = Int32.Parse(strDate);
 dt = DateTime.FromOADate(intOADate);
 }
 catch
 {
 try //try with dd/MM/yyyy
 {
 IFormatProvider culture = new CultureInfo("fr-FR", true);
 dt = DateTime.Parse(strDate, culture);
 }
 catch //try with MM/dd/yyyy
 {
 IFormatProvider culture = new CultureInfo("en-US", false);
 dt = DateTime.Parse(strDate, culture);
 }
 }
 return dt;
 }

 /// <summary>
 /// Function to export grid data to excel
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="entityList">The entity list.</param>
 /// <param name="hashTable">The hash table.</param>
 public void GetExcel<T>(List<T> entityList, Dictionary<int, string> hashTable) where T : class
 {

 ExportExcel(entityList, hashTable);

 }

 // Protected Methods (1)

 /// <summary>
 /// Exports the excel.
 /// </summary>
 /// <param name="entityList">The entity list.</param>
 /// <param name="hashTable">The hash table.</param>
 protected void ExportExcel(object entityList, Dictionary<int, string> hashTable)
 {

 DataTable dt = GenericListToDataTable(entityList);
 Response.Clear();
 Response.ClearContent();
 Response.ClearHeaders();
 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
 //Response.Clear();
 //Response.ContentType = "application/vnd.ms-excel";
 string sep = "";
 ICollection keyCollection = hashTable.Keys;
 int[] keys = new int[keyCollection.Count];
 keyCollection.CopyTo(keys, 0);
 for (int i = 0; i < dt.Columns.Count; i++)
 {

 Response.Write(sep + dt.Columns[i].ColumnName);
 sep = "\t";
 //excelTableColumn = excelTableColumn + "[" + hashTable[keys[i]] + "] text ,";
 }
 //foreach (DataColumn dc in dt.Columns)
 //{
 //    Response.Write(sep + dc.ColumnName);
 //    sep = "\t";
 //}
 Response.Write("\n");

 // int i;
 foreach (DataRow dr in dt.Rows)
 {
 sep = "";
 for (int i = 0; i < dt.Columns.Count; i++)
 {
 Response.Write(sep + dr[i].ToString());
 sep = "\t";
 }
 Response.Write("\n");
 }
 Response.End();

 }

 #endregion Methods

 }
}

The Above code you can be placed in common folder or App_code(freamwork 2.0) when you are creating report page
inherit this class to Report page like following code

/// <summary>
 ///
 /// </summary>
 public partial class DelayedVechiclesBodyTypeReport :  DownloadExcel
 {
 #region Private variables
//your code
 #endregion
 }

in Generate Report button Click write the following code :

#region Web Generated Events
 /// <summary>
 ///
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 protected void btnGenerateReport_Click(object sender, EventArgs e)
 {
 List<ReportDelayedVehicleEntity> delayedVehicleDetailsList;
 delayedVehicleDetailsList = reportService.GetDelayVechiclesBodyTypeReport(Convert.ToInt32( rdoBodyType.SelectedValue));
 Dictionary<int, string> excelHashTable = new Dictionary<int, string>();
 excelHashTable.Add(0, "BodyNo");
 excelHashTable.Add(1, "ModelCode");
 excelHashTable.Add(2, "SuffixCode");
 excelHashTable.Add(3, "ColorCode");
 excelHashTable.Add(4, "JigInDate");
 excelHashTable.Add(5, "FrameNo");
 excelHashTable.Add(6, "ASSPlanned_LO");
 excelHashTable.Add(7, "EngineNo");
 excelHashTable.Add(8, "CurrentStageID");
 excelHashTable.Add(9, "CarFamily");
 excelHashTable.Add(10, "LotCode");
 excelHashTable.Add(11, "SpecSheetNumber");
 excelHashTable.Add(12, "EngineModelcode");
 excelHashTable.Add(13, "StageName");
 excelHashTable.Add(14, "StandardDefectName");
 excelHashTable.Add(15, "Remark");
 excelHashTable.Add(16, "DelayedDays");
 excelHashTable.Add(17, "RootResponsibilityName");
 excelHashTable.Add(18, "RepairResponsibilityName");
 excelHashTable.Add(19, "TargetDate");

 GetExcel(delayedVehicleDetailsList, excelHashTable);
 }

 #endregion

Report Viewer Control

March 18, 2008

Steps for creating Web Control Library

from VS IDE Select FIle>New>Project>WebControlLibrary

As in following Image :

Web Control Lib


Try this code for creating Report Viewer Control to cs file:

//Author:Nissan .K. George
//Web Developer
//nissankg@yahoo.com

using System;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Data;
using Microsoft.Reporting.WebForms;
namespace DBReportViewer
{
[DefaultProperty(“DataTable”), ToolboxData(“<{0}:DBReportViewer runat=server></{0}:DBReportViewer>”)]

public partial class DBReportViewer : Microsoft.Reporting.WebForms.ReportViewer
{
[Bindable(true)]
[Category(“Appearance”)]
[DefaultValue(“”)]
[Localizable(true)]

#region “Properties”

private string objectTypeName;
[Bindable(true),
DefaultValue(“”)]

public string ObjectTypeName
{
get { return objectTypeName; }
set { objectTypeName = value; }
}

private DataTable DatatableSource;
public DataTable DataTableSource
{
get
{
return DatatableSource;
}

set
{
DatatableSource = value;
}
}
private Object objectSource;
public Object ObjectSource
{
get
{
return objectSource;
}

set
{
objectSource = value;
}
}

private string XmlDataSetName;

public string XMLDataSetName
{
get
{
return XmlDataSetName;
}

set
{
XmlDataSetName = value;
}
}
private string reportpath;

public string ReportPath
{
get
{
return reportpath;
}

set
{
reportpath = value;
base.LocalReport.ReportPath = value;

}
}

#endregion

#region Members overrides
//protected override void RenderContents(HtmlTextWriter output)
//{

// output.Write(Text);
//}

// METHOD:: OnLoad
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);

Type t = this.GetType();

}

// METHOD:: OnPreRender
protected override void OnPreRender(EventArgs e)
{
// Do as usual
base.OnPreRender(e);

}
public override void DataBind()
{

try
{
if ((XMLDataSetName == null) && (DatatableSource == null) && (objectSource == null))
{

base.LocalReport.DataSources.Add(new ReportDataSource());

}
else if ((XMLDataSetName == null) && (DatatableSource != null))
{

base.LocalReport.DataSources.Add(new ReportDataSource(“DB”, DatatableSource));

}
else if ((XMLDataSetName != null) && (DatatableSource != null))
{

base.LocalReport.DataSources.Add(new ReportDataSource(XMLDataSetName, DatatableSource));
}
else if ((ObjectSource != null) && (ObjectTypeName != null))
{

base.LocalReport.DataSources.Add(new ReportDataSource(ObjectTypeName, ObjectSource));
}

base.DataBind();

base.LocalReport.Refresh();
}
catch (Exception ex)
{
throw ex;
}
}

#endregion

}
}
after creating control add dll to toolkit then drag into page for use

use following code :

private void loadReports()
{
int allReopts = 1;
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();

MarketingReportView.LocalReport.ReportEmbeddedResource = “DB.Portal.Maintenance.MarketingReports.ReportTemplates.ActiveTrialReport.rdl”;

startDate = DateTime.Now.Date;
endDate = DateTime.Now.Date;
MarketingReportView.LocalReport.DataSources.Clear();

Sherston.Portal.BusinessData.Common.DataList<School> obj = SchoolService.GetSchoolsSubscriptionReports(SchoolService.ReportType.ActiveTrialUsersReport, startDate, endDate, allReopts, 0, 0);
MarketingReportView.ObjectSource = obj;

MarketingReportView.ObjectTypeName = “PortalDataSource”;
MarketingReportView.DataBind();

}

Export Reports into Diffrent File Formate using Crystal Reports Service

March 12, 2008

In this article I am going to explain , how to convert crystal reports to different file format using crystal reports service , The export file format that supported by our Crystal Reports as follows :

1) PDF

2) MS Word

3) MS Excel

4) HTML

5) RTF

please do the following steps :

Creating a class file ExportReports.cs

Export

Please place the following code in that file

public ExportReports()
{
//
// TODO: Add constructor logic here
//
}

public enum ReportFormats : int
{
None = 0,
PDF = 1,
MS_Word = 2,
MS_Excel = 3,
HTML = 4,
RTF = 5

}

public byte[] ExportReportsTo(DataList<T> dt, string fileName,

ReportFormats format)
{

CrystalDecisions.CrystalReports.Engine.ReportDocument doc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
//DataSet ds = (DataSet)context.Cache.Get(“ExportData”);
doc.Load(fileName);
doc.SetDataSource(dt);
CrystalDecisions.Shared.ExportOptions exportOpts = doc.ExportOptions;
if (format == ReportFormats.PDF)
{
exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
}
else if (format == ReportFormats.MS_Word)
{
exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.WordForWindows;

}
else if (format == ReportFormats.MS_Excel)
{
exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.Excel;

}
else if (format == ReportFormats.HTML)
{
exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.HTML40;

}
else if (format == ReportFormats.RTF)
{
exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.RichText;

}
exportOpts.ExportDestinationType = CrystalDecisions.Shared.

ExportDestinationType.NoDestination;
exportOpts.DestinationOptions = new CrystalDecisions.Shared.

DiskFileDestinationOptions();

// Response.Write(doc.ExportToStream(exportOpts.ExportFormatType));
byte[] _stream = new byte[1024];
MemoryStream _ms = new MemoryStream();

_ms = (MemoryStream)doc.ExportToStream(exportOpts.ExportFormatType);
return _ms.ToArray();
}

}

the parameter dt support following format :

1)Data set

2) Data Table

3)Data Reader

4) System.Collection.IEnumerable (in this example we used)

you can change the parameter according to your wish

After creating class file we can you this service in your web page (aspx)

before you should create rpt file (eg: NewSubscriptionReports.rpt)

Create Report

Use IDE to design crystal Report Templates :

IDE

Save this rpt file to a web folder then use the following code in the page

protected void btnExport_Click(object sender, EventArgs e)
{

yournamespace.ExportReports.ReportFormats format;
format = ExportReports.ReportFormats.None;
string strExt = “”;
if (ddlFormat.SelectedValue.Equals(“1”))
{
format = ExportReports.ReportFormats.PDF;
strExt = “Pdf”;
}
else if (ddlFormat.SelectedValue.Equals(“2”))
{
format = ExportReports.ReportFormats.MS_Word;
strExt = “Doc”;
}
else if (ddlFormat.SelectedValue.Equals(“3”))
{
strExt = “xls”;
format = ExportReports.ReportFormats.MS_Excel;

}
else if (ddlFormat.SelectedValue.Equals(“4”))
{
format = ExportReports.ReportFormats.HTML;
strExt = “HTML”;
}
else if (ddlFormat.SelectedValue.Equals(“5”))
{
strExt = “RTF”;
format = ExportReports.ReportFormats.RTF;
}

ExportReports exportReports = new ExportReports();
// System.IO.MemoryStream _ms = new System.IO.MemoryStream();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = “application/” + strExt;
HttpContext.Current.Response.AddHeader(“Content-Disposition”, “inline; filename=NewSubscriptionReports.” + strExt);

HttpContext.Current.Response.BinaryWrite(exportReports.ExportReportsTo

(SchoolService.GetSchoolsSubscriptionReports

(SchoolService.ReportType.NewSubscriptionReport, startDate, endDate, allReopts, 0, 0), Server.MapPath(“~/Maintenance/SubscriptionReports

/ReportTemplates/NewSubscriptionReports.rpt”), format));
HttpContext.Current.Response.End();

}

please specify the .rpt file path in calling function:

Server.MapPath(“~/Maintenance/SubscriptionReports/ReportTemplates/NewSubscriptionReports.rpt”),