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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s