locked
How to search within excel file? RRS feed

  • Question

  • Hi

    I have following requirement:

    1. A document library will have an excel file with around 10,000 rows. It will have one column as a primary key column. Column name is: Request ID
    2. A SharePoint site will have a page with text box and button. Users will enter Request ID in text box and click on button. Page division will display all the data from excel file associated with that Request ID

    I have tried using Infopath for this but Infopath fails running when item count increases. Here it is not possible. Uploading the data to SP list is also not possible because of many reasons like list item threshold or reporting methods(excel file will be uploaded on daily basis by some different team).

    How can I perform this search task in SharePoint? Is there a way by which we can search within excel file. May be something like - using excel services in CEWP or using filter web part and excel web access web part together on one page.

    Thanks in advance for any help.

    Regards

    Pratima

    Tuesday, February 10, 2015 11:48 AM

Answers

  • I got solution for this.

    1. Create an excel file with as much as vlookup values. In excel it is possible to refer any file in vlookup. Lookup with Request ID and retrieve all the required values. Let the name of this file be: SearchBook.xlsx. Upload it to SP site.
    2. Create an InfoPath form to have a front end. Connect this form to excel file SearchBook.xlsx using SOAP web service.
    3. Create two more data connections to set cell value and read cell value. Using these data connection set value of cell with the entered Request ID and read corresponding lookup values. Display it to the Infopath form.

    This solution perfectly works. And it is very very very less time consuming. For me it returns the result in 1-2 seconds for around 5k records in excel file.

    Tuesday, March 17, 2015 11:13 AM

All replies

  • Hi,

    You can use the excel REST service to search within excel.  One such example available is as follows

    http://blogs.msdn.com/b/cumgranosalis/archive/2009/11/02/excel-services-rest-apis-the-basics.aspx

    Please don't forget to mark it as answered, if your problem resolved or helpful.

    Tuesday, February 10, 2015 12:05 PM
  • Hi Pratima,

    You Can achieve this Using rest API

    Try below REST URL

    http://MyServer/ mywebsite/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Table('RevenueChart')?Ranges('TaxRate')=0.50&Ranges('FiscalYear')=2008&$format=html

    Reference

    http://blogs.office.com/2009/11/05/excel-services-in-sharepoint-2010-rest-api-syntax/

    Thanks & Regards

    Jaydev Deshmukh

    Tuesday, February 10, 2015 12:32 PM
  • Hi

    I am able to select the table with this URL. But for some tables page is not displaying anything. Also I am not able to use the Range filter. Is there anything that has to be taken care while creating excel files?

    Wednesday, February 11, 2015 6:06 AM
  • I got solution for this.

    1. Create an excel file with as much as vlookup values. In excel it is possible to refer any file in vlookup. Lookup with Request ID and retrieve all the required values. Let the name of this file be: SearchBook.xlsx. Upload it to SP site.
    2. Create an InfoPath form to have a front end. Connect this form to excel file SearchBook.xlsx using SOAP web service.
    3. Create two more data connections to set cell value and read cell value. Using these data connection set value of cell with the entered Request ID and read corresponding lookup values. Display it to the Infopath form.

    This solution perfectly works. And it is very very very less time consuming. For me it returns the result in 1-2 seconds for around 5k records in excel file.

    Tuesday, March 17, 2015 11:13 AM