Excel 导入数据到GridControl中的方法
private void simpleButton1_Click(object sender, EventArgs e){
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
ofd.Filter = "所有文件(*.*)|*.*|Excel2003文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx";
ofd.ValidateNames = true;
ofd.CheckFileExists = true;
ofd.CheckPathExists = true;
string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
}
if (strName == "")
{
XtraMessageBox.Show("没有选择Excel文件！无法进行数据导入");
return;
}
else
{
try
{
DataSet myDs = new DataSet();
string text = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties=Excel 8.0", strName);
string excelFirstTableName = GetExcelFirstTableName(text);
myDs.Tables.Clear();
myDs.Clear();
this.gridControl1.DataSource = null;
OleDbConnection selectConnection = new OleDbConnection(text);
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(string.Format("select * from [{0}]", excelFirstTableName), selectConnection);
oleDbDataAdapter.Fill(myDs);
this.gridControl1.DataSource = myDs.Tables;
this.gridView1.PopulateColumns();
}
catch (Exception ex)
{
XtraMessageBox.Show("从电子表格文件中装载数据异常！", ex.Message);
}
}
} /// <summary>
/// 返回Excel第一个工作表表名
/// </summary>
/// <param name="connectstring">excel连接字符串</param>
/// <returns></returns>
public static string GetExcelFirstTableName(string connectstring)
{
using (OleDbConnection connection = new OleDbConnection(connectstring))
{
string tableName = string.Empty;
if (connection.State == ConnectionState.Closed)
connection.Open();
DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt != null && dt.Rows.Count > 0)
{
tableName = ConvertTo<string>(dt.Rows);
}
return tableName;
}
}
/// <summary>
/// 将数据转换为指定类型
/// </summary>
/// <typeparam name="T">转换的目标类型</typeparam>
/// <param name="data">转换的数据</param>
public static T ConvertTo<T>(object data)
{
if (data == null || Convert.IsDBNull(data))
return default(T);
object obj = ConvertTo(data, typeof(T));
if (obj == null)
{
return default(T);
}
return (T)obj;
}
/// <summary>
/// 将数据转换为指定类型
/// </summary>
/// <param name="data">转换的数据</param>
/// <param name="targetType">转换的目标类型</param>
public static object ConvertTo(object data, Type targetType)
{
if (data == null || Convert.IsDBNull(data))
{
return null;
}
Type type2 = data.GetType();
if (targetType == type2)
{
return data;
}
if (((targetType == typeof(Guid)) || (targetType == typeof(Guid?))) && (type2 == typeof(string)))
{
if (string.IsNullOrEmpty(data.ToString()))
{
return null;
}
return new Guid(data.ToString());
}
if (targetType.IsEnum)
{
try
{
return Enum.Parse(targetType, data.ToString(), true);
}
catch
{
return Enum.ToObject(targetType, data);
}
}
if (targetType.IsGenericType)
{
targetType = targetType.GetGenericArguments();
}
return Convert.ChangeType(data, targetType);
}
gridcontrol 可以直接导入 excel ？ 这个得学习一下
页:
[1]