SharePoint Export external data to Excel with asp.net

External list does not provide the export to excel – it displays the command but it is greyed out. So what can you do? Well just write your own custom action and put a application page behind it and now you can do anything.

At first you can provide an user interface which helps the user to select the external data he wants to export. The export itself can be done by using the response. Just look at the code below, i commented it:



//first, we tell what kind of file the response should write
string attach = "attachment;filename=myExcel.xls";
Response.Clear();
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.UTF32;
Response.Charset = "";
Response.AddHeader("content-disposition", attach);
Response.ContentType = "application/vnd.ms-excel";


Now we fill the first line with some data. To step to the next column use “\t”. The data comes from my business data catalog but it could also be a webservice.



Response.Write(mydata1.Replace("\n",string.Empty).Replace("\r", string.Empty)+ " \t");
Response.Write(mydata2.Replace("\n", string.Empty).Replace("\r", string.Empty) + " \t");
Response.Write(mydata3.Replace("\n", string.Empty).Replace("\r", string.Empty) + " \t");
//Next Row
Response.Write(System.Environment.NewLine);

//Sequence end
Response.Flush();
Response.End();


Important notice: If your data is text and it contains line breaks, the line break will also take effect in your excel sheet. In mines it jumps to the next row instead stay in the column. Therefore i used the replace function, but i am sure there is a better option – Regex as example  – and i would expect that it is possible to create a line break in the column itself.

But that’s just a post to give some basics with an really easy example.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.