Exporting Dataview WebPart data into Excel using Javascript

Use following JS to export Dataview Webpart data in a excel sheet.
// JS SCRIPT
<Script Language="Javascript"> 
function isIE() 
// Function to Determine IE or Not 
{
 return /msie/i.test(navigator.userAgent) && !/opera/i.test(navigator.userAgent); 
} 
function exportToExcel() 
{ 
var isIEBrowser = isIE(); 
if(isIEBrowser== false) 
{ 
 // This script works for IE. If browser is not IE throw alert 
 alert('Please use Internet Explorer for Excel Export Functionality.'); 
 return false; 
} 
else 
{ 
 var strTableID = "dataTable"; // 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> 
 // CALL A JS FUNCTION ON A BUTTON CLICK
<button onclick="exportToExcel();">Export to Excel</button>

That's it.

Employee Activities Template – Signup Bugs

I installed Employee Activities template from Microsoft last night. It’s good in a way that I can create activities and allow people to sign up.  However, there is a problem with sign up. The workflow runs under default permissions “System Account” and hence does not allow any user other than OWNERS to sign up for an activity.

I gave edit permissions to Activity and sign-up lists, still it did not work. Users have edit permissions now, they can create activities but I do not want all people to create activity. Only few can create an activity. This leaves a security hole.

Activity creation should be restricted to specific group but sign ups should work for all even for users who have read permissions.

I did not find a way to fix this. Does anyone has a solution for this??

my last option is to create those workflows again with Sharepoint Designer and try to make it work.

Email a calendar/appointment link using SharePoint workflow

I am working on Employee Training template for a month now. We have customized a lot to suit our requirement. As we moved ahead few people in my team came up with a suggestion to email an appointment link using SharePoint Designer workflow. This was very crucial as people generally do registration but forget to export a calendar event in their outlook.

Open  Employee Training site in SharePoint Designer and navigate to workflows. There are 3 workflows -

• Attendee Registration
• Attendee Unregistration
• Instructor Reminder

These workflows already has a part with the “Send an email” action.  For you, if you’re looking at the same Employee Training template I’m looking at, you’ll want to open the “Attendee Registration” workflow, and then look at the first step of the workflow, where it says “Store This is a confirmation… in Variable: ConfirmationBody. Open ConfirmationBody by clicking it.

In the Confirmation Body, you need to show a link that will open an Outlook appointment.  Go into any event item in any SharePoint calendar.  In the toolbar at the top of that item, you’ll see an “Export Event” button.  You’ll notice that when you click it, an Outlook meeting box pops up.  This is the link that you want, so right-click on “Export Event”, and copy the link. For example

http://myserver/sites/sitename/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List={LIST-ID}&CacheControl=1&ID=2&Using=event.ics

We simply want this link to be included in the email.  The way to make this link specific to the actual event ID of the relevent list item, will be to replace the ID=2 in the link, to be the ID of the current list item in the workflow.  Delete the number after ID= in your link, and click “Add Lookup” button. This will open a window, 

In this field, you’re looking up the ID field from the Courses calendar, and matching it up with the Course ID field in the Registrations list.

This is the full text of what I put in my workflow.  It not only includes a pretty “CLICK HERE” link, it also lets the user know that they’ll need to click “Save and Close” once the appointment opens.  Yes, you can use HTML in SharePoint Designer workflow emails. If you would like to add this event to your Outlook calendar,

 <a href=”http://myserver/sites/sitename/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List={LIST-ID}&CacheControl=1&ID=[%Courses:ID%]&Using=event.ics”>CLICK HERE</a>, then click to Open, then Save & Close.

So now people will be able to open and save the event in their outlook calendar without having to export in from SharePoint site.

DataSheet View Not Working

For a list if you switch the view from standard to Datasheet, you often land up on error page in Sharepoint. This error could be because following conditions are true:

• The datasheet component that is compatible with WSS is not installed
• Microsoft Office Professional edition is not installed on Client System
• The Web browser does not support ActiveX

The solution for this problem is simple try this

1. Click Start and then Control Panel
2. Click Add or Remove program
3. Click Microsoft Office 2003 and then click Change
4. Click Add or Remove Features, and then select Next
5. Click to select choose advance customization of application check box and then click next
6. In choose update options for applicatoins and tool box, expand office Tools, click the down arrow next to Windows Sharepoint Services support and then click not Available
7. Click Update
8. After the Update is done repeat steps from 1 to 5
9. In choose update options for applications and tools box, expand office tools, click the down arrow next to Windows SharePoint Services Support and then Click Run From My Computer
10. Click Update Again

This should display datasheet view in correct format.

You can also install Office 2007 Access patch to enable datasheet view on your client machine.

Installing SPSearch Service with STSADM

Recently I came across a situation where there was no Search Service installed on SharePoint server. Service did not appear in “Services on a Server” page in Central Administration. I tried searching for a keyword on WSS 3.0 site but my search result always returned error saying that “site is not assigned an indexer”. What to do?? I followed number of blogs about this error. Some asked me to start Indexing services, search services from administrative services on my system. Few blogs said I need to assign a search database for a site.

After digging down through all the logs, I started looking for bits and pieces on my system. I found

1. WSS_Search search DB missing in SQL Server
2. WSS search service is not present on Admin Services
3. Indexing service was stopped
4. Option to select Search DB was grayed out

I immediately started SP configuration wizard in order to recover WSS_ search DB in sql server. After I recovered WSS_ Search DB, I attached a search DB for the site in CA, Started Indexing service on the Server. Still did not find search service installed….. I ran stsadm command line to install SPSearch Service and started a full crawl on a server.

stsadm.exe -o SPsearch -action start

stsadm.exe -o spsearch -action fullcrawlstart

Go to service in system, start WSS search service. My site can do search now.

Insert a DataView Web Part on a Page

So let’s get started with the basics:

1. In SharePoint Designer (SPD), click <File> and choose <Open Site>.  Paste the full URL of your SharePoint site, such as http://sharepoint/sites/teamsite
2. Click <Open>
   You can see that the left side of the screen shows the folder structure of your site.  Each document library is a folder, and all of the site’s lists live under the “Lists” folder.
3. Now, in SPD, on the left side, navigate to the new web part page by clicking on the name of the document library from step 2.  The contents of that library will be displayed in the main work area in the middle of the SPD screen.  If you don’t see your new file, click the F5 key to refresh the list.
4. Double-click the name of the new file to open it.
   Now, it’s time to start picking a data source to insert a data view web part.  You see the web part zones displayed on the page, and each zone says “click to insert a web part”.  It’s important at this point, that you pick a web part zone, and click <Click to insert a web part>. If a zone is not clicked on, a web part that’s inserted could end up on some random spot on the page.
5. Click the <Data View> menu at the top of the screen, and choose <Insert Data View…>. Since this initial article is really just to show you around in the DVWP, we’re not going to do anything fancy.  We’ll just insert a regular SharePoint list as our data view.  See, that now on the right side of the screen, there is a “Data Source Library” pane.  The following are types of sources that you can get data from:

• SharePoint Lists
• SharePoint Libraries
• Database Connections
• XML files
• Server-side scripts
• XML Web services
• Linked Sources

For Example

1. Let’s grab some data that’s in a list on the site.  In the “SharePoint Lists” section, pick a list (preferably one with some data in it).  Click the drop-down box on that list name, and choose <Show Data>.
2. Now, in the pane on the right side of the screen, instead of the “Data Source Library” tab, you’ll see the “Data Source Details” tab, that shows the details of the source that was just selected.  This means, that all of the fields in the list are displayed.  There are even some extra fields listed there, that are usually hidden from views.  While holding your CTRL key down, click on three or four field names to select them.
3. Click <Insert Selected Fields As…> right above those field names.  Choose <Multiple Item View>. In various articles of this series, I’ll go over some of these other options, but this one is the most commonly used.
4. When the DVWP is first inserted, the “Common Data View Tasks” menu is automatically displayed.

What’s New in SharePoint 2010 Beta

For all those who are looking for what is new in SP 2010. Here is the feature list (New):

• Access Services
• Business Connectivity Services
• Central Administration
• Digital Assest Management
• Enterprise Search
• Excel Services
• Health Monitoring
• Managed Metadata
• PerformancePoint Services
• Records Management
• Sandboxed Solutions
• Social Computing
• Vision Services
• Upgrade
• Windows Powershell

Keep visiting this blog for more details on all those features.