SharePoint Excel Services Loading Excel files from code

If you ever wondered how you might load an Excel Sheet from an Excel file dynamically from a webpart then this is your lucky day :). Well below is the code how to do it.

The code below is rather simple. You have an excel file URL within SharePoint which is used to retrieve data to build a excel web services URL from where the actualy HTML based excel data is retrieved.

There are two points to be made:
1. During the development of this functionality I noticed that without the definition for the headers excel services would not always operate as expected. This was especially true when in anonymous mode, in other words a public site.
2. If you are going to ry to use excel services through code like in this example you also need to take into consideration access. Under a Windows Authentication model you have no problems but if you use anonymous access you are going to have problems. so if you encounter problem with public sites consider creating an AD account which is going to be used as the anonymous user in IIS for your SharePoint application. Then to allow access to the target excel file location simply add the minimum privileges of “Read” to the account. Add these rights either to the library where the file is located of the file itself.

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

String composedExcelWebServicesURL = null;
            try
            {
                if (!String.IsNullOrEmpty(this.ExcelFileLocation) && !String.IsNullOrEmpty(this.ExcelSheetName) && !String.IsNullOrEmpty(this.ExcelSheetStartCell) && !String.IsNullOrEmpty(this.ExcelSheetEndCell))
                {
                    using (SPSite site = new SPSite(this.ExcelFileLocation))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            SPFile excelFile = web.GetFile(this.ExcelFileLocation);
                            if (excelFile != null)
                            {
                                String hostName = null;
                                if (!string.IsNullOrEmpty(Request.Url.Query))
                                {
                                    hostName = Request.Url.AbsoluteUri.Replace(Request.Url.Query, String.Empty);
                                    hostName = hostName.Replace(Request.Url.AbsolutePath, String.Empty);
                                }
                                else
                                {
                                    hostName = Request.Url.AbsoluteUri.Replace(Request.Url.AbsolutePath, String.Empty);
                                }
                                Uri webURI = new Uri(web.Url);
                                String presentWebReformedURL = hostName + webURI.AbsolutePath;
                                composedExcelWebServicesURL = String.Format(this.excelWebServicesURL, presentWebReformedURL, excelFile.Url, this.ExcelSheetName, this.ExcelSheetStartCell, this.ExcelSheetEndCell);

                                if (this.DebugMode == true)
                                {
                                    this.lDebugInfo.Text += composedExcelWebServicesURL != null ? composedExcelWebServicesURL : "URL data is empty";
                                }
                                using (var client = new WebClient())
                                {
                                    var newUri = new Uri(composedExcelWebServicesURL);
                                    var myProxy = new WebProxy();
                                    myProxy.Credentials = CredentialCache.DefaultCredentials;
                                    myProxy.Address = newUri;
                                    client.Proxy = myProxy;

                                    client.Encoding = System.Text.Encoding.UTF8;
                                    client.Headers.Add("User-Agent", @"Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)");
                                    client.Headers.Add("Accept", @"text/html, application/xhtml+xml, */*");
                                    if (this.DebugMode == true)
                                    {
                                        this.lDebugInfo.Text += "</br> Before download string";
                                    }
                                    this.lExcelWebServiceHTML.Text = client.DownloadString(composedExcelWebServicesURL);
                                    if (this.DebugMode == true)
                                    {
                                        this.lDebugInfo.Text += "</br> After download string";
                                    }
                                }
                            }
                        }
                    }
                }
				}
            catch (Exception ex)
            {

            }

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.

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

Excel Webservices Anonymous parametric data access problems

Hi,

So if you are having problems accessing data with a parametric data request here are a few solutions and the reason you are having problems:

The reason: Excel Services requires read AND write privileges to the excel file you are trying to access with a parametric data access request, like the example below:
http://<ServerName>/_vti_bin/ExcelRest.aspx/Docs/Documents/sampleWorkbook.xlsx/model/Ranges(‘Sheet1!A1|G5’)?$format=html

Possible solutions:
1. Creating a domain account to be used in the IIS SharePoint application as the anonymous account with anonymous access to your public site

As the title suggests create a new domain account that you are going to use instead of the out of the box anonymous account assigned to your public site. Then you will grant to the needed file read/write access from you SharePoint user interface. The other reason why you need a domain account is that excel services requires claims tokens to authenticate and authorize a user to access information. This procedure is not going to suit your needs IF you do not want to allow write access to your excel data or other security related issues that prohibit you from using this method.

Your steps: Go to your IIS 7 => Navigate to your web application => Go into the IIS area in your application and open the Authentication option:

Untitled

Next Select “anonymous”(this should be enabled if you have a public SharePoint site), select edit from the left options panel and change the user to your newly created domain user.

Untitled2

2. Option two is to go around the problem programmatically. Here is one example from the following link that might do the trick:

http://blog.mastykarz.nl/inconvenient-excel-rest-services-anonymous-users/

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.