Excel与DataGridView的操作示例

源代码下载 

Excel操作的过程放在一个类里，类代码如下：

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OleDb; namespace Excel2DatagridViewTest { public class HandleExcel { private static string strExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#DATA_SOURCE#;Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; static OleDbConnection GetExcelConnection(string strExcelFile) { OleDbConnection cnn = new OleDbConnection(); cnn.ConnectionString = strExcelConnString.Replace("#DATA_SOURCE#", strExcelFile); if (cnn.State != ConnectionState.Open) cnn.Open(); return cnn; } public static string SelectExcelFile() { System.Windows.Forms.OpenFileDialog ofdSelectExcel = new System.Windows.Forms.OpenFileDialog(); ofdSelectExcel.Multiselect = false; ofdSelectExcel.CheckFileExists = true; ofdSelectExcel.CheckPathExists = true; ofdSelectExcel.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"; if (ofdSelectExcel.ShowDialog() == System.Windows.Forms.DialogResult.OK) { strExcelConnString = strExcelConnString.Replace("#DATA_SOURCE#", ofdSelectExcel.FileName); return ofdSelectExcel.FileName; } return string.Empty; } public static string[] GetExcelSheets(string strExcelFile) { DataTable dtExcelSchema=new DataTable(); List<string> lstTables = new List<string>(); OleDbConnection cnn = GetExcelConnection(strExcelFile); dtExcelSchema = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); for (int i = 0; i < dtExcelSchema.Rows.Count; i++) { lstTables.Add(dtExcelSchema.Rows[i]["Table_Name"].ToString()); } string[] strTables = new string[lstTables.Count]; lstTables.CopyTo(strTables); return strTables; } public static DataTable GetExcelData(string strExcelFile, string strSQL) { DataTable dtExcelData = new DataTable(); OleDbConnection cnn = GetExcelConnection(strExcelFile); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(strSQL, cnn); adapter.Fill(dtExcelData); return dtExcelData; } } }