Export data from GridView/DataTable to Excel File using C#.NET for webpage and VB.NET window based application

How to Export GridViewData to Excel File using C#.NET ?

Step 1 : Create a function for Change Controls To Value :

private void ChangeControlsToValue(Control gridView)
    {
        Literal literal = new Literal();
       
        for (int i = 0; i < gridView.Controls.Count; i++)
        {
            if (gridView.Controls[i].GetType() == typeof(LinkButton))
            {

                literal.Text = (gridView.Controls[i] as LinkButton).Text;
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            else if (gridView.Controls[i].GetType() == typeof(DropDownList))
            {
                literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

                gridView.Controls.Remove(gridView.Controls[i]);

                gridView.Controls.AddAt(i,literal);

            }
            else if (gridView.Controls[i].GetType() == typeof(CheckBox))
            {
                literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            if (gridView.Controls[i].HasControls())
            {

                ChangeControlsToValue(gridView.Controls[i]);

            }

        }

    }

Step 2 : On Button Click to export data, Add the following Code :

protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        if (RadioButtonList1.SelectedIndex == 0)
        {
            GridView1.AllowPaging = false;
            GridView1.GridLines = GridLines.None;
            GridView1.DataBind();
        }
        else
        {
            GridView1.PagerSettings.Visible = false;
            GridView1.GridLines = GridLines.None;
            GridView1.DataBind();
        }

        ChangeControlsToValue(GridView1);
        Response.ClearContent();

        Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");

        Response.ContentType = "application/excel";

        StringWriter sWriter = new StringWriter();

        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

        HtmlForm hForm = new HtmlForm();

        GridView1.Parent.Controls.Add(hForm);

        hForm.Attributes["runat"] = "server";

        hForm.Controls.Add(GridView1);

        hForm.RenderControl(hTextWriter);
       
        // Write below code to add cell border to empty cells in Excel file
        // If we don't add this line then empty cells will be shown as blank white space

        StringBuilder sBuilder = new StringBuilder();
        sBuilder.Append(" <!--[if gte mso 9]>ExportToExcel<![endif]");
        sBuilder.Append(sWriter + "-->");

        Response.Write(sBuilder.ToString());

        Response.End();
    }

 Export data from data table to excel file in window based application using VB.NET

Private Sub exporttoexcel(ByVal dtable As DataTable)

        Dim strTempFile As String = My.Computer.FileSystem.GetTempFileName()

        Dim strLine As New Text.StringBuilder("")

        For c As Integer = 0 To dtable.Columns.Count - 1
            strLine.Append(dtable.Columns(c).ColumnName.ToString & ",")
        Next

        My.Computer.FileSystem.WriteAllText(strTempFile, strLine.ToString.TrimEnd(",") & vbCrLf, True)

        For r As Integer = 0 To dtable.Rows.Count - 1
            strLine = New Text.StringBuilder("")
            For c As Integer = 0 To dtable.Columns.Count - 1
                strLine.Append(dtable.Rows(r).Item(c).ToString & ",")
            Next

            My.Computer.FileSystem.WriteAllText(strTempFile, strLine.ToString.TrimEnd(",") & vbCrLf, True)
        Next

        Process.Start("excel", strTempFile)
    End Sub
-----------------------------------------------------------------------------------------------------------------------
Call this function on button click event to export the rows of the data table to excel file

How to save data using XML as input parameter SQL Server using C#.NET...!!

How to convert data into XML to transfer it as Parameter to save data into database using a datatable .

Step 1: create a function to parse the data using string parameter.

        private string ParseXpathString(string input)
        {
            if (input.Contains("'"))
            {
                int myindex = input.IndexOf("'");
                input = input.Insert(myindex, "'");
            }
            return input;
        }

Step 2:  Now convert the data into XML Data using the following code.

       string XMLData1;

       dt.TableName = "Table1";
          using (StringWriter sw = new StringWriter())
          {
              dt.WriteXml(sw, false);
              XMLData1 = ParseXpathString(sw.ToString());
          }

         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "";
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Connection = con;
         if (con.State == ConnectionState.Closed)
             con.Open();
         cmd.Parameters.AddWithValue("@XMLData1", XMLData1);
         cmd.ExecuteNonQuery();

Step 3: Create stored procedure to save the data in the appropriate table.

 

 CREATE PROCEDURE [dbo].[ProcedureName]

  @XMLData XML,  

  @return INT OUTPUT     

 AS
 BEGIN
 BEGIN TRANSACTION

            INSERT  INTO dbo.TableName
                    ( PId ,
                      CId ,
                      SId ,
                    )

            SELECT  CAST(colx.query('data(Col)') AS VARCHAR(20)) AS PId ,
                             CAST(colx.query('data(SizeId)') AS VARCHAR(50)) AS CId ,
                             CAST(colx.query('data(Price)') AS VARCHAR(50)) AS SId
                  INTO    #tmp
                  FROM    @XMLData.nodes('DocumentElement/Table1') AS Tabx ( Colx )

     
            IF @@ERROR <> 0
                BEGIN
                       ROLLBACK
                       SET @return=0
                END
            ELSE
                BEGIN
                       COMMIT
                       SET @return=1
                END
      END
GO

How to make the SQL Query Optimized.

Few Tips to make the SQL Query Optimized

Following are few tips to make your SQL Queries Optimized to reduce CPU usage to process it.  

• The Table should have a primary key. 
• Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned.
• Table should have minimum of one clustered index.
• Remove * from SELECT statement and use column Names instead which are only necessary in code.
• Always avoid the use of SUBSTRING function in the query.
• Remove any unnecessary joins from table.
• While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result.
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause.
• Triggers should not be used if possible, incorporate the logic of trigger in stored procedure.
• Check if there is at least 30% Hard Disc is empty – it improves the performance a bit
• Remove any adhoc queries and use Stored Procedure instead.
• When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS.
• While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works.
• Do not to use Views or replace views with original source table.