Need to Read an Excel file using Sharepoint Excel Service

See more:

MS-Excel

service

SharePoint2010

Hi,

I have a requirement where I will have an Excel file in which there will be 'n' no. of sheets and each sheet will have data in 'n' no. of columns Now I need to read this excel file using Excel Services of Sharepoint. In the below code I'm hard coding Read from "A1:F10". I need to use used range of the excel sheet is it possible. There is no usedRange option in ExcelService like in Interop.

I just need to know how to customize to read many sheets and all the columns without specifying the Range.Please help.

XLService.ExcelService objXL1 = new ExcelService();
                   ExcelService objXL = new ExcelService();
                   objXL.Url = "http://serverName:port/_vti_bin/excelservice.asmx";
                   objXL.PreAuthenticate = false;
                   objXL.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
                   XLService.Status[] stat1;
                   XLService.Status[] stat2;
                   string sessionId = string.Empty;
                   StringBuilder sbResult = new StringBuilder();
                   sessionId = objXL.OpenWorkbook(strFileUrl, String.Empty, String.Empty, out stat1);

                   object[] sheetNames = objXL.GetSheetNames(sessionId, out stat2);
                   
                   foreach (object[] sheetName in sheetNames)
                   {
                       for (int iCount = 0; iCount < sheetName.Length; iCount++)
                       {
                           strSheetName = Convert.ToString(sheetName[iCount]);
                           
                           // retrieve the range as an array of arrays
                           object[] rangeResults = objXL.GetRangeA1(sessionId, strSheetName, "A1:F10", true, out stat1);
                           foreach (object[] rangeResult in rangeResults)
                           {
                               for (int iIndex = 0; iIndex < rangeResult.Length; iIndex++)
                               {
                                   sbResult.Append(Convert.ToString(rangeResult[iIndex]));
                               }
                           }

                           objXL.CloseWorkbook(sessionId);
                       }
                   }

Thanks & Regards,
Mathi