如何获取列名称不是从OLEDB的第一行开始 [英] How to get columns names doesnt start from first row with OLEDB

问题描述

我有这段代码，但只有当列名从第一行开始时才导入数据。我的列名从第2列到第6行开始我是如何解决这个问题的？我知道我需要一个范围，但我不知道我的代码我试试这个



我尝试了什么：

Hi ,
I have this code but only import data when the column name start in first row. My column names starts in column 2 to column row 6 How I resolved this? I know i need a range but i dont know the code for that I try this

What I have tried:

ataTable dt = new DataTable();
                conString = string.Format(conString, filePath);

                using (OleDbConnection connExcel = new OleDbConnection(conString))
                {
                    using (OleDbCommand cmdExcel = new OleDbCommand())
                    {
                        using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                        {
                            cmdExcel.Connection = connExcel;

                            //Get the name of First Sheet.
                            connExcel.Open();
                            DataTable dtExcelSchema;
                            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            
                            connExcel.Close();

                            // TRY THIS BUT NOT WORKS
                            // string Range = 
                            //cmdExcel.CommandText = "SELECT * From [" + sheetName + "$B6:C10]";
                            //cmdExcel.CommandText = "SELECT * From [" + sheetName + Range +"]";
                            connExcel.Open();
                            cmdExcel.CommandText = "SELECT * From ["+ sheetName +"]"; 
                            
                            odaExcel.SelectCommand = cmdExcel;
                            odaExcel.Fill(dt);
                            connExcel.Close();
                        }
                    }
                }

                conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name.
                        sqlBulkCopy.DestinationTableName = "dbo.Tipo_Adquisicion";

                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("CÓDIGO EMPRESA", "Tipo_Adquisicion");
                        sqlBulkCopy.ColumnMappings.Add("PERÍODO INFORMACIÓN", "descripcion");
                       // sqlBulkCopy.ColumnMappings.Add("Country", "Country");

                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }
            }

推荐答案

B6：C10];
/ /cmdExcel.CommandText =SELECT * From [+ sheetName + Range +];
connExcel.Open（）;
cmdExcel.CommandText =SELECT * From [+ sheetName +] ;

odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill（dt）;
connExcel.Close（）;
}
}
}

conString = ConfigurationManager.ConnectionStrings [Constring]。ConnectionString;
using（SqlConnection con = new SqlConnection（conString））
{
using（SqlBulkCopy sqlBulkCopy） = new SqlBulkCopy（con））
{
//设置数据库表名。
sqlBulkCopy.DestinationTableName =dbo.Tipo_Adquisicion;

// [可选]：将Excel列与数据库表的列映射
sqlBulkCopy.ColumnMappings.Add（CÓDIGORMPRESA，Tipo_Adquisicion）;
sqlBulkCopy.ColumnMappings.Add（PERÍODOINFORMACIÓN，descripcion）;
// sqlBulkCopy.ColumnMappings.Add（Country，Country）;

con.Open（）;
sqlBulkCopy.WriteToServer（dt）;
con.Close（）;
}
}
}

B6:C10]"; //cmdExcel.CommandText = "SELECT * From [" + sheetName + Range +"]"; connExcel.Open(); cmdExcel.CommandText = "SELECT * From ["+ sheetName +"]"; odaExcel.SelectCommand = cmdExcel; odaExcel.Fill(dt); connExcel.Close(); } } } conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString; using (SqlConnection con = new SqlConnection(conString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name. sqlBulkCopy.DestinationTableName = "dbo.Tipo_Adquisicion"; //[OPTIONAL]: Map the Excel columns with that of the database table sqlBulkCopy.ColumnMappings.Add("CÓDIGO EMPRESA", "Tipo_Adquisicion"); sqlBulkCopy.ColumnMappings.Add("PERÍODO INFORMACIÓN", "descripcion"); // sqlBulkCopy.ColumnMappings.Add("Country", "Country"); con.Open(); sqlBulkCopy.WriteToServer(dt); con.Close(); } } }

请阅读我对该问题的评论。您无法通过OleDb提供程序读取（加载）多行标题。



如果您想从第二行开始读取工作表中的数据，则必须在 SELECT 语句，例如：

Please, read my comment to the question. You can not read (load) multiline header through the OleDb provider.

If you would like to read data from sheet starting from second row, you have to define it in SELECT statement, for example:

SELECT * FROM [Sheet1

a2：d]

有关详细信息，请参阅： Excel连接字符串 - ConnectionStrings.com [ ^ ]



另外，你可以使用以下方法读取Excel数据：

- OpenXml sdk [ ^ ]

- EPPlus库 [ ^ ]

For further details, please see: Excel connection strings - ConnectionStrings.com[^]

Alternatively, you can read Excel data using:
- OpenXml sdk[^]
- EPPlus library[^]

这篇关于如何获取列名称不是从OLEDB的第一行开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！