Excel Open XML & LINQ Part I

In the next few posts I'll roll out a little project for Excel that uses Open XML and LINQ. The scenario for this little Office solution has to do with my massive collection of music. I've collected over 1000 albums of music both from CDs and from purchases online.

[SideNote: Purchasing music legally is so easy and so cheap. Come on, everybody, let's get legal! ]

Well- I wanted a way to query, sort, and work with my music inventory in more flexible ways than the Zune software will allow. The Zune application is really just about the listening experience. It's not intended to be a customizable database application. So, I decided to export my music inventory to Excel. This would allow me to see, sort, and work with my music in the rich goodness that is Excel. Here you see an image of my band list in Excel, and I can sort by artist, album, or song:

While Excel is great for sorting and working with the data in many ways, but imagine that I have plans for using the data that go beyond just working in Excel (and imagine that I also needed some practice working with Open XML!). To fulfill those additional requirements, I decided to create a Winform app. I'll wire the app up to the Excel spreadsheet via Open XML. Here you can see the application running:

I'll spare you the details of how I do the export of my music library. Suffice it to say that I use System.IO to recursively roll through the music library directories and create XMLElements for each of the artists, albums, and songs. Once that is complete, it's an XML file that I can open in Excel 2010. My application relies heavily on the LtxOpenXML Namespace that features a number of extension classes (big thanks to Eric for all of his posts on Open XML). These classes greatly simplify the amount of code you need to write to walk and search through spreadsheets in Open XML. I could have written all of the code to talk to the TableRows in the spreadsheet, but the LtxOpenXML classes make it so much easier.

Read Eric White's blog post to find out more about this.

Alright-so here's how it works. First, I have a procedure that picks up the search term from a text box on the form. It also checks to see what kind of search is being performed (Band, Album, or Song). The procedure is nearly identical for all three types of searches. I'll show just the album search.

         public void QuerySimpleTable(searchType sType, string searchTerm)
        {
            using (SpreadsheetDocument spreadsheet =
                SpreadsheetDocument.Open(filename1, false))
            {
                // search for songs that match search query criteria

                if (sType == searchType.Album)
                {
                     var r = from c in spreadsheet.Table("Bands").TableRows()
                        where (string)c["Album"] == searchTerm
                             select new BandRecord()
                             {
                                 Band = (string)c["Band"],
                                 Album = (string)c["Album"],
                                 Song = (string)c["Song"]
                             };
                     ListResults(r);
                }
            }
        }

As you see, the code does the following:

1) Opens the spreadsheet using the DocumentFormat.OpenXml.Packaging API.

2) Uses LINQ to create a new query that targets the "Bands" table defined in the spreadsheet and brings back the tables rows matching the search criteria.

3) Associates the search results with an instance of the BandRecord class.

4) Calls a procedure to list all of the results into the form's listbox.

The BandRecord class looks like this:

 public class BandRecord
    {
        public string Band { get; set; }
        public string Album { get; set; }
        public string Song { get; set; }

    }

The ListResults procedure simply loops through the list of BandRecord instances and adds the songs for the band, album, or song search into the Listbox.

         public void ListResults(IEnumerable<BandRecord> list)
        {
            foreach (var z in list)
                try
                {
                    listBox1.Items.Add(z.Song);
                }
                catch (ArgumentNullException ex)
                {
                    continue;
                }
            int i = listBox1.Items.Count;
            itemCount.Text =i.ToString();
        }

You can see that the code is fairly streamlined, and that's a good thing. Now that the basic plumbing is working, what remains is to add features to the application and make it more useful. Things that are very much needed:

1) Making the search case-insensitive. Right now the query works only if the cases match.

2) Making the search look for any part of the term-basically a CONTAINS keyword search.

3) Provide a better view of the search results. For example, including the track numbers is not really necessary.

4) Extending the application to do more meaningful things.

Those are things that I will cover in subsequent posts.

Technorati Tags: Office 2010,Developer,Open XML,Office,Excel,Microsoft,Productivity

Rock Thought of the Day: The Stars Are Projectors by Modest Mouse

This song is getting a lot of rotation on my Zune player right now even though the album has been out a long time. The dissonance in the first part of the song is so beautiful. Out of the lush clashing of sounds emerges a mix of colors and textures that I believe cannot be found any other way. Give it a listen-- "all the stars are projects, yeah, projecting our lives down to this planet earth."

Rock On