In this article I will explain how can we download and read excel file using excel services in a simple way.

Step 1
  • Open document library where you have uploaded excel files in SharePoint.
  • Copy path of document library and add the path into trusted location to enable excel services.
Steps to add Path in Trusted File Location:-
  1. Open Shared Services from central administration.
  2. Go to Excel Service section and click on Trusted File Location.

    1.gif

  3. A new window will be open, Click on Add Trusted File Location.

    2.gif

  4. A new window will be open to add the path into trusted file location, paste the url in address field.

    3.gif

  5. Click on OK button. Now document library added in trusted file location.

    4.gif
Step 2
  • Open Visual Studio >> File >> New >>Project choose console application from C# Project Window.

    5.gif

  • Right click on ExcelServiceTest solution and select Add Web Reference.

    6.gif

  • In add web reference window specify excel web service url i.e http:// <sharepoint site url>/_vti_bin/excelservice.asmx.

    7.gif

  • Give the web reference name as XLService and click on Add Reference button. Now Excel Service web reference is added in the ExcelServiceTest solution as shown below.

    8.gif

  • Add namespace of the XLService reference in Program.cs file

    using ExcelServiceTest.XLService;
Function for Reading Excel File:-

9.gif
ReadExcelFile contains following arguments-
  1. strFileURL- Give the url of the excel i.e.

    http://<sharepointsiteurl>/<documentlibrary>/excelfilename.xlsx
  2. strSheetName- Give the name of sheet which you want to read i.e. Sheet1.
  3. strRange- Give the range of sheet i.e. A1:D4.
Function for download excel file:-

10.gif
DownloadXLFile contains following arguments-
  1. strPath- Give the url of the excel i.e.

    http://<sharepointsiteurl>/<documentlibrary>/excelfilename.xlsx
  2. strDest- Give the local file path to download i.e. c:\TestExcel.xlsx.
Note: - This solution will work only with Office 2007 onwards.

Please download complete code for Reading/downloading Excel File from the SharePoint Doc Library.

Enjoy it.