C#读取Excel

获取sheet名为“ZL”的表数据

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static DataTable GetData(string path, string year, string month, string sheetname = "ZL")
        {
            path += year + month + ".xls";
            string table = "mapTable";
            sheetname = sheetname + "$";
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            System.Data.DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, path));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                string sheetName = null;
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string name = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();
                    if (name == sheetname) // 这里取到特定sheet名称的那个表
                        sheetName = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();
                }
                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

获取表栏目

 /// <summary>
        /// 根据datatable获得列名
        /// </summary>
        /// <param name="dt">表对象</param>
        /// <returns>返回结果的数据列数组</returns>
        private static string[] GetColumnsByDataTable(DataTable dt)
        {
            string[] strColumns = null;
            if (dt.Columns.Count > 0)
            {
                int columnNum = 0;
                columnNum = dt.Columns.Count;
                strColumns = new string[columnNum];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strColumns[i] = dt.Columns[i].ColumnName;
                }
            }
            return strColumns;
        }