Geeks With Blogs

News





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

Here we are at Part 4 of this blog series.  Is it everything you had hoped for? I do offer a money back guarantee if you are not completely satisfied.  Just to summarize where we are at:

  • In Part 1 we created our console application and found a specific SharePoint List on our farm
  • Part 2 had us finding a specific SPView for that SPList from Part 1 and iterating through all the fields in the view in the correct order
  • We then created an Excel Worsheet in Part 3 where we stored the entries from the SPView we previously iterated through. 

Now, here we are at Part 4.  It is time to take that Excel spreadsheet and store it in a specific document library. The steps we will take to do this are:

  1. Find The SharePoint Document Library that will be storing the Excel Spreadsheet.
  2. Convert the Excel spreadsheet into a byte array for storage in the document library
  3. Add byte array to document library

Again, this turns out to be pretty easy stuff once you figure it out.  So, once more, let’s get started so I can get to bed.

Find The SharePoint Document Library

The first thing we need to do is find the Document Library where we will be storing our Excel spreadsheet.  The code for this looks almost identical to the code we used for finding an SPList.  If you were to take this code to production, best practice would dictate combining these two methods.  However, to again keep things easier to understand for those less experienced, I will keep them separate:

// Find a specific Documnet Library with the name 'listName'
// Almost identical to code for finding SPList except the
// "GetFolder" method is called to find the doclib
private void FindSPDocLib(String listName)
{
    //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 doclib by name
                        //this does not throw an exception.  you use
                        //oFolder.Exists to see if it exists
                        SPFolder oFolder = oWeb.GetFolder(listName);
                        if (oFolder.Exists)
                        {
                            Console.WriteLine("FOUND DOCLIB!");
                            return;
                        }
                    }
                    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();
            }
        }
    }
    Console.WriteLine("Document Library '{0}' was not found!",listName);
}

Convert the Excel spreadsheet into a byte array

Now that we have found our document library, we are almost ready to call the SPFolder.Files.Add() method.  However, this method requires the data for the file be in the form of a byte array (byte[]) or a Stream.  I decided to use the byte array.  So, we need to open our previously created Excel spreadsheet and read it in as a byte array.  This can be done using the code below.  It is very important to note that I am creating one buffer for the entire file.  For larger files you may need to do multiple reads or use a different method. Again, if you don’t like it, here’s your money back.

//load the saved excel spread sheet into a byte array.  A byte array
//is required by the document library to store the file
using (System.IO.FileStream logReader = new System.IO.FileStream(
    currentDirectory + fileName, System.IO.FileMode.Open,
    System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite))
{
    //make sure the buffer is big enough to hold the file.
    //larger files may require multiple reads
    byte[] fileBytes = new byte[logReader.Length];

    //read the spreadsheet into a buffer
    int amountOfBytes = logReader.Read(fileBytes, 0, (int)logReader.Length);

    //fileBytes now contains the data for the Excel spreadsheet as a byte array
    //'AddFile' is a dummy method
    AddFile(fileBytes);
    
    //clean up
    logReader.Dispose();
}

Add byte array to document library

Time too add the byte array to the Document Library.  In the first chunk of code we found the SPFolder object for our document library (oFolder in code below). What we are going to do is get the list of files (SPFileCollection) from the SPFolder and check to see if our Excel spreadsheet already exists.  If it does exist, we will delete it.  (Another note, you could probably do something with versioning instead of deleting files that already exist.  This was not part of the original requirements and file versioning can take up a LOT of space.) Then we add the file to the SPFileCollection.  That’s all there is to it:

//get list of files from doc lib
SPFileCollection files = oFolder.Files;

//does file already exist?  if so, delete it.
Console.WriteLine("Checking to see if file already exists");
foreach (SPFile file in files)
{
    if (file.Name.Equals(fileName))
    {
        //found file! deleting!
        Console.WriteLine("file already exists, deleting old file");
        file.Delete();
        break;
    }
}

Console.WriteLine("Adding file to doclib");
//add spreadsheet to doc library
SPFile uploadedFile = files.Add(fileName,attachment);

Nothing too complicated I hope?  Play around with the SPFileCollection.Add method.  There are 13 overrides.  Some of the parameters allow you to specify Metadata, ‘Created By’, ‘Modified By’, ‘Check In Comments’, etc.  Some of these could really come in handy. In fact here’s a link for more information about it:

SPFileCollection.Add Method (Microsoft.SharePoint)

Say it with me… ‘Let’s put it all together’

We now come to the part of the blog where I put all the code we’ve written to date together.  You could copy and paste this entire chunk of code into your cs file, change the variables appropriately, add your references, and run the program.  A quick note about the code below, for the sake of making things more maintainable I would probably not push the document library name variable so far down.  I would probably surface the file name and path to the Main() and call the “FindSPDocLibAndAddFile()” method from there.  I thought about doing that for this post, but it would have required changing more of the code and I did not want to risk losing someone who may be new and following this series closely.

Rather than sum up the blog after the mess of code that’s about to display, let me take this moment to once again thank you for stopping by, and as always please leave a comment and let me know how I can help you get more of these posts! Stay tuned for the final post in the series where we will do some clean up on this code and make it something you could actually run as a scheduled task. 

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";
    String docLibName = "Approval%20Documents";

    bool bStored = StoreSPInfo(listName, sGuid, docLibName);

    //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 uploaded to document library!!  Press enter to exit");
    }

    //wait for an enter key press
    Console.ReadLine();
}

private static bool StoreSPInfo(String listName, String sGuid, String docLibName)
{
    //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, docLibName);
                        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, String docLibName)
{
    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();

    FindSPDocLibAndAddFile(currentDirectory, fileName, docLibName);

}

// Find a specific Documnet Library with the name 'listName'
// Almost identical to code for finding SPList except the
// "GetFolder" method is called to find the doclib
private static void FindSPDocLibAndAddFile(String path, String fileName, String docLibName)
{
    //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 doclib by name
                        //this does not throw an exception.  you use
                        //oFolder.Exists to see if it exists
                        SPFolder oFolder = oWeb.GetFolder(docLibName);
                        if (oFolder.Exists)
                        {
                            Console.WriteLine("FOUND DOCLIB!");
                            AddFileToDocLib(path, fileName, oFolder);
                            return;
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Exception occured adding file to document library: {0}\r\n{1}", e.Message, e.StackTrace);
                    }
                    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();
            }
        }
    }
    Console.WriteLine("Document Library '{0}' was not found!", docLibName);
}

private static void AddFileToDocLib(String path, String fileName, SPFolder oFolder)
{
    //load the saved excel spread sheet into a byte array.  A byte array
    //is required by the document library to store the file
    using (System.IO.FileStream logReader = new System.IO.FileStream(
        path + fileName, System.IO.FileMode.Open,
        System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite))
    {
        //make sure the buffer is big enough to hold the file.
        //larger files may require multiple reads
        byte[] fileBytes = new byte[logReader.Length];

        //read the spreadsheet into a buffer
        int amountOfBytes = logReader.Read(fileBytes, 0, (int)logReader.Length);

        //fileBytes now contains the data for the Excel spreadsheet as a byte array
        //get list of files from doc lib
        SPFileCollection files = oFolder.Files;

        //does file already exist?  if so, delete it.
        Console.WriteLine("Checking to see if file already exists");
        foreach (SPFile file in files)
        {
            if (file.Name.Equals(fileName))
            {
                //found file! deleting!
                Console.WriteLine("file already exists, deleting old file");
                file.Delete();
                break;
            }
        }

        Console.WriteLine("Adding file to doclib");
        //add spreadsheet to doc library
        SPFile uploadedFile = files.Add(fileName, fileBytes);
        
        //clean up
        logReader.Dispose();

        //let's do some more cleanup and delete the excel spreadsheet
        //that is stored on the file system
        File.Delete(path + "\\" + fileName);
    }
}
Posted on Monday, August 24, 2009 2:37 PM | Back to top


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

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
I miss the 3rd series ... But I keep following :)
Left by Nyubi on Aug 31, 2009 3:26 AM

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
Thanks for the sharing & updating such post.
Left by MCSE Training on Sep 08, 2009 6:52 AM

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
Very well described!
Left by CCNA Boot Camp on Sep 13, 2009 10:45 PM

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
Thanks, this was really helpful :o)
Left by jennifer day on Oct 07, 2009 5:19 AM

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
How to open document from document library through custome code.
Left by Anand on Oct 12, 2009 8:59 AM

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
I never knew sharepoint code can be written in this manner. Thanks for the info!
Left by IT Training on Jul 09, 2010 12:25 AM

# re: Getting Your Feet Wet Writing Code For SharePoint – Part 4 of 5
Requesting Gravatar...
it's really fantastic blog post....thanks
Left by girish on Feb 24, 2011 7:41 AM

Your comment:
 (will show your gravatar)


Copyright © Mark Rackley | Powered by: GeeksWithBlogs.net