没有从dataGridView正确导出列名's到excel

本文关键字：excel dataGridView | 更新日期: 2023-09-27 18:10:16

我试图从dataGridView导出数据到excel。它工作得很好，但是我不能正确导出列名。问题是列名应该是:id，而它是idDataGridViewTextBoxColumn。另一个问题是没有导出数据库中的第一个条目。我的意思是，不是我在数据库中的第一个条目，而是列名称，然后，它只放了第二个条目，然后是第三个，以此类推。谢谢! 

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

没有从dataGridView正确导出列名's到excel

对于列名问题，

替换:

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

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

UPDATE:对于第一个缺失的行，做这样的修改:

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

首先，你必须找出问题发生在哪里:

  1. 从GridView获取数据
  2. 发送数据到excel

使用调试器，设置断点并检查中间值，如dataTable和colNames

相关文章：