Geek asks and answers

Dynamically analyze an Excel worksheet (the range of cells is not specified, since the location of the data on the worksheet can change dynamically)

I am using C # .NET 3.5 in VS2010 on a Win 7 machine. I have an Excel worksheet and I want to extract the data stored in it. I know how to parse Excel when specifying a range of cells. Here we connect using OLEDB, give the SQL command and map it to DataTable, and then access the data.

select * from [sheetname$A2:N50]

where "sheet name" is the name of the Excel sheet, and "$ A2: N50" is the range of cells.

BUT, BUT, my requirement is completely different.

I cannot hard-set the range of cells as indicated above, since the location of the data cells can change dynamically. For example: the data stored in cell A20 can be changed to C14 in the very next version.

I need to parse my Excel sheet based on a keyword search. I mean, I have to search for the keyword “XYZ” and then parse the table below it. This keyword can change its position for each performance.

Since I don’t know the range of cells, I can’t even get Excel data in DataTableusing the above query.

+3
source share
1 answer

Instead of selecting from a range of cells, you can fill in all the data as data and request it.

DataTable dt = new DataTable();
try
{
   OleDbConnection con = new OleDbConnection(string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=yes;IMEX=1""", excelPath));
   OleDbDataAdapter da = new OleDbDataAdapter("select * from [sheetname$]", con);

   da.Fill(dt);
}
catch (Exception ex)
{
   MessageBox.Show(ex.Message);
   return;
}
//now you can use dt DataTable
foreach (DataRow dr in dt.Rows)
{
  //....
}

hope this helps ...

+2
source

More articles:


All Articles