c# export gridview to excel with headers

SimonPrice33
SimonPrice33 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Systems and Integration Developer
Commented:
Just before your existing loop you just need to loop through each column and pick off the HeaderText value:
for (j = 0; j < dgEmir.Columns.Count; ++j)
    xlWorkSheet.Cells[1, j + 1] = dgEmir.Columns[j].HeaderText;

Open in new window

Don't forget to then offset your row loop by 1, otherwise you will overwrite the header row.

Author

Commented:
I will try that in a short while and come back to you thank you
Top Expert 2014

Commented:
I have extracted few methods from my live project..

Have a look at it, if you are interested in other ways of exporting GridView data to Excel/CSV file..

Download Sample Project

Good Luck :)

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial