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:
- Find The SharePoint Document Library that will be storing the Excel Spreadsheet.
- Convert the Excel spreadsheet into a byte array for storage in the document library
- 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);
}
}