Linking an Excel document to a List

Question

I have an excel document which is sent daily to me.

I have a SharePoint List that is then update daily by me with all the excel documents information (complete overwrite each day).

The list is used to generate a few calculation and metrics reports.

Is there a way to associate the List and Excel document together so I can just overwrite an excel document in a document library and the list will populate? I know I can create a List from an excel document but that is only a one time process as far as I can tell.

I have excel services turned on so I can view the document in the browser.

Currently, I use a query like this to query the List. If this can be changed to query the excel document that is another viable option.

$().SPServices({
        operation: "GetListItems",
        CAMLQuery: "<Query><OrderBy><FieldRef Name='date'/></OrderBy></Query>",
        CAMLViewFields: "<ViewFields><FieldRef Name=<fieldName>/></ViewFields>",
        listName: <list id>,
        completefunc: <functionname>
    });
});

Answer 1

It seems that you would want to upload an excel document to a document library and while that happens, do stuff.

One way to achieve this is creating an event receiver that captures the ItemUpdated event. For the "do stuff" part, you can use the openxml api, which will allow you do read data from the excel document and populate it in the list.

here are a few good links on example aproaches

http://www.sharepointwithattitude.com/archives/61

http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/5b5c96d4-17fb-42ad-8c30-e184fe26c5ef

http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/1fce430c-5e2c-401e-ad8a-99e890d1dcef

additionally, you could extend this even further by enabling incoming emails. if you set a rule in outlook to send the received email with the excel document and forward it to the doc lib, the whole process would be fully automated.

reference

http://social.technet.microsoft.com/Forums/sharepoint/en-US/866d1d99-f408-4231-8f45-a9ac33674d4a/reading-excel-data-from-sharepoint-document-library