Generating Excel and formatting numbers

I often write HTML tables and pass them off as Excel documents. This is an easy way to automate creation of an Excel sheet and gives you formatting options. The one I can never remember is how to force numbers to be formatted as text cells - to keep long numbers like 143417623220 from showing in scientific notation like 1.43+E11.

So here's the trick. Set a stylesheet and use this to force your numeric cells to format properly.

.text{
  mso-number-format:"\@"; /* force text format for numbers */
}

Use CAML when searching SharePoint

CAML is worth the effort. I went from this (powershell):

$listitem = $list.Items | Where { ($_.Title -eq $row["Title"]) -and ($_.Name -eq $row["Name"]) }

To this:

$query = New-Object Microsoft.SharePoint.SPQuery 
$caml = '<where><and><eq><fieldref name="Title"><value type="Text">' + $row["Title"] + '</value></fieldref></eq><eq><fieldref name="Name"><value type="Text">' + $row["Name"] + '</value></fieldref></eq></and></where>'
$query.Query = $caml 
$listitem = $list.GetItems($query)[0]

And the code in question is now about 60 times faster. I’m guessing that the first line is scanning every item until it finds the one it wants, while the CAML query somehow avoids that. Big improvement. 

Powershell tip 1: get-member

Get-Member will tell you all the properties and methods of an object.

So,

“this is a string” | Get-Member will return all the string options like split, join, whatever.

Get-SPContentDatabase | Get-Member on a sharepoint server will show you lots of methods you can run on the db, like “DiskSizeRequired” and “Server” to get information about the db.  

So if you have something in powershell and don’t know what to do with it, try piping it to get-member. 

ASP.NET without Visual Studio

Sometimes you just don't need a project. You're writing a report or something really low rent and you just need to get a page out there quickly. Well you're in luck, inline declarations can be used to obviate the need for a project, references, etc.

You can have these standalone in any IIS web directory or you can make them into an app by putting them in a folder, adding a web.config, declaring an app pool in IIS admin, etc.

You can mix HTML and ASP tags as needed, or generate elements in code (as I've done below).

Your first line needs to declare a page and language used:
<%@ Page Language="VB" Debug="true" %>

You can import libraries from the GAC by using Import
<%@ Import Namespace="System.Text" %>

You can import libraries from outside the GAC using Register Assembly and then Import
<%@ Register Assembly="System.DirectoryServices, Version=2.0.50727.3053, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="System.DirectoryServices" TagPrefix="SD" %>
<%@ Import Namespace="System.DirectoryServices" %>

VB.NET uses a Page_Init function to initialize your web app.
<script runat="server">

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)
 
    ' POST takes the usr and pwd fields and authenticates
    If Request.Form.Count > 0 Then
      AuthEntry(Request.Form.Item("usr"), Request.Form.Item("pwd"))
    End If
 
    ' no parameters will generate a login form
    If Request.QueryString.Count = 0 And Request.Form.Count = 0 Then
      GenerateForm
    End If
End Sub

' you can generate form elements using code or do more traditional ASP layout below the script tag
Protected Sub GenerateForm()
  Dim Form1 As System.Web.UI.HtmlControls.HtmlForm
  Dim Label1 As System.Web.UI.WebControls.Label
  Dim usr As System.Web.UI.WebControls.TextBox
  Dim pwd As System.Web.UI.WebControls.TextBox
  Dim btn1 As System.Web.UI.WebControls.Button

  Form1 = New HtmlForm()
  Form1.ID = "myForm"

  usr = New TextBox()
  usr.ID = "usr"
  Form1.Controls.Add(usr)

  pwd = New TextBox()
  pwd.ID = "pwd"
  pwd.TextMode = TextBoxMode.Password
  Form1.Controls.Add(pwd)

  btn1 = new Button()
  btn1.Text = "Login"
  Form1.Controls.Add(btn1)

  Page.Controls.Add(Form1)

End Sub

' Here is a function that will validate a user against Active Directory
' You are essentially performing an authenticated search for your own account.
Protected Sub AuthEntry(usr as String, pwd as String)

  Dim path as String = "LDAP://your.domain.com/CN=Users,DC=your,DC=domain,DC=com"
  Dim entry as DirectoryEntry = new DirectoryEntry(path,usr,pwd,AuthenticationTypes.Secure)

  Try
    Dim obj as Object = entry.NativeObject
    Dim search as DirectorySearcher = new DirectorySearcher(entry)
    search.Filter = "(SAMAccountName=" + usr + ")"
    search.PropertiesToLoad.Add("samaccountname")
    Dim result As SearchResultCollection = search.FindAll()
    If result.Count > 0 Then
      ' authenticated
      Response.Write("Success!")
      Exit Sub
    End If
  Catch ex As Exception
    Response.Write(ex.Message)
    Exit Sub
  End Try

End Sub

</script>

Basic sharepoint web part in .NET

1     Created a new Windows Class Library project in VS2008

2     Added a reference to System.Web

3     Added the following ‘using’ statements:

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

4     Changed the default class from public class Class1 to public class Hello: WebPart

5     Wrote an override for the “RenderContents” function (I got fancy and stripped off the leading domain portion of the login, and later wrote more code to change the font and add the little % bar)

protected override void RenderContents(HtmlTextWriter writer)

{

 string name = this.Context.User.Identity.Name;

 writer.Write(" You are logged in as " + name + "! ");

}

6     Built the dll in release mode and put it in a shared dir on my machine.

7     Logged onto the mossdev server and copied the dll to the sharepoint server into the web server’s ‘bin’ directory (\Inetpub\wwwroot\wss\VirtualDirectories\80\bin)

8     Added a line to web.config to make the server trust the dll (\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config). In the section of web.config, I added a entity that looked like this:

9     Went onto sharepoint, went to site settings (for the whole site), and chose ‘Web Parts’ under Galleries. Clicked New, found my ‘Hello’ part, and clicked ‘Populate Gallery’ which added it for use on the site.

1    Went to a site and added the part to my front page. Opah!

automating silent MSI installs

I had to dig a bit to figure this out, and I know I've done this before so I'm making a note here so I can find this information again the next time I forget about it.

To automate a standard MSI install, run it once and log all the properties to a file:
msiexec /i "My Program Installer.msi" /Lp options.log

Make a note of anything you change during this first run. Then take a look at your options.log file. It should have a lot of items like this:

Property(C): ProgramFilesFolder = C:\Program Files\
Property(C): SourceDir = E:\
Property(C): VersionNT = 501
Property(C): ALLUSERS = 1
Property(C): INSTALLLOCATION = C:\Program Files\Whoever\Whatever
Property(C): Manufacturer = Whoever


You can run a silent install by setting these properties on the command line. The ones in all caps are "public" so they can be set by msiexec:

msiexec /qn /i "My Program Installer.msi" INSTALLLOCATION="C:\MyFolder\Whatever"

That's all there is to it! Find the properties you want, pass them on the command line. I have no idea why I can't seem to remember the steps to do that.

Going Meta

Lately I've been using some of the same tricks over and over again in different contexts. Essentially I've just been doing a lot of complex queries, where I select a set of records first, and then select other data out of the first set:

select my_set.X, my_set.Y, my_set.Z
from (
select * from whatever_it_is
where my_item = 'something'
) my_set


Why would I need to do this? Well, for one thing it breaks a problem down into smaller bits, so it is easier to work on. I can write and validate a query that gets all the records I might want to work with, and then write two or three queries that use it to produce friendlier output. I can also use it to do things like counts:

select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X

This will produce something that looks like this:

Item	Num_Chicago	Num_Phoenix	Total
Item 1	4	1	5
Item 2	2	2	4

You can do grand totals using a UNION ALL with another query:

select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
UNION ALL
select 'Totals',
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set

The key to doing a UNION is you need your columns to match up. So note that I have the same number of columns (3) and I used the same names (Item, Num_Chicago, Num_Phoenix, Total) to describe them. By not including my_set.X in the second query I end up with total counts of all the rows, not simply of one item. The output from this should look like:

Item	Num_Chicago	Num_Phoenix	Total
Item 1	4	1	5
Item 2	2	2	4
Totals	6	3	9

I've used the same sort of complex queries to pull the earliest or latest item by category from a set of records; it is a fairly simple concept but can be very powerful.