C# 在读取 Excel 文件时跳过第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9713048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
skip first row in read of Excel file
提问by ikathegreat
Hello I'm trying to translate an Excel file to my dataGridView and it's having column name issues because the way the Excel file is formatted, there are two setup cells for the rest of the document. However the Column names are actually on Row #2. How can I skip the first row in the file read so that the Columns in the dataGridView show the cell values from the second row?
您好，我正在尝试将 Excel 文件转换为我的 dataGridView 并且它有列名问题，因为 Excel 文件的格式设置方式，文档的其余部分有两个设置单元格。然而，列名实际上在第 2 行。如何跳过读取的文件中的第一行，以便 dataGridView 中的列显示第二行的单元格值？
Current code:
当前代码：
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 8.0;", openFileDialog1.FileName);
string query = String.Format("select * from [{0}$]", "Sheet1");
var adapter = new OleDbDataAdapter(query, connectionString);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
techGrid.DataSource = dt;
采纳答案by NET Experts
Just like Thit Lwin has commented. Remove first row before set dt as datasource.
就像 Thit Lwin 所评论的那样。在将 dt 设置为数据源之前删除第一行。
DataRow row = dt.Rows[0];
dt.Rows.Remove(row);
techGrid.DataSource = dt;
回答by Tommy
There is an easier way than programatically removing the rows, use the header row property of the connection string. This should skip the first row for you and you can then do what you do with the rest of the rows from there. From ConnectionStrings.com:
有比以编程方式删除行更简单的方法，使用连接字符串的标题行属性。这应该为您跳过第一行，然后您可以从那里处理其余行。来自ConnectionStrings.com：
Provider=Microsoft.ACE.OLEDB.12.0; Data Source=myOldExcelFile.xls;
Extended Properties="Excel 12.0;HDR=YES";
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
“HDR=是；” 表示第一行包含列名，而不是数据。“HDR=否；” 表示相反。
回答by Anon
The correct method is to tell Excel exactly where in the worksheet to find your column headers and data. Importing the entire sheet and trying to reconstruct your headers from an arbitrary data row is asking for serious trouble. OPENQUERY does not guarantee row order.In testing it will appear to always import in order, but as soon as you move it to a system with a multi-volume tempdb or a heavily loaded production system, your imports will no longer be ordered, and your code will be trying to interpret your data as column headers.
正确的方法是告诉 Excel 在工作表中的哪个位置可以找到您的列标题和数据。导入整个工作表并尝试从任意数据行重建标题会带来严重的麻烦。 OPENQUERY 不保证行顺序。在测试中，它似乎总是按顺序导入，但是一旦您将其移动到具有多卷 tempdb 的系统或负载很重的生产系统，您的导入将不再按顺序进行，您的代码将尝试解释您的数据作为列标题。
instead of:
代替：
string query = String.Format("select * from [{0}$]", "Sheet1");
use:
用：
string query = String.Format("select * from [{0}]", "Sheet1","A2:ZZ");
EDIT: use "A2:end"instead of "A2:ZZ".
编辑：使用"A2:end"而不是"A2:ZZ".
回答by Melkor S.K
You can skip as many rows you want like this
您可以像这样跳过任意多行
IEnumerable<DataRow> newRows = dt.AsEnumerable().Skip(numberOfRows);
DataTable dt2 = newRows.CopyToDataTable();
