I have a visual web part that retrieves an excel workbook from a document library on SharePoint Server 2010. I am opening the workbook using Excel Services (as a normal reference, not a web reference nor service reference) without any problems and after a couple of attempts I have managed to get data from the spreadsheet. However, in all of the examples I've seen the programmer has known how many rows they wish to retrieve. In my case the number of rows will vary from file to file. My question is this:

Is there a way to determine how many rows in the worksheet have been populated and just retrieve those rows?


I've been googling for hours but documentation on Excel Services appears to be quite light on.
Here's the code I've got so far:

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);
        }
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.