SharePoint Excel Services debugging

Hi,

 

I was surprised to find this but this is a gem for anyone struggling with Excel Services errors. Microsoft made a flow chart of what Excel Services does during a data refresh. This helped me debug errors that made no sense at one point.

http://technet.microsoft.com/en-us/library/hh369968(v=office.14).aspx

Quote from the link above:

“Use this flowchart to better understand how Excel Services in Microsoft SharePoint Server 2010 refreshes data or to troubleshoot data connection errors from Excel Services.

The Excel Services Data Refresh Flowchart shows the sequence of actions performed by Excel Services when it refreshes data in a data-connected Microsoft Excel workbook that is being rendered by Excel Services. This includes sequences for the three available authentication options (Windows, Secure Store Service, and None), plus sequences for embedded connections and connections that use Office Data Connection files. The flowchart also includes the connection errors that can occur at each step, and it suggests configuration steps to avoid or correct the errors.”

Hope this helps someone.

Advertisements

Excel Services whitespace problems with parameterized queries when loading excel sheets

If you are having problems with spaces when accessing excel sheets with spaces in their names what you need to do is to add two single quotes on both left and right of the sheet name.

Sample query to get a HTML formatted sheet data with start and end cell definitions:
http://webapplicationname/_vti_bin/ExcelRest.aspx/Documents/file.xlsx/model/Ranges(”’sheet name with spaces goes here”!A1%7CF63′)?$format=html
The query above has a spaces in the sheet name. In the sample above the sheet name is “sheet name with spaces goes here“.

NOTICE: Add two single quote ‘ not one double quote “.

If you where to use a formatted string to fill in the missing data it would look something like this:

String excelWebServicesURL = “{0}/_vti_bin/ExcelRest.aspx/{1}/model/Ranges(”'{2}”!{3}|{4}’)?$format=html”;

0 = your site absolute URL. This means the present site location inside your site collection.

1 = the relative URL to the excel file

2 = the sheet name

3 = The cell where to start the reading of data from

4 = The cell where to end the reading of data to

SharePoint Excel Services REST API HTTP 400 Bad Request

If you happen to get this problem when trying to access an excel file to generate lets say HTML from the Excel file content then there are two things that are important to check if you KNOW that you have configured everything else correctly.

1. Check that you account that runs your excel service has sufficient privilages to the content database of the web application that you are using with Excel Services. This could be your Intranet website.

2. Make sure that your file extension is actually .xlsx NOT .xls. This also caused problems when I was wondering WTF(please excuse my language) is going on.

Importing Excel files into SharePoint as HTML content/pages

Hi,

In this post I’ll talk about the different way you can take an excel file, rip its content and export into into SharePoint as HTML to a page.

First we need to talk about the different way to do it:

  1. Excel file => HTML file => SharePoint content page
  2. Excel file => in memory data => SharePoint content page
  3. Excel Services REST API => SharePoint content page

In my research these are the two possibilities and here is why: It all depends on the HTML output requirements of your project. If you need highly customized HTML with your own styling and other content modifications then the second option is for you. IF on the other hand you need the same kind of styling found in your Excel file then you have to go with option one.

In both options you need tool to do these. Some tools are better at some things than the other tools. So depending on your requirements you have to make a choice. Check out their strengths and vices from these links. Not all tool may bend to your requirements. Here are the available tools:

Open XML for Office developers

PowerTools for Open XML

Microsoft.Office.Interop.Excel namespace

Spire.XLS for .NET

Excel Services REST API:

Sample URI For Excel Services REST API

Excel Services REST API

Test Lab Guide: Configure Excel Services

Here is a code sample on how to export the Excel file data as a HTML file with Microsoft.Office.Interop.Excel:

using Excel = Microsoft.Office.Interop.Excel;

….

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

string str;

xlApp = new Excel.Application();

// open the excel file

xlWorkBook = xlApp.Workbooks.Open(@”your file location either local HD or Web URL”,
0,
true,
5,
String.Empty,
String.Empty,
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
“\t”,
false,
false,
0,
true,
1,
0);

// Get your wanted sheet from within your excel file
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);

// Save the data into a HTML file

xlWorkSheet.SaveAs(@”c:\temp\tes.html”, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

xlWorkBook.Close(false, null, null);

xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show(“Unable to release the Object ” + ex.ToString());
}
finally
{
GC.Collect();
}
}

OR if you prefer to do a custom export of the excel data into whatever form or format you wish:

using Excel = Microsoft.Office.Interop.Excel;

….

using Excel = Microsoft.Office.Interop.Excel;

….

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

string str;

xlApp = new Excel.Application();

// open the excel file

xlWorkBook = xlApp.Workbooks.Open(@”your file location either local HD or Web URL”,
0,
true,
5,
String.Empty,
String.Empty,
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
“\t”,
false,
false,
0,
true,
1,
0);

// Get your wanted sheet from within your excel file
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);

// Custom data exporting

range = xlWorkSheet.UsedRange;

StringBuilder data = new StringBuilder();
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
var t = range.Cells[rCnt, cCnt] as Excel.Range;
if (t != null && t.Value2 != null)
data.AppendLine(t.Value2.ToString());
}
}

// A Sample of one way to export the data in a simple HTML table

this.richTextBox1.Text = ConvertDataTableToHtml(ref range);

xlWorkBook.Close(false, null, null);

xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show(“Unable to release the Object ” + ex.ToString());
}
finally
{
GC.Collect();
}
}

public static string ConvertDataTableToHtml(ref Excel.Range range)
{
string htmlString = “”;
int rCnt = 0;
int cCnt = 0;

if (range == null)
{
throw new System.ArgumentNullException(“targetTable”);
}

StringBuilder htmlBuilder = new StringBuilder();

htmlBuilder.Append(“<table border=’1px’ cellpadding=’5′ cellspacing=’0′ “);
htmlBuilder.Append(“style=’border: solid 1px Black; font-size: small;’>”);

for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
htmlBuilder.Append(“<tr align=’left’ valign=’top’>”);
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
var t = range.Cells[rCnt, cCnt] as Excel.Range;

htmlBuilder.Append(“<td align=’left’ valign=’top’>”);
if (t != null && t.Value2 != null)
htmlBuilder.Append(t.Value2.ToString());
htmlBuilder.Append(“</td>”);
}
htmlBuilder.Append(“</tr>”);
}

//Create Bottom Portion of HTML Document
htmlBuilder.Append(“</table>”);
//Create String to be Returned
htmlString = htmlBuilder.ToString();

return htmlString;
}

Remember this is one way to do it. There are different tools and different approaches. Even 3rd party tools that can do allot of things for you. Also notice that in the Excel to HTML file I have not express any solutions on how to bring the HTML file content into SP. This I will leave to you to figure out the best approach in your system. There are things to consider that can be system specific.

You can chose to implement this in a SharePoint list event where you Excel file gets dropped and modified. OR this can be a timer job that reads from a SP list location an excel file and performs your operations.