1public class ExcelToDatableHelper
2    {
3        private static ExcelToDatableHelper instance = null;
4        private static Object locker = new Object();
5
6        public static ExcelToDatableHelper Instance
7        {
8            get
9            {
10                if (instance == null)
11                {
12                    lock (locker)
13                    {
14                        if (instance == null)
15                        {
16                            instance = new ExcelToDatableHelper();
17                        }
18                    }
19                }
20                return instance;
21            }
22        }
23
24        /**//// <summary>
25        /// Reads the excel.
26        /// </summary>
27        /// <param name="strFileName">Name of the file.</param>
28        /// <param name="sheetName">Name of the sheet.</param>
29        /// <param name="colName">Name of the col.</param>
30        /// <returns></returns>
31        public DataSet ReadExcel(string strFileName, string sheetName, List<string> colName)
32        {
33            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strFileName + ";Extended Properties = Excel 8.0";
34            OleDbConnection oleConnection = new OleDbConnection(strConnection);
35            StringBuilder strColName = new StringBuilder("");
36            string myStrColName = "";
37            if (colName.Count > 0)
38            {
39                foreach (string str in colName)
40                {
41                    strColName.Append(str);
42                    strColName.Append(",");
43                }
44                myStrColName = strColName.ToString().Substring(0, strColName.Length - 1);  // 去掉","
45            }
46            try
47            {
48                oleConnection.Open();
49                DataSet dsRead = new DataSet();
50                OleDbDataAdapter oleAdper = new OleDbDataAdapter(" SELECT " + myStrColName + "  FROM [" + sheetName + "$]", oleConnection);
51                oleAdper.Fill(dsRead, "result");
52                return dsRead;
53            }
54            catch (Exception ex)
55            {
56                MessageBox.Show(ex.ToString());
57                return null;
58            }
59            finally
60            {
61                oleConnection.Close();
62            }
63        }
64
65        /**//// <summary>
66        /// Opens the file.
67        /// </summary>
68        /// <returns></returns>
69        public string OpenFile()
70        {
71            OpenFileDialog openFileDialog = new OpenFileDialog();
72            openFileDialog.InitialDirectory = Application.StartupPath;
73            //openFileDialog.Filter="文本文件|*.*|Excel文件|*.xls|C#文件|*.cs|所有文件|*.*"; 
74            openFileDialog.Filter = "Excel文件|*.xls";
75            openFileDialog.RestoreDirectory = true;
76            openFileDialog.Title = "打开文件";
77            openFileDialog.FilterIndex = 1;
78            if (openFileDialog.ShowDialog() == DialogResult.OK)
79            {
80                return (openFileDialog.FileName);
81            }
82            else return String.Empty;
83        }
84    }