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:-
- Open Shared Services from central administration.
- Go to Excel Service section and click on Trusted File Location.
- A new window will be open, Click on Add Trusted File Location.
- A new window will be open to add the path into trusted file location, paste the url in address field.
- Click on OK button. Now document library added in trusted file location.
Step 2
- Open Visual Studio >> File >> New >>Project choose console application from C# Project Window.
- Right click on ExcelServiceTest solution and select Add Web Reference.
- In add web reference window specify excel web service url i.e http:// <sharepoint site url>/_vti_bin/excelservice.asmx.
- 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.
- Add namespace of the XLService reference in Program.cs file
using ExcelServiceTest.XLService;
Function for Reading Excel File:-
ReadExcelFile contains following arguments-
- strFileURL- Give the url of the excel i.e.
http://<sharepointsiteurl>/<documentlibrary>/excelfilename.xlsx - strSheetName- Give the name of sheet which you want to read i.e. Sheet1.
- strRange- Give the range of sheet i.e. A1:D4.
Function for download excel file:-
DownloadXLFile contains following arguments-
- strPath- Give the url of the excel i.e.
http://<sharepointsiteurl>/<documentlibrary>/excelfilename.xlsx - 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.