Not exporting column name's to excel correctly from dataGridView

Question

I'm trying to export the data from dataGridView to excel. It works well, but I can't export the column name's correctly. The problem is that the column name should be: id and it is idDataGridViewTextBoxColumn . And the other problem is that the first entry from the db is not exported. I mean, instead of my first entry in the db it puts the column names, and then, it puts only the 2nd entry, then the 3rd, so on. Thanks !

private void button3_Click_1(object sender, EventArgs e)
        {
             var connString = (@"Data Source=" + Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName + @"\Angajati.sdf");
            using (var conn = new SqlCeConnection(connString))
            {
                try
                {
                    conn.Open();
                    var query = "SELECT * FROM info ";
                    var command = new SqlCeCommand(query, conn);
                    var dataAdapter = new SqlCeDataAdapter(command);
                    var dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

            }

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;

            Int16 i, j;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            for (i = 0; i <= dataGridView1.RowCount - 2; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                {
                    xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();
                }
            }

            //adds column names to excel
            string[] colNames = new string[dataGridView1.Columns.Count];
            int col = 0;

            foreach (DataGridViewColumn dc in dataGridView1.Columns)
                colNames[col++] = dc.Name;

            char lastColumn = (char)(65 + dataGridView1.Columns.Count - 1);

            xlWorkSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
            xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
            xlWorkSheet.get_Range("A1", lastColumn + "1").VerticalAlignment
                        = Excel.XlVAlign.xlVAlignCenter;

            xlWorkBook.SaveAs(@"C:\Users\Andrei\Documents\Visual Studio 2010\Projects\Stellwag\Stellwag\db.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("Salvat cu succes");
        }



        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();
            }
        }

jsanalytics · Accepted Answer · 2015-08-28 10:55:05Z

1

For the column name issue,

replace this:

foreach (DataGridViewColumn dc in dataGridView1.Columns)
    colNames[col++] = dc.Name;

with this:

foreach (DataGridViewColumn dc in dataGridView1.Columns)
    colNames[col++] = dc.HeaderText;

UPDATE: For the first missing row, make this change:

        for (i = 0; i <= dataGridView1.RowCount - 2; i++)
        {
            for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
            {
                //xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();
                xlWorkSheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
            }
        }

edited Aug 28, 2015 at 10:55

answered Aug 28, 2015 at 9:50

jsanalytics

13.2k4 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ezekiel Over a year ago

Works like charm this way. The only problem left is that it didn't get the first entry in the database, only the second,because the first is replaced with the column names. Why?

DrKoch · Accepted Answer · 2015-08-28 08:45:04Z

0

First, you have to find out, where the problem occurs:

getting the Data from the GridView
sending the data to excel

Use a debuger, set breakpoints and inspect the intermediate values like dataTable and colNames

answered Aug 28, 2015 at 8:45

DrKoch

9,7922 gold badges36 silver badges44 bronze badges

Collectives™ on Stack Overflow

Not exporting column name's to excel correctly from dataGridView

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related