Dot.Net Seva: January 2015

Friday 23 January 2015

Format INR,Date inside gridview item template in asp.net

//HTML INR

<asp:Label runat="server" ID="lbl_Amount" Text='<%#Eval("Amount","{0:#,##0.00;(#,##0.00)}") %>'></asp:Label>

</ItemTemplate>

//.CS page

int Amt=122222.00

//Result

amt=1,22,222.00

--------------------------------------------------------

//HTML Date

//only for DD/MM/YYYY

<asp:Label runat="server" ID="lbl_date" Text='<%#Eval("Date","{0:dd/MM/yyyy}") %>'></asp:Label>

</ItemTemplate>

//OR

//only for hh:mm:ss

<asp:Label runat="server" ID="lbltime" Text='<%#Eval("Date","{0:hh:mm:ss}") %>'></asp:Label>

</ItemTemplate>

//.CS page

datetime date=22/05/2015 05:10:30:000

//Result

date=22/05/2015

Time=05:10:30

Thursday 15 January 2015

Export to excel from data table in asp.net using table format

//button click event

protected void btn_show_GridReport_Click(object sender, EventArgs e)

{

DataTable dt= new DataTable();

dt=//get data from database or other sources

ExportTOExcel(dt);

}

//export to excel

public void ExportTOExcel(DataTable dtExport)

{

string name = "Fxportedfilename_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xls";

HttpResponse response = HttpContext.Current.Response;

response.Clear();

response.Charset = "";

response.ContentType = "application/vnd.ms-excel";

response.AddHeader("Content-Disposition", "attachment;filename=\"" + name + "\"");

System.Text.StringBuilder sbb = new System.Text.StringBuilder();

sbb.Append("<html>");

//Header information...........................................

int Rcount = dtExport.Rows.Count;

int Ccount = dtExport.Columns.Count;

sbb.Append("<Table border= '1'>");

//write top header

sbb.Append("<tr >");

sbb.Append("<td colspan='9' align='center' valign= 'top' style='background-color:#00CC99; font-size:20px; '><b>");

sbb.Append("Report");

sbb.Append("</b></td>");

sbb.Append("</tr>");

//write Header

sbb.Append("<tr>");

for (int c = 0; c < Ccount; c++)

{

sbb.Append("<td> <b>" + dtExport.Columns[c].ColumnName + "</b> </td>");

}

sbb.Append("</tr>");

//Write Content

for (int r = 0; r < Rcount; r++)

{

sbb.Append("<tr>");

for (int c = 0; c < Ccount; c++)

{

sbb.Append("<td> " + dtExport.Rows[r][c].ToString()+ "</td>");

}

sbb.Append("</tr>");

}

sbb.Append("</tr >");

sbb.Append("</Table>");

sbb.Append("</html>");

response.Write(sbb.ToString());

response.End();

return;

}

Friday 9 January 2015

Execute Sql query using Command Prompt

//Execute

sqlcmd -S [Your Sql server name] -U [User_ID] -P [Password] -i [Full_path_of_sql_file_that_will_be_executed]
ex-

sql file name > - D:\main_20150108.sql
Server name > ABC-PC
User Id > sa
Password > System@123
Output file > D:\main_20150108.txt

//Now open Command prompt using ADMINISTRATOR account

C:\Administrator\systerm32> sqlcmd -S ABC-PC -U sa -P System@123 -i D:\main_20150108.sql

//Execute and get in text file
sqlcmd -S [Your Sql server name] -U [User_ID] -P [Password] -i [Full_path_of_sql_file_that_will_be_executed] -o [full_path_of_output_file_after_sql_executed]

C:\Administrator\systerm32> sqlcmd -S ABC-PC -U sa -P System@123 -i D:\main_20150108.sql -o D:\main_20150108.txt

Thursday 8 January 2015

Treeview Dynamically populate and remove Hyperlinks from nodes using asp.net c#

//Html

<asp:TreeView ID="TreeView1" ShowCheckBoxes="All" runat="server" NodeIndent="15" >

<NodeStyle Font-Names="Arial" Font-Size="9pt" ForeColor="Black" HorizontalPadding="1px"

NodeSpacing="0px" VerticalPadding="1px" ></NodeStyle>

</asp:TreeView>

//Remove Hyperlinks

System.Web.UI.WebControls.TreeView RemoveHyperLinks(System.Web.UI.WebControls.TreeView treeView, TreeNodeCollection treeNodes)

{

foreach (TreeNode node in treeNodes)

{

node.SelectAction = TreeNodeSelectAction.None;//here the link is removed

if (node.ChildNodes != null && node.ChildNodes.Count > 0)

{

treeView = RemoveHyperLinks(treeView, node.ChildNodes);

}

return treeView;

}

//Populate Treeview Dynamically

private void PopulateTreeView(DataTable dtParent, string parentId, TreeNode treeNode)

{

foreach (DataRow row in dtParent.Rows)

{

if (parentId == "")

{

TreeNode child = new TreeNode

{

Text = row["InterventionType"].ToString(),

Value = row["InterventionID"].ToString()

};

TreeView1.Nodes.Add(child);

DataTable dtChild = this.GetData("");

PopulateTreeView(dtChild, Convert.ToString(child.Value), child);

}

else

{

TreeNode child1 = new TreeNode

{

Text = row["ActivityName"].ToString(),

Value = row["ActivityID"].ToString()

};

treeNode.ChildNodes.Add(child1);

}

TreeView1.CollapseAll();

RemoveHyperLinks(TreeView1, TreeView1.Nodes);

}

Remove Rows and Columns from existing Excel sheet using asp.net c#

using System;

using System.Collections.Generic;

using System.Text;

using Excel1 = Microsoft.Office.Interop.Excel;

namespace Removing_rows_from_existing_XLS

{ object misValue = System.Reflection.Missing.Value;

Excel1.Application xlApp;

Excel1.Workbook xlWorkBook;

Excel1.Worksheet xlWorkSheet;

xlApp = new Excel1.Application();

xlWorkBook = xlApp.Workbooks.Add(1);

xlWorkSheet = (Excel1.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Excel1.Range ChartRange;

ChartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, First cell], (object)xlWorkSheet.Cells[5, Last cell]);

//Delete rows

xlWorkSheet.Range[Startrows cell, End rows cell]. Rows .Delete(misValue);

//or

xlWorkSheet. ChartRange .Rows .Delete(misValue);

//Delete Columns

xlWorkSheet.Range[Startrows cell, End rows cell]. Cell.Delete(misValue);

//or

xlWorkSheet. ChartRange .Cell.Delete(misValue);

}

//Result