Originally posted on: http://geekswithblogs.net/SoYouKnow/archive/2009/08/18/getting-your-feet-wet-writing-code-for-sharepoint-ndash-part-yet-again.aspx
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.
<UPDATE>
Part 4 is now posted for your viewing pleasure.
</UPDATE>
Families in Germany who are facing divers heartiness problem, such persons can buy drugs from the Web without prescription. With the market flooded with divers web-sites selling sundry medicaments, purchasing medicines from th WEB is no longer a trouble for common man. Certain medications are used to treat infections caused by dental abscesses. Of course it isn’t all. If you’re concerned about erectile health problem, you probably know about Xenical and Hoodia. Probably every adult knows about Garcinia. (Read more PhentermineXenical). The symptoms of sexual health problems in men switch on improbability to have an erection sufficient for sexual functioning. Certain medications may add to sex drive difficulties, so its vital to cooperate with your health care professional so that the prescription can be tailored to your needs. Preparatory to capture Levitra or other medicament, speak to your druggist if you have any allergies. Talk to your heartiness care purveyor for more details. Preparatory to ordering this remedy, tell your doc if you are allergic to anything.