Archive for the ‘Codebook’ Category

Excel 2007, openXml helper

Monday, June 15th, 2009

public class ExcelMapperHelper

public DateTime ConvertExcelDateToDateTime(string date)
DateTime excelBaseDate = new DateTime(1899, 12, 30);
DateTime excelDate = DateTime.MinValue;
int integerExcelDays;

integerExcelDays = Convert.ToInt32(System.Math.Floor(Convert.ToDouble(date)));
excelDate = excelBaseDate + new TimeSpan(integerExcelDays, 0, 0, 0);

return excelDate;

public string GetColumnLetters(string columnName)
return Regex.Replace(columnName, @”(\d+\.?\d*|\.\d+)”, string.Empty);

public string NamespacePrefix
return “xlsx”;

public XmlNamespaceManager GetNamespaceManager(XmlDocument xDoc, string namespacePrefix)
XmlNamespaceManager namespaceManager = new XmlNamespaceManager(xDoc.NameTable);
namespaceManager.AddNamespace(namespacePrefix, @””);
return namespaceManager;

public void GetExcelRows(XmlDocument xDoc, out string namespacePrefix, out XmlNamespaceManager namespaceManager, out XmlNodeList rowList)
namespacePrefix = NamespacePrefix;
namespaceManager = GetNamespaceManager(xDoc, namespacePrefix);
string workSheetXPath = string.Format(“{0}:worksheet/{0}:sheetData/{0}:row”, namespacePrefix);
rowList = xDoc.SelectNodes(workSheetXPath, namespaceManager);

public List<string> ReadStringTable(Stream input)
List<string> stringTable = new List<string>();

using (XmlTextReader reader = new XmlTextReader(input))
for (reader.MoveToContent(); reader.Read(); )
if (reader.NodeType == XmlNodeType.Element && reader.Name == “t”)

return stringTable;

public int GetColumnNumber(string columnName)
string letters = GetColumnLetters(columnName);
double columnNumber = 0;
int counter = letters.Length – 1;
foreach (char c in letters)
if (Char.IsLetter(c))
int charSequence = (int)(c) – (int)(‘A’) + 1;
columnNumber += Math.Pow(26, counter) * charSequence;

return Convert.ToInt32(columnNumber);

public int GetNextColumnNumber(string columnName, int nextCount)
int number = GetColumnNumber(columnName) + nextCount;
return number;

public string GetColumnName(int columnNo)
int dividend = columnNo;
string columnName = String.Empty;
int modulo;

while (dividend > 0)
modulo = (dividend – 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend – modulo) / 26);

return columnName;



Silly CSS pains

Friday, December 5th, 2008

TD Height :
<tr><td  style=”height:5px;”><SPACER TYPE=”block” HEIGHT=”1″ WIDTH=”1″></td> </tr>

function stopEvent(e) {
if (!e) e = window.event;
if (e.stopPropagation) {
} else {
e.cancelBubble = true;

Executing a JS func for each user control inside a aspx page

Friday, November 21st, 2008

The scenario is, 1 aspx page needs to lead a usercontrol repetitively, and these user control needs to excute a JS method on load. The Method filters a JS array and rerenders a dropdown inside the usercontrol, so it has the reference of the server controls for respective usercontrols. thus the array is in the aspx page while the method is dynamic for each usercontrol. Now how to execute dynamic number of dynamically named functions for each usercontrol? the solution is below-

In aspx page

<head runat=”server”>
<script type=”text/javascript”>
var functionList = new Array();


<script type=”text/javascript”>
var lenght = functionList.length;
var newfunction = functionList.pop();
while(typeof newfunction != “undefined”)
newfunction = functionList.pop();

In ascx page(at the end of the page)

<script language=”javascript” type=”text/javascript”>
functionList.push(MethodName<%=ServerControl.ClientID %>);

Set focus @end of textbox

Tuesday, October 28th, 2008

function SetEnd (controlObj)
if (controlObj.createTextRange)
var FieldRange = controlObj.createTextRange();
FieldRange.moveStart(‘character’, controlObj.value.length);

Regular expressions

Wednesday, July 9th, 2008

Exclude some file types from a list

function Filter()
        var arr = new Array(9);
        arr[0] = "something.aspx";
        arr[1] = "something.ascx";
        arr[2] = "something.gif";
        arr[3] = "something.asrx";
        arr[4] = "";
        arr[5] = "";
        arr[6] = "something.bat";
        arr[7] = "something.arr";
        arr[8] = "something.ashx";
        var rgex = /.*[.](?!(aspx|ascx|gif)$).*$/;
        var filtered = new Array(8);

        for(var i=0;i<arr.length;i++)
                filtered[i] = arr[i];
                filtered[i] = "ok";



Event bubble up in user controls

Sunday, April 6th, 2008

As I was new in MVP and NMock, I was given a small assignment to code. The goal was to be able to add user controls dynamically in an aspx file by clicking an “Add control”. Then each control will have a delete button in them, which upon click will remove the control from the page. There was also one more thing, upon changes in the user control, each change will appear in a panel in the Default page.

Now it sounded pretty easy in the beginning. But to implement things in the MVP architecture created the first barrier. It was hard to put the mindset first on MVP. To take the control away from the web project made the event handling a bit tricky for me. There are actually 2 type of event which needed communication between the dynamically created user controls and the parent aspx page –

  • Upon deletion let the aspx page know which control was deleted, and update the view state. And do this by the Presenter.
  • Upon change in content of a control, let the aspx file know to update the change log.

The first event was handled in the following way-
( Code in the ascx.cs file ( the View) )

public delegate void DeleteControlDelegate(string id);
public event DeleteControlDelegate DeleteControl;

protected void btnDelete_Click(object sender, EventArgs e)

if (DeleteControl != null)




Now the Presenter for the Parent aspx page has a method with the same signature of the DeleteControl(id), UpdateViewState.

public void UpdateViewState(string id)

home.ControlList = _home.ControlList.Replace(id,String.Empty);

_home.ControlList = _home.ControlList.Replace(“||”, “|”);

_home.ActionList.Add(String.Format(“Control #{0} been removed”, id.Substring(0, 5)));


Now who to hook up the two? Thats actually the easy part. See, when I added the control dynamically in the aspx page I hooked it up as follows –

public string AddControl(string type, string id, bool isNew)

lblMsg.Text = String.Empty;
DemoModule uc = (DemoModule)Page.LoadControl(Constants.PATH);
uc.Type = type;
uc.DeleteControl += new DemoModule.DeleteControlDelegate(_homePresenter.UpdateViewState);
uc.PropagateAction = this.Show_Activity;
if (isNew)

uc.ID = System.Guid.NewGuid().ToString();


uc.ID = id;pnlControl.Controls.Add(uc);

this.ActionList.Add(String.Format(“Control #{0} has been added.”, uc.ID.Substring(0,5)));


return uc.ID;


The second event was rather easy. As I didnt have to bubble up dynamic information from the user control, just the action was bubbling up, I used the systems event handler delegate to work for me ( this.Show_Activity ).

protected void Show_Activity(object sender, EventArgs e)

Control ctl = (Control)sender;
string controlName = ctl.GetType().Name;
string text = _homePresenter.FormatMessage(controlName,”change”);
this.ViewState[Constants.CHANGELOG] = this.ViewState[Constants.CHANGELOG] + “|” + text;


Its the simplest solution. But its not complete, if we want to bubble up what was changed, the specific information, we would use the first custom delegate. Even still the code is messy, there is too much code and decisions in the code behind file. To clear it up, I could create an object that will emulate the user control. Presenter will update the object and view will just show it. But that looks like too much work for something so small. But as I now know, its quite easy if needed :).

the sample solution is here

My MVP Wireframe/Walkthrough

Thursday, March 13th, 2008

My friend Iftekhar was asking for a simple wireframe/walkthrough of MVP pattern. So here it is for him (and anybody else if they need ;) ).

First I created a website. It has one page ‘Default.aspx’ with two labels, one textbox, one dropdown and one button. Then I created 2 class library projects for presenter and Viewfactory (interfaces for each webpage). I created one IDefault interfce and implemented it in the code behind of ‘Default.aspx’.

IDefault interface:

public interface IDefault

string username { get;set;}

string role { get;set;}

void DisablePanel();

void ShowMessage(string msg);

void ShowHistory(string msg);


Now I also created a presenter class for the default page. It has a private IDefault property. Now the codebehind will instantiate the presenter by passing itself to it. The presenter will initialize its private IDefault with the view instance.

The Default.aspx.cs file:

public partial class _Default : System.Web.UI.Page, IDefault

DefaultPresenter _presenter;

protected void Page_Init(object sender, EventArgs e)

_presenter = new DefaultPresenter(this);


protected void Page_Load(object sender, EventArgs e)

username = TextBox1.Text.ToString();

role = drpList1.SelectedValue.ToString();



On the other hand the presenter class is as follows –

public class DefaultPresenter

Private IDefault _default;

public DefaultPresenter(IDefault def)

_default = def;


Now every business logic Default.aspx.cs needs to perform to update the GUI, the presenter will have it done with the interface. This is the basic beauty in it. The view became pluggable and more testable. It’s like instead of going and look for the business logic classes and call it to view, view itself goes to the logic and let that do its work in its own place. Once presenter is done with all its hocus pocus it just calls methods of the interface ( actually the view) to update the UI components accordingly.

Now to test the Model, I am keeping track of the value enter in textbox and selected value of the drop down. As its not directly connected to manipulating the UI components, I put it in the model. Presenter just ask the model to log the event and if it wants to show it to view, model returns something. Here also I have a IHistory for more testability and cleanliness.

The History and IHistory:

public interface IHistory
string Createhistory(string username, string role);

public class History:IHistory

#region IHistory Members

public string Createhistory(string username, string role)


return “No open session”;


return String.Format(“{0} logged in at {1} as {2}”, username, DateTime.Now, role);



In a nutshell –

  • First design your view interfaces,
  • create web pages implementing them ( or you can design the aspx/ascx with the designer first then write interfaces. I donno what the wise and learned people will say, I just didn’t get any difference, though most of the time it depends on what are you doing).
  • Then create presenter classes who will work with the interface to service the view. Write methods to calculate everything for the desired behavior of view in the presenter and at the end just call the interface methods so that view gets updated.
  • And if you need some more things you want to code about, do it in model.

Viola, just 3/4 projects and you are done.

The full solution file is here.

I am a humble learner. So if I made some mistakes or you would like some other angles to look at it, do say.

For my reference

Wednesday, March 12th, 2008

I had 3 tables. bookmark, tags and a bookmark-tag relations table. both bookmark and tag table were independent and the only link was the @ModuleID. the relation table(bookmarkID,tagID) had the mapping for which bookmark had which tags. now I need a query to get all the bookmarks, with all the the tagnames respect with tagIDs for each bookmark from the relation table in CSV in one projection. the following query gave me a perfect result.

CREATE procedure [dbo].[Module_Bookmark_GetAllBookmark]
@ModuleID int

FBK.ID,FBK.ModuleID,FBK.title, FBK.URL,FBK.Description,

STUFF(( SELECT ‘,’+ FBT.[Name]+':’+STR(FBT.[ID])

FROM [Module_BookmarkTags] FBT

INNER JOIN [Module_BookmarkTagsMapping] FBM



FOR XML PATH(”)),1,1,”) as Tags

FROM [Module_Bookmark] FBK where FBK.ModuleID = @ModuleID

GROUP BY FBK.title,FBK.ModuleID, FBK.ID, FBK.URL,FBK.Description

ORDER BY title