Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SimonPrice33
SimonPrice33

ASKER

I will try that in a short while and come back to you thank you
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 :)
Thank you