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类