Getting Started with developing applications on the SharePoint platform

 

Option 1: Installing SharePoint on your laptop/pc with Windows 7

This is as oppose to installing Visual Studio 2010  and SharePoint 2010 on the server (such as Windows Server 2008) and using it as a workstation.  The installation on Windows 7  is not straightforward because the current installers are configured to be used for Windows Server 2008.  But there’s a workaround. After the install, if you get any errors, you may want to check here for some clues.

Option 2: Installing SharePoint and VS2010 on Server 2008

This option requires installing Visual Studio 2010  and SharePoint 2010 on the server (such as Windows Server 2008) and using it as a workstation.

 

Tutorials

http://www.sharepointpittsburgh.org/

Developer Guide

10 Modules to get you going

Getting Worksheet based on the sheet name

 

When programmatically working with existing Excel documents I find that I need to modify a particular sheet in the Excel file and I would like to be able to find it by the name that the user sees in Excel. This is particularly handy when generating files from an existing Excel file that I am using as a template.

Here is the snippet of code to do just that.

private WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName)
{     Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
    if (sheet == null) throw new Exception(string.Format("Could not find sheet with name {0}", sheetName));
    else return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
}

 

Example Usage:

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open("MyFile.xlsx", true))

{

WorkbookPart workbookPart = myDoc.WorkbookPart;

WorksheetPart worksheetPart = GetWorksheetFromSheetName(workbookPart, "Sheet1");

}

 

Let’s break this into easy chunks. The first line just opens the Excel file called MyFile.xlsx. The second line gets the one and only WorkbookPart that all Excel files need. The third line calls our method. This is where it is a little strange in my opinion.

Breaking down the GetWorksheetFromSheetName method.

On the first line we get the Workbook itself (the root element) that is related to the WorkbookPart object. Then we ask it to give us all the child elements that are of type Sheet and only give us the first one we find with the name specified by sheetName parameter.

On the second line we check if the result was null. If it was null this means we didn’t find the sheet and we throw an exception.

On the third line we get the Id of the sheet we found and as the workbookPart to give us the WorksheetPart

Freeze the top row

If you are using the Open XML SDK 2.0 to write Excel file you can freeze the top row so that the column header don’t move as the user scrolls down. This can be done with the snippet below. Kudos to this post for explaining how to do this. I verified that it works when using the SAX like writer called OpenXmlWriter. Not only does it work, but it DOES appear to load the entire Excel file into memory which defeats the reason for using OpenXmlWriter in the first place. I just called it after I closed the OpenXmlWriter. Unfortunately, this won’t work for me or anyone else that is writing giant Excel files.

WorkbookPart wbp = doc.WorkbookPart;
WorksheetPart wsp = wbp.WorksheetParts.First();
SheetViews sheetviews = wsp.Worksheet.GetFirstChild<SheetViews>();
SheetView sv = sheetviews.GetFirstChild<SheetView>();
Selection selection = sv.GetFirstChild<Selection>();
Pane pane = new Pane(){
VerticalSplit = 1D,
TopLeftCell = "A2",
ActivePane = PaneValues.BottomLeft,
State = PaneStateValues.Frozen
};
sv.InsertBefore(pane,selection);
selection.Pane = PaneValues.BottomLeft;

The good news is that this can be solved another way. First You could (I think) use the OpenXmlWriter to write the SheetViews tag, Select tag, and Pane tag. However, it is a bit of work and I was looking for an easy win. What I decided to do was to use an Excel file as a template which already had the first row frozen. There are some tricks to using a template, so check out here to learn more.  Then when I wrote my data to it I automatically had the first row frozen. No coding needed.

Why is my left outer join acting like an inner join?

The short answer is that when you specify a column from TableB in the where clause this can happen because of how nulls are handled differently between the on and where clauses. To better understand the issue, let’s look at an example.

Let’s assume you have two tables that you are joining as shown below.

TableA ID PartNumber

TableB ID PartNumber DateCreated

 

Assume TableA has 3 rows of data and TableB has 5.

TableA rows:

ID	PartNumber
1	P1234
2	P5678
3	P9012

 

TableB rows:

ID	PartNumber	DateCreated
1	P1234	2012-02-10
2	P1234	2012-02-09
3	P5678	2012-02-09
4	P5678	2012-02-10
5	P9876	2012-02-10

 

select * from TableA as A left outer join TableB as B on (A.PartNumber= B.PartNumber)

This would give the result

A.ID	A.PartNumber	B.ID	B.PartNumber	B.DateCreated
1	P1234	1	P1234	2012-02-10
1	P1234	2	P1234	2012-02-09
2	P5678	3	P5678	2012-02-09
2	P5678	4	P5678	2012-02-10
3	P9012	null	null	null

 

No problems so far. Everything looks good and it straight forward.

Now we decide that actually we should only be considering records in TableB that have a DateCreated = ‘2012-02-10’.

This is where it is easy to make a mistake.

 

The WRONG Way

select * from TableA as A left outer join TableB as B on (A.PartNumber= B.PartNumber)

where DateCreated = ‘2012-02-10’

This will work just like this inner join

select * from TableA as A inner join TableB as B on (A.PartNumber= B.PartNumber)

where DateCreated = ‘2012-02-10’

Both return:

A.ID	A.PartNumber	B.ID	B.PartNumber	B.DateCreated
1	P1234	1	P1234	2012-02-10
2	P5678	4	P5678	2012-02-10

Why?

Think about what you have asked SQL Server to do. You have said select all rows from TableA and any matching ones in TableB, but you have also said only show records in TableB that have the DateCreated = ‘2012-02-10’. This means that you have told it to remove any rows where DateCreated is null. Note that one row has a null for DateCreated because there is no matching record in TableB for part number P9012.

 

 

The RIGHT Ways

There are at least three ways to solve this problem.

Solution 1: Move where clause into the on clause

select * from TableA as A left outer join TableB as B on (A.PartNumber= B.PartNumber and DateCreated = ‘2012-02-10’)

I like this one because it is easy to read, though the implications of moving DateCreated to the where clause isn’t quite as clear IMHO.

 

Solution 2: Use an embedded select statement

select * from TableA as A left outer join (select * from TableB where DateCreated = ‘2012-02-10’) as B on (A.PartNumber= B.PartNumber)

I like this one because it is clear what is being selected in TableB, but it is a bit messier reading IMHO.

 

Solution 3: Add check for null in where clause

select * from TableA as A inner join TableB as B on (A.PartNumber= B.PartNumber)

where (DateCreated = ‘2012-02-10’ or DateCreated is null)

This is my least favorite because it uses an OR in the where clause which can have a big negative impact on your performance and there is no need for it. This is particularly true if it was something like DateCreated = (select max(DateCreated from TableB)).

 

Conclusion

I suspect I may have created this very issue many times over the years and not even realized it when doing quick adhoc queries that I didn’t really validate my data very well. Interestingly the results may seem reasonable if you are looking at a lot of rows and are expecting most cases to have matching rows in TableA. The easiest way to tell if you have this problem with your query is to check the number of rows you are getting is the same as the table on the left (TableA in this case). If you have less rows then you may have this problem (or some other problem).