How to load an Excel spreadsheet into an ADO.NET DataSet

You can do this using following code:

DataSet objDS = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFileName.Replace("\\", "\\\\") + ";" +
"Extended Properties=\"Excel 8.0;\"";
objOLE = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
objOLE.Fill(objDS);


The above code snippet works great, but you must know the name of the worksheet (Sheet1 is the default for Excel). But what if you don't know the Worksheet name? What if you just want the first sheet? Then, you must use the Excel objects to get it:


1#region AnalyzeSpreadsheet
2public void AnalyzeSpreadsheet(string strFileName)
3{
4 //Excel variables
5 object con_true = true;
6 Excel.ApplicationClass objExcel = null;
7 Excel.Workbook objBook = null;
8 Excel.Worksheet objSheet = null;
9 try
10 {
11 //Create new instance of Excel Application
12 objExcel = new Excel.ApplicationClass();
13 //Set some options
14 objExcel.DisplayAlerts = false;
15 objExcel.ScreenUpdating = false;
16 objExcel.Visible = false;
17 objExcel.UserControl = false;
18 //Open spreadsheet
19 objBook = objExcel.Workbooks.Open(strFileName, Type.Missing,
20 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
21 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
22 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
23 //Find the 1st worksheet
24 objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1);
25 if (objSheet == null)
26 throw new Exception("Worksheet #1 not found!");
27 else
28 {
29 //Do something...
30 }
31 }
32 catch
33 {
34 //Handle exception
35 }
36 finally
37 {
38 ReleaseComObject(objSheet);
39 objSheet = null;
40 objBook.Close(con_true, strFileName, null);
41 ReleaseComObject(objBook);
42 objBook = null;
43 objExcel.Workbooks.Close();
44 objExcel.Application.Quit();
45 ReleaseComObject(objExcel);
46 objExcel = null;
47 }
48}
49#endregion
50#region ReleaseComObject
51private void ReleaseComObject(object o)
52{
53 Int32 i = 0;
54 Int32 j = 0;
55 try
56 {
57 for (i = 1; i <= System.Runtime.InteropServices.Marshal.ReleaseComObject(o); i++)
58 {
59 j = System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
60 }
61 }
62 catch
63 {
64 }
65 finally
66 {
67 o = null;
68 }
69}
70#endregion