Thursday, December 23, 2010

Using SPServices and Excel to Create Printable Reports

So.. I was happily designing a Sharepoint solution in Visual Studio. The same solution I have been working on for a while now. It really never occurred to me that users would actually want to print from Sharepoint. Why print what you can just go see right? Well, being in a major industrial type environment and being able to support meetings where computer access might be limited, there are times when a good paper report will just have to do. So my challenge was to incorporate printing into my solution. There are a few 3rd party tools that can be used to do some type of reporting. There is SQL Reporting Services and a few others that come to mind. However, some of the requirements for what we need were just easier to do in Excel. So I am about to discuss the wonderful world of Excel, XML, and XSL.

Let me begin by stating that it is very important to understand that my solution is running only in IE7 and is inside an company intranet. What this means is that I have to have the users trust the site and setup IE to allow Unsigned Active X controls because we are going to be automating Excel. However, in this environment it is okay and all the users will share the same experience because they all have Excel 2007. [I would like to sign my controls but we have no way to do that just yet.]

Excel 2007 supports XML using the Excel XML format. The simplest way to see this in action is to open Excel, put some stuff in it, and format it the way you want. You can even change the page layout, margins, header/footer, and setup conditional formatting. Save the file as an XML file. Open this file in your favorite (or not so favorite) XML editor. I have VS 2008 and it works fine for this. You will notice that this XML is actually very easy (scary I know) to read (or am I just a geek!). You can also see all of the formatting you added and all of that is well formed XML. Now comes the trick! You can convert this XML to XSL very easily and this XSL can then be used to format data to Excel readable XML that will open in Excel formatted the way you want. Cool huh?

ON WITH THE GOOD STUFF!!

So the first report that I was asked to create was similar to a list view. However, we have need for a dynamic header and footer option along with some custom color coding. We also needed to span 3 rows for each item. Without getting too detailed, here are some of the steps that were taken:

Step 1: Open Excel and design the look and feel. Repeat the formatting for the 3 rows below row 5. Remember to format the cells according to the data that will be in them.

Step 2: Save as XML! (File-Save As-Other Formats-->XML Spreadsheet)

Step 3: Convert this XML to XSL using your editor of choice (Or whatever you have!). I named my file PrintList2.xsl and loaded it to a document library called PrintTemplates.

First You have to Remove the XML declaration and change it to XSL stylesheet.

<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema" version="1.0">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />
<xsl:template match="/">

Then you can add/remove the needed XSL inside the xsl:template tag!
[I have removed all of the styles for brevity]
The DocumentProperties and ExcelWorkbook sections are fairly straightforward and are added automatically based on your selections.

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Daniel Walker</Author>
<LastAuthor>Dan</LastAuthor>
<LastPrinted>2010-06-18T10:19:06Z</LastPrinted>
<Created>2010-06-18T09:26:14Z</Created>
<LastSaved>2010-12-28T16:32:41Z</LastSaved>
<Company>My Company</Company>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11640</WindowHeight>
<WindowWidth>17400</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>45</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

The next section is the WorkSheet section and it contains most of the meat of the xml and xsl code

<Worksheet ss:Name="Sheet1">
<Names>
<NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!R1:R5"/>
</Names>
<Table ss:ExpandedRowCount="60000" x:FullColumns="1" x:FullRows="1">
<Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="61.5" ss:Span="3"></Column>
<Column ss:Index="5" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="56.25"></Column>
<Column ss:StyleID="s62" ss:Width="49.5" ss:Span="1"></Column>
<Column ss:Index="8" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="66.75"></Column>
<Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="57"></Column>
<Column ss:StyleID="s62" ss:Width="40.5"></Column>
<Column ss:AutoFitWidth="0" ss:Width="9"></Column>
<Column ss:AutoFitWidth="0" ss:Width="187.5" ss:Span="242"></Column>
<Row ss:AutoFitHeight="0" ss:Height="8.25">
<Cell ss:MergeAcross="9" ss:MergeDown="1" ss:StyleID="m65201024">
<Data ss:Type="String">Commitments - All Tasks</Data>
<NamedCell ss:Name="Print_Titles"/>
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="8.25"></Row>
<Row ss:AutoFitHeight="0" ss:Height="13.5">
<Cell ss:MergeAcross="1" ss:StyleID="s266">
<Data ss:Type="String">Priority</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s268">
<Data ss:Type="String">Division</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s268">
<Data ss:Type="String">Branch</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:MergeAcross="2" ss:StyleID="s268">
<Data ss:Type="String">Status</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s268">
<Data ss:Type="String">Serial #</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s268">
<Data ss:Type="String">ECD</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:MergeDown="2" ss:StyleID="m54658428">
<Data ss:Type="String">Wkg Days</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="6" ss:StyleID="m57148000">
<Data ss:Type="String">Title</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s195">
<Data ss:Type="String">Fnd #</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s195">
<Data ss:Type="String">Rev ECD</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
</Row>
<Row ss:Height="13.5">
<Cell ss:MergeAcross="6" ss:StyleID="m57148020">
<Data ss:Type="String">Description</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s234">
<Data ss:Type="String">Cmt #</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
<Cell ss:StyleID="s235">
<Data ss:Type="String">Created</Data>
<NamedCell ss:Name="Print_Titles"></NamedCell>
</Cell>
</Row>

This is the main header formatted the way we need. Most of the formatting options are declared later. This will produce just the first part of the spreadsheet as shown in the following image:

Now we need to setup the data portion of the template. When the page is called, it is going to be used to format XML data. We will need to loop through this data with the xsl:for-each command. As this report carries one list item over 3 rows we have to set that up correctly. Also, note that due to the way the data is returned from a webservice query, it might need to be tweaked to look correct. An example would be the need for the substring commands for the ows_Body. This data has opening and closing div tags because it is a note field.

<xsl:for-each select="listitems/rs:data/z:row">
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s271">
<Data ss:Type="String">
<xsl:value-of select="substring-after(@ows_Priority, '#')"/>
</Data>
</Cell>
<Cell ss:StyleID="s272">
<Data ss:Type="String">
<xsl:value-of select="@ows_AssignedDivision"/>
</Data>
</Cell>
<Cell ss:StyleID="s272">
<Data ss:Type="String">
<xsl:value-of select="@ows_AssignedBranch"/>
</Data>
</Cell>
<Cell ss:MergeAcross="2" ss:StyleID="s272">
<Data ss:Type="String">
<xsl:value-of select="@ows_Status"/>
</Data>
</Cell>
<Cell ss:StyleID="s272">
<Data ss:Type="String">
<xsl:value-of select="@ows_Serial"/>
</Data>
</Cell>
<Cell ss:StyleID="s139">
<Data ss:Type="DateTime">
<xsl:value-of select="substring-before(@ows_DueDate, ' ')"/>
</Data>
</Cell>
<Cell ss:MergeDown="2" ss:StyleID="m65201084">
<Data ss:Type="Number">
<xsl:value-of select="@ows_WorkingDays"/>
</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="6" ss:StyleID="s276">
<Data ss:Type="String">
<xsl:value-of select="@ows_Title"/>
</Data>
</Cell>
<Cell ss:StyleID="s274">
<Data ss:Type="String">
<xsl:value-of select="@ows_FindingNum"/>
</Data>
</Cell>
<Cell ss:StyleID="s140">
<xsl:choose>
<xsl:when test="@ows_RECD !=''">
<Data ss:Type="DateTime">
<xsl:value-of select="substring-before(@ows_RECD, ' ')"/>
</Data>
</xsl:when>
</xsl:choose>
</Cell>
</Row>
<Row ss:Height="13.5">
<Cell ss:MergeAcross="6" ss:StyleID="s277">
<Data ss:Type="String">
<xsl:value-of select="substring-before(substring-after(@ows_Body, '<div>'), '</div>')"/>
</Data>
</Cell>
<Cell ss:StyleID="s275">
<Data ss:Type="String">
<xsl:value-of select="@ows_CommitmentNum"/>
</Data>
</Cell>
<Cell ss:StyleID="s141">
<Data ss:Type="DateTime">
<xsl:value-of select="substring-before(@ows_Created, ' ')"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
</Table>

Now we have looped through the data and output the results. The rest of the code handles the formatting including conditional header and the conditional formatting. The conditional formatting has code to calculate the range of cells it should affect as well!

Note again that this XSL file was uploaded to a document library called PrintTemplates

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Layout x:Orientation="Landscape" x:CenterHorizontal="1"/>
<xsl:variable name="MarkRequired" select="count(listitems/rs:data/z:row[@ows_HandleAs = 'SPECIAL 1'])" />
<xsl:choose>
<xsl:when test="$MarkRequired >= 1">
<Header x:Margin="0.3" x:Data="&C&"Arial,Bold"&8SPECIAL: This document is special!"/>
</xsl:when>
</xsl:choose>
<Footer x:Margin="0.3" x:Data="&L&"Arial,Bold"CTS Commitments View&C&"Arial,Bold"&D&R&"Arial,Bold"&P of &N"/>
<PageMargins x:Bottom="0.75" x:Left="0.25" x:Right="0.25" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<Scale>88</Scale>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<PageBreakZoom>60</PageBreakZoom>
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>5</SplitHorizontal>
<TopRowBottomPane>5</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>11</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<ConditionalFormatting xmlns="urn:schemas-microsoft-com:office:excel">
<xsl:variable name="ItemCount" select="count(listitems/rs:data/z:row)" />
<xsl:variable name="Range1" select="$ItemCount * 3"/>
<xsl:variable name="Range2" select="$Range1 + 5" />
<Range>R6C10:R<xsl:value-of select="$Range2"/>C10</Range>
<Condition>
<Qualifier>GreaterOrEqual</Qualifier>
<Value1>6</Value1>
<Format Style='color:black;font-weight:700;background:red'/>
</Condition>
<Condition>
<Qualifier>Between</Qualifier>
<Value1>0</Value1>
<Value2>5</Value2>
<Format Style='color:black;font-weight:700;background:#FC6E04'/>
</Condition>
<Condition>
<Qualifier>LessOrEqual</Qualifier>
<Value1>0</Value1>
<Format Style='color:black;font-weight:700;background:#00CC00'/>
</Condition>
</ConditionalFormatting>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>

BRING ON THE DATA!!

So the next thing we need to do is to get the data and automate Excel. This is all done in javascript and jQuery and I had placed this code in the master page. I also chose to make it an action from a list view, but you can choose whatever method you want.

Excel Automation Using Javascript

<script language="javascript" type="text/javascript">
var viewpage = null;
var test = null;
$(document).ready(function() {
viewname = $("td[id*='onetViewSelector']").text();
test = new String(window.location);
createPrintMenuItems()
});
function createPrintMenuItems() {
// Attach to the Actions menu
if (test.indexOf("Lists/Commitments") > 0) {
var oNodes = document.getElementsByTagName("a");
for (var c = 0; c < oNodes.length; c++) {
if (oNodes[c].id.indexOf("_ListActionsMenu") > -1) {
var oTokens = MMU_ParseNVAttr(oNodes[c], "menuTokenValues");
var sMenuID = MHash_Item(oTokens, "TEMPLATECLIENTID");
var oMenu = document.getElementById(sMenuID);
oMenu.innerHTML = "<ie:menuitem description='Prints the current view' id='printListMenuItem' type='option' onMenuClick='javascript:printList()' iconSrc='/_layouts/images/WPICON.GIF' text='Print View' menuGroupId='200'></ie:menuitem>" + oMenu.innerHTML;
return;
}
}
setTimeout("createPrintMenuItems()", 500);
}
}

So now let's call the printlist function that does most of the real work here. I tip my hat to Marc Anderson's SPServices library for making this a lot easier. It can be found here

function printList() {
var camlQuery;
var xmldata1 = new ActiveXObject("MSXML.DomDocument");
xmldata1.async = false;
var xmldata2 = new ActiveXObject("MSXML.DomDocument");
xmldata2.async = false;
var xsldata1 = new ActiveXObject("MSXML.DomDocument");
xsldata1.async = false;
xsldata1.load(L_Menu_BaseUrl + "/PrintTemplates/PrintList2.xsl")
var zpath = "c:\\tmpdata1.xml";
if (viewname != "All") {
camlQuery = "<Query><Where><Eq><FieldRef Name='AssignedDepartment'/><Value Type='Text'>" + viewname + "</Value></Eq></Where><OrderBy><FieldRef Name='ID' /></OrderBy></Query>";
}
else {
camlQuery = "<Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query>";
}
var camlViewFields = "<ViewFields><FieldRef Name='Priority' />";
camlViewFields += "<FieldRef Name='Priority' />";
camlViewFields += "<FieldRef Name='AssignedDivision' />";
camlViewFields += "<FieldRef Name='AssignedBranch' />";
camlViewFields += "<FieldRef Name='Title' />";
camlViewFields += "<FieldRef Name='Serial' />";
camlViewFields += "<FieldRef Name='FindingNum' />";
camlViewFields += "<FieldRef Name='CommitmentNum' />";
camlViewFields += "<FieldRef Name='DueDate' />";
camlViewFields += "<FieldRef Name='RECD' />";
camlViewFields += "<FieldRef Name='Status' />";
camlViewFields += "<FieldRef Name='WorkingDays' />";
camlViewFields += "</ViewFields>";
$().SPServices({
operation: "GetListItems",
async: false,
webURL: L_Menu_BaseUrl,
listName: "Commitments",
CAMLQuery: camlQuery,
CAMLViewFields: camlViewFields,
completefunc: function(xData, Status) {
alert(xData.responseXML.xml);
var rtz = elementContents(xData.responseXML.xml, "GetListItemsResult");
try {
xmldata1.loadXML(rtz);
}
catch (e) { }
xmldata1.transformNodeToObject(xsldata1, xmldata2);
var fs = new ActiveXObject("Scripting.FileSystemobject");
var f = fs.OpenTextFile(zpath, 2, true, 0);
var output = "<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>" + xmldata2.xml;
f.write(output);
f.close();
var oxl = new ActiveXObject("Excel.Application");
oxl.Visible = true; // set to false
var owb = oxl.Workbooks.Open(zpath);
oxl.ActiveSheet.Cells.Select;
oxl.Selection.EntireColumn.Autofit;
//oxl.ActiveSheet.PrintOut;
//owb.Close(false);
//oxl.Quit;
} // end completefunc
}); // end spservices
}
function elementContents(text, tagName) {
var sTag = "<" + tagName + ">";
var eTag = "</" + tagName + ">";
var sTagIndex = text.indexOf(sTag);
var eTagIndex = text.indexOf(eTag, sTagIndex + sTag.length);
if ((sTagIndex >= 0) && (eTagIndex > sTagIndex)) return text.substring(sTagIndex + sTag.length, eTagIndex);
return null;
}
</script>

So, this code does the rest! In this case, I allowed for different CAML query based on the viewname variable which is set based on the current view for this list. In this case, it is based on the department the commitments are assigned to. I commented out the last 3 lines of the function that force the print out just to be sure it looks like what it should. This technique allowed me to create a header based on certain content and I can repeat the header fields on subsequent pages automatically too. This same approach could be used for single item printouts in a special layout as well. To see the results, on the list view I would go to Actions-> Print View (Or whatever you call it in the javascript code)

Resulting in the following Excel output.

There it is folks! Just print the spreadsheet and it will retain all the formatting you did on the original. As I said, it is very versatile for what we are doing and we can do a lot of stuff with it. Feel free to ask whatever questions you may have!!

Tuesday, November 23, 2010

Help Needed With Site Provisioning in Sharepoint 2010

I have recently been trying to upgrade my skillset for Sharepoint 2010 and Visual Studio 2010. I am facing an issue and I would like the best way of appraching the problem. I was using the VseWSS 1.3 extensions and used a file called siteprovisioning.cs to do a lot of things when provisioning my site definition. I would use it to create lists, set the site theme, create groups and permissions, etc. I am unable to locate that functionaly directly in 2010 version. So here are basically the questions I am asking.

Can you still do this during site creation? If so, what do I need to do to get this functionaly back. I welcome all comments and suggestions!

Friday, October 29, 2010

jQuery Saves The Day Again!

This is just a quick post on what I needed to get and what I did to get it.

I was using SPServices today and I needed to know the view name for the view that I was on. I have a list that has several views and I wanted to know the name so that I could use it for the "getlistitems" webservice call. I know that ctx.view will get you the current view by guid, but I wanted the actual name.

The name of the view is actually on the page in the viewselector dropdown and this little piece of code gets the text of the current view

var viewname = $("td[id*='onetViewSelector']").text();

Now that is really neat huh?

Thursday, October 28, 2010

Was It Really Cool or Is It Just Me

So today was the day that I had to migrate data from an old system to a new system. I was tasked with converting an old Access database to Sharepoint. Since we only have WSS 3, it was going to a little harder to get it done and to do it right. I know that there may have been other options, but I will outline what I did and why I did it that way.

The choice was made that we would only need to convert the open items from the older system and that was my task. The main problem to overcome was that there were several changes in the new system. These included different fields and some functionality differences. But, that just made it challenging right?

Well, I love a challenge so I immediately hatched an evil plan (ha ha) to make it happen.

I first took the Access table and exported it to the Sharepoint site. This may seem strange, but it gave me a list in Sharepoint that I could work with. Then the rest was creating a custom aspx page with a code file and some object model code to loop through the items that came from Access and add them to the new custom list. I had to massage the data and ensure the old data matched the fields in the new custom list. The code was actually not too difficult and worked great for this and I can use this same approach for any future migrations. Was that really cool?

Any questions or requests, please let me know!

Sunday, October 24, 2010

It Has Been Way Too Long!

I have been quite horrible in keeping up with this blog! I am trying my best to change that! So I have taken some steps to try to make it easier for me to build a useful blog that others might find interesting. My Sharepoint knowledge is continuing to grow and I would love to share that with you! I am hoping that I can finish some of my older thoughts in some of the previous posts, but most of them are no longer on my plate as I have had to abandon them for time considerations. I think that I will be focusing on the printing article later and will mostly talk about a few things that I particularly enjoy.

My work environment does not allow me to use Sharepoint Designer on the production network, and we are still running WSS 3 only. All of my development is on a stand alone machine and most of what we do has to be created as .WSP solutions. However, I like to consider myself a rogue and sometimes I must challenge myself to find a way to make things work that may seem unorthodox to most. But as they say, there is more than one way to skin a cat! (Figuratively Speaking as I have five cats!) So within the next few days, I will be blogging about the Edit Control Block and how it can be customized. Along the way, I will be mixing javascript, jQuery (with SPServices), and .Net of the c# flavor to create an ECB you could write home about (Well, maybe not but..) So there it is and I will be blogging a lot more often! This time I mean it!

Thursday, May 20, 2010

My First Sharepoint Speaking Event

Today I was able to present at a local SPUG meeting. This was my first event and I think it went well. There was definitely some good feedback and a lot of questions. One of the questions really made me wish I could setup a full environment here at home! I do not have a laptop yet so I had to do the Powerpoint slide show option. My presentation was on creating a custom Edit Control Block for list view pages. I am thinking that I might be able to bring that presentation to my blog and provide more code and really show you what can be done at this level as both a power user and developer. Does that sound like a good idea

Wednesday, February 17, 2010

Creating a Blog Suite as a Feature (Intro)

WHAT EXACTLY IS A BLOG SUITE?

So, I was asked to create a blog-like suite of components that could be added as a feature or series of features to an existing site or subsite. The suite was to have a blog, a contacts list, an events(calendar) list, a quicklinks list, and a component that allows users to submit a future blog topic and allows them to vote on a series of topics to blog next. So this to me is a Blog Suite or as the customers wanted to call it an electronic communications suite. The customer will get their wish ;-)

The customer came prepared in this case with a fully working prototype of the look/feel they wanted in an access database. My job is to create a Sharepoint solution that mimics this access solution. I felt a challenge was coming and it was exciting.

So, I started thinking about what I wanted to do to make this as seamless as possible. Initially I created a blog site using the OOB sharepoint template and looked at some of the things that it does and how it looked. This was not going to work the way the customer wanted and I could not just make it a subsite. However, I could use the Sharepoint Solution Generator tool or the (SPSource) tool to pull out the list schemas of the blog site. Or I could create custom lists in a temp site and do the same thing.

I decided that I needed three lists for the blog part and then a separate list for the contacts, quicklinks, and events sections. So in total there will be 6 lists for this solution. My first task was to decide if I wanted to use list templates as mentioned above or if I wanted to create the lists programmatically. I opted for the latter in this case. I decided that I will create a feature in Visual Studio using the VseWss 1.3 extensions. I will add the feature with a feature receiver and this feature receiver will do most of the work in programmatically creating the lists that I need and adding the fields that I need to make it work. I decided to also add a module that would add a webpart page to the root of the site where the feature is activated. This page is going to have a custom webpart that I will add later.

In my next post on this, I will post my first steps in creating the feature and the lists.

First Test of adding html code

<html>
<head>
<title>Blogger Code Test</title>

Wednesday, February 10, 2010

jQuery Forms Replacement In Sharepoint (Phase III)

Okay, now it is time for a few updates on what I have been able to do and what my final solution is going to look like including the reasons I chose to go that way. In the last post on this topic, I talked about building the forms either with a Visual Studio webpart or a Dataform webpart from Sharepoint Designer. I talked some on what I had done on the Visual Studio side and now I will talk about what I did on the other side. First I again create a webpart page to add the webpart to and for this form I wanted to use it for adding new items to a custom task list that I had created. So I have a page and the list so I then navigated to the page in Sharepoint and chose to edit the page. This allows me to add a webpart and this when it really hit me to try something different. I added a content editor webpart. This is a very powerful webpart. I then used Sharepoint designer to create the html of a form using standard html controls and pasted those into the source editor of the webpart. Now I have a page that has controls on it and I changed the location of the javascript to the actual PlaceHolderMain content area of the page. This is the javascript that does all the work for getting the data from Sharepoint or updating the data depending on what I am doing. Again I am using the SPServices jQuery library and it really is neat how fast it is at getting and setting the data. This form was very basic as there were no special controls really. It just had a few text boxes and a couple of select boxes. This works great and is a great way to build small forms for displaying and updating certain pieces of data. I am still working on creating a code with code examples but this may be a few weeks from now. As I said, this does work and I bet I could even get this to work for inline editing of some items directly in the forms or maybe popup a window that does it. Again very useful and I like it. Now, for the environment that I work in and the tough restrictions placed on it, there are some things that I really can not do in our production environment. The main thing that we are not allowed to do is connect Sharepoint Designer to the site. This really makes it harder to update things or to build webparts that you do not have to export and import. As I mentioned in an earlier post, we have to provide solution packages and we have to have as much of the solution as possible in the package and we use Visual Studio for this. With that said, I chose to continue to create the forms using VS by creating a .ascx page that has the server controls on it. Using the VseWSS 1.3 extensions I add a new webpart to my solution and in the code-behind page, I attach the .ascx page and get a handle to all the controls. I also connect up the list to this form and prefill the form with the data. This form of course still renders standard controls and I can still use jQuery to do any updating of the data. This method makes it easier if we need to update the form or change it. I do hope to get some code up here soon, but until then, please let me know if you have any questions or feedback. If something is not clear please let me know!

Saturday, February 6, 2010

jQuery Forms Replacement In Sharepoint (Phase II)

Okay, so in my last piece on this topic I tried to layout several options or paths I could take to accomplish my end goal. At the time, my end goal was to replace my Sharepoint forms with jQuery forms. Over the past few days I have done a lot of experimenting with those options that I had laid out in the previous posts. I will now discuss what I have tried first and then what I think I will try next as another approach. I think I will mainly point out here two things. The main purpose of doing this at all is to avoid all of the postbacks to the server and this particular approach requires replacing the forms with custom forms. With that being said let me begin. For my first test, I wanted to replace the form that users get to when they go to the My Settings section of SP. This is usually found at the top left where it says Welcome (whomever). This dropdown allows you to get to that option which will take users to the userdisp.aspx page if they have the correct permissions. We decided that users should be allowed to change certain options here and we also had a lot more fields for them to fill out. The two main issues were the fields we did not want them to touch and the annoying normal 2 column format of the form which leads to scrolling. So I needed to replace the form or do a lot of other things to make this happen. I chose to replace the form with a custom form. I did not however replace the application pages, I just made a new page and make the users go to that page instead. Here is how I did that. (Remember from the earlier post that I have to make this as a solution package in Visual Studio (VS) as that is a requirement for portability to other locations from the customer.) First, I copied the default.aspx page to a new page called Employees.aspx. I did this mainly because I needed a webpart page to hold my form. This means that I am not solving my popup idea at this time but I am choosing to utilize the real estate of the PlaceHolderMain section of the masterpage. So now I have a page to put my form on. Now, there were basically at this point 2 options I could take here. One option was to build a custom webpart form and drop it on the page and the other was to create a custom webpart form on the page. I know those seem like the same thing, but the difference is how I create the webpart forms. I can build a .Net webpart in VS which allows an easy path for a code-behind page, or I could create a dataform webpart in Sharepoint Designer (SD). I know how to do both of those options but the first choice I made was to go with the VS option first to see what all I could accomplish with that. So, yes, this does not sound like jQuery and I do plan on getting to that but I wanted to show you how I get there and what I was able to do. So, I firstly created my aspx page and now I need a form with controls on it. I did this by creating an ascx user control page that just had a table and the form controls laid out the way I wanted them to be. This meant I could design the form with more columns and make it wider so there was no need to scroll. I also had to provide a cascading dropdown option for the customer so that users could select their organizational structure based on where they worked. This is like a Department, Division, and SubDivision rollup. I decided that this rollup could be done in a single list with the 3 fields tied together. Basically this means that you have an entry for each subdivision including its primary division and department. Now I have my controls so I needed to have a webpart to put them in. In VS I just added a new webpart to my project and this creates the code-behind class file that I need. I used this file to call the ascx page which is placed into the controltemplates folder of the 12 hive in a subfolder. I now have a form with controls that I can use. This is where the fun starts I hope! If you are following along this far you may be thinking that I have taken a long approach to this and it does indeed seem to be that way. I just had a feeling that I could do something with this that would be cool. I was right so lets press on. I purposely ensured that I did not have the autopostback option turned on for any of the controls on the form. Using c# I also added onchange events to a couple of dropdown (select) boxes. When an asp dropdown control is rendered in this case, it is rendered as a select html control. The onchange events are stored in the additionalpagehead content section of the Employees.aspx page. The codebehind page will prefill the options from the UserInfo list if they are there. It is already drawing the form so this is a good thing in this case. The Department dropdown box is filled with just the departments and it has the client-side onchange event attached! The event utilizes the SPServices jQuery library to to fill the second dropdown box with the divisions of whatever department is chosen in the first one. This second one also has an event to drive the 3rd select box. The form has a button that also uses the SPServices library to save the data back to the UserInfo list! This is pretty cool and very fast and the page does not refresh! Eureka I say! One note here is that the SPServices library does have cascade dropdown support that I may utilize in the second approach which I will try next. This means that my next approach will be to user Designer to create my forms to add to the Employees.aspx page. This means that I will get to utilize SP controls and see what I can do with them!

Saturday, January 30, 2010

jQuery Forms Replacement In Sharepoint (Phase I)

In my intro post on this series, I tried to lay the ground work for my approach to replacing my forms with jQuery. I wanted to quickly discuss a few points about why I wanted to do this. I guess one of my main issues for the forms I have is maybe one of the no no's designers often speak of. Many of the forms in the tool that I am currently creating contains about 15 to 20 fields that a user has to fill in. The standard forms in SP render with a two column form that scrolls when the content gets too large. I hate scrolling! I really dislike it if I am filling out a form. This left me with a few choices to make that I would pose to the customer. Do they want a single form with all these fields or perhaps build a wizard-like form system that would break the form into smaller pieces so it could fit in a smaller space. I had also seen what the new SP 2010 forms looked like and thought they were better than before. They also popped up instead of redrawing the whole interface. This is what I want to achieve in this series. The customer was not picky about a single form or a series of smaller forms, and this was good because it leaves the options open a little more. So my first ideas were somewhat valid but then I had a few other ideas that I want to lay out to see what they might be able to do. 1: Create a custom form in an iframe contained in a hidden div. 2: Let Sharepoint create the form in an iframe contained in a hidden div. 3: Use jQuery's ajax functions to create the form and return the content to an iframe contained in a hidden div. 4: Use one of my earlier choices. I really had to think about this as to me it seems logical that I wanted to ensure that I could control everything and have some particular functionality that the customer wanted. WSS 3 does not support cascading dropdowns out of the box. I have seen some solutions that require setting up lists that the users use to support the drop downs. I did not have to do that on the .Net side so much as I was able to create cascading dropdowns in c sharp just fine. However, I really wanted to avoid as much of that as I could because in this version that requires postbacks which is what I want to avoid. Marc Andersons SPServices jQuery library I mentioned in the intro has a nice cascading dropdowns setup. I like this so I am going to see how it will work for the customer and which option it fits in best. So, my next phase I think is going to be to mix a few ideas together. I think that I will use SP Designer to draw the forms in a hidden frame and then use jQuery to provide the pieces that are needed to the user in a wizard like approach. This will hopefully allow me to use all of jQuery's tools and keep the choices available at one time to a minimum. I will let you know how this goes in a few days. If you have a better idea then please let me know!!

Sharepoint EvilGenius