Monday, March 7, 2011

Sharepoint Branding With CSS and Javascript Part 1

Okay, so after a few years of working with SharePoint and WSS 3 I decided to see what I could do to make my site look a lot less like Sharepoint and more like a traditonal site. I looked at all the angles that I could think of and came up with what I thought would be some key points I wanted to include and some things that might be a challenge to correct but possibly worth it in the end. One of my requirements is to create my sites as .WSP solutions so that they can be sent to other areas. With this as a major requirement I created a site definition solution in Visual Studio 2008. However, most of what I cover here does not require that you do this. Here is the list:

Most functionality should work from a document libray
Allow for different screen "resolutions"
Keep it fairly simple but allow for easy updating

There are more points but these are some of the major ones. Here are a few screenshots of what it looks like so far. There are some things I have not yet changed:

So there are the screenshots. Let's get to the meat of it all shall we??

My full solution is actually built as a full .WSP solution in Visual Studio, but basically what I have done is the following:

Modified the Master Page for the layout I wanted.
Created several stylesheets representing different screen sizes.
Added the style css files to the master page gallery including a subfolder for images.
Created a javascript file to change the stylesheet based on screen size.
Created a "Scripts" document library for the javascript files.
Uploaded all the files to the libraries

So the first thing I want to go over is the Master Page. Here is the code for the master page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@ Master Language="C#" Debug="true" %>
<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>
<%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="wssuc" TagName="Welcome" Src="~/_controltemplates/Welcome.ascx" %>
<%@ Register TagPrefix="wssuc" TagName="DesignModeConsole" Src="~/_controltemplates/DesignModeConsole.ascx" %>
<%@ Register TagPrefix="asp" Namespace="System.Web.UI.DataVisualization.Charting" Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="System.Web.UI.DataVisualization.Charting" %>
<html dir="<%$Resources:wss,multipages_direction_dir_value%>" runat="server" xmlns:o="urn:schemas-microsoft-com:office:office">
<head runat="server">
<meta name="GENERATOR" content="Microsoft SharePoint"/>
<meta name="progid" content="SharePoint.WebPartPage.Document"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<meta http-equiv="Expires" content="0"/>
<SharePoint:RobotsMetaTag ID="RobotsMetaTag1" runat="server" />
<title id="onetidTitle">
<asp:ContentPlaceHolder ID="PlaceHolderPageTitle" runat="server" />
</title>
<SharePoint:CssLink runat="server" />
<SharePoint:Theme runat="server" />
<SharePoint:ScriptLink language="javascript" name="core.js" Defer="true" runat="server" />
<SharePoint:CustomJSUrl runat="server"/>
<SharePoint:SoapDiscoveryLink runat="server" />
<SharePoint:DelegateControl runat="server" ControlId="AdditionalPageHead" AllowMultipleControls="true" />
<asp:ContentPlaceHolder ID="PlaceHolderAdditionalPageHead" runat="server"></asp:ContentPlaceHolder>
<link rel="stylesheet" type="text/css" title="default" href="mcurves.css" />
<link rel="alternate stylesheet" type="text/css" title="mcurves1024" href="mcurves1024.css" />
<link rel="alternate stylesheet" type="text/css" title="mcurves1200" href="mcurves1200.css" />
<link rel="alternate stylesheet" type="text/css" title="mcurves1440" href="mcurves1440.css" />
<link rel="alternate stylesheet" type="text/css" title="mcurves1600" href="mcurves1600.css" />
</head>
<body onload="javascript:if (typeof(_spBodyOnLoadWrapper) != 'undefined') _spBodyOnLoadWrapper();" >
<form id="Form1" runat="server" onsubmit="return _spFormOnSubmitWrapper();">
<WebPartPages:SPWebPartManager ID="m" runat="Server" />
<div id="search">
<table>
<tr>
<td>
<input type="text" name="s" id="search-text" value="" />
</td>
<td>
<a target='_self' href='javascript:' onClick="javascript:SearchIt();javascript:return false;" title="Go Search" ID=onetIDGoSearch><img border='0' src="/_layouts/images/gosearch.gif" alt="Go Search"></a>
</td>
</tr>
</table>
</div>
<div id="wrapper">
<div id="header">
<div id="logo">
<h1><a href="javascript:loadpage('/default.aspx');">MCurves</a></h1>
<p> design by Daniel Walker</a></p>
</div>
</div>
<div id="menu" class="menu">
</div>
<div id="page">
<div id="page-bgtop">
<div id="page-bgbtm">
<div id="content">
<asp:ContentPlaceHolder id="PlaceHolderScriptTop" runat="server">
<script language="javascript" type="text/javascript" src="http://wss_sa/tools/mcurves/Scripts/mcurves_script.js"></script>
</asp:ContentPlaceHolder>
<asp:ContentPlaceHolder id="PlaceHolderMain" runat="server"/>
<div style="clear: both;"> </div>
</div>
<div id="sidebar">
<div id="welcome">
<wssuc:Welcome id="IdWelcome" runat="server" EnableViewState="false"></wssuc:Welcome>
</div>
<div id="sidemenu">
</div>
</div>
<div style="clear: both;"> </div>
</div>
</div>
</div>
</div>
<div id="footer">
<p>FOR OFFICIAL USE ONLY.</p>
</div>
<asp:ContentPlaceHolder ID="WSSDesignConsole" runat="server">
<wssuc:DesignModeConsole id="IdDesignModeConsole" runat="server" />
</asp:ContentPlaceHolder>
<asp:ContentPlaceHolder ID="SPNavigation" runat="server">
<SharePoint:DelegateControl ID="DelegateControl2" runat="server" ControlId="PublishingConsole"
PrefixHtml="<tr><td colspan="4" id="mpdmconsole" class="ms-consolemptablerow">"
SuffixHtml="</td></tr>"
>
</SharePoint:DelegateControl>
</asp:ContentPlaceHolder>
<div id='printalert' style='width: 100%; height: 100%; border: #99ccff 2px solid;font: bold 8pt tahoma; display:none;'>
<table style='width: 100%; height: 100%;' border="0" cellpadding="0" cellspacing="0">
<tr><td align='center'>This display is not formatted for printing and your prints may not look as expected.</td></tr>
<tr><td align='center'>If you need to print, you should use Snagit to capture what you need.</td></tr>
</table>
</div>
<div style="display:none">
<SharePoint:SiteActions runat="server" AccessKey="<%$Resources:wss,tb_SiteActions_AK%>" id="SiteActionsMenuMain"
PrefixHtml="<div><div>"
SuffixHtml="</div></div>"
MenuNotVisibleHtml="&nbsp;">
<CustomTemplate>
<SharePoint:FeatureMenuTemplate ID="FeatureMenuTemplate1" runat="server"
FeatureScope="Site"
Location="Microsoft.SharePoint.StandardMenu"
GroupId="SiteActions"
UseShortId="true"
>
<SharePoint:MenuItemTemplate runat="server" id="MenuItem_Create"
Text="<%$Resources:wss,viewlsts_pagetitle_create%>"
Description="<%$Resources:wss,siteactions_createdescription%>"
ImageUrl="/_layouts/images/Actionscreate.gif"
MenuGroupId="100"
Sequence="100"
UseShortId="true"
ClientOnClickNavigateUrl="~site/_layouts/create.aspx"
PermissionsString="ManageLists, ManageSubwebs"
PermissionMode="Any" />
<SharePoint:MenuItemTemplate runat="server" id="MenuItem_EditPage"
Text="<%$Resources:wss,siteactions_editpage%>"
Description="<%$Resources:wss,siteactions_editpagedescription%>"
ImageUrl="/_layouts/images/ActionsEditPage.gif"
MenuGroupId="100"
Sequence="200"
ClientOnClickNavigateUrl="javascript:MSOLayout_ChangeLayoutMode(false);"
/>
<SharePoint:MenuItemTemplate runat="server" id="MenuItem_Settings"
Text="<%$Resources:wss,settings_pagetitle%>"
Description="<%$Resources:wss,siteactions_sitesettingsdescription%>"
ImageUrl="/_layouts/images/ActionsSettings.gif"
MenuGroupId="100"
Sequence="300"
UseShortId="true"
ClientOnClickNavigateUrl="~site/_layouts/settings.aspx"
PermissionsString="EnumeratePermissions,ManageWeb,ManageSubwebs,AddAndCustomizePages,ApplyThemeAndBorder,ManageAlerts,ManageLists,ViewUsageData"
PermissionMode="Any" />
<SharePoint:MenuItemTemplate runat="server" id="MenuItem_AllContent"
Text="All Site Content"
Description="All Site Content"
ImageUrl="/_layouts/images/ActionsSettings.gif"
MenuGroupId="100"
Sequence="400"
UseShortId="true"
ClientOnClickNavigateUrl="~site/_layouts/viewlsts.aspx"
PermissionsString="EnumeratePermissions,ManageWeb,ManageSubwebs,AddAndCustomizePages,ApplyThemeAndBorder,ManageAlerts,ManageLists,ViewUsageData"
PermissionMode="All" />
</SharePoint:FeatureMenuTemplate>
</CustomTemplate>
</SharePoint:SiteActions>
</div>
<asp:ContentPlaceHolder ID="PlaceHolderFormDigest" runat="server">
<SharePoint:FormDigest ID="FormDigest1" runat="server" />
</asp:ContentPlaceHolder>
<input type="text" name="__spDummyText1" style="display: none;" size="1" />
<input type="text" name="__spDummyText2" style="display: none;" size="1" />
</form>
<asp:Panel ID="Panel1" visible="false" runat="server">
<asp:ContentPlaceHolder id="PlaceHolderLeftNavBarDataSource" runat="server" />
<asp:ContentPlaceHolder id="PlaceHolderLeftNavBarTop" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderCalendarNavigator" runat="server" />
<asp:ContentPlaceHolder id="PlaceHolderLeftActions" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderLeftNavBar" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderUtilityContent" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderBodyAreaClass" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTitleAreaClass" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderPageDescription" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderSearchArea" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTopNavBar" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderHorizontalNav" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTitleLeftBorder" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTitleRightMargin" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTitleAreaSeparator" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderNavSpacer" runat="server"><IMG SRC="/_layouts/images/blank.gif" width=138 height=1 alt=""></asp:ContentPlaceholder>
<asp:ContentPlaceHolder id="PlaceHolderLeftNavBarBorder" runat="server"></asp:ContentPlaceHolder>
<asp:ContentPlaceHolder id="PlaceHolderBodyLeftBorder" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderBodyRightMargin" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderPageImage" runat="server" />
<asp:ContentPlaceHolder ID="PlaceHolderTitleBreadcrumb" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderPageTitleInTitleArea" runat="server" />
<asp:ContentPlaceHolder ID="PlaceHolderMiniConsole" runat="server" />
<asp:ContentPlaceHolder ID="PlaceHolderSiteName" runat="server"/>
</asp:Panel>
</body>
</html>

So there is the Master Page. It is very different than a standard WSS master page, but it does work! In my next post, I will go over the stylesheet code used to make this.

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

Sharepoint EvilGenius