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!