Search Results to Excel WebPart

Hi,

There was a requirement i.e. when user searches something by entering the keyword, he will be getting the search results.

He should be able to export those search results to excel. Front end was something as below

Approach:

After entering the keyword in the search field, his entered keyword will sit as Query String (k=)as below

And there will be a page called OSSSearchResults.aspx, where search results will be displayed to the user as below

There is SharePoint object KeywordQuery where we can retrieve the search results through Query String  (k=)

if we don’t specify the particular fields that we want to retrieve as below

Then it will retrieve some default properties like WorkID, Site Name, Path etc

But if we want to retrieve the particular fields, then we have specify the properties as below

   objkeywordquery.SelectProperties.Add("Title");

            objkeywordquery.SelectProperties.Add("Path");

            objkeywordquery.SelectProperties.Add("Description");

            objkeywordquery.SelectProperties.Add("Size");

            objkeywordquery.SelectProperties.Add("Write");

            objkeywordquery.SelectProperties.Add("Author");

Below is the whole code that has been written to retrieve the search results

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Collections;

using System.Configuration;

using Microsoft.Office.Server;

using Microsoft.Office.Server.Search;

using Microsoft.Office.Server.Search.Query;

using Microsoft.Office.Server.Search.Administration;

using System.Data;

using Microsoft.SharePoint;

namespace SearchResultsToExcel.VisualWebPart1

{

    public partial class VisualWebPart1UserControl : UserControl

    {

       

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

            }

        }

        /// <summary>

        /// get query result by user

        /// </summary>

        /// <param name="siteCollectionConfig"></param>

        /// <param name="ssa"></param>

        /// <param name="strQuery"></param>

        /// <returns></returns>

        public DataTable getResult(string siteCollectionConfig, string ssa, string strQuery)

        {

            SPSite site = SPContext.Current.Site;

            KeywordQuery objkeywordquery = new KeywordQuery(site);

            objkeywordquery.QueryText = strQuery;

            objkeywordquery.SelectProperties.Add("Title");

            objkeywordquery.SelectProperties.Add("Path");

            objkeywordquery.SelectProperties.Add("Description");

            objkeywordquery.SelectProperties.Add("Size");

            objkeywordquery.SelectProperties.Add("Write");

            objkeywordquery.SelectProperties.Add("Author");

            objkeywordquery.ResultsProvider = SearchProvider.Default;

            objkeywordquery.ResultTypes = ResultType.RelevantResults;

            ResultTableCollection queryResults = objkeywordquery.Execute();

            ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];

            DataTable queryDataTable = new DataTable();

            queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

            return queryDataTable;

        }

        public void printXls(DataTable dt)

        {

            System.Web.UI.WebControls.DataGrid dgExport = null;

            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            System.IO.StringWriter strWriter = null;

            System.Web.UI.HtmlTextWriter htmlWriter = null;

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    string temp = dt.Rows[i][j].ToString();

                    bool flag = false;

                    while (temp.IndexOf("\n") != -1)

                    {

                        temp = temp.Replace("\n", "  ");

                        flag = true;

                    }

                    if (flag)

                        dt.Rows[i][j] = temp;

                }

            }

            if (dt != null)

            {

                curContext.Response.ContentType = "application/vnd.ms-excel";

                curContext.Response.ContentEncoding = System.Text.Encoding.Default;

                curContext.Response.Charset = "gb2312";

                curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");

                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

                curContext.Response.AppendHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("temp.xls", System.Text.Encoding.UTF8));

                strWriter = new System.IO.StringWriter();

                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

                dgExport = new System.Web.UI.WebControls.DataGrid();

                dgExport.DataSource = dt.DefaultView;

                dgExport.AllowPaging = false;

                dgExport.DataBind();

                dgExport.RenderControl(htmlWriter);

                curContext.Response.Write(strWriter.ToString());

                curContext.Response.End();

            }

        }

        protected void btnExportToExcel_Click(object sender, EventArgs e)

        {

            DataTable dt = getResult("", "SearchQuerySSA", Request.QueryString.Get("k").ToString());          

            System.Data.DataColumn newColumn = new System.Data.DataColumn("Creator", typeof(System.String));

            dt.Columns.Add(newColumn);

            for (int i = 0; i < dt.Rows.Count; i++)

            {

              

                if (dt.Rows[i]["Author"] != DBNull.Value)

                {

                   

                    dt.Rows[i]["Creator"] = ((string[])dt.Rows[i]["Author"])[0];                

                }

            }

            dt.AcceptChanges();

            printXls(dt);

        }

    }

}

Issues

Of course definitely there will be so many issue while developing.

Major issue was Author values were not coming in the excel sheet, through debug mode it has been noticed that Author values were coming in the format System.string[]/DBNull.Value

So through for loop i changed those values to string format as below, added one more column to the data table and got the values

dt.Rows[i]["Creator"] = ((string[])dt.Rows[i]["Author"])[0];  

Result

Finally I got the result as below

Deployment

First I added this web part to one of the aspx page in the site.

I opened that page through SharePoint designer.

I copied the Register Tag, web part zone tag of this web part that were available on this page as below

 So that when user enters keyword, based upon the entered keyword, web part can get this through Request string and get the Search Results to export Excel