Jan 17

Written by: Michael Washington
1/17/2012 6:41 PM 

Online Demo: http://lightswitchhelpwebsite.com/Demos/ExpenseReport.aspx

Download project: http://lightswitchhelpwebsite.com/Downloads.aspx

Note: The author has received compensation from ComponentOne for the following article:

In this article we will  cover a project that implements the ComponentOne Excel control, from the Studio for Silverlight suite, in Visual Studio LightSwitch. Specifically, it will demonstrate importing and exporting Microsoft Excel files. The sample project expands on the Expense Report application covered in the article: Using the ComponentOne FlexGrid Control in Visual Studio LightSwitch.

This project enables the following scenarios:

• Allow employees to use an Excel spread sheet to create their expense reports.
• Display the Excel spread sheet in the ComponentOne FlexGrid control, with all formatting and formulas, and allow it to be edited before importing.
• Import the Excel spread sheet into the Expense Report application.
• Export existing expense reports to the control and save them as Excel files.

Walk-Thru

When we open the application, we can create a new report, or open an existing one, by selecting it on the Dashboard screen.

We can click the Export To Excel button to open the report on the Excel screen.

When the report opens on the Excel screen in the ExcelBook control (that implements the ComponentOne FlexGrid control), we notice that it implements Excel formatting and formulas.

We click the Save button on the control to save the report.

This allows us to save the report as a normal Excel file.

We can open the file in Microsoft Excel and add, edit, and remove data.

We can return to the LightSwitch application and open the Excel file.

Any changes to the Excel file will show, and we can make further edits in the control if needed.

When we click Import, custom code determines if this is an existing report to update or a new report to insert (based on the report name, report date, and the user importing the report).

When the data is imported, we see that all normal LightSwitch business rules are processed.

Set-Up

To set-up the project, we install the ComponentOne Studio for Silverlight suite. It will automatically create shortcuts in our Windows Start Menu. We select Samples under Studio for Silverlight.

When the ComponentOne Sample Explorer opens, we select the ExcelBook sample and launch it.

When we launch the ExcelBook solution and load a sample Excel report, we see that it implements the ComponentOne FlexGrid for Silverlight control to display the Excel files. The ExcelBook sample contains a number of features such as filtering, sorting, grouping, copy and paste, redo and undo, and printing.

One of the most fascinating things about this sample is how it implements the Calculation Engine that allows the FlexGrid control to implement Excel formulas. The Calculation Engine is covered in detail here: http://our.componentone.com/2011/08/19/a-calculation-engine-for-flexgrid-part-2-silverlight/.

In LightSwitch, we create a Screen and add a Custom Control.

We reference the ExcelBook and FlexGridBook projects that make up the ExcelBook sample solution.

We also add references to the C1.Silverlight.FlexGrid.4 and C1.Silverlight.FlexGridFilter.4 assemblies because we will implement code later inside LightSwitch that will require them.

We select MainPage from the ExcelBook project as the control to insert on the Screen.

As covered in the article: Databinding A Complex Multi-Table Silverlight Control In LightSwitch: ComponentOne Scheduler, we create a public property in the code behind of the Silverlight control to enable programmatic access of the control in LightSwitch.

When we run the application and select the Excel screen, the control displays.

Creating a New Excel File in the Control

In the sample download, we have implemented a New Report button on the Excel screen.

This creates a blank expense report that, for example, could be given to employees to complete when they are on the road and may not have access to the LightSwitch application.

To create a valid expense report (that can be imported later), we will use the following rules:

• Cell A1 must contain Report Name: followed by a report name.
• Cell A2 must contain Report Date: followed by a valid date for the report.
• Starting at cell B5, the column must contain a valid expense date. 
• Starting at cell C5, the column must contain a valid expense description.
• Starting at cell D5, the column must contain a valid expense amount.
• A cell in column C must contain the word Total (so the import knows when to stop importing expenses).

The following code is used to implement the New Report button:

 

        // Create an Excel Book with one Sheet
        var book = new C1XLBook();
        C1.Silverlight.Excel.XLSheet sheet = book.Sheets[0];
        // set column widths
        sheet.Columns[0].Width = 3000;
        sheet.Columns[1].Width = 1000;
        sheet.Columns[2].Width = 2000;
        sheet.Columns[3].Width = 1000;
        // Set report name
        sheet[0, 0].Value = string.Format("Report Name: {0}",
            "My Report");
        // set report date
        sheet[1, 0].Value = string.Format("Report Date: {0}", 
            DateTime.Now.ToShortDateString());
        // make headers for expense report columns
        sheet[3, 1].Value = "Date";
        sheet[3, 2].Value = "Description";
        sheet[3, 3].Value = "Amount";
        // set total cell
        sheet[12, 2].Value = "Total";
        // set formula for total cell
        sheet[12, 3].Formula = "=SUM(D3:D12)";
        // **** Formatting  
        // create currency cells format
        var styleCurrency = new XLStyle(book);
        styleCurrency.Format = "$ .00";
        // Format a few rows with currency format
        for (int i = 4; i < 13; i++)
        {
            XLCell CurrencyCell = sheet[i, 3];
            CurrencyCell.Style = styleCurrency;
        }
        // create expense header cells format
        var styleText = new XLStyle(book);
        styleText.AlignHorz = XLAlignHorzEnum.Center;
        styleText.BackColor = Colors.LightGray;
        // Format the header cells
        sheet[3, 1].Style = styleText;
        sheet[3, 2].Style = styleText;
        sheet[3, 3].Style = styleText;
        // create total cell format
        var styleTotal = new XLStyle(book);
        styleTotal.AlignHorz = XLAlignHorzEnum.Right;
        styleTotal.BackColor = Colors.LightGray;
        styleTotal.Font = new XLFont("Arial", 12, true, false);
        // format the total cell
        sheet[12, 2].Style = styleTotal;
        Dispatchers.Main.BeginInvoke(() =>
        {
            // Create a memory stream
            using (MemoryStream memory = new MemoryStream())
            {
                // Save the book to the stream
                book.Save(memory);
                // load the book from the stream
                _flex.Load(memory);
            }
        });

 

Note: Access to the Excel formatting features is available only through the C1XLBook class. We must create the Excel spread sheet using that class and then load it into the C1FlexGridBook object that displays the spread sheet.

Importing an Excel File

When we open an existing Excel file and click the Import button, we need to determine if it is an existing report or a new one. If it is an existing report we need to update the report.

The first thing we do is gather the required values from the control:

 

            // Get the required values
            string strReportName = "";
            DateTime dtReportDate = new DateTime();
            int intTotalRow = 0;
            // Get Report Name
            string strRawReportName = Convert.ToString(_flex[0, 0]);
            strReportName = strRawReportName.Replace("Report Name: ", "");
            // Get Report Date
            string strRawReportDate = Convert.ToString(_flex[1, 0]);
            strRawReportDate = strRawReportDate.Replace("Report Date: ", "");
            DateTime.TryParse(strRawReportDate, out dtReportDate);
            // Get Total Row
            // Loop through all rows starting at row 4, looking for 'Total'
            for (int i = 4; i < _flex.Rows.Count; i++)
            {
                if (Convert.ToString(_flex[i, 2]) == "Total")
                {
                    intTotalRow = i;
                    break;
                }
            }

 

Next, we validate that we have all required fields:

 

            // Validate that all fields are found
            if (strReportName == ""
                || dtReportDate < new DateTime(1900, 1, 1)
                || intTotalRow == 0)
            {
                Dispatchers.Main.BeginInvoke(() =>
                {
                    // show error that we cannot import
                    MessageBox.Show("Cannot Import");
                });
                return;
            }

 

We attempt to locate the report, and if we cannot find it, we create a new one; otherwise, we open the existing report and delete all the existing expense items:

 

            // Locate an expense report that matches the current one
            this.ExpenseReport = (from ExpenseReport in 
                                      this.DataWorkspace.ApplicationData.ExpenseReports
                                      .GetQuery().Execute()
                                  where ExpenseReport.UserName == this.Application.User.Name
                                  where ExpenseReport.ReportName == strReportName
                                  where ExpenseReport.ReportDate == dtReportDate
                                  select ExpenseReport).FirstOrDefault();
            if (this.ExpenseReport == null)
            {
                // Create a new Expense Report
                this.ExpenseReport = new ExpenseReport();
                this.ExpenseReport.ReportDate = dtReportDate;
                this.ExpenseReport.ReportName = strReportName;
            }
            else
            {
                // Update Expense Report
                // First, Delete all existing Expense Report Details
                var colReportDetails = from ReportDetails in this.ExpenseReport.ReportDetails
                                       select ReportDetails;
                foreach (var item in colReportDetails)
                {
                    item.Delete();
                }
            }

 

Now, we import the expense line items:

 

            // Import the Report Details
            // Get the Expense Details from the Excel control
            for (int i = 4; i < intTotalRow; i++)
            {
                DateTime dtExpenseDetailDate = new DateTime();
                decimal dExpenseDetailAmount = 0.00M;
                // Gather Raw values
                var strRawDate = Convert.ToString(_flex[i, 1]);
                var strRawAmount = Convert.ToString(_flex[i, 3]);
                // Cast the values
                DateTime.TryParse(strRawDate, out dtExpenseDetailDate);
                Decimal.TryParse(strRawAmount, out dExpenseDetailAmount);
                // Get the description
                var strExpenseDetailDescription = Convert.ToString(_flex[i, 2]);
                // Validate that all fields are found
                if (strExpenseDetailDescription != ""
                    && dtExpenseDetailDate > new DateTime(1900, 1, 1)
                    && dExpenseDetailAmount > 0)
                {
                    // Add Expense Detail 
                    var objReportDetails = this.ExpenseReport.ReportDetails.AddNew();
                    objReportDetails.ExpenseDate = dtExpenseDetailDate;
                    objReportDetails.ExpenseDescription = strExpenseDetailDescription;
                    objReportDetails.ExpenseRequestedAmount = dExpenseDetailAmount;
                }
            }
            // Save the Expense Report
            this.Save();

 

Finally, we open the report in the Report Details screen (or switch to it if it is already open), and refresh the data (in case the screen is already open):

 

            // Open the Expense Report in the Details Screen
            Application.ShowExpenseReportDetail(this.ExpenseReport.Id);
            // Get a reference to the ExpenseReport Details screen 
            Microsoft.LightSwitch.Client.IActiveScreen 
                ExpenseReportDetailScreen = Application.ActiveScreens
                .Where(a => a.Screen is ExpenseReportDetail).FirstOrDefault();
            if (ExpenseReportDetailScreen != null)
            {
                ExpenseReportDetailScreen.Screen.Details.Dispatcher
                    .BeginInvoke(() =>
                {
                    // Refresh the ExpenseReportDetail Screen 
                    ((ExpenseReportDetail)ExpenseReportDetailScreen.Screen)
                        .Refresh();
                });
            }

 

Exporting an Existing Report to the ExcelBook Control

The final feature we will implement will allow us to open an existing expense report record and export it to the ExcelBook control.

We add an Integer property to the Excel screen called ExpenseReportID. We ensure that Is Required is not checked because we still want to allow the option to open the screen without passing an ExpenseReportID.

We then select Is Parameter in the properties of the ExpenseReportID property.

This will cause LightSwitch to automatically create a ShowExcel(Nullable<int> ExpenseReportID) method that will allow another screen to open the Excel screen and optionally pass an ExpenseID.

We also add an ExpenseReport entity and an associated ReportDetails collection to the screen.

Note: It is important that we add this entity and collection to the screen so that the LightSwitch code that we will create will have entities and collections to use when loading the data. While it is possible to load the data using entities and collections created entirely in code, we will run into challenges related to the dual-threading that LightSwitch uses to process user interface code and background processes. Adding the entities and collections to the screen and hydrating them provides less friction when implementing our custom code.

In the InitializeDataWorkspace method of the Excel screen, we implement code that will load the expense report if passed an ExpenseReportID:

 

        partial void Excel_InitializeDataWorkspace(List<IDataService> saveChangesTo)
        {
            // If an ExpenseReportID was passed...
            if (this.ExpenseReportID != null)
            {
                // Load the Expense Report into the ExpenseReport property
                this.ExpenseReport =
                    this.DataWorkspace.ApplicationData.
                    ExpenseReports_SingleOrDefault(this.ExpenseReportID);
                // Refresh the ReportDetails query
                // to pull in all the data now
                this.ReportDetails.Refresh();
                // Set the name of the Tab
                this.SetDisplayNameFromEntity(this.ExpenseReport);
            }
        }

 

Notice the ReportDetails.Refresh() line. I have found that without implementing this in the InitializeDataWorkspace method, the report details would not load, even when you implement code in other methods to explicitly load the data.

The operation to open the Excel screen and load a spread sheet requires us to interact with the ExcelBook control without the normal process of having a screen fully load and waiting for an end-user to click a button.

We must wire-up a method to notify us when the screen is loaded and the ExcelBook (that implements the FlexGrid control) is available:

 

    partial void Excel_Created()
    {
        // Get an instance of the FlexGrid Control
        c1FlexGridProxy = this.FindControl("SilverlightControl");
        // Create a handler to fire when the control is actually available            
        c1FlexGridProxy.ControlAvailable +=
            new EventHandler<ControlAvailableEventArgs>
                (c1FlexGridProxy_ControlAvailable);
    }

 

When the method is called, it allows us to then perform the import:

 

    void c1FlexGridProxy_ControlAvailable
        (object sender, ControlAvailableEventArgs e)
    {
        // Get an instance of the Silverlight Control
        System.Windows.Controls.UserControl objUserControl =
            e.Control as System.Windows.Controls.UserControl;
        // Get an instance of the FlexGrid Control
        _flex = objUserControl.FindName("_flex") as C1FlexGridBook;
        // Remove handler           
        c1FlexGridProxy.ControlAvailable -=
            new EventHandler<ControlAvailableEventArgs>
                (c1FlexGridProxy_ControlAvailable);
        // If an ExpenseReportID was passed...
        if (this.ExpenseReportID != null)
        {
            // Load the Expense Report
            LoadExpenseReport();
        }
    }

 

The LoadExpenseReport method resembles the New Report code with this additional code to gather the expense details:

 

        // load the expense details
        int intHighestRow = 4;
        foreach (var ExpenseDetail in ReportDetails)
        {
            var ExpenseDate = ExpenseDetail.ExpenseDate.ToShortDateString();
            var ExpenseDescription = ExpenseDetail.ExpenseDescription;
            var ExpenseAmount = ExpenseDetail.ExpenseRequestedAmount;
            sheet[intHighestRow, 1].Value = ExpenseDate;
            sheet[intHighestRow, 2].Value = ExpenseDescription;
            sheet[intHighestRow, 3].Value = ExpenseAmount;
            intHighestRow++;
        }

 

The code required for the Export To Excel button on the Excel screen is very simple:

 

        partial void ExportToExcel_Execute()
        {
            // Save the Expense Report
            this.Save();
            // Open the Expense Report in the Excel Screen
            Application.ShowExcel(this.ExpenseReport.Id);
        } 

 

Big Functionality - Small Price

The ComponentOne ExcelBook is a feature-rich control that still requires a considerable amount of back-end features to be implemented to process the Excel files that it loads. Using it with LightSwitch allows us to implement these requirements in a fraction of the time because most of the required features, such as screen management and saving and loading records from the database, are already built-into LightSwitch.

 

The Code

Online Demo: http://lightswitchhelpwebsite.com/Demos/ExpenseReport.aspx

Download project: http://lightswitchhelpwebsite.com/Downloads.aspx

Resources

Online help for ComponentOne Excel: http://helpcentral.componentone.com/nethelp/c1excelSL/

Notes

• When you download the code, you may need to right-click on the .zip file and select Unblock, so you can fully build the project.
• When performing an import of an Excel file, close any report detail tabs first for it to properly open the tab after the import (the import will always import correctly). Due to the async calls, a surprisingly large amount of code was required in a number of places to properly open the correct tab, so I left it out because it made it hard to understand the important code that is the point of the article.