Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have an excel spreadsheet that I would like to use as data for a SharePoint 2010 list. I know how to do this without code, but for this particular task, I need a coded solution.

Does anyone know how to create a SharePoint 2010 list and populate the list with items from an excel spreadsheet? Here is what I have so far:

using (SPSite site = new SPSite(@"http://sitecollection/"))
        {
            SPWeb web = site.RootWeb;
            Guid listGuid = web.Lists.Add("List Title", "List Description", SPListTemplateType.GenericList);
        }

Now how do I add the SPListItems that are populated using the excel spreadsheet?

share|improve this question
    
Where is the excel spreadsheet? Uploaded into a document library? Where is this code running? From VS on the server directly? or as part of something like a workflow? –  Zork May 14 '12 at 20:01
    
This code is running from Visual Studio on the server directly. The spreadsheet is on the hard drive but could easily be uploaded to a document library. This is not part of a workflow –  Meyer Denney May 14 '12 at 20:15
add comment

3 Answers

You may need to use the Office COM Interop libraries to access the Excel file, pull the necessary data, and save it to the appropriate list columns. That's what I've used in the past for Excel automation. Let me give you some resources to get started.

The Class Library

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.14%29.aspx

Some Code Examples

http://msdn.microsoft.com/en-us/library/ms173186%28v=vs.80%29.aspx

http://msdn.microsoft.com/en-us/library/dd264733%28VS.100%29.aspx

http://www.dotnetperls.com/excel

But remember, these are interface wrappers around COM objects, so you need to explicitly clean up your code:

http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp

Hope this helps.

share|improve this answer
add comment

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b6e8a28c-6760-4e86-a1aa-e2ce9ec36380 has all of the information you need to pull the excel values and then it is a simple matter of cycling through each row and using something similar to the below

SPListItem myItem = myList.Add();
myItem["Field1"] = value1;
myItem["Field2"] = value2;
//etc....

myItem.Update();
share|improve this answer
add comment

I've recommended this before, but we have had pretty good luck with NPOI. It works best when the excel file is simple.

NPOI basically gives you the API to iterate over the excel file and then you'd just call SharePoint's API to create a list, columns, and then items.

A quick search also revealed this codeplex project: http://spreadsheet2splist.codeplex.com/. I haven't used it so I'm not sure if it will work for your situation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.