is there any way to programatically download data list from sharepoint into excel

Question

we have some data (list) stored in sharepoint. i can manually click "Actions"-> "Export to Spreadsheet" and then run a bunch of code on the Excel output.

I now want to do this on a daily basis from a C# application. Is there anyway to programatically automated that download steps that i am doing now. (the site DOES require authentication)

the "Export to Spreadsheet" fires off some javascript (so doesn't directly just point to a URL so i can read in a URL directly (i dont think)

Answer 1

if you are not limited to excel and can use other office products, MS-Access 2007 has a very good integration with sharepoint. you can write your macro and vb scripts to run on that with minor adjustments.

you can also use it as a transfer application, if you build a simple EXPORT TO EXCEL macro, and then set it to run periodically.

Answer 2

Basic approach: One you have created the spreadsheet with "Export to Spreadsheet" in SharePoint, the excel file holds a list which is linked to the sharepoint list.

This is a list object in Excel which allows for refresh from the same SharePoint list at a later date, make changes in excel and update the SharePoint list from these. So updating your download is really a refresh of the list which you originate in Excel

Answer 3

I would recommend getting the data using SharePoint web services and then using NPOI to convert the data into an excel spreadsheet; Use web services so that you are not limited to running the app on the server and NPOI for reading/writing to excel.