All About .Net

Saturday, January 18, 2014

Export HTML to Excel ap.net c#

aspx page code

<form id="form1" runat="server">
<div>
<div runat="server" id="ExportDiv">
<span class="SubSectionHeading">First Table:</span><table rules="all" cellspacing="0"
cellpadding="3" width="922">
<tr>
<th width="200px">
Item Name
</th>
<th width="100px">
Price
</th>
<th width="100px">
Market Price
</th>
<th width="100px">
Actual Price
</th>
<th width="100px">
Difference
</th>
<th width="200px">
Notes
</th>
</tr>
<tr>
<td>
Item 1
</td>
<td>
€78.00
</td>
<td>
€67.00
</td>
<td>
€67.00
</td>
<td>
€0.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 2
</td>
<td>
€90.00
</td>
<td>
€789.00
</td>
<td>
€90.00
</td>
<td>
-€699.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 3
</td>
<td>
€900.00
</td>
<td>
€700.00
</td>
<td>
€8.00
</td>
<td>
-€692.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 4
</td>
<td>
€789.00
</td>
<td>
€78.00
</td>
<td>
€980.00
</td>
<td>
€902.00
</td>
<td>
description of Item
</td>
</tr>
<tfoot>
<tr>
<td style="text-align: center;">
Table 1 Total
</td>
<td>
€1,857.00
</td>
<td>
€1,634.00
</td>
<td>
€1,145.00
</td>
<td>
-€489.00
</td>
<td>
</td>
</tr>
<tfoot>
</table>
</br><span class="SubSectionHeading">Second Table:</span><table rules="all" cellspacing="0"
cellpadding="3" width="922">
<tr>
<tr>
<th width="200px">
Item Name
</th>
<th width="100px">
Price
</th>
<th width="100px">
Market Price
</th>
<th width="100px">
Actual Price
</th>
<th width="100px">
Difference
</th>
<th width="200px">
Notes
</th>
</tr>
<tr>
<td>
Item 1
</td>
<td>
€45.00
</td>
<td>
€67.00
</td>
<td>
€8.00
</td>
<td>
-€59.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 2
</td>
<td>
€600.00
</td>
<td>
€899.00
</td>
<td>
€65.00
</td>
<td>
-€834.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 3
</td>
<td>
€7.00
</td>
<td>
€9.00
</td>
<td>
</td>
<td>
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 4
</td>
<td>
€78.00
</td>
<td>
€90.00
</td>
<td>
€67.00
</td>
<td>
-€23.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 5
</td>
<td>
€789.00
</td>
<td>
€980.00
</td>
<td>
€67.00
</td>
<td>
-€913.00
</td>
<td>
description of Item
</td>
</tr>
<tr>
<td>
Item 6
</td>
<td>
€6.00
</td>
<td>
€6.00
</td>
<td>
€78.00
</td>
<td>
€72.00
</td>
<td>
description of Item
</td>
</tr>
<tfoot>
<tr>
<td style="text-align: center;">
Table 2 Total
</td>
<td>
€1,525.00
</td>
<td>
€2,051.00
</td>
<td>
€285.00
</td>
<td>
-€1,757.00
</td>
<td>
</td>
</tr>
<tfoot>
</table>
</br><span class="SubSectionHeading">Total:</span><table rules="all" cellspacing="0"
cellpadding="3" class="rgMasterTableTotal" width="630">
<tr>
<th width="200px">
Table
</th>
<th width="100px">
Price
</th>
<th width="100px">
Market Price
</th>
<th width="100px">
Actual Price
</th>
<th width="100px">
Difference
</th>
</tr>
<tr>
<td style="text-align: center;">
Table 1
</td>
<td>
€1,857.00
</td>
<td>
€1,634.00
</td>
<td>
€1,145.00
</td>
<td>
-€489.00
</td>
</tr>
<tr>
<td style="text-align: center;">
Table 2
</td>
<td>
€1,525.00
</td>
<td>
€2,051.00
</td>
<td>
€285.00
</td>
<td>
-€1,757.00
</td>
</tr>
<tfoot>
<tr style="background-color: #404040; color: White;">
<td style="text-align: center;">
Grand Total
</td>
<td>
€3,382.00
</td>
<td>
€3,685.00
</td>
<td>
€1,430.00
</td>
<td>
-€2,246.00
</td>
</tr>
<tfoot>
</table>
</div>
<asp:Button runat="server" ID="ExportToExcelButton" OnClick="ExportToExcelButton_Click"
Text="Export To Excel" />
</div>
</form>

--------------------------------------------------------------
code hehind

protected void ExportToExcelButton_Click(object sender, EventArgs e)
{
Response.AppendHeader("content-disposition", "attachment;filename=ExportedHtml.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;
Response.Write(ExportDiv.InnerHtml);
Response.End();

}

Set "everyone" Permission to Folder using C# in Asp.Net

string redirectionFolder = @"D:\test\subtest\";

FileSystemAccessRule everyOne = new FileSystemAccessRule("Everyone", FileSystemRights.FullControl,AccessControlType.Allow);
DirectorySecurity dirSecurity = new DirectorySecurity(redirectionFolder, AccessControlSections.Group);
dirSecurity.AddAccessRule(everyOne);
Directory.SetAccessControl(redirectionFolder, dirSecurity);

Note:
If you get the error "Attempted to perform an unauthorized operation"

add the tag (<identity impersonate="true"/>) in your web.config file.

Window Service in Studio 2010

1) Create New Project ("WindowsService")

2) Open Service1.cs => press f4 key
you will see property of service1.cs

3) Below Property box you will see Add Installer Option => Click there

you will see ProjectInstaller.cs file in solution explorer

4) Now open ProjectInstaller.cs

you will see two option there

one is serviceProcessInstaller1

second is serviceInstaller1

5) Right click on serviceInstaller1 and select property

in property box set StartType = Manual

6) Now build your solution

7) Now , go to All programe => visual studio => visual studio tools => visual studio command prompt , Right click there Run As Administrator

8) go your Root path where your project located

now type this line C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\InstallUtil.exe or InstallUtil.exe

then type windowservice.exe

give Enter

9) Now Press Win+R key and type services.msc

Find Service Name windowservice.exe

Right click on it and Start it.

Convert datatable to html table in c#

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

public static string Datatable2HTMLtable(DataTable dt)

{

try

{

GridView dg = new GridView();

dg.AutoGenerateColumns = true;

dg.DataSource = dt;

dg.DataBind();

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

System.IO.StringWriter sw = new System.IO.StringWriter(sb);

HtmlTextWriter htw = new HtmlTextWriter(sw);

dg.RenderControl(htw);

return sb.ToString();

}

catch (Exception ex)

{

return "Datatable2HTMLtable ERROR: " + ex.Message;

}

Thursday, January 16, 2014

Iframe Seft Redirection From Code behind Asp.net

this.Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "myUniqueKey",
"self.parent.location='results.aspx';", true);

URL Redirect/Rewrite when opened from Mobile Browser

Fastest way to Import data from XML to SQL asp.net

//create dataset which will have all data
 DataSet ds1 = new DataSet("XML data1");
 
 //load schema first, this is updates schema will attch with email ds1.ReadXmlSchema(@"D:\userdocs\Ken_XML\FAC_SSIS_Identities\output.xsd");
 
 ds1.EnforceConstraints = false;
 
 //load data in dataset
 ds1.ReadXml(@"<XML file>");
 
 //connect to sql server and appropriate database see if table already present, if not present create new table after that load data from dataset to sql using bulkcopy
 try
 {
 SqlConnection conn = new SqlConnection("Server=localhost;Initial Catalog= XML_data;Trusted_Connection=True;");
 conn.Open();
 foreach (DataTable dt in ds1.Tables)
 {
 //check if table is present or not
 string exists = null;
 try
 {
 SqlCommand cmd = new SqlCommand("SELECT * FROM sysobjects where name = '" + dt.TableName + "'", conn);
 exists = cmd.ExecuteScalar().ToString();
 }catch (Exception exce)
 {
 exists = null;
 }
 // selecting each column of the datatable to create a table in the database
 Console.WriteLine("Bulk Insert Started table:" + dt.TableName);
 SqlBulkCopy bulk = new SqlBulkCopy(conn);
 bulk.DestinationTableName = "[" + dt.TableName + "]";
 foreach (DataColumn dc in dt.Columns)
 {
 bulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
 string type = "";
 //Getting right data type for column is very importatnt as it can create problem later if wrong data type is chosen
 //for mapping we are using below key value pair
 //Dictionary<string, string> typemappings = new Dictionary<string, string>();
 //typemappings.Add("Decimal", "Numeric");
 //typemappings.Add("String", "varchar(255)");
 //typemappings.Add("Int32", "Int");
 typemappings.TryGetValue(dc.DataType.FullName.Split('.')[1], out type);
 if (type == null)
 type = "varchar(255)";
 if (exists == null)
 {
 SqlCommand createtable = new SqlCommand("CREATE TABLE [" + dt.TableName + "] ([" + dc.ColumnName + "] " + type + ")", conn);
 createtable.ExecuteNonQuery();
 exists = dt.TableName;
 }
 else
 {
 try
 {
 SqlCommand addcolumn = new SqlCommand("ALTER TABLE [" + dt.TableName + "] ADD [" + dc.ColumnName + "] " + type, conn);
 addcolumn.ExecuteNonQuery();
 }catch (Exception ex2) { }
 }
 }
 //load data in sql
 bulk.WriteToServer(dt);
 Console.WriteLine("Bulk Insert completed table:" + dt.TableName);
 }
 conn.Close()
 }catch (Exception ex)
 {
 Console.WriteLine(ex.Message.ToString() + "\n" + ex.StackTrace.ToString());
 }
 finally
 {
 ds1.Clear();
 ds1.Dispose();
 }