#region 导入
/// <summary>
/// 点击读取按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnReadFile_Click(object sender, EventArgs e)
{
//打开一个文件选择框
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
//为了获取特定的系统文件夹,
//可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举,其中可以定义要返回路径的哪个系统目录
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
ofd.Filter = "Excel文件(*.xlsx)|*xlsx";
ofd.ValidateNames = true;//文件有效性验证ValidateNames,验证用户输入是否是一个有效的windows文件名
ofd.CheckFileExists = true;//验证路径有效性
ofd.CheckPathExists = true;//验证文件有效性
string strName = this.txtPutInFile.Text;
if (strName == "")
{
MessageBox.Show("没有选择excel文件!!无法导入--!!");
return;
}
PreExitExcel();
EcxelToDataGridView(strName, this.dgvEmail);
//MessageBox.Show("导入成功!!!");
}
/// <summary>
/// excel数据导入方法
/// </summary>
/// <param name="filePath"></param>
/// <param name="dgv"></param>
public void EcxelToDataGridView(string filePath, DataGridView dgv)
{
//根据路径打开一个excel文件并将数据填充到dataset中
string strConn = @"Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filePath + "; Extended Properties = 'Excel 12.0;HDR = NO; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
strExcel = "select * from [sheet1$]";
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "table1");
//根据DataGridView的列构造一个DataTable
DataTable td = ds.Tables[0];
try
{
//MessageBox.Show(td.ToString());
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
dc.DataType = dgvc.ValueType;
//MessageBox.Show(dc.ToString());
td.Columns.Add(dc);
}
}
//根据excel的行逐一对上面的构造的datatable的列进行赋值
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
//int j = 0;
DataRow dr = td.NewRow();
DataRow excelRow = ds.Tables[0].Rows[i];
foreach (DataColumn dc in td.Columns)
{
dr[dc] = excelRow[i];
//MessageBox.Show(dr[dc].ToString());
i++;
}
td.Rows.Add(dr);
}
//在datagridview中显示导入的数据
dgv.DataSource = td;
}
catch (Exception)
{
MessageBox.Show("该表已存在你即将导入的excel文件...,请点击清空按钮重新导入...");
return;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 预关闭未退出的Excel进程方法
/// </summary>
public void PreExitExcel()
{
System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process thisprocess in allProcess)
{
string processName = thisprocess.ProcessName;
if (processName.ToLower() == "excel")
{
try
{
thisprocess.Kill();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return;
}
}
}
}
#endregion