Click here to Skip to main content
15,174,684 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I used the following code for exporting my grid view data to excel in C# . the code will work but it did not show its headers .only column content is displaying.
my code is Given bellow


C#
private void button1_Click_1(object sender, EventArgs e)
        {
            try
            {
                saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    if (!saveFileDialog1.FileName.Equals(String.Empty))
                    {
                        FileInfo f = new FileInfo(saveFileDialog1.FileName);
                        if (f.Extension.Equals(".xls"))
                        {
                            DataGridViewExport(saveFileDialog1.FileName);
                        }
                        else
                        {
                            MessageBox.Show("Invalid file type");
                        }
                    }
                    else
                    {
                        MessageBox.Show("You did pick a location to save file to");
                    }
                }
}
            catch (Exception)
            {
                MessageBox.Show(" error");
            }

         
        }
        public void DataGridViewExport(String filepath)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            Excel.Range oRange = null;
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0;
            // for header
            for (i = 0; i <= dataGridView1.Columns.Count - 1; i++)
            {
                oRange = (Excel.Range)xlWorkSheet.Cells[1, i+1];

                oRange.Value2 = dataGridView1.Columns[i].HeaderText;
            }

            //for Column content
            for (i = 0; i <= dataGridView1.RowCount -1; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j,i];
                    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                }
            }
             
            xlWorkBook.SaveAs(filepath, 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("Export SuccessFully");
        }
        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();
            }
        }
    }
}


please help me to clear my mistake

Thanks in advance
Posted
Updated 26-Nov-10 23:24pm
v2

For column content section in your code add
C#
if (i == 0)
                        {
                            xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1.Columns[j].HeaderText.ToString();
                        }


Your Updated COde Will Be..

C++
//for Column content

for (i = 0; i <= dataGridView1.RowCount -1; i++)

{
                
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)  
{
  if (i == 0)
{
xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1.Columns[j].HeaderText.ToString();//if the i is zero then read the column header text of datagrid and send to excel work sheet..
}
else{//else send the data from datagrid cell to excel

DataGridViewCell cell = dataGridView1[j,i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}           
}
   
Comments
sevenbell 27-Nov-10 5:20am
   
thanks for your replay
your code is better but i want both header names and all its content to excel sheet. please help me .
nitin bhoyate 27-Nov-10 5:22am
   
I am sending You Code to Export DataGrid View Content To Excel
private void Exportbutton_Click(object sender, EventArgs e)
        {
            SaveFileDialog SaveFile = new SaveFileDialog();
            //SaveFile.ShowDialog();
            SaveFile.InitialDirectory = @"C:\";
            SaveFile.RestoreDirectory = true;
            SaveFile.Title = "Browse Text Files";
            SaveFile.DefaultExt = ".xls";
            SaveFile.Filter = "Excel Spreadsheet(*.xls)|*.xls|All files (*.*)|*.*";
            SaveFile.FilterIndex = 2;
            SaveFile.RestoreDirectory = true;
            string filename="SpareList";
            if (SaveFile.ShowDialog() == DialogResult.OK)
            {
                filename = SaveFile.FileName;
               //PleaseWait wait = new PleaseWait("Creating Excel File");
                Thread thread = new Thread(new ThreadStart(WorkThreadFunction));
                thread.Start();
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Range oRange = null;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0;
                
                for (i = 0; i <= dataGridView1.RowCount - 1; i++)
                {
                    for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                    {
                        if (i == 0)
                        {
                            xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1.Columns[j].HeaderText.ToString();//this is code for adding header to excel sheet
                        }
                        else { //and this one for adding data to excel sheed
                        DataGridViewCell cell = dataGridView1[j, i-1];//If You does not take i-1 it will ommit first row.
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                        }
                    }
                }
                xlWorkBook.SaveAs(filename, 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);
                thread.Abort();
                MessageBox.Show("Excel file created , you can find the file" + filename);
            }
            else {
                MessageBox.Show("Enter Valid File Name");
            }
         
        }
   
v3
Comments
sevenbell 29-Nov-10 0:59am
   
THANK YOU SIR
your code is a great one i used that and i got output .sir if you dont mind please help me to convert datagridview data to word.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900