Geeks With Blogs

News





The SharePoint Hillbilly Fewer Big Words... More Pretty Pictures...

Here we are! Half way through! Actually… half-way through this post will be halfway.  So, we are not quite half-way done now and will be more than halfway done at the end. Do you half-way understand?  By the way, thank you for putting up with me.  Now you know what my wife goes through every day. 

So, at this point you have read Part 1 and Part 2 of this blog series and we have a nice little console application that iterates through a particular View of a SharePoint List and writes the field name/value pairs out to the console window.  Everyone still with me?  Are you learning anything?  In this post we will be taking those field names and values and storing them in an Excel spreadsheet. 

This may be the smallest of the blog posts as we will be leveraging functionality offered by the Office Primary Interop Assemblies to create the Excel spreadsheet which greatly reduces the effort involved.  Also, I am using Version 12 of “Microsoft.Office.Interop.Excel”.  Version 11 is for Office 2003 and Version 12 is for Office 2007.  The API I use here works for both (I double checked to be sure). 

Once again, let’s get started

First thing you need to do is add a reference to “Microsoft.Office.Interop.Excel” (refer to Part 1 if you forgot how to add a reference).

After the reference is added be sure to add the following “using” statement to your code:

using Excel = Microsoft.Office.Interop.Excel;

Although not important for this particular application, if you are also using one of the other Interop Assemblies it important to name the “using” statement like I did here to help avoid ambiguous references to objects.  No, you don't have to.

So! We know how to get our data for our Excel spreadsheet from the previous two blog posts and we have added the correct reference to create our Excel spreadsheet.  Let’s write some code.  How many steps I can break this down into:

  • Create filename for the Excel spreadsheet
  • Determine directory to save file in
  • Delete file if it already exists
  • Create Excel spreadsheet
  • Store SPView Data in WorkSheet
  • Save Excel spreadsheet

Create filename for the Excel spreadsheet

First thing we need to do is determine what we are going to name our spreadsheet.  Any ideas?  Well, this application is going to be a scheduled task right? So, part of our filename could be the date?  Also, the spreadsheet is for a particular view.  So, the filename could also contain the name of the View.  Therefore, the file name could be the View name followed by the date?  What a great idea!

First thing we need to do is get the name for the view, after all we just passed in the GUID remember?  Using the SPView we found in Part 2 we can easily get the name of the View as follows:

String fileName = oView.Title;

Now we need to append the current date to the filename (let’s put a space between the view name and date as well to make it more readable):

fileName += “ “ + System.DateTime.Now.Date.ToShortDateString();

One last thing, we need to remove those pesky “/” from the filename.  Let’s replace them with a “-“ instead:

fileName = fileName.Replace('/','-');

Oh, don’t forget to append the “xls” extension to the filename:

fileName += ".xls";

If you are one of those people who can’t stand to do things in multiple lines of code that can be done in one:

String fileName = oView.Title + “ “ + System.DateTime.Now.Date.ToShortDateString().Replace('/', '-') + “.xls”;

If you prefer, use a StringBuilder to do the above. There’s many ways of doing some of this stuff. I’m trying to keep it as simple as possible. Regardless, we now have the fileName for our spreadsheet (and there was much rejoicing).

Determine directory to save file in

We also need to specify where we are going to save the file locally.  For the purposes of my application, when all is said and done this is a temporary file that will be deleted after we have uploaded it to the document library (wait for Part 5).  So, I want to just save it in the same directory the application is running in. 

String currentDirectory = Directory.GetCurrentDirectory() + "\\";

Be sure to add a using statement for “System.IO” in order to use the Directory object.

using System.IO;

That’s all there is to getting the directory, pretty easy? eh? On second thought, maybe this series is not such a good thing.  The more people that know how to do this stuff and realize how easy it is, the less I’m needed.  Hmmm… something I should have thought about sooner.

Delete file if it already exists

Where were we? Oh yes, we now have the file name and the current directory.  We need to make sure the file does not already exist.  If it does, we’ll just delete it. This is also really simple code:

 if (File.Exists(currentDirectory + fileName))
 {
       File.Delete(currentDirectory + fileName);
 }

Create Excel spreadsheet

Okay, so we got all of that requisite garbage out of the way, let’s create our Excel spreadsheet and get it ready to start populating with our View data:

//we use Missing.Value when we don’t have some of the parameters below
object misValue = System.Reflection.Missing.Value;

//Create Excel Application
Excel.Application xlApp = new Excel.ApplicationClass();;

//Create Workbook in Excel Application
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);

//get the WorkSheet for the WorkBook
Excel.Worksheet xlWorkSheet  = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Store SPView Data in WorkSheet

We now have an Excel Worksheet where we can store our data from the the 2nd blog post.  Writing to a cell of a WorkSheet is as easy as:

xlWorkSheet.Cells[row, column] = value;

All we need to do now is maintain the correct row and column values when we iterate through our View data and store the data accordingly.  Using the code from Part 2, that code may look something like:

//initialize row number
int row = 0;

//go through each row in the list for the view
foreach (SPListItem item in oItemCol)
{
    //increment row value (Excel Spreadsheet rows and columns are 1 based)
    row++;
    //starting a new row, initialize column number
    int column = 1;

    //get each field from the view and get value from list
    foreach (String viewField in stringCol)
    {
        //get field from row of data (viewField will be iterated through in order)
        SPField field = item.Fields.GetField(viewField);

        //get the value for the field (we are getting it as text for this application, so if you don’t need text you’ll need to play around here)
        String fieldValue = field.GetFieldValueAsText(item[field.InternalName]);
        
        //store value in appropriate row and column
        xlWorkSheet.Cells[row, column++] = fieldValue;
    }
}  

Okay, so now you have a WorkSheet with all of your View data in it, however field names are not in the WorkSheet, just the field values.  I can see how you might want the first row of the spreadsheet to contain the names of the fields. This can be done by storing the field titles the first time through the iteration, and only the first time.

//initialize row number
int row = 1;
// boolean value to determine if field names have been 
// added to worksheet yet
bool fieldNamesAdded = false;

//go through each row in the list for the view
foreach (SPListItem item in oItemCol)
{
    //increment row value (Excel Spreadsheet rows and columns are 1 based)
    //we are starting the row out at 2 so that the first row of the Worksheet (row - 1)
    //can contain the field names
    row++;
    //starting a new row, initialize column number
    int column = 1;

    //get each field from the view and get value from list
    foreach (String viewField in stringCol)
    {
        //get field from row of data (viewField will be iterated through in order)
        SPField field = item.Fields.GetField(viewField);

        //get the value for the field (we are getting it as text for this application, so if you don’t need text you’ll need to play around here)
        String fieldValue = field.GetFieldValueAsText(item[field.InternalName]);
        
        //if the field names have not been added, be sure to add them
        if (!fieldNamesAdded)
        {
            //get the display name of the current field
            String fieldTitle = field.Title;
            
            //store field names in appropriate row and column
            xlWorkSheet.Cells[row - 1, column] = fieldTitle;
        }
        
        //store value in appropriate row and column
        xlWorkSheet.Cells[row, column++] = fieldValue;
    }
    //field names have been added, don't allow them to be added again
    fieldNamesAdded = true;
}  

Again, you may have come up with something more elegant, but I’m trying to keep this code simple and understandable, but please feel free to post improvements in the comments.

Save Excel spreadsheet

Now that we have created our Excel spreadsheet we just need to save it.  Again, this is fairly straightforward:

//save excel spreadsheet locally
xlWorkBook.SaveAs(currentDirectory + fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
 

Time to put it all together… again…

So, once more, let’s put it all together. 

static void Main(string[] args)
{
    //specify the name of the list your want to find
    String listName = "AuthorizedRequestors";
    String sGuid = "7674ABF8-C71D-49FB-A9A3-7FD45993EDEC";

    bool bStored = StoreSPInfo(listName, sGuid);

    //write a message to the console window
    if (!bStored)
    {
        Console.WriteLine("There was a problem getting or storing list: {0}", listName);
    }
    else
    {
        Console.WriteLine("Spreadsheet successfully created!!  Press enter to exit");
    }
    //wait for an enter key press
    Console.ReadLine();
}

private static bool StoreSPInfo(String listName, String sGuid)
{
    //go through each SPWebApplication
    foreach (SPWebApplication oWebApp in SPWebService.ContentService.WebApplications)
    {
        Console.WriteLine("Searching WebApp: " + oWebApp.DisplayName.ToString());
        //go through each SPSite
        foreach (SPSite oSite in oWebApp.Sites)
        {
            Console.WriteLine("Searching Site: " + oSite.Url.ToString());
            try
            {
                //go throuth each SPWeb in the SPSite
                foreach (SPWeb oWeb in oSite.AllWebs)
                {
                    Console.WriteLine("Searching web: " + oWeb.Url.ToString());
                    try
                    {
                        //get the list based upon name.
                        //if list is not in spweb this will cause an exception
                        //to be thrown.  This is Ok. so, just catch it and continue
                        //I know.. I know.. this is not good practice.  But there is not
                        //a oWeb.ListExists() method or something like that.
                        SPList oList = oWeb.Lists[listName];
                        //list was found!
                        Console.WriteLine("LIST FOUND!");
                        StoreViewForList(oList, sGuid);
                        return true;
                    }
                    catch
                    {
                        //catch and continue
                        Console.WriteLine("List not found in web: " + oWeb.Url.ToString());
                    }
                    finally
                    {
                        //make sure to dispose of web
                        oWeb.Dispose();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception occured: {0}\r\n{1}", e.Message, e.StackTrace);
            }
            finally
            {
                oSite.Dispose();
            }
        }
    }
    return false;
}

private static void StoreViewForList(SPList oList, String sGuid)
{
    SPView oView = oList.GetView(new Guid(sGuid));
    SPListItemCollection oItemCol = oList.GetItems(oView);
    //get the list of fields in the view, in order
    SPViewFieldCollection collViewFields = oView.ViewFields;
    //put list of view fields in a string collection to iterate through
    StringCollection stringCol = collViewFields.ToStringCollection();

    //Get file name for Excel spreadsheet
    String fileName = oView.Title + " " + System.DateTime.Now.Date.ToShortDateString().Replace('/', '-') + ".xls";

    //Get the directory where the Excel spreadsheet will be saved
    String currentDirectory = Directory.GetCurrentDirectory() + "\\";

    Console.WriteLine("Excel Spreadsheet to create: " + currentDirectory + fileName);

    //delete the spreadsheet if it already exists
    if (File.Exists(currentDirectory + fileName))
    {
        Console.WriteLine("Spreadsheet Exists! Deleting...");
        File.Delete(currentDirectory + fileName);
    }

    //Create Excel Spreadsheet
    //we don't have a template for our WorkBook so we will be passing in Missing.Value
    object misValue = System.Reflection.Missing.Value;

    //Create Excel Application
    Excel.Application xlApp = new Excel.ApplicationClass(); 
    //Create Workbook in Excel Application
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    //get the WorkSheet for the WorkBook
    Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    //initialize row number
    int row = 1;
    // boolean value to determine if field names have been 
    // added to worksheet yet
    bool fieldNamesAdded = false;

    //go through each row in the list for the view
    Console.WriteLine("Storing View Data in SpreadSheet");
    foreach (SPListItem item in oItemCol)
    {
        //increment row value (Excel Spreadsheet rows and columns are 1 based)
        //we are starting the row out at 2 so that the first row of the Worksheet (row - 1)
        //can contain the field names
        row++;
        //starting a new row, initialize column number
        int column = 1;

        //get each field from the view and get value from list
        foreach (String viewField in stringCol)
        {
            //get field from row of data (viewField will be iterated through in order)
            SPField field = item.Fields.GetField(viewField);

            //get the value for the field (we are getting it as text for this application, so if you don’t need text you’ll need to play around here)
            String fieldValue = field.GetFieldValueAsText(item[field.InternalName]);

            //if the field names have not been added, be sure to add them
            if (!fieldNamesAdded)
            {
                //get the display name of the current field
                String fieldTitle = field.Title;

                //store field names in appropriate row and column
                xlWorkSheet.Cells[row - 1, column] = fieldTitle;
            }

            //store value in appropriate row and column
            xlWorkSheet.Cells[row, column++] = fieldValue;
        }
        //field names have been added, don't allow them to be added again
        fieldNamesAdded = true;
    }

    Console.WriteLine("Saving Spreadsheet...");
    //save excel spreadsheet and clean up
    xlWorkBook.SaveAs(currentDirectory + fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

}

Now, when we run our application an Excel spreadsheet is created named with the View name and current date.  The file is created in the same directory as the application and contains all of our SPList data for a particular SPView, with the field names in the first row.

Stay tuned for the next blog post where we will upload this file to a specific document library.

Part 4 is now posted for your viewing pleasure.

Posted on Tuesday, August 18, 2009 4:38 PM | Back to top


Comments on this post: Getting Your Feet Wet Writing Code For SharePoint – Part 3 of 5

# SharePoint Contest $2000
Requesting Gravatar...
Please let your folks know about the "Share the SharePoint" contest started September 1, running through October 16, 2009, with the first place award of $2000. Microsoft is participating as one of the judges along with Georgia Tech and other commercial organizations, for SharePoint sites that meet certain Rules and Guidelines for submission which can be found along with FAQ's, etc. at www.sharepointportals.com . In addition to the awards, entrants will be considered for a case study or video. Only submissions from Alabama, Georgia, Florida, Tennessee, North Carolina, and South Carolina are eligible. Second and third place awards are valued at approximately $500.
Left by Ken Guthrie on Sep 02, 2009 7:27 PM

Your comment:
 (will show your gravatar)


Copyright © Mark Rackley | Powered by: GeeksWithBlogs.net