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