public static int ImportFromXlsFile(DataGridView dataGridView, string path, string sheetName) { string sConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;IMEX=1;'", path.Replace("//", "")); using (OleDbConnection conn = new OleDbConnection(sConnectionString)) { conn.Open(); List<string> sheetNames = GetWorkSheetNames(conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)); if (!sheetNames.Contains(sheetName) && sheetNames.Count > 0) sheetName = sheetNames[0]; if (!String.IsNullOrEmpty(sheetName)) { Dictionary<string, DataGridViewColumn> columnMapping = new Dictionary<string, DataGridViewColumn>(StringComparer.OrdinalIgnoreCase); foreach (DataRow dataRow in conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, String.Format(sheetName.Contains(" ") ? "'{0}$'" : "{0}$", sheetName), null }).Rows) { columnMapping[(string)dataRow["COLUMN_NAME"]] = null; } foreach (DataGridViewColumn column in dataGridView.Columns) { if (columnMapping.ContainsKey(column.HeaderText)) columnMapping[column.HeaderText] = column; } using (OleDbCommand objCmdSelect = new OleDbCommand(String.Format("select * from [{0}$]", sheetName), conn)) { using (OleDbDataReader reader = objCmdSelect.ExecuteReader()) { dataGridView.SuspendLayout(); dataGridView.CurrentCell = null;//如果CurrentCell在最后一行会引发异常,所以置空 object[] values = new object[dataGridView.ColumnCount]; int count = 0; while (reader.Read()) { bool hasValue = false; foreach (KeyValuePair<string, DataGridViewColumn> column in columnMapping) { if (column.Value != null) { object dbValue = reader[column.Key]; if (!Equals(dbValue, DBNull.Value) && dbValue != null && !Equals(dbValue, string.Empty)) hasValue = true; ; values[column.Value.Index] = ConvertToValue(dbValue, column.Value.ValueType); } } if (hasValue) { if (dataGridView.DataSource != null) { BindingManagerBase bindingManager = dataGridView.BindingContext[dataGridView.DataSource]; bindingManager.AddNew(); dataGridView.Rows[bindingManager.Count - 1].SetValues(values); } else { dataGridView.Rows.Add(values); } count++; } } dataGridView.ResumeLayout(); return count; } } } else { throw new Exception("No sheets found"); } } } private static object ConvertToValue(object dbvalue, Type objectType) { if (dbvalue != null && dbvalue.GetType() == objectType) return dbvalue; Type underlyingType = Nullable.GetUnderlyingType(objectType); if (dbvalue is string) dbvalue = ((string)dbvalue).Trim(); if (dbvalue == null || (dbvalue is string && String.IsNullOrEmpty((string)dbvalue)) || Equals(dbvalue, DBNull.Value)) { if (underlyingType == null && objectType.IsValueType) return Activator.CreateInstance(objectType); else return null; } else return Convert.ChangeType(dbvalue, underlyingType ?? objectType); } private static List<string> GetWorkSheetNames(DataTable table) { List<string> sheetNames = new List<string>(); foreach (DataRow row in table.Rows) { string tableName = Convert.ToString(row["TABLE_NAME"]).Trim('/''); if (tableName.EndsWith("$")) { sheetNames.Add(tableName.TrimEnd('$')); } } return sheetNames; } 要求DataSource必须是IBindingList或者为空