如何从Excel中读取数据时算空行 [英] How to count empty rows when reading from Excel

问题描述

我使用OLEDB连接并通过数据从Excel电子表格读取。我有IMEX =1，一切工作正常。我的问题是我在读从可能有几个空行开始，空行数是很重要的床单。例如，如果我读一个5x5的网格，如：

I'm using OLEDB to connect and read through data from an Excel spreadsheet. I have IMEX="1" and everything works ok. My problem is the sheets I'm reading from may start with several empty rows and the number of empty rows is important. For example, if I was reading a 5x5 grid like:

- - - - -
- - - - -
2 - 3 3 8
- - - - -
- - 5 2 2

其中 - 代表一个空单元格。这前两行是空的这一事实是很重要的。网格的大小是动态的。我的代码似乎忽略了第一个空行。但随着空行涉及第4行确定。

where '-' represents an empty cell. The fact that the first two rows are empty is important. The size of the grid is dynamic. My code appears to be ignoring the first empty rows. But deals with the empty row at line 4 ok.

我如何使用OLEDB算空行数在Excel工作表的开始？

How can I count the number of empty rows at the start of an Excel sheet using OLEDB?

我只能使用OLEDB，我也不会，如果我没得; - ）

I'm restricted to using OLEDB, I wouldn't if I didn't have to ;-)

using (var adapter = new OleDbDataAdapter("SELECT * FROM [" + worksheetName + "]", connString)) {
  var ds = new DataSet();
  adapter.Fill(ds, "FareChart");
  table = ds.Tables["FareChart"];
}

连接字符串：

Connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Windows\\TEMP\\e1842f90-74a7-42f2-a6fa-208396a1072e;Extended Properties=\"Excel 8.0;IMEX=1;HDR=No\""

更新

指定'的.xls为固定这个问题在连接字符串中的文件扩展名，并正确地开始读取空行。

Specifying '.xls' as the file extension in the connection string fixed this issue and correctly reads the empty rows at the start.

推荐答案

我觉得你的问题是在你的连接字符串。我测试了下面的代码和它的工作对我来说：

I think your problem is with your connection string. I tested the below code and it worked for me:

     DataSet Contents = new DataSet();
     using (OleDbDataAdapter adapter = new OleDbDataAdapter("select FirstName,LastName,Email,Mobile from [" + mySheet + "]", connection))
     {
         adapter.Fill(Contents,"MyTable");
     }

     foreach (DataRow content in Contents.Tables["MyTable"].Rows)
     {
         if (content[0].ToString() == "" && content[0].ToString() == "" && content[0].ToString() == "" && content[0].ToString() == "")
         {
             Console.WriteLine("Empty Row");
         }
         else
         {
             Console.WriteLine(content[0] + " | " + content[1] + " | " + content[2] + " | " + content[3]);
         }
     }

我的连接字符串是：

My Connection String is:

    string cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Untitled 1.xls\";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

这篇关于如何从Excel中读取数据时算空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！