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.