none
Not able to export search results more than 10,000 records to excel.

    Question

  • Hi,

    I'm using the below code to export the search results to excel. but i can export up to 10 thousand records not more than that. I have increased the timeout(seconds) to 600000 in Proxy server for crawling and federation in CA, hence I'm not getting any error time-out exception.

    public DataTable getResult(string siteCollectionConfig, string ssa, string strQuery)
            {
                ResultTableCollection rtc = null;
                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.RowLimit = 99999;
                objkeywordquery.RowsPerPage = 500;
              
                objkeywordquery.TrimDuplicates = false;
                objkeywordquery.EnableStemming = true;
                
    
                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", Page.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);
    
    
            }


    And i want to use the refinement filters too. let me know how i can implement in the above code.


    Please help me solve this. 

    Thanks in advance.

    Tuesday, June 05, 2018 9:54 AM

All replies

  • Hi,

    We can use KeywordQuery.Refiners property to set refiners to be returned in a search result. The value for each refiner is a format string that specifies the name of a refiner and optionally a set of refinement options for this refiner.

    More information about the Refine queries in the following article for your reference:

    https://docs.microsoft.com/en-us/sharepoint/dev/general-development/query-refinement-in-sharepoint#SP15_Adding_refiners

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, June 06, 2018 6:55 AM
    Moderator
  • Hi,

    Thanks for the reply, can you please explain how can pass the value to KeywordQuery.Refiners from sharepoint url.

    Thanks in advance !!


    Thursday, June 07, 2018 6:03 AM
  • Hi,

    Do you want to pass the value to KeywordQuery.Refiners from SharePoint url?

    if that is the case, you can use QueryString to achieve it.

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, June 07, 2018 7:04 AM
    Moderator
  • Yes correct, but how can i get the particular value of refiner (i can see some GUID for the r value) from URL

    URL: abc.com/s/newsite/subsite/SitePages/SearchResults.aspx?k=apple#Default=%7B%22k%22%3A%22apple%22%2C%22r%22%3A%5B%7B%22n%22%3A%22DisplayAuthor%22%2C%22t%22%3A%5B%22%5C%22%C7%82%C7%824a6f686e736f6e2c204a6f736570682053%5C%22%22%5D%2C%22o%22%3A%22and%22%2C%22k%22%3Afalse%2C%22m%22%3Anull%7D%5D%7D

    how to achieve this?



    Thursday, June 07, 2018 7:21 AM
  • Hi,

    We can use the code below to decode the URL.

    string urlStr=HttpUtility.UrlDecode(url);

    Get this: http://abc.com/s/newsite/subsite/SitePages/SearchResults.aspx?k=apple#Default={"k":"apple","r":[{"n":"DisplayAuthor","t":["\"ǂǂ4a6f686e736f6e2c204a6f736570682053\""],"o":"and","k":false,"m":null}]}

    And use the code below convert "4a6f686e736f6e2c204a6f736570682053" from HEX to ASCII, you'll get "Johnson, Joseph S".

    var hexString = "4a6f686e736f6e2c204a6f736570682053";
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i <= hexString.Length - 2; i += 2)
    {
       sb.Append(Convert.ToString(Convert.ToChar(Int32.Parse(hexString.Substring(i, 2), System.Globalization.NumberStyles.HexNumber))));
    }

    You can refer to the article below to get more help.

    https://www.eliostruyf.com/part-5-search-refiner-control-methods-explained/

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, June 07, 2018 8:24 AM
    Moderator
  • Thanks Dennis for quick reply, Any idea how to get the hexstring "4a6f686e736f6e2c204a6f736570682053" from decoded url?



    Thursday, June 07, 2018 2:40 PM
  • Hi,

    The following code for your reference:

    string str = "http://abc.com/s/newsite/subsite/SitePages/SearchResults.aspx?k=apple#Default=%7B%22k%22%3A%22apple%22%2C%22r%22%3A%5B%7B%22n%22%3A%22DisplayAuthor%22%2C%22t%22%3A%5B%22%5C%22%C7%82%C7%824a6f686e736f6e2c204a6f736570682053%5C%22%22%5D%2C%22o%22%3A%22and%22%2C%22k%22%3Afalse%2C%22m%22%3Anull%7D%5D%7D";  
    var decodeUrl = HttpUtility.UrlDecode(str);
    var defualtValue = Regex.Split(decodeUrl,"Default=")[1];
    JObject jsonStr = JObject.Parse(defualtValue);    
    var hexString = jsonStr["r"][0]["t"][0].ToString().Split('ǂ')[2].Split('\"')[0];

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, June 08, 2018 2:03 AM
    Moderator
  • Hi Dennis,

    Thanks for the code. Now I'm facing issue in using JObject in the code. i tried including the NewtonSoft.Json.dll of version 11 and 9 in the solution. I'm getting the error as "Could not load file or assembly 'Newtonsoft.Json, Version=11.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The system cannot find the file specified.":"Newtonsoft.Json, Version=11.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed"} System.IO.FileNotFoundException".

    how to solve this or any alternative code for this?

    Thanks in advance !!

    Friday, June 08, 2018 9:08 AM
  • Hi,

    Install the package below in the visual studio.

    Install-Package Newtonsoft.Json -Version 11.0.2 

    https://www.nuget.org/packages/newtonsoft.json/

    Best Regards,
    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, June 08, 2018 9:12 AM
    Moderator
  • Hi Dennis,

    i have installed the package Newtonsoft.json-version 11.0.2. but i still get the below error.

    System.IO.FileNotFoundException:

    'Could not load file or assembly 'Newtonsoft.Json, Version=11.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The system cannot find the file specified.'

    How to solve this?

    Thanks in advance !!

    Friday, June 08, 2018 2:07 PM
  • Hi Dennis,

    After adding the Newtonsoft.json ddl in virtual directory it is working fine now.

    But, I have one more concern here, how to get the full URL of the site? I'm not able to get the values in URL after K=apple. I'm able to get the URL up to k=apple as mentioned below:

    http://abc.com/s/newsite/subsite/SitePages/SearchResults.aspx?k=apple

    through below mentioned code I'm able to get the URL up to k=apple:

    string thisPageUrl = HttpContext.Current.Request.Url.ToString();

    or

    Uri myUri = new Uri(HttpContext.Current.Request.Url.AbsoluteUri);

    How to get the full URL after the keyword parameter i.e. #Default=%7B%22k%22%3A%22apple%22%2C%22r%22%3A%5B%7B%22n%22%3A%22DisplayAuthor%22%2C%22t%22%3A%5B%22%5C%22%C7%82%C7%824a6f686e736f6e2c204a6f736570682053%5C%22%22%5D%2C%22o%22%3A%22and%22%2C%22k%22%3Afalse%2C%22m%22%3Anull%7D%5D%7D"

    Thanks in advance !!


    Monday, June 11, 2018 9:19 AM
  • Hi,

    If the code below doesn't works, as a workaround, we can use JavaScript to get the current page url (window.location.href), and set value to a hidden control, then get the url from this hidden, or using ajax to send the value to the server.

    string thisPageUrl = HttpContext.Current.Request.Url.ToString();

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, June 13, 2018 1:49 AM
    Moderator
  • Hi Dennis,

    Thanks for the reply, I'm not able to get full URL from the above code. i have one more concern here can you please explain with hexstring how we can pass to querytext with refiners?

    KeywordQuery.Refiners is fatching the whole 10,000 records.

    Thanks!!


    Monday, June 18, 2018 12:23 PM