How-To, Samples, and Such

1/27/2010Getting The Bits Swinging in the Business

Read Part 1 & Part 2 of this series if you haven't already.

Last entry I worked up the basic origins and destinations for the data we have. Now we have to start turning this into something real, something concrete. The following processes include a whole soup of acronyms and other cryptic vocabulary. The most common thing I will probably use is the ETL acronym, which stands for Extract, Transform, and Load. This is the process of bridging the data to the various originations and destination and moving that data appropriately.

In the last entry I finalized the originations and destinations as shown below;

Originations
Excel & Access (Office 2007)	.mdb, .xlsx, and .csv/.txt data stores
Internal Account Software (IAS)	This one is a prospective can of worms. Proprietary layouts, de-normalized & normalized data, and all sorts of redundant, non-atomic data. This sounds like an accounting package right? :p
Webtrends Analytics Data Exchange Web Services (DX)	Webtrends web services provide REST style architecture, with the ability for data to be retrieved in XML, JSON, HTML, or other formats (we can add more if need be, just let us know).
Point of Sale System (POS)	This system provides two daily exports, one at 6:00am and one at noon for processing. The export format is *.csv.

Destination
SSRS	SQL Server Reporting Services, with the core underlying data stored in SQL Server.

In my previous entry you may have noticed that I had posted SSIS with the Destination list. Being one that corrects themselves when mistaken, I took it out, as it does not belong there. The SSIS is our tool that will perform the ETL functionality for this project.

At this point we are finally going to get into the dirty bits of these pieces of technology, and how we need to tie them together. I am going to attack them over the next few entries based on the order in the lists above. The first item, is the Excel & Access 2007 customer relations listings from sales. Here?s a description and a few shots of what this thing looks like.

The access database is setup with a very simple relational data schema. This is shown below (click image for larger view).

You can see there are pretty standard pieces of data, in a generally normalized (3rd form for the most part) structure. This is fine.

Next is a shot of the data entry screen for adding opportunities. There are respective screens for customers and employees. Everything needed for a basic customer list & tracking basic things. Nothing too extravagant here either. Again, all is fine.

Below is a simple report that shows the available opportunities that are open.

Another report showing the forecasts.

Below is another forecast sliced grid.

So all that seems normal enough. But the processes are what makes things tricky. If everyone just managed sales from the database, all would be right in the world. The first thing that breaks this is that each sales person enters their sales during the day and other information in a spreadsheet that is not linked to the underlying database. Someone each morning puts the previous days sales information. This of course, breaks down data integrity. Below is a sample sheet that is used each day.

One thing that Excel is used for, that doesn?t break reports is the lists of prospective customers to call. As shown below.

Now that we have a break down of the Excel & Access Customer Relations Management Software, I will move onto the other pieces of technology in the next entry. This is the data point that has the most prospective data risk, so I put it at the top of the list to cover first. After I cover each of the systems, we will move into the architecture of the system overall. So keep reading, more juicy bits to come.

1/15/2010Encryption and Decryption Code Bits

I recently had to do some simple encryption and decryption for password storage. I wanted to document this so a blog entry seemed in order. So without any yammering here's the simple class I created.

public static class EncryptionHelper

        private const string CryptographyKey = "CryptKey";

        // The Initialization Vector for the DES encryption routine

        private static readonly byte[] iv =

            new byte[] { 220, 13, 41, 29, 1, 63, 73, 9 };

        /// <summary>

        /// Encrypts provided string parameter

        /// </summary>

        public static string Encrypt(string s)

            if (string.IsNullOrEmpty(s)) return string.Empty;

            byte[] buffer = Encoding.ASCII.GetBytes(s);

            var des = new TripleDESCryptoServiceProvider();

            var md5 = new MD5CryptoServiceProvider();

            des.Key = md5.ComputeHash(Encoding.ASCII.GetBytes(CryptographyKey));

            des.IV = iv;

            string result = Convert.ToBase64String(

                des.CreateEncryptor().TransformFinalBlock(

                    buffer, 0, buffer.Length));

            return result;

        /// <summary>

        /// Decrypts provided string parameter

        /// </summary>

        public static string Decrypt(string s)

            if (string.IsNullOrEmpty(s)) return string.Empty;

            byte[] buffer = Convert.FromBase64String(s);

            var des = new TripleDESCryptoServiceProvider();

            var md5 = new MD5CryptoServiceProvider();

            des.Key = md5.ComputeHash(Encoding.ASCII.GetBytes(CryptographyKey));

            des.IV = iv;

            string result = Encoding.ASCII.GetString(

                des.CreateDecryptor().TransformFinalBlock(

                    buffer, 0, buffer.Length));

            return result;

So simple enough. Hope that is helpful to anyone interested in a super easy encrypt & decrypt example. : )

1/6/2010ASP.NET MVC Scorecard Web Application - Part 3

In this entry I want to cover a helper I wrote and some other bits to simplify creating charts for the scorecard application.

One of the things I wanted to do was get rid of the excessive amount of code needed in the view to generate various charts. Since at some point I wanted to be able to dynamically create charts based on user input, I figured what better time than now to start chiseling out a helper class for displaying the charts.

Take the pie chart I created in the last entry. The code in the view looked like this.

using (Chart chartPie = new Chart())

    double[] yValues = (double[])ViewData["TopCountryCounts"];

    string[] xValues = (string[])ViewData["TopCountries"];

    Title t = new Title("Pie Chart Representation",

        Docking.Top,

        new System.Drawing.Font("Verdana, Helvetica, Sans-Serif", 14, System.Drawing.FontStyle.Bold),

        System.Drawing.Color.FromArgb(26, 59, 105));

    chartPie.Titles.Add(t);

    chartPie.ChartAreas.Add("Default");

    // create a couple of series

    chartPie.Series.Add("Default");

    chartPie.Series["Default"].Points.DataBindXY(xValues, yValues);

    // Set Doughnut chart type

    chartPie.Series["Default"].ChartType = SeriesChartType.Pie;

    // Set labels style

    chartPie.Series["Default"]["PieLabelStyle"] = "Inside";

    // Set Doughnut radius percentage

    chartPie.Series["Default"]["DoughnutRadius"] = "40";

    // Explode data point with label "USA"

    chartPie.Series["Default"].Points[3]["Exploded"] = "true";

    chartPie.Width = 400;

    chartPie.Height = 300;

    chartPie.Page = this;

    HtmlTextWriter writer = new HtmlTextWriter(Page.Response.Output);

    chartPie.RenderControl(writer);

At 31 lines of code, I was not really stoked about this. A lot of this, such as creating the title, the chart area, and having a series was something that would be needed by most charts. With that I went to work writing a set of tests to test the helper class.

public const string title = "Chart Title";

public double[] YValues = {3};

public string[] XValues = {"X Value"};

[TestMethod]

public void InstantiateChartHelper()

    ChartHelper chartHelper = new ChartHelper("title", YValues, XValues, SeriesChartType.Pie);

    Assert.IsNotNull(chartHelper);

This first test and default values, that I assumed I would use for the other tests, got me a nice skeleton class & constructor. I used ReSharper to flesh it out a little and then went to writing the other tests. Each contributing a bit more functionality to the overall class. I have excluded the remaining tests from this blog entry, but they will be available when I provide the solution for download (that means keep reading and stay tuned). The class however, that ended up with is below.

public class ChartHelper

    public ChartHelper(string chartTitle, IEnumerable<double> seriesValues, IEnumerable<string> seriesKeys, SeriesChartType chartType)

        // Setup defaults.

        System.Drawing.Font font = new System.Drawing.Font("Verdana, Helvetica, Sans-Serif", 14, System.Drawing.FontStyle.Bold);

        System.Drawing.Color color = System.Drawing.Color.FromArgb(26, 59, 105);

        // Title

        Title title = new Title(chartTitle, Docking.Top, font, color);

        // Chart Area

        ChartArea chartArea = new ChartArea("DefaultChartArea");

        chartArea.Area3DStyle.Enable3D = true;

        // Series

        Series series = new Series("DefaultSeries");

        series.Points.DataBindXY(seriesKeys, seriesValues);

        series.ChartType = chartType;

        ResultingChart = new Chart();

        ResultingChart.Titles.Add(title);

        ResultingChart.ChartAreas.Add(chartArea);

        ResultingChart.Series.Add(series);

    public Chart ResultingChart { get; set; }

As you can see, a nice simple class. With this class I was then able to use to reduce my lines of code to 9 lines (including the multi-line instantiation, which I suppose could be one line, leaving me with 6).

Scorecard.Views.ChartHelper chartHelper = new Scorecard.Views.ChartHelper("Pie Chart Representation",

   (double[])ViewData["TopCountryCounts"],

   (string[])ViewData["TopCountries"],

   SeriesChartType.Pie);

Chart chartPieTwo = chartHelper.ResultingChart;

chartPieTwo.Page = this;

HtmlTextWriter writer1 = new HtmlTextWriter(Page.Response.Output);

chartPieTwo.RenderControl(writer1);

This now generates the following pie chart.

There are a few more defaults I want to set though, so I went ahead and added a logic section for pie charts as shown below, with the switch view to provide a bit of factory style magic.

ResultingChart = new Chart();

// Setup defaults.

System.Drawing.Font font = new System.Drawing.Font("Verdana, Helvetica, Sans-Serif", 14, System.Drawing.FontStyle.Bold);

System.Drawing.Color color = System.Drawing.Color.FromArgb(26, 59, 105);

// Title

Title title = new Title(chartTitle, Docking.Top, font, color);

ResultingChart.Titles.Add(title);

// Chart Area

ChartArea chartArea = new ChartArea("DefaultChartArea") {Area3DStyle = {Enable3D = true}};

ResultingChart.ChartAreas.Add(chartArea);

// Series

Series series = new Series("DefaultSeries");

series.Points.DataBindXY(seriesKeys, seriesValues);

series.ChartType = chartType;

ResultingChart.Series.Add(series);

// Legend

Legend legend = new Legend("DefaultLegend");

ResultingChart.Legends.Add(legend);

switch (chartType)

    case SeriesChartType.Bar:

        break;

    case SeriesChartType.Column:

        break;

    case SeriesChartType.Pie:

        series["PieLabelStyle"] = "Inside";

        ResultingChart.Legends[0].Docking = Docking.Bottom;

        ResultingChart.Legends[0].Enabled = true;

        break;

    case SeriesChartType.Funnel:

        break;

    case SeriesChartType.Line:

        break;

    default:

        throw new ArgumentOutOfRangeException("chartType");

ResultingChart.Width = 400;

ResultingChart.Height = 300;

Notice I also reorganized where some things where instantiated, such as the Chart Object, and organized the order according to each thing I needed to have for the chart. Once I did this I ran and rendered the chart, which I was happy with for now.

At this point I went back in and added some more charts to my scorecard view. While I did this I noticed one last thing I ought to refactor.

<table>

<tr>

<td>

<%

        Scorecard.Views.ChartHelper chartHelper = new Scorecard.Views.ChartHelper("Top Countries",

           (double[])ViewData["TopCountryCounts"],

           (string[])ViewData["TopCountries"],

           SeriesChartType.Pie);

        Chart chartPieTwo = chartHelper.ResultingChart;

        // Explode data point with label "USA"

        chartPieTwo.Series["DefaultSeries"].Points[3]["Exploded"] = "true";

        chartPieTwo.Page = this;

        HtmlTextWriter writer = new HtmlTextWriter(Page.Response.Output);

        chartPieTwo.RenderControl(writer);

%>

</td>

<td>

<%

        chartHelper = new Scorecard.Views.ChartHelper("View Cart Trend",

           (double[])ViewData["LineValues"],

           (string[])ViewData["TopEngines"],

           SeriesChartType.Line);

        Chart lineChart = chartHelper.ResultingChart;

        lineChart.Page = this;

        lineChart.RenderControl(writer);

%>

</td>

</tr>

<tr>

<td>

<%

        chartHelper = new Scorecard.Views.ChartHelper("Yesterday's Page Views",

         (double[])ViewData["ColumnStats"],

         (string[])ViewData["ColumnStatHeaders"],

         SeriesChartType.Column);

        Chart columnChart = chartHelper.ResultingChart;

        columnChart.Page = this;

        columnChart.RenderControl(writer);

%>

</td>

<td>

<%

        double[] theValues = (double[]) ViewData["ColumnStats"];

        double[] newValues = new double[]{0,0,0,0};

        int count = 0;

        foreach(double d in theValues)

            newValues[count] += d*DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month) +

                                DateTime.Now.Month + DateTime.Now.Millisecond;

            count++;

        chartHelper = new Scorecard.Views.ChartHelper("Current Month Page Views",

    newValues,

    (string[])ViewData["ColumnStatHeaders"],

    SeriesChartType.Bar);

        Chart barChart = chartHelper.ResultingChart;

        barChart.Page = this;

        barChart.RenderControl(writer);

%>

</td>

</tr>

</table>

I was assigning the page and rendering the control at every single section within my table. I didn't need to do that, so I went back and added a render method to the ChartHelper Class.

public void RenderChart(Page page)

    ResultingChart.Page = page;

    HtmlTextWriter writer = new HtmlTextWriter(page.Response.Output);

    ResultingChart.RenderControl(writer);

With a small change to the view I can now render each chart with one line of code.

chartHelper.RenderChart(this);

Now that puts everything in a better situation. Until the next part. Happy coding.

Posted by: Adron
Posted on: 1/6/2010 at 5:02 AM
Tags: asp.net mvc, mvc, asp.net, charts, mscharts, scorecard
Categories: How-To, Samples, and Such
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (4) | Post RSS RSS comment feed

12/31/2009ASP.NET MVC Scorecard Web Application - Part 2

Alright, time to add some actual functionality to this thing. First I want a report showing up on the scorecard page that has already been created. I don't really care what it displays, just want to get it working and displaying. I will get one report working and then pull up others and connect them with actual data from the Model.

First things first I grabbed the MS Charting controls, which you will need to follow along with this section of code. I assume for the rest of this entry that the charting controls are installed.

First off I created some nice fake data in a DataTable. The object I created is below. This is located in the Models path for now. Just to get this mocked up.

public class ReportSampleData

    public ReportSampleData()

        SampleData = new DataTable("Adron's Sample Data");

        SampleData.Columns.Add(new DataColumn("Col1"));

        SampleData.Columns.Add(new DataColumn("Col2"));

        SampleData.Columns.Add(new DataColumn("Col3"));

        SampleData.Columns.Add(new DataColumn("Col4"));

        DataRow dr1 = SampleData.NewRow();

        dr1[0] = "Sample Set 1";

        dr1[1] = 14;

        dr1[2] = 13;

        dr1[3] = 10;

        DataRow dr2 = SampleData.NewRow();

        dr2[0] = "Sample Set 2";

        dr2[1] = 15;

        dr2[2] = 13;

        dr2[3] = 12;

        DataRow dr3 = SampleData.NewRow();

        dr3[0] = "Sample Set 2";

        dr3[1] = 17;

        dr3[2] = 11;

        dr3[3] = 9;

        SampleData.Rows.Add(dr1);

        SampleData.Rows.Add(dr2);

        SampleData.Rows.Add(dr3);

    public DataTable SampleData { get; set; }

Once I created the fake data, I dropped in the chart control into the Display.aspx view under the Scorecard Directory of the views.

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>

<%@ Register Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">

    Scorecard

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

    <form id="form1" runat="server">

    <h2>

        Scorecard

    </h2>

<%

        using (Chart chartColumns = new Chart())

            chartColumns.Width = 412;

            chartColumns.Height = 296;

            chartColumns.RenderType = RenderType.ImageTag;

            chartColumns.Palette = ChartColorPalette.None;

            Title t = new Title("Column Bar Chart",

                Docking.Top,

                new System.Drawing.Font("Verdana, Helvetica, Sans-Serif", 14, System.Drawing.FontStyle.Bold),

                System.Drawing.Color.FromArgb(26, 59, 105));

            chartColumns.Titles.Add(t);

            chartColumns.ChartAreas.Add("Series 1");

            // create a couple of series

            chartColumns.Series.Add("Series 1");

            chartColumns.Series.Add("Series 2");

            // add points to series 1

            foreach (int value in (List<int>)ViewData["Chart"])

                chartColumns.Series["Series 1"].Points.AddY(value + DateTime.Now.Second);

            // add points to series 2

            foreach (int value in (List<int>)ViewData["Chart"])

                chartColumns.Series["Series 2"].Points.AddY(value + DateTime.Now.Minute);

            chartColumns.Legends.Add("Legend1");

            chartColumns.BorderSkin.SkinStyle = BorderSkinStyle.None;

            // Render chart control

            chartColumns.Page = this;

            HtmlTextWriter writer = new HtmlTextWriter(Page.Response.Output);

            chartColumns.RenderControl(writer);

%>

    </form>

</asp:Content>

Once that was finished the final step is to toss in the controller the view data.

public ActionResult Display()

    ViewData["Chart"] = new List<int>() {4, 6, 12, 3, 11};

    return View("Display");

Now run the app and check out the page. You should see a chart like that shown below. (Click for larger image)

That covers the second part of this series. I will have another addition up soon, tying even more bits together.

Posted by: Adron
Posted on: 12/31/2009 at 5:06 AM
Tags: asp.net mvc, mvc, asp.net, charts, mscharts, scorecard
Categories: How-To, Samples, and Such
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (12) | Post RSS RSS comment feed

12/29/2009ASP.NET MVC Scorecard Web Application - Part 1

There have been a lot of requests for MVC based scorecards, so I am going to kick off a new series of wiring up an MVC scorecard site. This first part will simply cover setting up the basic site and skeleton framework using the default ASP.NET MVC Project.

Start off by starting a new ASP.NET MVC Project. Since I will be creating tests for this walk through, be sure to add a unit test project also. I am just using the default MS-test libraries for now, but feel free to apply whatever test framework you would like.

To create additional views and controllers we'll add a unit test for a new controller. I will name this new controller ScorecardController, so I have added a test file called ScorecardControllerTest.cs. The first test is just the standard action for the appropriate method actions and returning the view, as follows. I added a similar test for the Index method also.

[TestClass]

public class ScorecardControllerTest

    [TestMethod]

    public void Scorecard()

        ScorecardController scorecardController = new ScorecardController();

        var result = scorecardController.Scorecard() as ViewResult;

        Assert.IsNotNull(result);

    [TestMethod]

    public void Index()

        ScorecardController scorecardController = new ScorecardController();

        var result = scorecardController.Index() as ViewResult;

        Assert.IsNotNull(result);

Once the skeleton implementation is made to get green lights on these tests, a controller will be created with the following code.

public class ScorecardController : Controller

    public ActionResult Index()

        return View();

    public ActionResult Scorecard()

        return View();

At this point I need to create some tests for what is expected in the view, but I will get to that once a bit more of the skeleton of the site is done. As soon as I wrote these tests, based on where they are located in my folder structure I decided that I need to do a quick refactor of the name. Having a Scorecard/Scorecard path just seemed stupid, so I renamed the Scorecard.aspx to Display.aspx and changed the method name appropriately. If you are following along, the subsequent code will be using the renamed page/method, etc.

The next thing I wanted to do was to get the view coming up appropriately, so first things first, new tests.

[TestMethod]

public void ScorecardViewExists()

    ScorecardController scorecardController = new ScorecardController();

    var result = scorecardController.Display() as ViewResult;

    Assert.AreEqual("Display", result.ViewName);

First of course it fails, but with the appropriate change shown, all runs green again.

public ActionResult Display()

    return View("Display");

So after this I added the following tests, controller actions, and views.

[TestMethod]

public void Select()

    ScorecardController scorecardController = new ScorecardController();

    var result = scorecardController.Select() as ViewResult;

    Assert.IsNotNull(result);

[TestMethod]

public void SelectViewNamed()

    ScorecardController scorecardController = new ScorecardController();

    var result = scorecardController.Select() as ViewResult;

    Assert.AreEqual("Select", result.ViewName);

[TestMethod]

public void IndexViewNamed()

    ScorecardController scorecardController = new ScorecardController();

    var result = scorecardController.Index() as ViewResult;

    Assert.AreEqual("Index", result.ViewName);

After that I edited the CSS so I could get a better feel to the site relative to what I am working toward. I will leave you to decide what you want to do with the CSS & such yourself. One of these days I will be graphic artists worthy, for now I am just happy with a decent looking layout and reasonable colors. ;)

When I was done my Solution Explorer looked like this.

Last I added the following to the Site.Master page for appropriate linkages. This spot is located in the menucontainer section of the page.

<div id="menucontainer">

    <ul id="menu">

        <li>

            <%= Html.ActionLink("Home", "Index", "Home")%></li>

        <li>

            <%= Html.ActionLink("About", "About", "Home")%></li>

        <li>

            <%= Html.ActionLink("Scorecard", "Display", "Scorecard") %></li>

    </ul>

</div>

When I run the app I now have a basic skeleton and the initial pages I am going to work with. Next steps coming soon.

12/8/2009Charts ala visiFire

As mentioned in my last blog entry, I have been on the hunt for some good charting displays. I found visifire and here is the quick setup & display of some initial reports. I used some sample data via trusty Webtrends Analytics 9 API, since we have a bit of example data about the office. ; )

Before even getting started, I am using Visual Studio 2010 beta2. If you don't have access to this then your screen shots will appear a good bit different then mine, or simply you will not have the same screens I do at all.

First I downloaded the files needed. The zip with the assemblies has the following files.

GPL v3.txt and License.txt are text files about the respective licensing of the charting assemblies.
Bin - This folder has the actual assemblies for Silverlight and WPF.
Samples - This folder has, simply, samples in it.

Within the Bin folder there are four files.

I'm not really keen on their naming conventions, as the naming seems to be here or there, but it is easily understood. I would rather just see something along the lines of Visifire.Charts.Sl.dll and Visifire.Charts.Wpf for instance. But hey, one should not complain, these controls are awesome.

Next I added the assembly to another folder within the project I intend to use the charts in. In this particular case, the project is a WPF Project, so I am just adding the WPF assembly.

Once you add the assembly make a reference to that assembly the way you would normally make the reference.

Now that we have the appropriate assemblies referenced we?ll throw some basic reports into a WPF User Control. The next part of this process is were things get a little interesting. Our next step is to go back out to the visifire site and use the report designer. When you navigate to the report designer you will see this view.

Over on the left hand side of the designer there are various properties for the reports; Chart, Data, Title, PlotArea, Legend, TendLine, AxisY, AxisX, AxisLables, ChartGrid, Tick, and Events. Just below those menu items there is a radio button to select Silverlight or WPF. Click on the WPF radio button.

I enabled a few others options. For a complete duplication of the changes I made, check out the XAML snippet below. I made some changes after the initial designer work was done. My best practice is to set the basic report, a few data points, and then start working with it in Design Time & XAML view in VS 2010. Otherwise the designer may redraw something and you want have an undo option.

 <vc:Chart xmlns:vc="clr-namespace:Visifire.Charts;assembly=WPFVisifire.Charts"

                  Grid.Row="2" Grid.Column="0" Grid.ColumnSpan="2"

                  Height="300" BorderThickness="0" Theme="Theme3" View3D="True" AnimationEnabled="True" ColorSet="VisiBlue" LightingEnabled="True"   >

            <vc:Chart.Titles>

                <vc:Title Text="Visit Trending" FontSize="16" FontStretch="SemiCondensed" FontWeight="Bold" TextAlignment="Center" />

            </vc:Chart.Titles>

            <vc:Chart.AxesY>

                <vc:Axis Title="Total Visits" AxisType="Primary" />

            </vc:Chart.AxesY>

            <vc:Chart.Series>

                <vc:DataSeries RenderAs="Column" AxisYType="Primary" >

                    <vc:DataSeries.DataPoints>

                        <vc:DataPoint AxisXLabel="1/1/08" YValue="351139" />

                        <vc:DataPoint AxisXLabel="2/1/08" YValue="345254" />

                        <vc:DataPoint AxisXLabel="3/1/08" YValue="318845" />

                        <vc:DataPoint AxisXLabel="4/1/08" YValue="274316" />

                        <vc:DataPoint AxisXLabel="5/1/08" YValue="277349" />

                        <vc:DataPoint AxisXLabel="6/1/08" YValue="351139" />

                        <vc:DataPoint AxisXLabel="7/1/08" YValue="345254" />

                        <vc:DataPoint AxisXLabel="9/1/08" YValue="318845" />

                        <vc:DataPoint AxisXLabel="10/1/08" YValue="274316" />

                        <vc:DataPoint AxisXLabel="11/1/08" YValue="297349" />

                        <vc:DataPoint AxisXLabel="12/1/08" YValue="237349" />

                        <vc:DataPoint AxisXLabel="1/1/08" YValue="353139" />

                        <vc:DataPoint AxisXLabel="2/1/08" YValue="385989" />

                        <vc:DataPoint AxisXLabel="3/1/08" YValue="435989" />

                    </vc:DataSeries.DataPoints>

                </vc:DataSeries>

            </vc:Chart.Series>

        </vc:Chart>

You will notice I also added a bit of sample data. In my actual XAML form things are setup as shown below.

<Window x:Class="ClientApp.PrototypeReportView"

        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

        Title="PrototypeReportView" Height="500" Width="500">

    <Grid Background="Black">

        <Grid.ColumnDefinitions>

            <ColumnDefinition></ColumnDefinition>

            <ColumnDefinition></ColumnDefinition>

        </Grid.ColumnDefinitions>

        <Grid.RowDefinitions>

            <RowDefinition Height="Auto"></RowDefinition>

            <RowDefinition Height="Auto"></RowDefinition>

            <RowDefinition Height="*" />

        </Grid.RowDefinitions>

    </Grid>

</Window>

Under the </Grid> and above the </Window> tags I added the chart XAML above. Now simply run the application and view the chart. Mine shows up as shown below.

The data I plugged in is some sample data from the Webtrends DX Web Services. In the near future I am intending to hustle up some connections to actual Webtrends DX Web Services, so I can show how to wire up to some live, changing, or even real time data.

9/9/2009Excel Helper Utilities Library Part Four

Over the last few entries I've added various pieces of functionality to the Excel Helper Library. Well, I decided to put together and offer the the documentation I've created, the assembly, and the solution files themselves via my site. Here's everything that is included so far.

The image above shows the page where the assembly, documentation, and visual studio solution links are available. Below I have a shot of the *.chm file and the html based documentation. Click either image to get at either of those forms of documentation.

Now don't think I am finishing this series here, I will absolutely be adding more functionality and capabilities to the Excel Helper. Each step will have added code and information right here on my blog.

Stay tuned for more, specifically what I intend to build next, but am not promising, is some ribbon functionality addins. Currently I'm finalizing what architecture I'll be using for the Excel Ribbon addin. So stay tuned, more coming.

Be sure to give this article a kick

8/27/2009Excel Helper Utilities Library Part Three

Obviously I've missed my self made time schedule of Monday, Wednesday, and Friday for these entries, so bare with me while I get em' published as fast as possible. With that written . . .

Welcome to part three of the Excel Helper Utilities Library series. In this entry I will cover the extension of the ExcelHelper class created in Part Two. The first method is one I wrote ages ago for VB.NET, but I've added here for the C# helper. As always, tests first.

[Test]

public void GetColumnLetterA()

    string twentySeventColumn = ExcelHelper.GetExcelColumnLetter(1);

    Assert.AreEqual(twentySeventColumn, "A");

[Test]

public void GetColumnLetterB()

    string twentySeventColumn = ExcelHelper.GetExcelColumnLetter(2);

    Assert.AreEqual(twentySeventColumn, "B");

[Test]

public void GetColumnLetterAa()

    string twentySeventColumn = ExcelHelper.GetExcelColumnLetter(703);

    Assert.AreEqual(twentySeventColumn, "AAA");

. . . and the implemented code . . .

/// <summary>

/// This method gets the appropriate "A" thru "ZZZ" column header for Excel based

/// on a passed in long data type.

/// </summary>

/// <param name="columnIndex">Pass in a long type to derive the appropriate column header letter.</param>

/// <returns>A string of the appropriate "A" thru "ZZZ" header letter.</returns>

public static string GetExcelColumnLetter(int columnIndex)

    int dividend = columnIndex;

    string columnName = String.Empty;

    while (dividend > 0)

        int mod = (dividend - 1)%26;

        columnName = Convert.ToChar(65 + mod) + columnName;

        dividend = ((dividend - mod)/26);

    return columnName;

The next thing I wanted was a column range set, basically something that would return "A:A" if I passed it the start column index and ending column index. Easy enough, again, tests first.

[Test]

public void GetColumnLetterSetAandA()

    const string columnStart = "1";

    const string columnEnd = "4";

    const string expectedValue = "A:D";

    string returnedValue = ExcelHelper.GetExcelColumnSet(columnStart, columnEnd);

    Assert.AreEqual(expectedValue, returnedValue);

[Test]

public void GetColumnLetterSetAandAaa()

    const string columnStart = "1";

    const string columnEnd = "703";

    const string expectedValue = "A:AAA";

    string returnedValue = ExcelHelper.GetExcelColumnSet(columnStart, columnEnd);

    Assert.AreEqual(expectedValue, returnedValue);

[Test]

public void GetColumnLetterSetAandZ()

    const string columnStart = "1";

    const string columnEnd = "26";

    const string expectedValue = "A:Z";

    string returnedValue = ExcelHelper.GetExcelColumnSet(columnStart, columnEnd);

    Assert.AreEqual(expectedValue, returnedValue);

. . . and the implementation . . .

/// <summary>

/// This method returns the ("A:ZZZ") part of the Range("A:ZZZ") column interface.

/// </summary>

/// <param name="firstColumn">Enter the first column to start the range with.</param>

/// <param name="secondColumn">Enter the second column to end the range with.</param>

/// <returns>A string formatted as A:ZZZ is returned from this method.</returns>

public static string GetExcelColumnSet(string firstColumn, string secondColumn)

    int firstColumnValue = Convert.ToInt32(firstColumn);

    int secondColumnValue = Convert.ToInt32(secondColumn);

    return GetExcelColumnLetter(firstColumnValue) + ":" + GetExcelColumnLetter(secondColumnValue);

That is the short and sweet of this entry. I have to catch a bus and get a move on, but the next entry will be a few cell manipulations to simplify capturing and putting things in the cells in Excel.

If you visit, please give this entry a kick over at

8/19/2009Excel Helper Utilities Library Part Two

In the last entry I covered a range of utility class methods including appropriate unit tests. Today I want to cover the first steps into the twisted world of Excel Development with C#. Note, there are many things I probably will not mention that are pivotal to this code working. Namely the order in which Excel objects & framework interfaces and components are instantiated are of massive importance within managed code. So if you are following along, first do the exact example here and then step on to changes. That way you will have working code before arbitrarily having non-working code.

As always, tests are first. In this entry I will actually step from test, to code, then green light the test for each part versus writing all the tests and then working through the methods. That way I can cover each method and the purpose of the method.

Before even jumping straight into tests we will need to add two specific assemblies that are needed to test Excel appropriately; Microsoft.Office.Interop.Excel and Microsoft.Office.Tools.Excel.v9.0. (Click the images to see larger images)

Now to get started with Excel unit tests, we really do need to use the Excel Assemblies we've added because mocking just doesn't cut it. Because of the legacy code & other such droves of complexities, it is extremely hard to really mock accurately. One really needs to know when the actual application is behaving appropriately under load & use from software written to use it. With that said, here's my first few tests & setup code.

I start out by creating the test class and creating a public property to act for all tests as the actual Excel Application under test.

[TestFixture]

public class ExcelHelperTests

    public Application ExcelApplication { get; set; }

Next I create two tests for setup and teardown of the Excel Application object. In addition to the basic Excel Application object, one thing I have learned about Excel from experience, is that one needs to go ahead and immediately add a workbook. If there is no workbook, Excel can throw all sorts of odd errors that provide very little insight into what is going on. So just remove that possibility by adding that default workbook.

[TestFixtureSetUp]

public void CreateExcelAppAppropriately()

    ExcelApplication = new Application();

    ExcelApplication.Workbooks.Add(Type.Missing);

    ExcelApplication.Visible = true;

[TestFixtureTearDown]

public void QuitExcelAppAppropriately()

    foreach (Workbook workbook in ExcelApplication.Workbooks)

        workbook.Close(false, false, Type.Missing);

    ExcelApplication.Quit();

Now that this is setup I can really test how the class will react and behave under test with Excel. Just to make sure, I verify that the appropriate Excel Application object is created and has a single workbook that was added in the fixture setup.

[Test]

public void InitializeDefaultWorkbook()

    Assert.GreaterOrEqual(1, ExcelApplication.Workbooks.Count);

Next on the list is finally getting to the real nitty gritty of the Excel Helper class I'm creating. My first test to begin this class is as follows.

[Test]

public void ExcelHelperInstantiation()

    var excelHelper = new ExcelHelper(ExcelApplication);

    Assert.IsNotNull(excelHelper);

    Assert.IsNotNull(excelHelper.ExcelApplication);

With these tests throwing errors I'm now ready to add my initial class skeleton.

/// <summary>

/// This class provides helper utility methods for Excel 2007 that cover common

/// needs such as creating a new worksheet, or getting an insantiated instance

/// of an existing worksheet.

/// </summary>

public class ExcelHelper

    /// <summary>

    /// This class has a single constructor used to

    /// setup the needed Microsoft.Office.Interop.Excel.Application

    /// object for use.

    /// </summary>

    /// <param name="excelApplication">Pass the active Excel Application

    /// object.</param>

    public ExcelHelper(Application excelApplication)

        ExcelApplication = excelApplication;

    /// <summary>

    /// This property is set to the active application the class

    /// executes methods against.

    /// </summary>

    public Application ExcelApplication { get; set; }

Run the test now and you should get a green light. Moving along I added a test for the first method I want, a way to retrieve a Worksheet that already exists in Excel. First my test looks something like this.

[Test]

public void GetWorksheet()

    var newWorksheet = ExcelApplication.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing,

                                                             Type.Missing) as Worksheet;

    const string newWorksheetName = "UniqueName";

    newWorksheet.Name = newWorksheetName;

    Worksheet foundNewWorksheet = new ExcelHelper(ExcelApplication).GetWorksheet(newWorksheetName);

    Assert.IsNotNull(foundNewWorksheet);

    Assert.AreEqual(foundNewWorksheet.Name, newWorksheet.Name);

I get a red light and then implement the code to return a Worksheet Object based on the passed in worksheet name. It fails, since the method isn't created, so I create the method.

/// <summary>

/// This method retrieves a specific worksheet by name.

/// </summary>

/// <param name="worksheetName">Pass the name of the

/// worksheet to retrieve.</param>

/// <returns>The Worksheet Object of the requested

/// worksheet, or if not found null.</returns>

public Worksheet GetWorksheet(string worksheetName)

    Worksheet foundSheet = null;

    foreach (Worksheet sheet in ExcelApplication.Worksheets)

        if (sheet.Name == worksheetName)

            foundSheet = sheet;

    return foundSheet;

Next test is for creating a new Worksheet, and adding it to the Excel Workbook.

[Test]

public void NewWorksheet()

    const string newWorksheetName = "UniqueName";

    new ExcelHelper(ExcelApplication).NewWorksheet(newWorksheetName);

    bool foundWorksheet = false;

    foreach (Worksheet worksheet in ExcelApplication.Worksheets)

        if(worksheet.Name == newWorksheetName)

            foundWorksheet = true;

    Assert.IsTrue(foundWorksheet);

Red lighted, so I add the method.

/// <summary>

/// This method creates a new worksheet with the name

/// specified.

/// </summary>

/// <param name="name">Pass the name you want the new

/// worksheet to have.</param>

/// <returns>Returns the new Worksheet object.</returns>

public Worksheet NewWorksheet(string name)

    var returnToThisWorksheet = ExcelApplication.ActiveSheet as _Worksheet;

    var returnSheet = ExcelApplication.Worksheets.Add(

                          Type.Missing,

                          Type.Missing,

                          Type.Missing,

                          Type.Missing) as Worksheet;

    returnSheet.Name = name;

    if (returnToThisWorksheet != null) returnToThisWorksheet.Activate();

    return returnSheet;

Now that I've simplified the methods to create and retrieve a worksheet from Excel, I want to add some functionality around determining if a Worksheet already exists within a workbook. This is usually a common need within Excel and I find many examples are simply a foreach loop stepping through each Worksheet until it found or did not find the Worksheet it is looking for. This is a perfect example of something that would be better as a method.

First the test, which I'll make a static method in case one wants to call this method without instantiating?

[Test]

public void IsExistingWorksheetName()

    var newWorksheet = ExcelApplication.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing,

                                                                         Type.Missing) as Worksheet;

    const string newWorksheetName = "UniqueName";

    newWorksheet.Name = newWorksheetName;

    ExcelHelper.IsExistingWorksheetName(newWorksheetName, ExcelApplication.Worksheets);

?and a test for a static method?

[Test]

public void IsExistingWorksheetNameInstantiatedMethod()

    var newWorksheet = ExcelApplication.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing,

                                                              Type.Missing) as Worksheet;

    const string newWorksheetName = "UniqueName";

    newWorksheet.Name = newWorksheetName;

    ExcelHelper helper = new ExcelHelper(ExcelApplication);

    Assert.IsTrue(helper.IsExistingWorksheetName(newWorksheetName));

    foreach (Worksheet sheet in ExcelApplication.Worksheets)

        if (sheet.Name == newWorksheetName)

            sheet.Delete();

    Assert.IsFalse(helper.IsExistingWorksheetName(newWorksheetName));

As I finished this test and got a red light I realized, I needed to assure the other tests cleaned up after themselves like this one does with the deletion of the Worksheet. I then went back and add the snippet

foreach (Worksheet sheet in ExcelApplication.Worksheets)

    if (sheet.Name == newWorksheetName)

        sheet.Delete();

to each of the tests that needed it. After the tests where appropriately refactored I ran the tests to assure they still where all green lights except for the one I just created. I then jumped back into my ExcelHelper Class and created the methods I just wrote tests for.

        /// <summary>

        /// This method checks for existing names to prevent identical name collisions within the

        /// worksheet collection.

        /// </summary>

        /// <param name="worksheetName">Pass in the worksheet name to check for.</param>

        /// <param name="existingWorksheets">Pass in the Excel worksheets collection to search in.</param>

        /// <returns>Return true if existing worksheet name is found, false if no existing worksheet names.</returns>

        public static bool IsExistingWorksheetName(string worksheetName, Sheets existingWorksheets)

            foreach (_Worksheet worksheet in existingWorksheets)

                string existingWorksheet = worksheet.Name;

                if (worksheetName == existingWorksheet)

                    return true;

            return false;

        /// <summary>

        /// This method checks for existing names to prevent identical name collisions within the

        /// worksheet collection.

        /// </summary>

        /// <param name="worksheetName">Pass in the worksheet name to check for.</param>

        /// <returns>Return true if existing worksheet name is found, false if no existing worksheet names.</returns>

        public bool IsExistingWorksheetName(string worksheetName)

            return IsExistingWorksheetName(worksheetName, ExcelApplication.Worksheets);

For this entry we've covered appropriate tests and methods for getting an Excel Worksheet, creating a new Excel Worksheet, and finding out if a Excel Worksheet Name is already taken. In my next entry on this topic I'll keep adding more Excel Helper Methods that will help make Excel Addin Development easier.

8/19/2009Excel Helper Utilities Library Part One

I'm kicking off a series that is a long time in the making. The series is 99% complete at this point, and will be published every couple days. Right now I?m aiming for a Monday, Wednesday, Friday type of frequency, but might throw in an extra one here or there. When completed the code will be posted to CodePlex for easy download & an additions that someone might want to contribute. Just hit me up at adron [at@] adronbhall [dot.] com.

Here?s a code file I've put together to help project using Excel 2007. These are just some small helper methods, etc., that remove some of the redundant Type.Missing and other insidious things one has to go through when developing with Excel.

This first class is a pure and simple utility class to help clean up strings for use in certain situations that come up with Excel. Some examples might be when trying to clean up a string for a worksheet name.

Of course, as usual, the first thing I hit where the unit tests. Since these methods are relatively simple, I just went ahead and wrote tests for each.

[TestFixture]

public class StringParsingTests

    [Test]

    public void IsNumberTrue()

        const string isNumber = "234";

        Assert.IsTrue(StringParsing.IsNumeric(isNumber));

    [Test]

    public void IsNumberFalse()

        const string notNumber = "noNumberHere";

        Assert.IsFalse(StringParsing.IsNumeric(notNumber));

    [Test]

    public void IsNumberFloat()

        const string isNumber = "232.4419";

        Assert.IsTrue(StringParsing.IsNumeric(isNumber));

    [Test]

    public void IsAlphaNumericTrue()

        const string isAlphaNumeric = "asdf1234";

        Assert.IsTrue(StringParsing.IsValidAlphaNumeric(isAlphaNumeric));

    [Test]

    public void IsAlphaNumericFalse()

        const string notAlphaNumeric = "asdf.!~1234";

        Assert.IsFalse(StringParsing.IsValidAlphaNumeric(notAlphaNumeric));

    [Test]

    public void RemoveSpecialCharactersRemoval()

        const string notAlphaNumeric = "asdf!@#$";

        string isAlphaNumeric = StringParsing.RemoveSpecialCharacters(notAlphaNumeric);

        Assert.IsFalse(StringParsing.IsValidAlphaNumeric(isAlphaNumeric));

    [Test]

    public void RemoveSpecialCharactersCheckOriginalString()

        const string notAlphaNumeric = "asdf!@#$";

        const string expectedResult = "asdf    ";

        string isAlphaNumeric = StringParsing.RemoveSpecialCharacters(notAlphaNumeric);

        Assert.AreEqual(expectedResult, isAlphaNumeric);

    [Test]

    public void TrimAndRemoveSpecialCharacters()

        const string notAlphaNumeric = "asdf!@#$";

        string isAlphaNumeric = StringParsing.TrimAndRemoveSpecialCharacters(notAlphaNumeric);

        Assert.IsTrue(StringParsing.IsValidAlphaNumeric(isAlphaNumeric));

    [Test]

    public void TrimAndRemoveSpecialCharactersCheckOriginalString()

        const string notAlphaNumeric = "asdf!@#$";

        const string expectedResult = "asdf";

        string isAlphaNumeric = StringParsing.TrimAndRemoveSpecialCharacters(notAlphaNumeric);

        Assert.AreEqual(expectedResult, isAlphaNumeric);

The class I created is named StringParsing within a namespace of Abh.Core.Excel2k7.Utilities. I?ve left the comments in place in case you want to copy & paste the code for use.

using System;

using System.Text.RegularExpressions;

namespace Abh.Core.Excel2k7.Utilities

    /// <summary>

    /// This class provides static methods for parsing and manipulating strings to provide

    /// Excel compliant, or other cleaning methods to trim, check for alphanumeric, and numeric

    /// data types within a string.  These methods are in addition to most existing methods

    /// already available on existing objects in the .NET Framework.

    /// </summary>

    public class StringParsing

Within the class are four methods:

The first method is RemoveSpecialCharacters(string stringToClean). This method does exactly what the name implies, it removes a selection of special characters from a passed in string.

/// <summary>

/// This method removes special characters from a string.

/// </summary>

/// <param name="stringToClean">Pass in the string to remove the special characters from.</param>

/// <returns>A string based on the passed in string without special characters is returned.</returns>

public static string RemoveSpecialCharacters(string stringToClean)

    return stringToClean.Replace("!", " ").

        Replace("@", " ").

        Replace("#", " ").

        Replace("$", " ").

        Replace("%", " ").

        Replace("^", " ").

        Replace("&", " ").

        Replace("*", " ").

        Replace("(", " ").

        Replace(")", " ").

        Replace("_", "").

        Replace("+", "").

        Replace("-", "").

        Replace("=", "").

        Replace("`", "").

        Replace("~", "").

        Replace("<", "").

        Replace(">", "").

        Replace("?", "").

        Replace(":", "").

        Replace(@"\", "").

        Replace("{", "").

        Replace("}", "").

        Replace("|", "").

        Replace("[", "").

        Replace("]", "").

        Replace(";", "").

        Replace("'", "").

        Replace(",", "").

        Replace(".", "").

        Replace("/", "").

        Replace("'", "");

The second method is TrimAndRemoveSpecialCharacters(string stringToClean). Again, this method name describes exactly what the method does. The string passed in has the edges trimmed of whitespace and then executes the previous method to remove special characters.

/// <summary>

/// This method removes special characters and trims any white space from the left and right of the

/// string.

/// </summary>

/// <param name="stringToClean">Pass in the string to remove the special characters from.</param>

/// <returns>A string based on the passed in string without special characters and trimmed

/// of white space is returned.</returns>

public static string TrimAndRemoveSpecialCharacters(string stringToClean)

    RemoveSpecialCharacters(stringToClean);

    return stringToClean.Trim();

The third method, again with the theme of self describing method names, is IsValidAlphaNumeric(string verifyThis).

/// <summary>

/// This method takes a string as input and return a boolean value representing

/// if the string is or is not alphanumeric.

/// </summary>

/// <param name="verifyThis">Pass the string to verify if it is or is not alphanumeric.</param>

/// <returns>True is returned if alphanumeric, false if not.</returns>

public static bool IsValidAlphaNumeric(string verifyThis)

    if (String.IsNullOrEmpty(verifyThis))

        return false;

    for (int i = 0; i < verifyThis.Length; i++)

        if (!(Char.IsLetter(verifyThis[i])) && (!(Char.IsNumber(verifyThis[i]))))

            return false;

    return true;

The last method is one of the most needed of all, the IsNumeric(string verifyThis) method. Again, self explanatory.

/// <summary>

/// This method takes a string as input and returns a boolean value representing

/// if the string is numeric or not.

/// </summary>

/// <param name="verifyThis">Pass the string to verify if it is or is not numeric.</param>

/// <returns>True is returned if numeric, false if not.</returns>

public static bool IsNumeric(string verifyThis)

    verifyThis = verifyThis.Trim();

    var m = regex.Match(verifyThis);

    return !string.IsNullOrEmpty(m.Value);

That covers the string parsing utility file. More to come later ? stay tuned. The next few entries I'll cover the ExcelHelper Class I've written that simplifies creation & retrieval of Excel Worksheets, and more.

<< Previous posts

Pages

Categories

Tags

Archive

Blogroll

Authors

Disclaimer