We help IT Professionals succeed at work.
Get Started

c# export gridview to excel with headers

SimonPrice33
SimonPrice33 asked
on
4,439 Views
Last Modified: 2014-05-04
Hi Experts

I am exporting details from a gridview to an excel sheet, but its not pulling the headers.

this is the code i am working with at the moment.

using Excel = Microsoft.Office.Interop.Excel; 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO; 

namespace csEmirOut_Winform
{
    public partial class Form1 : Form
    {

        SqlConnection sqlconn = new SqlConnection();
            string connstr = "Data Source=<servername>;Initial Catalog=Reports;Persist Security Info=True;Integrated Security=SSPI;";
            SqlCommand sqlcmd = new SqlCommand();
            DataTable dt = new DataTable();
            

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            sqlcmd.Connection = sqlconn;
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.CommandText = "<sql select statement> ";

            try
            {
                sqlconn.ConnectionString = connstr;
                sqlconn.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(sqlcmd))
                {

                    da.Fill(dt);
                    dgEmir.DataSource = dt;
                    }
                sqlconn.Close();

                

            }
            catch
            {
                Console.WriteLine("The connection to the database has not been made");
                Console.WriteLine("Please check that you have the necessary permissions and try again");
                Console.ReadKey();
            }


        }

        private void button2_Click(object sender, EventArgs e)
        {

            
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0;

            for (i = 0; i <= dgEmir.RowCount  - 1; i++)
            {
                for (j = 0; j <= dgEmir.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = dgEmir[j, i];
                    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                }
            }

            xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, 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 C:\\csharpnet-informations.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();
            }
        }
    }
}

Open in new window


Any help would be very much appreciated.

thanks

Simon
Comment
Watch Question
Senior Systems and Integration Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE