Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
Hi all,
 
I have generated one application using asp.net with c#.
 
The concept is,
 
when i click button, the data has been display excel sheet, but in my application its getting few errors like "incorrect syntax near datadownload". using stored procedure am retrieving the data from database, please give me a solution for this app..
 
my code is:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page 
{
    string s = ConfigurationManager.AppSettings["const"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
    private DataTable Getdata(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        
        SqlConnection con = new SqlConnection(s);
        SqlDataAdapter da = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        try
        {
            con.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            da.Dispose();
            con.Dispose();
 
        }
    }
    protected void download_Click(object sender, EventArgs e)
    {
       
            SqlConnection con = new SqlConnection(s);
            SqlCommand cmd = new SqlCommand("datadownload", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Destinations", SqlDbType.VarChar);
           
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
                cmd.ExecuteNonQuery();
 
            }
            con.Close();         
        DataTable dt = Getdata(cmd);
 
        GridView gv1 = new GridView();
        gv1.AllowPaging = false;
        gv1.DataSource = dt;
        gv1.DataBind();
 
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
 
        for (int i = 0; i < gv1.Rows.Count; i++)
        {
            gv1.Rows[i].Attributes.Add("class", "textmode");
 
        }
        gv1.RenderControl(hw);
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}
Posted 15-Jan-13 0:51am
v2
Comments
Tadit Dash at 15-Jan-13 6:04am
   
What is the name of the stored procedure you are using?
Is that "datadownload"?
stellus at 15-Jan-13 6:07am
   
yes my sp name is
"datadownload"
Tadit Dash at 15-Jan-13 6:10am
   
Can you post the Stored Procedure code here and what is the connection string, is it correct?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hope this example help you....
 
You need to add this ( Microsoft.Office.Interop.Excel)reference to your solution you can find it in Add Reference-->.Net Tab

using System.ComponentModel;
private void btnGenerateExcel_Clickddd(object sender, EventArgs e)
{
// Start the BackgroundWorker.

backgroundWorker1.RunWorkerAsync();
 
 
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
 
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
 
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
 
// see the excel sheet behind the program
app.Visible = false;
 
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
 
// changing the name of active sheet
worksheet.Name = "Inspection Order Detail";
 
 

// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; // Header in Excel Sheet which u want to show
}
 
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView2.Columns.Count; j++) 
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); // in ur case you can give first three colums
 
}
} 
 
// save the application
workbook.SaveAs("C:\\Users\\xxxxxx\\Desktop\\Temp\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
// Exit from the application
app.Quit();
 
releaseObject(worksheet);
 
releaseObject(workbook);
releaseObject(app);
 
MessageBox.Show("Excel file created , you can find the file C:\\Users\\xxxxxxx\\Desktop\\Temp\\output.xlsx");
}
  Permalink  
v3
Comments
stellus at 15-Jan-13 6:10am
   
hi,
thanks for your code,
but am using stored procedure in sql database,
please refind the application above and give me a drop of solution
Ask Dj at 15-Jan-13 6:50am
   
Just add your code of binding grid as given below
 
using System.ComponentModel;
private void btnGenerateExcel_Clickddd(object sender, EventArgs e)
{
 
SqlConnection con = new SqlConnection(s);
SqlCommand cmd = new SqlCommand("datadownload", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Destinations", SqlDbType.VarChar);

if (con.State == ConnectionState.Closed)
{
con.Open();
cmd.ExecuteNonQuery();

}
con.Close();
DataTable dt = Getdata(cmd);

GridView dataGridView1= new GridView();
dataGridView1.AllowPaging = false;
dataGridView1.DataSource = dt;
dataGridView1.DataBind();
 
// Start the BackgroundWorker.
 
backgroundWorker1.RunWorkerAsync();


// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

// see the excel sheet behind the program
app.Visible = false;

// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;

// changing the name of active sheet
worksheet.Name = "Inspection Order Detail";


 
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; // Header in Excel Sheet which u want to show
}

// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView2.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); // in ur case you can give first three colums

}
}

// save the application
workbook.SaveAs("C:\\Users\\xxxxxx\\Desktop\\Temp\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Exit from the application
app.Quit();

releaseObject(worksheet);

releaseObject(workbook);
releaseObject(app);

MessageBox.Show("Excel file created , you can find the file C:\\Users\\xxxxxxx\\Desktop\\Temp\\output.xlsx");
}

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150327.1 | Last Updated 15 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100