/// <summary>
/// 从Exce表中直接导入到数据库中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void barButtonItem从Excel导入_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
killAllProcess();
tz = false;
barButtonItem查询_ItemClick(null, null);
int n = 0;
string tableName = "";
string str = "";
openFileDialog1.FileName = "配件编码表";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
str = Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
DataTable tb =GetExcelTableName(openFileDialog1.FileName) ;
checkform checkform1 = new checkform(tb, "选择Excel表名");
DialogResult result = checkform1.ShowDialog();
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1;/"");
string connectionString = "Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;";
if (result == DialogResult.OK)
{
tableName = checkform1.ExcelName.Replace("$","");
try
{
DateTime t = DateTime.Now;
con.Open();
//string sql = "select * from [" + tableName + "]";
//--------------------------------------------------------------------------------------------------
//用bcp导入数据
TimeSpan sp = new TimeSpan();
TransferData(openFileDialog1.FileName, tableName, Conn.sqlstr);
//------------------------------------------------------------------------------------
//OleDbCommand cmd = new OleDbCommand(sql, con);
//OleDbDataReader read = cmd.ExecuteReader();
do
{
//while (read.Read())
//{
// if (tz)
// {
// read.Close();
// break;
// }
//if (!DbHelperSQL.Exists("select count(*) from fittingscode where 配件编码=" + read["配件编码"]))
//{
// bandedGridView1.AddNewRow();
// DataRow row = bandedGridView1.GetDataRow(bandedGridView1.FocusedRowHandle);
// for (int i = 0; i < bandedGridView1.Columns.Count; i++)
// {
// string strs = bandedGridView1.Columns[i].Caption;
// try
// {
// row[strs] = read[strs];
// }
// catch (Exception)
// {
// }
// }
// //row["录入时间"] = DbHelperSQL.getservertime();
// //row["录入人员"] = MainForm.UserName;
// //row["录入部门"] = MainForm.UserDepartment;
// n += 1;
// Application.DoEvents();
// bandedGridView1.CloseEditor();
// bandedGridView1.UpdateCurrentRow();
// sqlbuildAdapter.Update(ds);
//}
//}
//} while (read.NextResult());
//read.Close();
//con.Close();
//bandedGridView1.Bands[0].Caption = openFileDialog1.FileName + "-" + tableName;
sp = DateTime.Now - t;
MessageBox.Show("已经成功打开Excel表" + n + "条记录;耗时" + sp.TotalSeconds + "秒", "获取数据成功");
}
catch (OleDbException)
{
con.Close();
MessageBox.Show("至少有一个参数未指定");
}
catch (Exception ex)
{
con.Close();
MessageBox.Show(ex.ToString());
}
finally
{
con.Close();
}
}
}
bandedGridView1.Bands[0].Caption = "配件编码表";
}
private void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不存在则创建
string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
}
strSql = strSql.Trim() + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
//进度显示
private void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
this.Text = e.RowsCopied.ToString();
this.Update();
}
/// <summary>
/// 获取EXCEL的表名
/// </summary>
/// <param name="p_ExcelFile">Excel文件</param>
/// <returns>数据表</returns>
private static DataTable GetExcelTableName(string p_ExcelFile)
{
try
{
if (System.IO.File.Exists(p_ExcelFile))
{
OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0/";Data Source=" + p_ExcelFile);
_ExcelConn.Open();
DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null) as DataTable;
_ExcelConn.Close();
return _Table;
}
return null;
}
catch
{
return null;
}
}
private static void killAllProcess() // 杀掉所有winword.exe进程
{
System.Diagnostics.Process[] myPs;
myPs = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process p in myPs)
{
if (p.Id != 0)
{
string myS = "EXCEL.EXE" + p.ProcessName + " ID:" + p.Id.ToString();
try
{
if (p.Modules != null)
if (p.Modules.Count > 0)
{
System.Diagnostics.ProcessModule pm = p.Modules[0];
myS += "/n Modules[0].FileName:" + pm.FileName;
myS += "/n Modules[0].ModuleName:" + pm.ModuleName;
myS += "/n Modules[0].FileVersionInfo:/n" + pm.FileVersionInfo.ToString();
if (pm.ModuleName.ToLower() == "excel.exe")
p.Kill();
}
}
catch
{ }
finally
{
}
}
}
}
private void barButtonItem停止导入_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
tz = true;
}