Export a DataTable to Excel in ASPNET

http://madskristensen.net/post/Export-a-DataTable-to-Excel-in-ASPNET

SQL Bulk Copy in C#

 

Programmers usually need to transfer production data for testing or analyzing. The simplest way to copy lots of data from any resources to SQL Server is BulkCopying. .NET Framework 2.0 contains a class in ADO.NET "System.Data.SqlClient" namespace: SqlBulkCopy. The bulk copy operation usually has two separated phases.

In the first phase you get the source data. The source could be various data platforms such as Access, Excel, SQL.. You must get the source data in your code wrapping it in a DataTable, or any DataReader class which implements IDataReader. After that, in the second phase, you must connect the target SQL Database and perform the bulk copy operation.

The bulk copy operation in .Net is a very fast way to copy large amount of data somewhere to SQL Server. The reason for that is the Bulkcopy Sql Server mechanism. Inserting all data row by row, one after the other is a very time and system resources consuming. But the bulkcopy mechanism process all data at once. So the data inserting becomes very fast.



 That's all. Just a few lines and in a few seconds...

// Establishing connection
SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(); 
cb.DataSource = "SQLProduction"; 
cb.InitialCatalog = "Sales"; 
cb.IntegratedSecurity = true;
SqlConnection cnn = new SqlConnection(cb.ConnectionString);  

// Getting source data
SqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn); 
cnn.Open(); 
SqlDataReader rdr = cmd.ExecuteReader(); 

// Initializing an SqlBulkCopy object
SqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +<BR>                                  "Integrated Security=SSPI"); 

// Copying data to destination
sbc.DestinationTableName = "Temp"; 
sbc.WriteToServer(rdr); 

// Closing connection and the others
sbc.Close(); 
rdr.Close(); 
cnn.Close(); 

Learn DataGirdView

Learn DataGirdView

http://csharp.net-informations.com/datagridview/csharp-datagridview-tutorial.htm

selectionchangecommitted vs selectedindexchanged

 selectionchangecommitted vs selectedindexchanged

SelectionChangeCommitted is raised only when the user changes the combo box selection. 
Do not use SelectedIndexChanged or SelectedValueChanged to capture user changes, because those events are also raised when the selection changes programmatically.

You can create a SelectionChangeCommitted event handler to provide special handling for the ComboBox when the user changes the selected item in the list.

Function to import/upload and read Excel File in C#

Function to import/upload and read Excel File in C#

ASPX SOURCE OF PAGE

============================ 

<form id="form1" runat="server">
<div>
    
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" 
            Height="21px" Text="Upload" 
            Width="92px" onclick="btnUpload_Click"/>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</form>

===================================

===================================

C# SOURCE CODE

============================

protected void btnUpload_Click(object sender, EventArgs e)
    {
        string connectionString ="";
        if (FileUpload1.HasFile)
        {
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/App_Data/" + fileName);
            FileUpload1.SaveAs(fileLocation);
            
            //Check whether file extension is xls or xslx

            if (fileExtension == ".xls")
            {
                //connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; 

                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();

            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            //Create OleDB Connection and OleDb Command

            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            con.Close();
            GridView1.DataSource = dtExcelRecords;
            GridView1.DataBind();
        }
    } 

Export gridview or datalist data to Excel/word document in asp.net

I have one gridview that has filled with user details now I need to export gridview data to word or excel document based on selection. To implement this functionality first we need to design aspx page like this 

 

==================================================

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<table>

<tr>

<td align="right">

<asp:ImageButton ID="btnExcel" runat="server" ImageUrl="~/ExcelImage.jpg"

onclick="btnExcel_Click" />

<asp:ImageButton ID="btnWord" runat="server" ImageUrl="~/WordImage.jpg"

onclick="btnWord_Click" />

</td>

</tr>

<tr>

<td>

<asp:GridView runat="server" ID="gvdetails" DataSourceID="dsdetails"  AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false">

<RowStyle BackColor="#EFF3FB" />

<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

<Columns>

<asp:BoundField DataField="UserId" HeaderText="UserId" />

<asp:BoundField DataField="UserName" HeaderText="UserName" />

<asp:BoundField DataField="LastName" HeaderText="LastName" />

<asp:BoundField DataField="Location" HeaderText="Location" />

</Columns>

</asp:GridView>

</td>

</tr>

</table>

<asp:SqlDataSource ID="dsdetails" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>"

SelectCommand="select * from UserInformation"/>

</div>

</form>

</body>

</html>

================================== 

After that write the following code in code behind

=================================================

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

/// <summary>

/// This event is used to export gridview data to word document

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void btnWord_Click(object sender, ImageClickEventArgs e)

{

gvdetails.AllowPaging = false;

gvdetails.DataBind();

Response.ClearContent();

Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.doc"));

Response.Charset = "";

Response.ContentType = "application/ms-word";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvdetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

/// <summary>

/// This Event is used to export gridview data to Excel

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void btnExcel_Click(object sender, ImageClickEventArgs e)

{

Response.ClearContent();

Response.Buffer = true;

Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvdetails.AllowPaging = false;

//Change the Header Row back to white color

gvdetails.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Applying stlye to gridview header cells

for (int i = 0; i < gvdetails.HeaderRow.Cells.Count; i++)

{

gvdetails.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");

}

int j = 1;

//This loop is used to apply stlye to cells based on particular row

foreach (GridViewRow gvrow in gvdetails.Rows)

{

gvrow.BackColor = Color.White;

if (j <= gvdetails.Rows.Count)

{

if (j % 2 != 0)

{

for (int k = 0; k < gvrow.Cells.Count; k++)

{

gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");

}

}

}

j++;

}

gvdetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

=================
=================

other formates:  
===============================
 //---For CSV uncomment the following lines----------
      //  'Response.ContentType = "text/csv";
      //  'Response.AddHeader("content-disposition", "attachment;filename=FileName.csv");

     //   '---For TXT uncomment the following lines----------
     //   'Response.ContentType = "text/plain";
     //   'Response.AddHeader("content-disposition", "attachment;filename=FileName.txt");

    //---For PDF uncomment the following lines----------
      ///  'Response.ContentType = "application/pdf";
       // 'Response.AddHeader("content-disposition", "attachment;filename=FileName.pdf");
 

c# to vb.net converter

http://www.developerfusion.com/tools/convert/csharp-to