Add hyper link in Excel using EPPlus

Many People has asked me that how they can add hyper link to another sheet through EPPlus. This is bit tricky because ws.Cells[1,1].Hyperlink takes Uri object which is generally used for external references only (file or website).

So after doing bit RnD I have developed a function through which you can easily add hyperlinks within your sheets. Here is the function:-

/// 
/// Adds the hyper link.
/// 
/// "ws">The ws.
/// "source">The source.
/// "destination">The destination.
/// "displayText">The display text.
private static void AddHyperLink(ExcelWorksheet ws, ExcelRange source, ExcelRange destination, string displayText)
{
	source.Formula = "HYPERLINK(\"[\"&MID(CELL(\"filename\"),SEARCH(\"[\",CELL(\"filename\"))+1, SEARCH(\"]\",CELL(\"filename\"))-SEARCH(\"[\",CELL(\"filename\"))-1)&\"]" + destination.FullAddress + "\",\"" + displayText + "\")";
}
Here is how you can call above function to add hyperlinks in your cell:-

ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
ExcelWorksheet sheet2 = CreateSheet(p, "Sample Sheet 2");
sheet2.Cells[1, 1].Value = "Hyperlink Target";
AddHyperLink(ws, ws.Cells[13, 1], sheet2.Cells[1,1], "Sample Hyperlink");
Hopefully this tip will be useful for you, Happy Coding !!!

Presentations - Introduction to ASP .NET MVC

Last month I was invited at PNSC (Pakistan National Shipping Corporation) to have a session related to ASP .Net MVC 3. It was really a wonder full experience to share the knowledge with other persons. You can find the slides shared during session below:-

Also have a look at some interesting moments:-




Get List of Process with Process Owner/User

Recently I have come to a situation where I want to list all the process in the computer. Solution was quite simple using Process.GetProcess().

Process[] processlist = Process.GetProcesses();
 
foreach (Process theprocess in processlist)
{
 Console.WriteLine("Process: {0} ID: {1}", theprocess.ProcessName, theprocess.Id);
}


However I also want to see that process is created with which user. For this after some googling, I found this solution. Hopefully it will be useful for you happy coding !!!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Management;
using System.Runtime.InteropServices;
 
namespace ProcessViewer
{
 class Program
 {
  static void Main(string[] args)
  {
   GetProcessInfo();
  }
 
  /// Gets the process info.
  static void GetProcessInfo()
  {
   foreach (var process in Process.GetProcesses())
   {
    string name = process.ProcessName;
    int processId = process.Id;
    string windowTitle = process.MainWindowTitle ?? "N/A";
 
    Console.Out.WriteLine(string.Format("{0,20}|{1,10}|{2,20}|{3,20}",
     name,
     processId,
     GetProcessOwner(processId),
     windowTitle));
   }
   Console.ReadKey();
  }
 
  /// Gets the process owner.
  static string GetProcessOwner(int processId)
  {
   string query = "Select * From Win32_Process Where ProcessID = " + processId;
   ManagementObjectSearcher moSearcher = new ManagementObjectSearcher(query);
   ManagementObjectCollection moCollection = moSearcher.Get();
 
   foreach (ManagementObject mo in moCollection)
   {
    string[] args = new string[] { string.Empty };
    int returnVal = Convert.ToInt32(mo.InvokeMethod("GetOwner", args));
    if (returnVal == 0)
     return args[0];
   }
 
   return "N/A";
  }
 }
}

Article of the Day! jQGrid and MVC 3 with Custom Paging and Custom Search Filters

I am quite pleased to announce that on 25th April 2012, my article jQGrid and MVC 3 with Custom Paging and Custom Search Filters was selected as Article of the Day on forums.asp.net. For details please visit:-


http://www.asp.net/community/articles/


I am among top 125 contributors around the world in forums.asp.net

I am quite pleased to share that I have received All-Star rank in forums.asp.net. See this:-

It was really wonderful experience while posting in forums. It helped me a lot in gaining in-depth knowledge of ASP .Net technologies. I recommend all other developers to participate in such activity.

You can also checkout my profile at forums.asp.net.

Happy coding !!!

jQGrid and MVC 3 with Custom Paging and Custom Search Filters

During past some days I was studying MVC 3. In start I found MVC little bit challenging. But its features are really exiting, I believe MVC has the power of ASP .Net along with the flexibility of classic ASP.

While implementing data driven application in MVC you have three major choices for grids i.e. MVC DataGrid, jQGrid and MvcContrib grid. jQGrid is really something which has worked best for me. So, I am sharing a sample with you to highlight key features of jQGrid along with MVC.

First of all you have to download jQGrid from jQGrid Home Page. Also you can download the source code for this article.

Client Side Code

First you have to place some container divs where your grid will be rendered like this:-
<div id="content-grid">
    <table id="jq-grid" class="scroll" cellpadding="0" cellspacing="0">
    table>
    <div id="jq-grid-pager" class="scroll" style="text-aligncenter;">
    <div>
<div>
In jq-grid table, your grid will be rendered, while jq-grid-pager will be used for the pager in the grid.

Now you need to initate jQGrid with call to$("#jq-grid").jqGrid({ in your document.ready event. Here I am giving you a description of individual parameters which I used in my sample.

Url is used to point to the location from where grid will fetch the data.
url: 'http://localhost:60760/Home/GetList',//Service URL to fetch data

I generally uses JSON because it is really light weight
datatype: 'json',//Data Type which service will return

Now because I am using custom filters to search the records for my grid. So we have to explicitly pass them to server. They will be passed as JSON string in filters parameter or our server side function which I will discuss later.
postData://Data that will be posted with every request
{
filters: function () {
      return $.toJSON([
                          //Here add the parameters which you like to pass to server
                          { Key: "EmployeeName", Value: $("#txtEmployeeName").val() },
                          { Key: "Id", Value: $("#txtId").val() }
                    ]);
                     }
}

Now we define column and their properties through above code. I always like to fix the widths of columns, however you can ignore width parameter as well. Also note that in last column I disabled sorting using sortable:false.
colNames: ['Employee Id''Employee Name''Action'],//Column Names
colModel: [//Column details
           { name: "Employee Id", index: "Employee Id", width: "220px" },
           { name: "Employee Name", index: "Employee Name", width: "220px" },
           //Do not allow sorting on Action Column
           { name: "Action", index: "Action", sortable: false, width: "220px" }
          ],
I am persisting sort order, sort column, total pages and current page in session, so whenever page is reloaded, I am populating them  from my ViewBag.
sortname: '@ViewBag.EmployeeGridRequest.sidx',//This will persist sorting column 
sortorder: '@ViewBag.EmployeeGridRequest.sord'// This will persist sorting order 
rowNum: @ViewBag.EmployeeGridRequest.rows, //Total Pages
page: @ViewBag.EmployeeGridRequest.page, // Current Page
rowList: [@ViewBag.EmployeeGridRequest.rowList], // Page Selection Options

This one is simple, it will set the grid's caption, if you leave this blank then grid's header will be hidden.
caption: 'Employee Grid List',//Grid's Caption, you can set it to blank if you do not want this

Also there are some parameters which you should know, see above inline comments for their details.
altRows: true,//Use alternate row colors
altclass: 'jqgaltrow',//Alternate row class
hoverrows: true//Do not hover rows on mouse over
pager: $('#jq-grid-pager'),//Div in which pager will be displayed
toppager: true,
pgbuttons: true,//Show next/previous buttons
loadtext:'Loading Data please wait ...',
loadui :'block',//enable by default, block will disable inputs, remove this line if you do not want to block UI

I have created a function to load the data for grid whenever search or refresh button is pressed. Here is the code:-
    function reloadGrid(refresh) {
        if (refresh == true) {
            //Set initial values of your filters here
            $("#txtId").val("");
            $("#txtEmployeeName").val("");
    
            //reload grid's page 1 instead of current page
            $("#jq-grid").trigger("reloadGrid", { page: 1 });
        }
        else {
            $("#jq-grid").trigger("reloadGrid");
        }
    }
When I want to refresh grid, I set page:1, which will load the first page when ever you press refresh button.

Server Side Code
First Method which be called when Page is loaded is:-
[HttpGet]
public ActionResult Index()
{
    ViewBag.Filters = string.Empty;
    if (EmployeeGridRequest.DataFilters().Count > 0)
    {
        ViewBag.Filters = "$('#txtEmployeeName').val('" + EmployeeGridRequest.DataFilters()["EmployeeName"] + "');";
        ViewBag.Filters += "$('#txtId').val('" + EmployeeGridRequest.DataFilters()["Id"] + "');";
    }
 
    ViewBag.EmployeeGridRequest = EmployeeGridRequest;
    return View();
}
In above code I have checked from my session wrapper EmployeeGridRequest that if there is any filters available then use them to populate the initial values for the my filters and save in my ViewBag Object. As you can see below, in document.ready I have checked that if ViewBag contains filters then render them as Raw Html:-
$(document).ready(function () {
@if (ViewBag.Filters != string.Empty)
{
    @Html.Raw(ViewBag.Filters);
}

Now comes my main method which is responsible for providing data to grid. I have created some custom classes which encapsulate all the parameters which are used by jQGrid. So every time my implementation remains the same. See:-
[HttpGet]
        public JsonResult GetList(GridDataRequest request)
        {
            EmployeeGridRequest = request;
            var response = model.GetRecords(request);//Tricky Part Here :)
            var jsonData = new
            {
                total = response.TotalPages,
                page = request.page,
                records = response.TotalRecords,
                rows = response.Rows
            };
            return Json(jsonData, JsonRequestBehavior.AllowGet);
        }

Now in our model we have to create the list which we want to display in grid based on the parameters provided.
public GridDataResponse GetRecords(GridDataRequest request)
{
    .....
 GridDataResponse response = new GridDataResponse();
 response.TotalRecords = selectedList.Count;
 response.TotalPages = (int)Math.Ceiling((float)selectedList.Count() / (float)request.rows);
 response.Rows =
  (
  from item in selectedListAfterPagging
  select new GridDataDetail()
  {
   id = item.Id,
   cell = new string[] { 
   item.Id.ToString(),
   item.Name,
   "+item.Id.ToString()+"' title='Edit'>Edit " +
   "+item.Id.ToString()+"' title='Delete'>Delete"
   }
  }
  ).ToArray();
 return response;
}

I have used a List to store my Employee objects, this is only because I do not want to add a database to my sample, in real world off course you will be using database to access data. In above method I am creating a GridResponse object which will be bind with grid.
Since I am using custom paging instead of sending all records to client, so I have to manually calculate the no of records as i did:-
response.TotalPages = (int)Math.Ceiling((float)selectedList.Count() / (float)request.rows);

Also see that Grid detail object contain two properties i.e. id and cell. The id will be used as identified in grid and col will be bind to the columns in our grid.

Final Output

Take a look at our final output. First see how page is loaded first time:-
Now I have changed some sorting and applied filters to the grid.
Now edit some record by pressing edit link.
And press save button to get back to list page and see that our grid settings are persisted across page.

Finally its all done. And now you can see your grid in action. Hopefully this will be useful for you. Feel free to comment. Happy Coding !!!

Read/Edit Excel 2007 File Contents using EPPlus

In my previous articles related to EPPlus I have shown you that how you can generate Basic and Advance reports using EPPlus. Many people has asked me that how we can read the contents of an excel file using EPPlus. This is actually quite simple task. Here is the code to do this:-
using (ExcelPackage p = new ExcelPackage())
{
    using (FileStream stream = new FileStream("92b69c48-dda7-4544-bdcc-c6925a5f1bec.xlsx"FileMode.Open))
    {
        p.Load(stream);
 
        ExcelWorksheet ws = p.Workbook.Worksheets["Sample Sheet"];
        int rowIndex = 2;
 
        string text = ws.Cells[rowIndex, 1].Value.ToString(); //Read Text
        MessageBox.Show("Text in [" + rowIndex + ",1]=" + text);
 
        string comment = ws.Comments[0].Text; // Access Comments
        MessageBox.Show("Comments = " + comment);
 
        string pictureName = ws.Drawings[0].Name;// Access Picture
        MessageBox.Show("Picture = " + pictureName);
    }
}

Things are very simple so I am not sharing more details. You can use above code to edit your existing excel files as well.

To download code, use this link.

Feel free to comment. Happy Coding !!!

Automatically redirect user when session timout

You can create a BasePae class which inherits System.Web.UI.Page. Override the OnInit method and write appropriate code in that which will redirect to default page.
public class BasePage : System.Web.UI.Page
{
    protected override void OnInit(EventArgs e)
    {
        if (Session["UserName"] == null)
        {
            Response.Redirect("~/Login.aspx");
        }
    }
}

And inherit all your ASPX pages from BasePage class, instead of System.Web.UI.Page like this:-
public partial class HomePage : BasePage
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
}

And finally in your login page, simply set Session["UserName"] with user's name. Feel free to share your comments. Happy Coding !!!

$(document).ready(), pageLoad() and Sys.Application.add_init()

When I have basic understanding of AJAX and jQuery, I thought that $(document).ready(), pageLoad() and Sys.Application.add_init() are equivalent and they does the same job. In most of the samples they seems to behave in same way and I wondered that why there are too many ways to do the same thing. So I did bit research on this and I am sharing that with the developer community.

jQuery's $(document).ready()
This function is called when DOM is ready. Generally if browser supports DOMContentLoaded event, $(document).ready() will fire after this event. If browser do not support DOMContentLoaded, then when document achieve readyState, then $(document).ready() will be fired. If above two events are not available in Brower's event model then window.onload event is used to fire $(document).ready() event.

If you are using jQuery, then my No. 1 recommendation is this function as this will work on
all browsers. Here is a sample how you can use this function:-
$(document).ready(function () {
//Your code here
});

Application.Init
This event is fired only once when page is loaded first time. Remember, that if you are using update panel then this method will not be called whenever update panel refresh your page. So, this is best event when you want initialization which should be done only once and not after every update panel refresh.
<script type="text/javascript">
    Sys.Application.add_init(function () {
        // Your code here
    }); 
script>

ASP.NET AJAX's pageLoad() Method
As we all know java scripts runs as a single thread, pageLoad method uses a trick, it calls a setTimeout() function of JavaScript with 0. So, whenever DOM is ready and JavaScript execution is started, you  pageLoad() will be triggered.

Now, use this method when you want to call your code every time when your update panel gets refresh.
<script type="text/javascript">
    function pageLoad() {
        // Your code here 
    } 
script>
 
Note that to use AJAX methods you need to place ScriptManager object on  your page other wise they won't work.

Feel free to comment, Happy Coding !!!