how to export the data from a webpart to excel and pdf in sharepoint 2010

Question

I have a custom Web Part which retrieve the data from SQL Server 2008 manipulates it and shows the data table in UI.

Below is the UI of the data on the Web Part. The data contains URLs, Dates, Names etc. And the data is huge. I have applied pagination to display only 10 records per Page.

Now I want to add a button to export/download all the data to excel and PDF. Is there any way to do it from Client Side or do I have to write server side code only.

Answer 1

You need to use Server Side Code.

The easiest option is to convert the back end DataTable to comma-seperated file. Then then use Response.Write to save it as a csv file. This file can be opened using Excel.

response.ClearContent();
response.Clear();
response.ContentType = "text/plain";
response.AddHeader("Content-Disposition", "attachment; filename=DownloadedData.txt;");

StringBuilder sb = new StringBuilder(); 

string[] columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString()).
                                    ToArray();
    sb.AppendLine(string.Join(",", fields));
}

// the most easy way as you have type it
response.Write(sb.ToString());

response.Flush();
response.End();

https://stackoverflow.com/questions/16582993/how-to-save-a-datatable-as-a-csv-file-on-client-pc-asp-net

Answer 2

the export to excel can be done by client side. The SPGRidView is rendered as table itself so a simple javascript can suffice exporting to excel.This is the code

<input type="button" id="Button1"  value="Export" onclick="TableToExcel('TableID');" />

 function TableToExcel(tableid) {
    var id = $('[id$="' + tableid + '"]');
    var output = $('<div></div>').html(id.clone()).html();       
    window.clipboardData.setData("Text", output);
    var objExcel = new ActiveXObject("Excel.Application");
    objExcel.visible = false; var objWorkbook = objExcel.Workbooks.Add; var objWorksheet = objWorkbook.Worksheets(1);      
    objWorksheet.Columns.columnWidth = 35;
    objWorksheet.Paste;
    objExcel.visible = true;
}