Tag Archives: javascript object model

Using Javascript to export a Dynamically created Data view in SP2010

As part of the current project I’m working on, I needed to create a data view which passed a querystring parameter into a data view web part to display filtered data. The idea was to export this data to Excel using the export feature on the office ribbon available on SharePoint 2010.

To create the the view and apply the filter was very simple. I used SharePoint designer 2010, created a new web part page and inserted a data view. I re-arranged my columns and then applied the Query string parameter filter to the view. When I tested the view, it worked nicely. So the next step was to export the data to excel. This didn’t seem to work… so I was left scratching my head…

It seems when you create this type of view using Querystring parameter, on the export to excel function, it loses the value due to a page post back resulting in a blank view. So to get around this I used some Javascript to export the HTML to Excel which I found here. I have extracted the code require below:

<Script Language="Javascript">
function isIE() // Function to Determine IE or Not
{
 return /msie/i.test(navigator.userAgent) && !/opera/i.test(navigator.userAgent);
}

function exportToExcel() // Function to Export the Table Data to Excel.
{
var isIEBrowser = isIE();
if(isIEBrowser== false)
{
alert('Please use Internet Explorer for Excel Export Functionality.');
return false;
}
else
{
var strTableID = "detailsTable"; // It's the Table ID of Table in Webpart
var detailsTable = document.getElementById(strTableID);
var objExcel = new ActiveXObject("Excel.Application");
var objWorkBook = objExcel.Workbooks.Add;
var objWorkSheet = objWorkBook.Worksheets(1);

for (var intRowIndex=0;intRowIndex<detailsTable.rows.length;intRowIndex++)
{
for (var intColumnIndex=0;intColumnIndex<detailsTable.rows(intRowIndex).cells.length;intColumnIndex++)
 {
  if(intColumnIndex != 3)
  objWorkSheet.Cells(intRowIndex+1,intColumnIndex+1) = detailsTable.rows(intRowIndex).cells(intColumnIndex).innerText;
 } 
}
objExcel.Visible = true;
objExcel.UserControl = true;
}
}
</Script>

<button onclick=”exportToExcel();”>Export to Excel File</button>

To find the table Id click here

So this worked, but there is a few gotchas:

  • This will only work in when using IE
  • You will need to set your browser to Allow Active X controls
  • You will need to have Excel on your machine 😉

Thanks to the guys on MS SharePoint forums!!

Tagged , , , , , , , , , ,

Using JavaScript to query SharePoint Lists

So, there I was – a developer at heart with the same tools as a designer (SharePoint Designer to be precise), limited to what can be accomplished on a site collection (i.e. no C# code or custom features) . So what do I do? I had a hunt around the internet and found a couple of ways to query and update lists in SharePoint 2010 one was to use JQuery and the other is to use to the JavaScript Object Model provided in the ’14 Hive’.

Based on my findings and the great examples available on MSDN I went with the latter option. Here is a link to the finer points on how to use the SharePoint 2010 JOM:

http://msdn.microsoft.com/en-us/library/hh185015

Enjoy!

Tagged ,