如何从包含c＃中的标题行的Excel工作表中获取列数据 [英] how to get column data from Excel sheet which contains Header row in c#

问题描述

如何从包含标题行的Excel表格中获取列数据





这是我的代码

how to get column data from Excel sheet which contains Header row


this is my code

private void button2_Click(object sender, EventArgs e)
        {
            try
            {

                OpenFileDialog openfiledialog1 = new OpenFileDialog();
                openfiledialog1.ShowDialog();
                string filePath = openfiledialog1.FileName;
                string extension = Path.GetExtension(filePath);
                string header = rdbuttonyes.Checked ? "YES" : "NO";
                string conStr, sheetName;

                conStr = string.Empty;
                switch (extension)
                {

                    case ".xls": //Excel 97-03
                        conStr = string.Format(Excel03ConString, filePath, header);
                        break;

                    case ".xlsx": //Excel 07
                        conStr = string.Format(Excel07ConString, filePath, header);
                        break;
                }

                //Get the name of the First Sheet.
                using (OleDbConnection con = new OleDbConnection(conStr))
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.Connection = con;
                        con.Open();
                        DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        con.Close();
                    }
                }

                //Read Data from the First Sheet.
                using (OleDbConnection con = new OleDbConnection(conStr))
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        using (OleDbDataAdapter oda = new OleDbDataAdapter())
                        {
                            DataTable dt = new DataTable();
                            cmd.CommandText = "SELECT top 1 * From[" + sheetName + "]";



 

                       

                            cmd.Connection = con;
                            con.Open();
                            oda.SelectCommand = cmd;
                            oda.Fill(dt);
                            //dgvExcelResult.SelectedRows.ToString();
                            con.Close();
                            dgvExcelResult.AutoGenerateColumns = true;
                            //Populate DataGridView.
                         

                            dgvExcelResult.DataSource = dt;
                            dgvExcelResult.Visible = true;

                          
                        }
                    }
                }
            }
            catch
            {
               
            }
        }

i想获得列值，如ID，DESCRIPTION，来自EXCEl文件的PRODUCT ...我尝试在上面的查询中给出列标题，但是没有绑定到datagrid，因为这些列是标题行。

i want to get column values like, ID, DESCRIPTION,PRODUCT from EXCEl file...i tried giving column header in the Above Query but not binding to datagrid because these columns are header row.

推荐答案

我怀疑你是sql命令是错的：

I suspect you sql command is wrong:

cmd.CommandText = "SELECT top 1 * From[" + sheetName + "]";

应该是这样的：

It should be something like:

cmd.CommandText = string.Concat("SELECT top 1 * From [", sheetName, "

）;

");

请参阅：从.NET应用程序访问Microsoft Office数据 [ ^ ]

这篇关于如何从包含c＃中的标题行的Excel工作表中获取列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！