Tuesday, May 12, 2009

Programmatically Export records from SPList to MS Excel

Programmatically export records from SPList to MS Excel in SharePoint.
What does above sentence mean???

Basically if you are a SharePoint developer OR user then you might know that we can do Import/Export between SPList and Microsoft Excel.
In this post I will discuss only about Export of records from SPList to MS Excel.
If you open a SharePoint's SPList, then in Action Toolbar, contains a link to open/export the SPList (in current applied SPView) in Excel Workbook and hence save it a Excel file (*.xls / *.iqv).

Now to achieve the above task programmatically we have to do some extra efforts:

1) Get the GUID of SPList - from where you want to export the records/SPListItem.



//SPList oList = ""; //Code to get the SPList

//Here I convert the GUID in Uppercase
//and remove the special character hypen ('-') with required string.
string listGUID = oList.ID.ToString().ToUpper().Replace("-", "\u00252D");



2) Get the GUID of SPView - it can be default one (All Items) or custom SPView with required columns and settings like sorting, filter, etc.



//Here I convert the GUID in Uppercase
//and remove the special character hypen ('-') with required string.
string viewGUID
= oList.Views["ExportRecords"].ID.ToString().ToUpper().Replace("-", "\u00252D");



3) Build the command, that will get executed on link's click.


//string spListName --- is SPList name.
//I removed the special characters in that also.

//Here for readibility point of view I used string,
//you must use StringBuilder from performance point of view.
string strCommand = "javascript:EnsureSSImporter();"
+ "javaScript:ExportList('\u002f_vti_bin\u002fowssvr.dll?CS=65001\u0026"
+ "Using=_layouts\u002fquery.iqy\u0026"
+ "List=\u00257B" + listGUID + "\u00257D\u0026"
+ "View=\u00257B" + viewGUID + "\u00257D\u0026"
+ "RootFolder=\u00252FLists\u00252F"
+ spListName.Replace("_", "\u00255F") + "\u0026"
+ "CacheControl=1')";



4) Consume/Call the above command via link's click.


<a id="linkExportData" onclick="<%=strCommand %>"
class="ms-sitetitle" style="font-size: 10px;">Export Records</a>




You can write the C# code of Step 1,2 and 3 in Page_Load event and HTML code of step 4 anywhere in you aspx page.

So when you click the "Export Records" link, it pop-up a dialog box to open the Records/SPList in Execl Workbook, that you can later on SAVE AS Excel file.

Once again IE Developer toolbar helped me to find the above solution/trick :)

9 comments:

festivalarmband said...

Thanx.
Work like a dream!!!!
/Oren

Alexis said...

I don't often work with excel files.But some days ago I used it.But then I opened it again and didn't see anything.A friend advised this tool-xls file recovery.It helped me very easy and for free.Besides it demonstrated how can be easily solved with Excel recovery software for recovery xls.

aviramin said...

Hi,

is it possible ot export to splist to word document?

Avinash said...

Hello Aviramin,

I don't think its possible in SP 2007.
In this post I am using in-built feature of SharePoint, that open the SPList in Excel Spreadsheet.

But programmatically you can read SPList and populate WORD.

In case of Word, you can populate pre-defined Bookmarks (using for-loop iteration).

kais said...

hi ,

I made the same solution but now i'm focusing on to put an spquery in that iqy file to get only fields i need.

Have u made this? please tell me if u got a trick or hint.
thanks

Avinash said...

Hi Kais,

I am too not able to display the filtered columns in Excel file :(

As this is SP in-built functionality, we don't have much control.

You can try with filtered SPView and then click "Export to Spreadsheet" (in toolbar) ... if it works then it will definitely work in our custom code....

Please do let me know if you find any solution :)

Alex said...

This week I have seen a lot of excel files. Some of them were corrupted and I didn't know what to do then. I was upset. But enexpectedly my brother called me up and advised - recover Excel file xlsx. It helped me for seconds and without money as I kepy in mind. I have been using for short time,but it likes me.

Soorya said...

Hi Avinash,

This is very information and thank you for this post.

Is it possible to attach workflow with it so that some action happen then this code run by workflow.

Thank you

Sri

Anonymous said...

hi Avinash,
I am getting "object expected" error in the script.when i click on "export Records" link

Google