最近做项目，遇到了处理Excel的问题，要求以Excel为数据源，直接将Excel的数据在dataGridView中显示，主要是分三步进行。
第一：创建Excel文件的连接；
第二：将Excel数据放入datatable；
第三：绑定数据。
//创建Excel文件的连接，返回连接字符串
private string GetStr()
{
string connectionString = string.Empty;
OpenFileDialog f = new OpenFileDialog();
f.ShowDialog();
if (f.FileName != "")
{
if (lookUpEdit1.EditValue.ToString() == "2003")//Excel版本不一样，连接字符串也是不一样的
{
//Excel2003
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + f.FileName + ";" + "Extended Properties=Excel 8.0 ;"
}
else
{
//excel2007、2010
connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""", f.FileName);
}
}
return connectionString;
}
private void Btn_导入_Click(object sender, EventArgs e)
{
if (gridResult == null) return;
// gridResult.Init(bga.db);
gridResult.CurrentDataSet.Tables[0].Clear();//清空数据
DataTable dt_result=gridResult.CurrentDataSet.Tables[0].Clone();
OleDbConnection conn = null;
DataTable dt = new DataTable();
string connectionString = string.Empty;
string filepath = GetStr();
string strSheetName = "";
if (filepath != "" || filepath.Length > 0)
{
try
{
conn = new OleDbConnection(filepath);
conn.Open();
System.Data.DataTable dtTemp = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); strSheetName = dtTemp.Rows[0][2].ToString().Trim();//获取工作薄的name
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + strSheetName + "]", conn); //根据工作薄的名字查找对应工作薄的数据
oada.Fill(dt);
conn.Close();
gridResult.DataSource = dt;
gridResult.Refresh();
gridResult.CurrentDataSet.Tables[0] = dt;
XControl.bga_grid.gridResult = null;
this.Dispose();//释放资源
this.Close();
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message+" 请重新选择Excel版本");
return;
}
}
}