KeyLimeTie Blog

How to load an Excel spreadsheet into an ADO.NET DataSet
By Brian Pautsch – 6/7/2005 8:40:52 PM. Posted to Code Snippets.

Have you ever wondered how to load a Microsoft Excel spreadsheet into an ADO.NET DataSet? Actually, it's pretty easy...only six lines of code! Now you can let people import data into your website in batches. Here's how:

1DataSet objDS = new DataSet();
2string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
3 "Data Source=" + strFileName.Replace("\\", "\\\\") + ";" +
4 "Extended Properties=\"Excel 8.0;\"";
5objOLE = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
6objOLE.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