SharePoint DateTime Format Conversions

SharePoint 2007 uses ISO8601 DateTime format internally. It understands either of 2 possible notations for DateTime:

YYYY-MM-DDThh:mmTZD or
YYYY-MM-DDThh:mm:ssTZD
where,

• understandably YYYY being 4-digit year, MM for 2-digit month number, DD for day of month
• T stands for start of time.
• Following that denotes the time which could be "hh:mm" (hours-mins) or "hh:mm:ss" (hours-mins-seconds)
• Z indicates the Coordinated Universal Time (UTC)

On many occassions during MOSS 2007 development, we are required to convert from DateTime to ISO8601 DateTime or vice-versa. So, I decided to post on how this could be achieved.

Convert from DateTime to ISO8601 DateTime (C#):

DateTime date = DateTime.Now; string isoDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(date);

When you are trying to insert or modify some DateTime fields to or from a SPListItem, you could use the code given above to format values into ISO 8601.
You can build the CAML query for the SPQuery object as shown below:
Example:

Note: Don't forget to add reference of Microsoft.SharePoint.Utilities in your class file before building your project.

Convert from ISO8601 to DateTime (C#):

DateTime date = DateTime.Parse("2010-06-15 00:00:00"); string sysDate = date.ToString("MM/dd/yyyy");

Note: In above example, the ISO DateTime string is converted to DateTime equivalent using DateTime.Parse method.

Also, incase you want to change the Site Collection date format:

1. Go to "Site Settings" & navigate to "Modify All Site Settings"
2. Click on Regional Settings under Site Administration
3. Change the time zone. Save the changes.

This brings us to the end of this lengthy post. Hope it helps you SharePoint folks, goodbye for now.

Find the IP Address of the SharePoint 2007 User

Suppose you have a SharePoint website that is being accessed by a lot of users everyday (~1K users daily).

Now for some reason or the other you want to capture the client IP address of the end user accessing your MOSS web application.

Purposes could be like:

• Reporting/Logging user IP address
• Security purposes
• Filtering content based on the client IP address
• Getting the location of the user based on the client IP address
• so on..

You could simply use HttpContext.Current.Request.UserHostAddress to get the IP address of a user.

This could be easily used in both ASP.Net applications as well as SharePoint development projects.

Difference between SPWeb.Users, SPWeb.AllUsers, SPWeb.SiteUsers

MOSS 2007 offers 3 different user collections in the SPWeb object.

• SPWeb.Users
  This represents the collection of users or user objects who have been explicitly assigned permissions in the Web site . This does not return users who have access through a group.
  
  Example (C#):
  SPUserCollection users = web.Users;
• SPWeb.AllUsers
  This gives us the collection of user objects who are either members of the site collection or who have atleast navigated to the site as authenticated members of a domain group in the site.
  
  Example (C#):
  SPUserCollection users = web.AllUsers;
• SPWeb.SiteUsers
  This is the collection of all users that belong to the site collection.
  
  Example (C#):
  SPUserCollection users = web.SiteUsers;

These terms tend to confuse us unless we really know the differences between each one of them.

So I thought it would be nice to write an article stating which is what. Hope this information helps you SharePoint 2007 developers.

Read an .xls file using OleDb in seconds

How often we have been involved with Microsoft's Office applications like MS Word, MS Excel in our day-to-day life. Every time we hear about importing/exporting the contents of an Excel file. I fortunately have been involved with both the tasks.

Here, I would like to show a fast way to read an excel file using OleDb. I have tried to make it compatible with excel 2003(.xls) as well as 2007(.xlsx) formats.

Add these references at the top of the class file:-

using System; using System.Text; using System.Data; using System.Data.OleDb;

Create a method, name it ReadExcelContents and pass the fileName as a parameter to that function.

public static DataTable ReadExcelContents(string fileName) { try { OleDbConnection connection = new OleDbConnection(); if (fileName.ToLower().IndexOf(".xlsx") != -1)//Checking source file for Excel 2007 { if (CheckOfficeVersion.ExcelVersion() == 2007)//Checking if Excel 2007 is installed or not { connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="+ fileName); //Excel 2007, .xlsx } else//if Excel 2003 or other are installed { MessageBox.Show("Sorry! You do not have Excel 2007 installed on your machine.", "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="+ metadataFile); //Excel 97-2003, .xls } string excelQuery = @"Select [Name], [Address], [Age], [Occupation], [Degree] FROM [Sheet1$] where LTRIM(RTRIM([Name]))<>'' & LTRIM(RTRIM([Degree]))<>'' connection.Open(); OleDbCommand cmd = new OleDbCommand(excelQuery, connection); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; DataSet ds = new DataSet(); adapter.Fill(ds); DataTable dt = ds.Tables[0]; connection.Close(); return dt; } catch(Exception ex) { MessageBox.Show("Sorry! "+ex.Message, "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } }

Explanation:

• 
  
  At first, we are opening an OleDb connection, checking the file extenstion to see if the selected file is having an Excel 2007(.xlsx) format. If this is the case, we are checking the system HKLM(Local machine level) registry to see if Excel 2007 is even installed on the client system or not?
• 
  
  If present we are setting the connectionString properties according to Excel 2007 where
  Provider: Microsoft.ACE.OLEDB.12.0 andExtended Properties: Excel 12.0Otherwise, if the user has excel 2003 & has selected an Excel 2007 file, we are throwing an error message, else, we know that the user has selected an Excel 2003 file, so we set the Provider: Microsoft.Jet.OLEDB.4.0 and Extended Properties: Excel 8.0
• 
  
  Alongwith this we need to pass the filename as the DataSource.Now that you must have understood that it is very similar to Ado.net, our next step should be to write the query. In the query we need to pass the ColumnNames same as the ones present in xl column headers. You can provide all clauses such as where, having, etc.
• 
  
  Rest is straight forward for any .Net developer as to opening a connection, setting up a CommandObject with the excelQuery & connectionString. Create an OleDbAdapter object and set its command property. Execute the adapter.Fill(ds) to put the contents read in a DataSet.
• 
  
  Close the connection after reading is over so that there are no fileOpen conflicts and return the datatable.

If you felt that this post has been of help to you, please drop in a small note. It would be a big encouragement for me to write more.