Excel C#读取类

原理使用OleDb数据源操作。

代码：

/// <summary>
    /// Excel读取类
    /// created by lihui 20100716
    /// </summary>
    public static class ExcelHelper
    {
        public static DataTable ExecuteDataTable(string filePath, string sheetName)
        {
            var t = OleDbHelper.ExecuteDataTable(BuildConnectionString(filePath), CommandType.Text,
                                                     string.Format(@"SELECT * FROM [{0}$]", sheetName));
            #region =过滤空行=
            var rowListForRemove = new List<DataRow>();
            for (var i = 0; i < t.Rows.Count; i++)
            {
                var isNull = true;
                for (var j = 0; j < t.Columns.Count; j++)
                {
                    var o = t.Rows[i][j];
                    if (o == null || o is DBNull)
                        continue;
                    var s = Convert.ToString(o);
                    if (string.IsNullOrEmpty(s.Trim()))
                        continue;
                    isNull = false;
                    break;
                }
                if (isNull)
                    rowListForRemove.Add(t.Rows[i]);
            }
            foreach (var row in rowListForRemove)
            {
                t.Rows.Remove(row);
            }
            #endregion
            return t;
        }
        private static string BuildConnectionString(string filePath)
        {
            var fi = new FileInfo(filePath);
            if (!fi.Exists)
            {
                throw new IOException(string.Format("文件[{0}]不存在！", filePath));
            }
            if (fi.Extension.ToLower().Equals(".xls"))
            {
                return string.Format(
                        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'",
                        filePath);
            }
            if (fi.Extension.ToLower().Equals(".xlsx"))
            {
                //要求安装office2007数据连接组件
                return string.Format(
                    @"Provider=Microsoft.ace.oledb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'",
                    filePath);
            }
            throw new Exception("文件类型必须是Excel!");
        }
    }

OleDbHelper类见

也谈OleDbHelper,由SqlHelper类转OleDbHelper类