HOW TO READ AN EXCEL DATA USING SHAREPOINT WEB SERVICES

INTRODUCTION
This post aims to describe how to access Excel File data using the built-in Excel webservice exposed by Sharepoint. It will also detail out how to export extracted data from an Excel file to a datatable.

SCOPE
This post assumes that the Excel File has already been uploaded into one of the document libraries (Shared Documents) in a site collection. It will not detail out how to upload Excel files in a document library.

REQUIREMENTS
-Sharepoint 2010
-Visual Studio 2010

IMPLEMENTATION


1.  Open Visual Studio and create an Empty Sharepoint Application. Input your Site Collection URL and click Finish.

2. On the Solution Explorer Toolbar, right-click on references and click on ‘Add Service Reference’. Click on the ‘Advanced’ button. On the dialog box, Click on Add Web Reference button.

3. On the Add Web Reference dialog box, access the excelservice.asmx. On my Virtual Machine, the URL is: ‘http://win-a80foe01p0e:4039/_vti_bin/excelservice.asmx’. Type in the name of the Web reference and click on the ‘Add Reference’ button.

4. In this tutorial, I have named my Web Reference to be ‘XLService’. This should now be available in the web references section in the Solution Explorer.

5. On your code-behind file, add a reference to the Excel Service in the Using section.

using TestExcelServices.XLService;

6. Inside your method, create an instance of the ExcelService class and set its URL and Credentials Properties. When the application calls the web service, the default network credentials need to be passed to the web service to get authenticated to use the data repository.

ExcelService objXL = new ExcelService();

objXL.Url = @"http://win-a80foe01p0e:4039/_vti_bin/excelservice.asmx";
objXL.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

7. Next is to create the datatable that will be the container of the queried data from the Excel file. On this example, I will declare a datatable with 4 columns.

//Create Datatable Structure
DataTable dt = new DataTable();
dt.Columns.Add("Product", typeof(string));
dt.Columns.Add("ProductType", typeof(string));
dt.Columns.Add("Date", typeof(string));
dt.Columns.Add("TotalProductType", typeof(int));

8. Declare the Cell ranges of each of your fields on the datatable.

My Excel file looks something like the one below:

On this example, I will assign my cell ranges to be like this:
string ProductRange = "B2:B1000";
string ProductTypeRange = "C2:C1000";
string DateRange = "E2:E1000";
string TotalProductRange = "D2:D1000";

9. Get the SessionID by calling the OpenWorkbook method. Add on your code like the one below.

string sessionID = objXL.OpenWorkbook(@"http://win-a80foe01p0e:4039/Shared%20Documents/Test.xlsx", "en-US", "en-US", out wsStatus);

10. Call the GetRangeA1 method to query the excel data per range. So for my example, the following code should be implemented.

object[] ProductData = objXL.GetRangeA1(sessionID, sheetName, ProductRange, false, out wsStatus);
object[] ProductTypeData = objXL.GetRangeA1(sessionID, sheetName, ProductTypeRange, false, out wsStatus);
object[] DateData = objXL.GetRangeA1(sessionID, sheetName, DateRange, false, out wsStatus);
object[] TotalProductData = objXL.GetRangeA1(sessionID, sheetName, TotalProductRange, false, out wsStatus);

11. Formulate a For Loop that will browse through each of the generated objects and save it on the datatable that we have created earlier.

for (int i = 0; i < ProductData.Length; i++)
            {
                if (string.IsNullOrEmpty(Convert.ToString(((object[])(ProductData[i]))[0])))
                {
                    break;
                }
                newRow = dt.NewRow();
                newRow["Product"] = ((object[])(ProductData[i]))[0].ToString();
                newRow["ProductType"] = ((object[])(ProductTypeData[i]))[0].ToString();
                newRow["Date"] = ((object[])(DateData[i]))[0].ToString();
                newRow["TotalProductType"] = Convert.ToInt32(((object[])(TotalProductData[i]))[0]);

                dt.Rows.Add(newRow);
            }

12. Here is the complete code for this method.

13. After this call, we can now bind the resulting datatable in our gridview.