Avatar of SimonPrice33
SimonPrice33
 asked on

c# export gridview to excel with headers

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
C#.NET ProgrammingC

Avatar of undefined
Last Comment
SimonPrice33

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Carl Tawn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SimonPrice33

ASKER
I will try that in a short while and come back to you thank you
Md Shah

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 :)
SimonPrice33

ASKER
Thank you
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23