Excel 2007, openXml helper

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, @”http://schemas.openxmlformats.org/spreadsheetml/2006/main”);
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;



Posted in Codebook | 35 Comments »

Silly CSS pains

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;

Posted in Codebook | 43 Comments »

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 %>);

Posted in Codebook | 28 Comments »

Set focus @end of textbox

October 28th, 2008

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

Posted in Codebook | 28 Comments »

Regular expressions

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] = "something.jp";
        arr[5] = "something.cab";
        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";


Posted in Codebook | 29 Comments »

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 asp.net 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

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.

In my new office I was studying the following articles.

  • http://www.codeproject.com/KB/architecture/DependencyInjection.aspx
  • http://www.martinfowler.com/articles/injection.html
  • http://codebetter.com/blogs/jeremy.miller/archive/2005/10/06/132825.aspx
  • http://msdn2.microsoft.com/en-us/magazine/cc163739.aspx

I came to read these when I started to study about Unit Testing on ASP.NET projects. And here all use MVP. Its all new experience for me.
I have always been a fan of NTiers applications pattern. But now I am required to work on MVP with asp.net. Feels little out of my normal brainwave, but its stimulating. Stepping out of my comfort zone, thinking architectures in a sort of reverse way, or top-down and bottom up in parallel. Currently the architectures I have managed to put up of Dependency Injection is through constructor injection. I am loving how pluggable and traceable the solution is becoming using MVP.

I am trying to explain or map MVP from a NTierian’s point of view. Lets say a solution have 4 projects (DAL, BLL, Facade and website). In NTiers Website will communicate with Facade. Facade will communication with BLL and website. BLL will communicate with DAL and facade. In NTier BLL handles the business logic with Data layer. Website’s ASPX pages handles the UI related business logic themselves in their code-behind files.

Now say if I am to translate the design in MVP. There is no 101 relation there. See the website will be called the ‘VIEW‘ but it will not handle the business logic related to the population of the GUI, a ‘PRESENTER‘ will do it with the help of the interfaces for each view ( Factory method). Now ‘MODEL‘ will act as the facade here who will handle all sorts of other logics, both Presenter and DAL have communication with it. Good to know that the DAL will always be DAL, no matter what framework it is in.

The work flow of MVP and MVC from Nikola Malovic

Actually I don’t know if I am right or wrong. Its just 3 to 4 days I am playing with MVP and this is what I felt. I am a complete beginner in t, so if anyone is reading do leave your view on it, and correct me if you find me wrong. It will be highly appreciated. My MVP readings will be found at Delicious.

I am working on a sample project where I am using MVP. I will write about it once i am done :).

For shortnote there are 3 types of dependency injection – Constructor Injection(I use it for NMock Unit testing my presenters), Setter Injection(Spring.NET framework), and Interface Injection(Avalon). I am still not wise enough to shade some lights on those. I will do that someday.

For my reference

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


Posted in Codebook | 41 Comments »

Some reading

March 4th, 2008







SQL server