I am trying to retrieve data from a spreadsheet in a document library on SharePoint Server 2010. I have managed to successfully open the workbook and I then call ExcelService.GetRange() as shown below, however, it is returning empty cells. I have double-checked the file that's being read and it definitely contains data.

private void ParseFile(SPFile file)
{
    this.UpdateStatus(string.Format("Parsing file {0} at Url {1}...", file.Name, file.ServerRelativeUrl));
    // Instantiate the Web service and create a status array object and range coordinate object
    ExcelService excelService = new ExcelService();
    Status[] outStatus;
    RangeCoordinates rangeCoordinates = new RangeCoordinates();

    string sheetName = this.ParentWebPart.DefaultWorksheetName;
    string targetWorkbookPath = string.Format("{0}{1}", SPContext.Current.Web.Url, file.ServerRelativeUrl);

    // Set credentials for requests
    // NOTE: all examples I've seen have this but doesn't compile for me so commented out for now
    // excelService.Credentials = System.Net.CredentialCache.DefaultCredentials;
    // this.UpdateStatus(string.Format("Cred: {0}", excelService.Credentials));

    this.UpdateStatus("About to open workbook");
    try
    {
        // Call open workbook, and point to the trusted   
        // location of the workbook to open.
        string sessionId = excelService.OpenWorkbook(targetWorkbookPath, this.ParentWebPart.UiCultureName, this.ParentWebPart.DataCultureName, out outStatus);
        this.UpdateStatus(string.Format("sessionID : {0};", sessionId));

        // Prepare object to define range coordinates
        // and the GetRange method.
        rangeCoordinates.Column = this.ParentWebPart.StartColumn;
        rangeCoordinates.Row = this.ParentWebPart.StartRow;
        rangeCoordinates.Height = 20; // TODO: Work out how many rows are populated
        rangeCoordinates.Width = this.ParentWebPart.NumberOfColumns;

        object[] rangeResults = excelService.GetRange(sessionId, sheetName, rangeCoordinates, true, out outStatus);
        this.UpdateStatus(string.Format("Total Rows in Range: {0};", rangeResults.Length));

        if (rangeResults != null && rangeResults.Length > 0)
        {
            for (int row = 0; row < rangeCoordinates.Height; row++)
            {
                for (int col = 0; col < rangeCoordinates.Width; col++)
                {
                    this.UpdateStatus(
                        string.Format("Values[{0}][{1}]: {2}; ", row, col, ((object[])rangeResults[row])[col]));
                }
            }
        }

        if (outStatus != null)
        {
            foreach (Status status in outStatus)
            {
                this.UpdateStatus(string.Format("Status[{0}]: {1}", status.Name, status.Message));
            }
        }

        // Close workbook. This also closes session.
        excelService.CloseWorkbook(sessionId, out outStatus);
    }
    catch (SoapException e)
    {
        this.UpdateStatus(string.Format("SOAP Exception Message: {0}", e.Message));
    }
    catch (Exception ex)
    {
        this.UpdateStatus(string.Format("Error occurred: {0}", ex.Message));
    }

    this.UpdateStatus("Exiting ParseFile()");
}

private void UpdateStatus(string message)
{
    if (this.statusLabel == null)
    {
       this.statusLabel = new Label();
    }

    this.statusLabel.Text += string.Format("\r\n{0}", message);
}

If I alter the code to call:

object[] rangeResults = excelService.GetRangeA1(sessionId, sheetName, "A1:X10", true, out outStatus);

then my data is returned. Also using GetCell() works:

object cellValue = excelService.GetCellA1(
                                    sessionId,
                                     sheetName,
                                     "A1",
                                     true,
                                 out outStatus);

Is there a known bug with ExcelService.GetRange() or am I doing something wrong when calling it? Whilst I could use GetRangeA1(), the parameters for the rows and columns are integers pulled from the WebPart properties and I'd rather not mess around with converting them to letters, etc if possible though obviously if that's what I've got to do then that's what I've got to do ;)
Any help would be gratefully received!!
Thanks!

link|improve this question
feedback

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown

Browse other questions tagged or ask your own question.