how can i export the data from gridview to excel in windows forms?

Posted by Sudheep.grandhe under C# on 12/21/2011 | Points: 10 | Views : 1896 | Status : [Member] | Replies : 2
Hi,

here is my requirment.in excel sheet i want to display the column names.how can i do this.please do the needful.here i have to display tbl_report1 columns in gridview also

public partial class ReportPage : Form
{
public ReportPage()
{
InitializeComponent();
}
public void reportsgridview()
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand("select compname,compaddress,InvoiceNo,OrderNo, convert(varchar(15),ondate,105) as [OnDate],convert(varchar(15),invoicedate,105) as [InvoiceDate] ,NetAmount,NetVat,TAmount from tbl_report2", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
da.Fill(dt);
if (con.State == ConnectionState.Open)
{
con.Close();
}
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;

}
else
{

}
}

public void getorderno()
{
SqlConnection con = new SqlConnection("data source=mars-104;intial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand("select orderno from tbl_report2 where invoiceno=@invoiceno", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
con.Open();
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
}




private void btn_ETexcel_Click(object sender, EventArgs e)
{
Excel.Excel.Application XlApp;
Excel.Excel.Workbook XlWorkBook;
Excel.Excel.Worksheet XlWorkSheet;
object misValue = System.Reflection.Missing.Value;

XlApp = new Excel.Excel.ApplicationClass();
XlWorkBook = XlApp.Workbooks.Add(misValue);
XlWorkSheet = (Excel.Excel.Worksheet)XlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= GridView1.RowCount - 1; i++)
{
for (j = 0; j <= GridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = GridView1[j, i];
XlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
XlWorkBook.SaveAs("reports.xls", Excel.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
XlWorkBook.Close(true, misValue, misValue);
XlApp.Quit();

releaseObject(XlWorkSheet);
releaseObject(XlWorkBook);
releaseObject(XlApp);

MessageBox.Show("Excel file created,you can find the file f:\\reports.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("exception occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}

}

Best,
Sudheep.



Responses

Posted by: Anweshabhowmick on: 12/21/2011 [Member] Starter | Points: 25

Up
0
Down

Call This function to Button click.......

here gvstocksummary will be your Gridview Id

public void ExportToExcel()
{
Response.Clear();
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";
Response.AddHeader("content-disposition", "attachment;filename=StockDetails.xls");
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
HtmlForm frm = new HtmlForm();
this.gvstocksummary.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(this.gvstocksummary);
frm.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

}

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.grandhe on: 12/21/2011 [Member] Starter | Points: 25

Up
0
Down
hi,

thanks for replying to me.But,i am developing windows forms.

Best,
Sudheep.

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response