Monday, May 10, 2010

Sum total line in footer of ASP.Net gridview

A few years ago, I was looking for a solution to sum up a column of numbers in a gridview in ASP.Net and display the total in the footer of the gridview.  I found a solution on aspalliance that worked well for me for these few years.  It uses TemplateFields to call one function in the ItemTemplate that adds the values and another function in the FooterTemplate that displays the total.  I enhanced it to use a Hashtable so that when I had lots of fields I wanted to total, I didn't have to make a separate variable for each one.  I just added a new key to the Hashtable.

But today I wanted to try to find a solution that was less work, where I could still use BoundFields.  I found a good solution on Microsoft.com using the RowDataBound event.  With this solution, you can still use BoundFields, but you have to add code to the function for each column that you want to sum up.  I have a grid with over 60 columns (because we hide and show different ones for different scenarios) so I was really hoping to  find some way where I didn't have to write code for each field.

So the solution I came up with is below, in C#.  It's very similar to the Microsoft.com solution, but with a couple modifications.  First, I'm using Hashtables again, two this time: one for the totals and one for the type of the field -- integer or currency.  Second is the automatic check for whether a field is numeric or not, and if its is, what kind of number -- integer or currency.  I check this simply by looking for the dollar sign in the text of the cell.  Note that when parsing the text to number format, the System.Globalization.NumberStyles.Any parameter is very important because it handles characters like the $ and parentheses for negative numbers in accounting format.

One drawback of this method is that it will total every numeric field, including zip codes, for example.  But it could be easily modified to either list specific columns to total or specific columns to exclude.  One other drawback is that it doesn't work for percentage columns.  You can't total up different percentage values anyway to get a percentage that is accurate for the total line, but at least with a TemplateField solution, you can divide the appropriate totals to get the correct percentage in the FooterTemplate.

Listing, in C# (VB listing below):


    Hashtable total = new Hashtable();
    Hashtable fieldType = new Hashtable();


    protected void reportGrid_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        int i;
        double result;
        string cellText;

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            for (i = 0; i < e.Row.Cells.Count; i++)
            {
                cellText = e.Row.Cells[i].Text;

                try
                {
                    result = Double.Parse(cellText, System.Globalization.NumberStyles.Any);

                    // if this didn't go to the catch, then field was a number
                    // if it has a $ in it, save in Hashtable that it is currency field

                    // this code assumes that the field won't change type from row to row

                    if (cellText.Contains("$"))
                    {
                        fieldType[i] = "C";
                    }
                    else
                    {
                        fieldType[i] = "N0";
                    }

                    if (total.ContainsKey(i))
                    {
                        total[i] = double.Parse(total[i].ToString()) + result;
                    }
                    else
                    {
                        total[i] = result;
                    }
                }
                catch
                {
                    // do nothing
                }
            }
        }
        else if (e.Row.RowType == DataControlRowType.Footer)
        {
            for (i = 0; i < e.Row.Cells.Count; i++)
            {
                // display the value of any Hashtable total field that has a key
                if (total.ContainsKey(i))
                {
                    e.Row.Cells[i].Text = String.Format("{0:" + fieldType[i].ToString() + "}", total[i]);
                }
            }
        }
    }

Listing, in VB, using excluded fields

    Dim fieldType As Hashtable = New Hashtable()
    Dim total As Hashtable = New Hashtable()

   Protected Sub reportGrid_RowDataBound(sender As Object, e As GridViewRowEventArgs)
        Dim i As Integer
        Dim result As Double
        Dim cellText As String

        ' excluded fields
        Dim excludedFields As String = ",0,2,"

        If (e.Row.RowType = DataControlRowType.DataRow) Then
            For i = 0 To e.Row.Cells.Count - 1
                cellText = e.Row.Cells(i).Text

                Try
                    result = Double.Parse(cellText, System.Globalization.NumberStyles.Any)

                    ' if this didn't go to the catch, then field was a number
                    ' if it has a $ in it, save in Hashtable that it is currency field
                    ' NOTE: this code assumes that the field won't change type from row to row

                    If Not excludedFields.Contains("," & i.ToString() & ",") Then
                        If (cellText.Contains("$")) Then
                            fieldType(i) = "C"
                        Else
                            fieldType(i) = "N0"
                        End If

                        If (total.ContainsKey(i)) Then
                            total(i) = Double.Parse(total(i).ToString()) + result
                        Else
                            total(i) = result
                        End If
                    End If
                catch
                    ' do nothing
                End Try
            Next
        ElseIf (e.Row.RowType = DataControlRowType.Footer) Then
            For i = 0 To e.Row.Cells.Count - 1
                ' display the value of any Hashtable total field that has a key
                If (total.ContainsKey(i)) Then
                    e.Row.Cells(i).Text = String.Format("{0:" & fieldType(i).ToString() & "}", total(i))
                End If
            Next
        End If
    End Sub

Tuesday, May 4, 2010

Microsoft Office Viewer applications

It's been a couple months now that I've been going without Microsoft Office or Open Office.  I'm creating all my documents in Google Documents.  The biggest problem, at first, was viewing Microsoft Office documents that other people sent  to me.  I would have to upload them to Google Documents in order to view them.

But then I learned about the free Viewer applications provided by Microsoft for Microsoft Office documents.  There is a free PowerPoint Viewer, free Word Viewer, and free Excel Viewer.  These applications make it very easy to open an Office document that someone sends you without having to buy Office yourself.  The PowerPoint viewer and the Word Viewer especially are great, I've had no problems with them.

I have had problems with the Excel Viewer.  First, it does not open comma separated value (CSV) files.  I do a lot of database work with exports of database tables to CSV.  So for CSV files, I still have to upload them to Google Docs to view them.  Also, Excel Viewer does not read HTML files that are saved as .xls files.  This probably isn't common for most people, but again, for my work we sometimes export a table to a text file as HTML text wrapped in the HTML
tags.  When these files have a .xls extension, Excel opens them fine.  Excel Viewer does not.  And Google Docs doesn't like those files either.  So to get those in to a spreadsheet, I have to:
1) Open the file in a browser
2) Highlight and copy the HTML table
3) Paste into a Google spreadsheet

However, if the file is very large, Google will not let me paste it into the spreadsheet.  It gives me an error like, "You can not paste that much data from the clipboard" (and sometimes it just fails with no error).  So then I have to take these additional steps:

3) (instead of 3 above) Paste the copied table into Notepad, which gives me a tab delimited data file
4) Save that text file temporarily
5) Import the tab delimited file into a Google spreadsheet using the spreadsheet's Import... menu item

So those are some ways to work around Excel Viewer's limitations and survive without installing Microsoft Office or Open Office.  Unfortunately, Google spreadsheets also lack some key features, like Print Preview.  So because of the various spreadsheet issues, I still may break down and install Open Office, we'll see.