Introduction

A Basic guide for creating reports in excel using DevExpress. This will show you how to create a custom class that will create excel documents in stead of the traditional Reports provided by DevExpress.

Using the code

Now the best way to create a Report that will create a excel sheet will need the following classes.

The first and foremost is the form layout. This class will provide any and all info needed to put into the report parameters. The class needs to be non-persistent to allow multiple access 'points', and the basic constructors.

[NonPersistent]
[CreatableItem(false)]
[ImageName("Action_Export_ToExcel")]
[ModelDefault("Caption", "Testing Report")]
public class ExampleReportForm : XPCustomObject
public ExampleReportForm(Session session) : base(session) { }
public override void AfterConstruction() { base.AfterConstruction()

Then what you will need for the parameters. Like lets say for example two dates (From and To).

Note:

I only use this as example now but in the rest of the document I will show basic example where there are no parameters but only a button getting set data.

In this example I thought to put a twist in where you automatically get the 'To Date' 7 days after the 'From Date'

[RuleRequiredField("RuleRequired.Example.FromDate", "From Date is required")]
[ImmediatePostData]
public DateTime FromDate
{
    get
    {
        return _FromDate;
    }
    set
    {
        if (SetPropertyValue("FromDate", ref _FromDate, value) && !IsLoading && (_ToDate == DateTime.MinValue || _ToDate < _FromDate))
        {
            ToDate = FromDate.AddDays(7);
            OnChanged("ToDate");
        }
    }
}

[RuleRequiredField("RuleRequired.Example.ToDate", "To Date is required")]
[ImmediatePostData]
public DateTime ToDate
{
    get
    {
        return _ToDate;
    }
    set
    {
        if (SetPropertyValue("ToDate", ref _ToDate, value) && !IsLoading && (ToDate.CompareTo(FromDate) < 0))
        {
            SetPropertyValue("ToDate", ref _ToDate, FromDate);
        }
    }
}

Then you will need to create a class to handle the non-persistent class as DevExpress would not handle it by default. Now as you will see i used Method 'OnCustomShowNavigationItem' for the purpose to make devexpress display the non-persistent class as it would any other class.

public abstract class ShowNonPersistentObjectDetailViewFromNavigationControllerBase<nonpersistentobjecttype> : ViewController where NonPersistentObjectType : XPCustomObject
{
    private const string DefaultReason = "ShowNonPersistentObjectDetailViewFromNavigationControllerBase is active";
    public ShowNonPersistentObjectDetailViewFromNavigationControllerBase()
    {
        TargetObjectType = typeof(NonPersistentObjectType);
    }
    
    protected override void OnFrameAssigned()
    {
        base.OnFrameAssigned();
        Frame.GetController<shownavigationitemcontroller>().CustomShowNavigationItem += OnCustomShowNavigationItem;
    }
    
    protected override void OnActivated()
    {
        base.OnActivated();
        UpdateControllersState(false);
    }

    protected override void OnDeactivated()
    {
        base.OnDeactivated();
        UpdateControllersState(true);
    }
    protected virtual void UpdateControllersState(bool flag)
    {
        //Frame.GetController<detailviewcontroller>().Active[DefaultReason] = flag;
        Frame.GetController<modificationscontroller>().Active[DefaultReason] = flag;
        Frame.GetController<deleteobjectsviewcontroller>().Active[DefaultReason] = flag;
        Frame.GetController<newobjectviewcontroller>().Active[DefaultReason] = flag;
        Frame.GetController<filtercontroller>().Active[DefaultReason] = flag;
        Frame.GetController<viewnavigationcontroller>().Active[DefaultReason] = flag;
        Frame.GetController<recordsnavigationcontroller>().Active[DefaultReason] = flag;
        Frame.GetController<refreshcontroller>().Active[DefaultReason] = flag;
    }
    
    void OnCustomShowNavigationItem(object sender, CustomShowNavigationItemEventArgs e)
    {
        if (e.ActionArguments != null)
        {
            if (e.ActionArguments.SelectedChoiceActionItem != null)
            {
                //Debug.WriteLine(e.ActionArguments.SelectedChoiceActionItem.Id + " " + TargetObjectType.Name);
                if (e.ActionArguments.SelectedChoiceActionItem.Id == (TargetObjectType.Name + "_DetailView"))
                {
                    XPObjectSpace os = (XPObjectSpace)Application.CreateObjectSpace();
                    NonPersistentObjectType obj = CreateNonPersistemObject(os);
                    CustomizeNonPersistentObject(obj);
                    DetailView dv = Application.CreateDetailView(os, obj);
                    e.ActionArguments.ShowViewParameters.CreatedView = dv;
                    CustomizeShowViewParameters(e.ActionArguments.ShowViewParameters);
                    e.Handled = true;
                }
            }
        }
    }
    
    protected virtual NonPersistentObjectType CreateNonPersistemObject(XPObjectSpace objectSpace)
    {
        return objectSpace.CreateObject<nonpersistentobjecttype>();
    }
    
    protected virtual void CustomizeShowViewParameters(ShowViewParameters parameters)
    {
        parameters.Context = TemplateContext.ApplicationWindow;
        parameters.TargetWindow = TargetWindow.Current;
        ((DetailView)parameters.CreatedView).ViewEditMode = DevExpress.ExpressApp.Editors.ViewEditMode.Edit;
    }
    
    protected virtual void CustomizeNonPersistentObject(NonPersistentObjectType obj){ }

Using above class i will now show the creation of the Navigation Control, this is basically just unique to the report to call on the above class, this allows you to be able to handle and see the non-Persistant class(es if you have more, they will all use the above code int the same way as the code to follow), using the ReportForm Class.

namespace Example.Module.Controllers
public class ExampleNavigationController : ShowNonPersistentObjectDetailViewFromNavigationControllerBase<examplereportform>
{ 
    protected override void CustomizeNonPersistentObject(ExampleReportForm obj)
    base.CustomizeNonPersistentObject(obj);
}

protected override void CustomizeShowViewParameters(ShowViewParameters parameters)
{
    base.CustomizeShowViewParameters(parameters);
}

protected override void UpdateControllersState(bool flag)
{
    base.UpdateControllersState(flag);
    Frame.GetController<devexpress.expressapp.validation.allcontextsview.showallcontextscontroller>().Action.Active.SetItemValue("Hide", false);
}

Now the viewController is obviously needed. Here I will use the results I will get in the final class, zip it and allow it to be downloaded as an .xls document. So create a ViewController and place a button on it with the following properties.

In the TargetObjectType use the dropdown to select your ReportForm (ExampleReportForm). Then obviously the (name), Caption, Category etc. as you please.

Now for the Execute code... I just did a basic query here where i get static data. (after all this is only a basic training article ;-))

Session session = ((XPObjectSpace)View.ObjectSpace).Session;
using (ExampleReport report = new ExampleReport())
{
    TblPerson per = session.FindObject<tblpersonnel>(CriteriaOperator.Parse("OID = 23"));// Get Only set person's detail
    string tempFileName = report.generateReport(per, null, session);
    
    if (tempFileName != null)
        {
        string fileName = String.Format(@"Example-{0}.zip", String.Format("{0:yyyy-MM-dd_HH_mm_ss}", DateTime.Now)).Replace(" ", "");
            tempFileName = FileEditor.CompressFile(tempFileName, fileName);
        Page page = null;
        
        if (Frame.Template is Page)
            page = (Page)Frame.Template;
        else
        {
            page = ((Control)Frame.Template).Page;
            string script = String.Format(@"<script>var openedWindow = window.open('{0}', 'xls');</script>", tempFileName);
            page.ClientScript.RegisterStartupScript(GetType(), "clientScript", script);
        }
    }
}

private void RunExampleReport_Execute(object sender, SimpleActionExecuteEventArgs e)
{

Now for the fun part where all the actual logic lies within. This will be in sets and not as a whole as above..The class must be an IDisposable.

public class ExampleReport : IDisposable 
public string generateReport(TblPerson user, UnitOfWork workUnit, Session session)
    {
       string xlsTemplate = String.Format(@"{0}ExampleReport.xlsx", Globals.xlsFilePath); 
       DirectoryInfo outputDir = new DirectoryInfo(String.Format(@"{0}", Globals.otherFilePath));

Now I used a global class (Globals.xlsFilePath and Globals.otherFilePath) for the path way so you will just need to do your own thing for the path.

Now for the templates to hold the info as needed

string tempFileName = null;
string webTempFileName = null;
if (File.Exists(xlsTemplate))
{
    try
    {
        if ((session ?? workUnit) != null)
        {
            string fileName = String.Format(@"ExampleReport-{0}.xlsx", String.Format("{0:yyyy-MM-dd_HHmmss}", DateTime.Now)).Replace(" ", "");
            tempFileName = String.Format(@"{0}{1}", Globals.otherFilePath, fileName);
            webTempFileName = String.Format(@"{0}{1}", Globals.webTempFilePath, fileName);

Now for the Actual code that will populate and get the values as passed through with the controller. I setup an XPCollection to get all the data into a collection with a query to the db for the data.

using (ExcelPackage excel = new ExcelPackage(new FileInfo(xlsTemplate), true))
{
    XPCollection test = new XPCollection(workUnit ?? session, typeof(ProTask), CriteriaOperator.Parse("ReportedBy = ? && Status = ? && SysStatus <> ?", user, Status.Done, SystemStatus.Done));
    ExcelWorksheet sheet = excel.Workbook.Worksheets["Testing"];
    if (test != null && test.Count > 0)
    {
        int row = 3;
        foreach (JobRef job in test)
        {
           int column = 1;
           if (job!= null)
           {
                sheet.Cells["A3:D3"].Copy(sheet.Cells[String.Format("A{0}:D{0}", row)]);
                sheet.Cells[row, column++].Value = String.Format("{0} {1}", test.Assign.FirstName, test.Assigned.Surname);
                sheet.Cells[row, column++].Value = test.Subject;
                sheet.Cells[row++, column].Value = test.Description;
           }
        }
           Byte[] arr = excel.GetAsByteArray();
           File.WriteAllBytes(tempFileName, arr);
    }
return workUnit != null ? tempFileName : webTempFileName;

Here is what the end result will look like for the idisposable class

using DevExpress.Data.Filtering;
using DevExpress.Xpo;
using OfficeOpenXml;
using OwnNameSpace.Module.Classes.ProTaskClasses;
using OwnNameSpace.Module.CommonFunctions;
using System;
using System.IO;

namespace OwnNameSpace.Module.Classes.Reports.TestingReport
{
      public class TestingReport : IDisposable 
	  {
		public TestingReport() { }
		
		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(this);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing) { }
		}
		
		~TestingReport()
		{
			Dispose(false);
		}
		
		public string generateReport(TblPersonnel user, UnitOfWork workUnit, Session session)
		{
			string xlsTemplate = String.Format(@"{0}TestingReport.xlsx", Globals.xlsFilePath);
			DirectoryInfo outputDir = new DirectoryInfo(String.Format(@"{0}", Globals.otherFilePath));
			if (!outputDir.Exists)
				throw new Exception(String.Format(@"Folder: {0} does not exist! Cannot create temporary file", Globals.otherFilePath));

            if (user == null)
				throw new Exception("User supplied to TestingReport.generateReport() is null");

            if (workUnit == null && session == null)
				throw new Exception("UnitOfWork and Session supplied to TestingReport.generateReport() is null");

            string tempFileName = null;
			string webTempFileName = null;

			if (File.Exists(xlsTemplate))
			{
				try
				{
					if ((session ?? workUnit) != null)
					{
						string fileName = String.Format(@"TestingReport-{0}.xlsx", String.Format("{0:yyyy-MM-dd_HHmmss}", DateTime.Now)).Replace(" ", "");
						tempFileName = String.Format(@"{0}{1}", Globals.otherFilePath, fileName);
						webTempFileName = String.Format(@"{0}{1}", Globals.webTempFilePath, fileName);
						using (ExcelPackage excel = new ExcelPackage(new FileInfo(xlsTemplate), true))
						{
							using (XPCollection test = new XPCollection(workUnit ?? session, typeof(taskClass), CriteriaOperator.Parse("AssignedBy = ? && Status = ? && SysStatus <> ?", user, UserStatus.Completed, SystemStatus.Completed)))
							{
								ExcelWorksheet sheet = excel.Workbook.Worksheets["Testing"];
								if (test != null && test.Count > 0)
								{
									int row = 3;
									foreach (taskClass task in test)
									{
										int column = 1;
										if (task != null)
										{
											sheet.Cells["A3:D3"].Copy(sheet.Cells[String.Format("A{0}:D{0}", row)]);
											sheet.Cells[row, column++].Value = String.Format("{0} {1}", task.employeeDetails.FirstName, task.employeeDetails.Surname);
											sheet.Cells[row, column++].Value = task.Subject;
											sheet.Cells[row++, column].Value = task.Description;
										}
									}

									Byte[] arr = excel.GetAsByteArray();
									File.WriteAllBytes(tempFileName, arr);
								}
							}
						}
					}
				}
				catch (Exception ex)
				{
					throw new Exception(String.Format("Message:{1}{0}{0}StackTrace:{2}", Environment.NewLine, ex.Message, ex.StackTrace));
				}
			}
			else
				throw new Exception(String.Format("Could not access template: {0}", xlsTemplate));
			
			return workUnit != null ? tempFileName : webTempFileName;
		}
	}
}

Points of Interest

For the sheet data I used a nifty tool so that I can leave the entire template blank and only leave the header and first line in the template. Now I can format the entire row where all the data will be collected and everything will only copy over to next line as needed. Thus by worksheet will only be as big as needed and nothing more nor less. SO there is no need for the formatting going on long after data is completed, it just looks unprofessional :-).
The tool I used is a build in excel tool in VS. (In the example)

sheet.Cells["A3:D3"].Copy(sheet.Cells[String.Format("A{0}:D{0}", row)]);

History

This is my very first Article and since I had very little to no help (google or otherwise) I thought it a good idea to post my success on the matter thus far for those who might have the same questions, dilemma.

Creating Excel Report in DevExpress XAF