Last time I showed you the Practical use of CountIf with Google Docs Spreadsheet.

Today I’m showing a practical use of a function called Right that can help you in a lot of situations.

Consider this sample spreadsheet:

I’m using the above spreadsheet to keep track of my progress during online theoretical practice tests for my first driver’s license.

I like math so I wanted to know how many tests people have taken or how many tests have been served by the server since my last test.

The Right function is being used in the column named ‘’Tests since last’’ starting at the second practice test I’ve taken. For example, the actual formula in cell C4 is this:

C4 = right(B4, 7) - right(B3, 7)

Let’s break this thing and explain each piece:

B4 contains the URL that points to the practice test I took. In this case the URL is this:

http://www.provadetran.com.br/simulado_termino.php?simulado=4396981

As you see, there’s a number at the end of the URL. This number has 7 digits of length and is a sequential that identifies each test. This allows me to go to the test to review what I did right and what I did wrong at a later time.

Well, to answer that intriguing question I need to subtract this number from the number present in the URL just above cell B4, namely B3. Now look at the formula in cell C4 above. It does just that. The right function is giving me the last 7 characters of the URL. Last because it gets the characters from right to left. If you wanted to get the first 7 characters you’d use the left function instead.

The right function has this form: RIGHT(text, number). It defines the last character or characters in a text string. Text is the text of which the right part is to be determined. Number (optional) is the number of characters from the right part of the text.

Easy to understand, isn’t it?

Let’s do the math making the substitution of values:

C4 = right(B4, 7) - right(B3, 7)
C4 = 4396981      - 4396181
C4 = 800

Now I know that from the time I took the first practice test on 11/25 to the second test on the same day but at a different time 800 tests have been served. This is more of a curiosity metric than anything. I like math anyways. :o)

This was a simple and practical use of the right function that came in handy in this situation.

In one of my previous posts I embedded a Google spreadsheet in the post to show you a practical use of the CountIf function. Read it here: Practical use of CountIf with Google Docs Spreadsheet.

This time I’m going to use Microsoft Office Web Apps, Google’s competitor when it comes to online documents.

Excerpt taken from Microsoft Office Web Apps site:

Whether you’re in the office, at home, or on the road, Microsoft Office Web Apps help you get more things done virtually anywhere and anytime. These convenient online companions to Microsoft Word, Excel, PowerPoint, and OneNote offer you an easy way to access, view, and edit documents directly from your Web browser.

I first read about embedding Microsoft Web Apps documents through Office Web Apps blog post Embedded Excel and PowerPoint Available Now on SkyDrive. Then I went to this post dedicated to Excel: Embedding Excel Web App in your own web page or blog. This last post shows some good samples of the power that embedded spreadsheets gives you.

My intent is to highlight a really important feature that Google is currently missing in its service: cell references (line numbers and column letters) in online published documents.

Take the same Excel spreadsheet from last post but now hosted in the cloud by Office Excel Web App:

As I used cell references throughout that last post to explain things, it would be really useful if Google spreadsheet had cell references so that users could spot the cell I was referring to instantly.

You can see above that Excel Web App does show cell references making the overall user web experience better.

For example, above I allow you to edit (AllowTyping=True) the contents of this spreadsheet on the fly. I also allow you to sort the table contents (AllowInteractivity=True). The modifications you make are valid only in this session. If you close or reload the page your changes won’t be saved. This is really nice. A lot of possibilities emerge from this.

Changes you make are reflected in the spreadsheet. Try for example adding one more TL class in cell D5. The cell color will change to yellow. You’ll see that cell I17 will have its value changed. It’s going to be -1. The formulas are being updated automagically. You can even see the formulas by double clicking a specific cell that has a formula.

One thing I noticed is that color formatting rules applied to cells are kept as you define in Excel Desktop App (Excel Web App counterpart). I tried to configure these rules in Excel Web App but I couldn’t find a menu option that would allow me to change them.

It’s important to mention that although great part of Excel Desktop App features aren’t available for configuration online, Excel Web App still honors Excel Desktop App configurations you make in your workbook. This is cool!

If you're curious, this is the code I'm using to embed the spreadsheet in this post:

<iframe src="http://r.office.microsoft.com/r/rlidExcelEmbed?su=-6822816632184108919&Fi=SDA1507C6BFF0A0889!186&AllowInteractivity=True&AllowTyping=True" frameborder="0" width="525" height="475" scrolling="no"></iframe>

If you want to learn more about the options available to embed an Excel Web App workbook in your site or blog, take a look at: Customize how your Excel workbook is embedded.

If you wanna follow the explanation of this post by looking at cell references, go to this post: Taking Microsoft Office Excel Web App for a spin

I’ll show you a practical use of the CountIf function that you can apply in both Microsoft Excel spreadsheets and Google Docs spreadsheets.

Let’s start with this sample spreadsheet:

I’m using the above spreadsheet to keep track of my progress during the course for my first driver’s license.

The CountIf function is being used in column Remaining classes for each discipline. For example, the actual formula in cell I17 is this:

I17 = H17 - countif(D3:I14, B17)

Let’s break this thing and explain each piece:

H17 is a fixed value = 18. I know it beforehand. In this case for the Transit Legislation discipline I must have a total of 18 classes.

The countif function has this form: COUNTIF(range, criteria). So the above countif(D3:I14, B17) is telling us that:

D3:I14 is the range of cells we’re searching and B17 is what we’re after… The range encompasses all the disciplines’ acronyms from 11/23/2010 to 12/9/2010 for all the timetable.

B17 is the discipline’s acronym, in this case it is TL.

Easy to understand, isn’t it?

Doing the above with countif I can tell how many classes I have scheduled for that discipline. Then I just subtract this value from the total necessary classes to have a picture of how many classes I still have to go through to fulfill the necessary ones.

Let’s do the math:

CountIf returns 18 because LT appears 18 times in the timetable. Making the substitution of values:

I17 = H17 - countif(D3:I14, B17)
I17 =  18 - 18 = 0

Now I know that if I follow the above schedule I’ll probably finish the theoretical course by 12/9/2010. Pretty good.

This was a simple and practical use of the countif function that came in handy in this situation.

Scott Guthrie recently posted about the New ASP.NET Charting Control: <asp:chart runat="server"/>.

This weekend I decided to give it a try and as always I had to go through the samples to learn how to assemble a chart.

To learn about the new charting controls I got the Microsoft Chart Controls Samples project and played with the contents of the Getting Started section.

At the same time I was translating to Portuguese ScottGu’s blog post about the ASP.NET MVC 1.0 Release Candidate. I was also updating a Google spreadsheet that I use to keep track of worked hours at Chemtech.

To learn how to integrate ASP.NET charting controls with ASP.NET MVC and Google Spreadsheet Data API I created a new ASP.NET MVC project and started coding a sample application.

The following is how I got it all together:

using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Web.Mvc;
using System.Web.UI.DataVisualization.Charting;
using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;
namespace MvcChartGoogleSpreadsheet.Views.Home
{
    /// <summary>
    /// ASP.NET MVC application + ASP.NET charting controls + Google Spreadsheet Data API web-based application
    /// It demonstrates the operations supported by each of these technologies.
    /// It requires authentication in the form of your Google Docs & Spreadsheets username
    /// and password, and retrieves data from a worksheet of your choice.
    /// A chart is created with the data acquired through a CellFeed query.
    /// </summary>
    public partial class Index : ViewPage
    {
        private List<WorksheetEntry> allWorksheets = new List<WorksheetEntry>();

        protected void Page_Load(object sender, System.EventArgs e)
        {
            // Calling the method that configures the chart.
            ConfigureChart();

            // Creating a Google SpreadsheetService object passing to it the name of the application.
            SpreadsheetsService service = new SpreadsheetsService("MvcChartGoogleSpreadsheet");

            // Google account information (login and password)
            service.setUserCredentials("username@gmail.com", "password");

            GetAllSpreadsheetsAndWorksheets(service);

            // Using LINQ query expression to get a specific worksheet.
            var entry = from wse in allWorksheets
                        where
                            wse.Title.Text == "2008 leniel.net" // This is the name of the worksheet.
                        select wse;

            // Demonstrate a CellFeed query.
            CellFeed cellFeed = GetWorksheetCellFeed(service, entry.First());

            // Each entry represents a cell within the worksheet.
            foreach(CellEntry cellEntry in cellFeed.Entries)
            {
                // I just want to get the contents of column 2 of the worksheet.
                // The value of the cell present in column 2 will be used in the X axis.
                if(cellEntry.Cell.Column == 2)
                {
                    // I get the value of column 7 (cellEntry.Cell.Column + 5) of the same row. This value will be used in the Y axis.
                    // I replace the colon present in the value with a dot. I do so to make the data valid for calculating values.
                    string yValue = ((CellEntry)cellFeed.Entries.SingleOrDefault(es => ((CellEntry)es).Cell.Row == cellEntry.Cell.Row && ((CellEntry)es).Cell.Column == cellEntry.Cell.Column + 5)).Cell.Value.Replace(":", ".");

                    // I then remove the extra data that isn't necessary at all in my case.
                    yValue = yValue.Remove(yValue.Length - 3, 3);

                    // I pass the X and Y values to create a Point used in the series of the chart.
                    chart1.Series["Hours of work"].Points.AddXY(cellEntry.Cell.Value, yValue);
                }
            }
        }

        private void ConfigureChart()
        {
            chart1.Series.Add("Hours of work");

            chart1.Titles.Add("My chart title");

            // Add header separator of type line      
            chart1.Legends["Default"].HeaderSeparator = LegendSeparatorStyle.Line;
            chart1.Legends["Default"].HeaderSeparatorColor = Color.Gray;

            // Add Color column      
            LegendCellColumn firstColumn = new LegendCellColumn();
            firstColumn.ColumnType = LegendCellColumnType.SeriesSymbol;
            firstColumn.HeaderText = "Color";
            firstColumn.HeaderBackColor = Color.WhiteSmoke;
            chart1.Legends["Default"].CellColumns.Add(firstColumn);

            // Add Legend Text column      
            LegendCellColumn secondColumn = new LegendCellColumn();
            secondColumn.ColumnType = LegendCellColumnType.Text;
            secondColumn.HeaderText = "Name";
            secondColumn.Text = "#LEGENDTEXT";
            secondColumn.HeaderBackColor = Color.WhiteSmoke;
            chart1.Legends["Default"].CellColumns.Add(secondColumn);

            // Add AVG cell column      
            LegendCellColumn avgColumn = new LegendCellColumn();
            avgColumn.Text = "#AVG{N2}";
            avgColumn.HeaderText = "Avg";
            avgColumn.Name = "AvgColumn";
            avgColumn.HeaderBackColor = Color.WhiteSmoke;
            chart1.Legends["Default"].CellColumns.Add(avgColumn);

            // Add Total cell column      
            LegendCellColumn totalColumn = new LegendCellColumn();
            totalColumn.Text = "#TOTAL{N1}";
            totalColumn.HeaderText = "Total";
            totalColumn.Name = "TotalColumn";
            totalColumn.HeaderBackColor = Color.WhiteSmoke;
            chart1.Legends["Default"].CellColumns.Add(totalColumn);

            // Set Min cell column attributes      
            LegendCellColumn minColumn = new LegendCellColumn();
            minColumn.Text = "#MIN{N1}";
            minColumn.HeaderText = "Min";
            minColumn.Name = "MinColumn";
            minColumn.HeaderBackColor = Color.WhiteSmoke;
            chart1.Legends["Default"].CellColumns.Add(minColumn);

            // Show labels at every 2 days
            chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 2;
            chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Interval = 2;
            chart1.ChartAreas["ChartArea1"].AxisX.MajorTickMark.Interval = 2;

            // Set series tooltips
            chart1.Series["Hours of work"].ToolTip = "#VALX";

// Set the width of the chart
           chart1.Width = 510;

           // Set legend docking
           chart1.Legends["Default"].Docking = Docking.Bottom;

           // Set legend alignment
           chart1.Legends["Default"].Alignment = StringAlignment.Center;

        }

        /// <summary>
        /// Gets a list of all the user's spreadsheets, and the
        /// list of worksheets that each spreadsheet contains.
        /// </summary>
        /// <param name="service">An authenticated SpreadsheetsService object</param>
        private void GetAllSpreadsheetsAndWorksheets(SpreadsheetsService service)
        {
            SpreadsheetQuery query = new SpreadsheetQuery();

            SpreadsheetFeed feed = service.Query(query);

            foreach(SpreadsheetEntry entry in feed.Entries)
            {
                GetAllWorksheets(service, entry);
            }
        }

        /// <summary>
        /// Gets a list of all worksheets in the specified spreadsheet.
        /// </summary>
        /// <param name="service">An authenticated SpreadsheetsService object</param>
        /// <param name="entry">The spreadsheet whose worksheets are to be retrieved</param>
        private void GetAllWorksheets(SpreadsheetsService service, SpreadsheetEntry entry)
        {
            AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);

            WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());

            WorksheetFeed feed = service.Query(query);

            foreach(WorksheetEntry worksheet in feed.Entries)
            {
                allWorksheets.Add(worksheet);
            }
        }

        /// <summary>
        /// Performs a cell range query on the specified worksheet to
        /// retrieve only the cells contained within the specified range.
        /// </summary>
        /// <param name="service">An authenticated SpreadsheetsService object</param>
        /// <param name="entry">The worksheet to retrieve</param>
        private static CellFeed GetWorksheetCellFeed(SpreadsheetsService service, WorksheetEntry entry)
        {
            AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);

            CellQuery query = new CellQuery(listFeedLink.HRef.ToString());

            // Defining the range of cells that I want to be retrieved.
            query.Range = "B5:G29";

            CellFeed feed = service.Query(query);

            return feed;
        }
    }
}

The above code is commented so I don’t think it needs more words.

ASP.NET MVC
I’ve already written about ASP.NET MVC in a post titled Hello World Web Site with ASP.NET MVC. If you don’t know what MVC means, don’t panic! It’s just an architectural and design pattern that advocates a clean separation of concerns in software engineering.

To get the ASP.NET MVC I’d recommend the Web Platform Installer that Microsoft released not so long ago.

Just download and install the Web Platform Installer and select what web development tools you want to be installed on your machine.

As you can see above I already have the ASP.NET MVC Release Candidate 1 installed on my machine. In case you don’t have it yet, select the checkbox and click the Install button. The Web Platform Installer will do the rest of the job, that is, it’ll download the packages and install it conveniently for you.

An interesting thing about the Web Platform Installer is that it’ll always have the most updated bits to be installed.

After installing the ASP.NET MVC you’re ready to create an ASP.NET MVC Web Application in Visual Studio 2008:

Google Spreadsheet API
To accomplish the goal of this post I needed more documentation and so I went to Google Spreadsheets APIs and Tools page. From there I went to read the Google Spreadsheets Data API Developer's Guide. Specifically the Developer's Guide for .NET since I’m playing with Microsoft development platform.

After reading some bits here and some bits there I went directly to the Libraries and Code page and went to the download page of google-gdata – the .NET library for the Google Data API.

I downloaded the file Google Data API Setup(1.3.1.0).msi and installed it. It has sample projects for all Google Data APIs. I was interested in the Spreadsheet API and so I got the specific code and dropped it on the ASP.NET MVC project.

As you can see in the above code the using statements include 3 references to Google Data API DLLs:

using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;

These DLLs are part of Google Data API Setup(1.3.1.0).msi installation file and are located at C:\Program files\Google\Google Data API SDK\Redist in my machine. You’ll need to add these DLLs to the References folder of the ASP.NET MVC project.

ASP.NET Charting Controls

There are charts of all types:

- Bar and Column Charts
- Line Charts
- Area Charts
- Pie and Doughnut Charts
- Point Charts
- Range Charts
- Circular Charts
- Accumulation Charts
- Data Distribution Charts
- Price Change Financial Charts
- Advanced Financial Charts
- Combinational Charts

To get the charting controls in Visual Studio 2008 I downloaded 2 files:

- Microsoft Chart Controls for Microsoft .NET Framework 3.5
- Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008

After installing both files, open Visual Studio 2008 and go to a webform/viewpage .ASPX page. Now in the Toolbox you’ll find the new Chart control.

Drag and drop the chart onto the page and you’re ready to go.

Look to the using statement present in the code behind page:

using System.Web.UI.DataVisualization.Charting;

This is the column chart I got when I ran the application:

LINQ
I used some constructs of LINQ in the above sample application. If you need further details about this fantastic technology: read my post LINQ - Language Integrated Query.

Note:
To work with Dates in a chart I had to go through the Microsoft Chart Controls Samples project and look at Working with Chart Data – Working with Dates – Setting Time Scale.

Summary
As you could see the ASP.NET charting control is feature rich and you can use it to show data from all sorts of sources as for example a Google spreadsheet. I just showed here the simplest of the charts.

Using the charting controls in an ASP.NET MVC application is a really straightforward task. Of course, you’d have action methods that’d generate the chart for you based on some logic you implement in such methods. The sample given here was just to exercise the integration of technologies available nowadays to build richer internet applications.

References
ASP.NET MVC
Hello World Web Site with ASP.NET MVC
ASP.NET MVC : The Official Microsoft ASP.NET Site
ScottGu's Blog
Model View Controller (MVC) at Wikipedia

Google Spreadsheet API
Google Spreadsheets APIs and Tools page
Google Spreadsheets Data API Developer's Guide
Google Spreadsheets Developer's Guide for .NET
Google Spreadsheets Libraries and Code page
google-gdata – the .NET library for the Google Data API

ASP.NET Charting controls
Download the Microsoft Chart Controls Documentation
Microsoft Chart Control Forum
Alex Gorev's Weblog - Data Visualization
Charting With ASP.NET And LINQ

Downloads
ASP.NET MVC
Web Platform Installer

Google Spreadsheet API
Google Data API Setup(1.3.1.0).msi

ASP.NET Charting controls
Microsoft Chart Controls for Microsoft .NET Framework 3.5
Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008
Microsoft Chart Controls Samples project

Visual Studio 2008 C# ASP.NET MVC Web Application
You can get the Microsoft Visual Studio Project at:

http://leniel.googlepages.com/MvcChartGoogleSpreadsheet.zip

Note: Remember to change/substitute the username and password present in the file C:\MvcChartGoogleSpreadsheet\Views\Home\Index.aspx.cs so that the application can log you on to Google Spreadsheet service and get your data (spreadsheet along with the worksheets).