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?

  • 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

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


Some Code Examples




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


Hope this helps.

| improve this answer | |

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;

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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