C# .NET - Reading Empty cells from excel sheet to DataTable

Hi,

I am reading an excel file to a datatable using oledb.

OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Price Table$] ", cnCSV);

I got data binded to the datatable.

In my excel sheet, The Dropdownlist cells were there for few columns. It was dragged to nearly 500 rows.

This is the problem: After reading excel, in my datatable i am having no. of rows were 500. But actually the excel sheet contains values only upto 10 rows.

But when i try to insert, 500 rows were created in the database table.

I should take those 10 rows only and insert into the database table.

Checking string.empty will not solve this problem, coz I am inserting some data apart from the excel too.

I couldnt able to resolve this problem. Can anybody help me in this regard.

Thanks.

hi,

try specifying the range

Excel.Sheets sheets = m_Excel.Worksheets;

Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

System.Array myvalues;

Excel.Range range = worksheet.get_Range("A1", "E1".ToString());

myvalues = (System.Array)range.Cells.Value;