How to access Excel files via ADO.NET

The C# snippet below will dump ALL sheets of a given Excel file (c:\test.xls) onto the console. It took me a while to figure out how to access sheets within a Excel file which names I don't know - so here it is:

  1. String connectionString =
  2. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";
  3. DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
  4. try
  5. {
  6. using (OleDbConnection connection = new OleDbConnection(connectionString))
  7. {
  8. connection.Open();
  9. DataRowCollection rows = connection.GetSchema("Tables").Rows;
  10. foreach (DataRow row in rows)
  11. {
  12. string sheetName = row["TABLE_NAME"] as string;
  13. Console.WriteLine("************************************************************************");
  14. Console.WriteLine("Dumping Sheet: "+sheetName);
  15. Console.WriteLine("************************************************************************");
  16. using (DbCommand command = connection.CreateCommand())
  17. {
  18. string[] restrictions = { null, null, sheetName, null };
  19. DataRowCollection columns = connection.GetSchema("Columns", restrictions).Rows;
  20. string[] columnNames = new string[columns.Count];
  21. for (int i=0; i<columns.Count; i++)
  22. {
  23. columnNames[i] = columns[i]["COLUMN_NAME"] as string;
  24. }
  25. Console.Write("Columns in Sheet: ");
  26. foreach (string s in columnNames)
  27. {
  28. Console.Write(s);
  29. Console.Write(" ");
  30. }
  31. Console.WriteLine("");
  32.  
  33. command.CommandText = "SELECT * from [" + sheetName + "]";
  34. using (DbDataReader dr = command.ExecuteReader())
  35. {
  36. while (dr.Read())
  37. {
  38. foreach (string columnName in columnNames)
  39. {
  40. Object fieldValue = dr[columnName];
  41. Console.WriteLine("'" + columnName + "' : " + "'" + fieldValue + "'");
  42. }
  43. Console.WriteLine("------------------------------------------------------------------------");
  44. }
  45. }
  46. }
  47. }
  48. }
  49. }
  50. catch (Exception e)
  51. {
  52. Console.Write(e.StackTrace);
  53. }

To be honest - I did not expect it to be that easy. I guess I spent too much time developing Java applications.