private void button1_Click(object sender, EventArgs e)
{
string connString = setting;
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
sqlhelp.User_add_bom("truncate table [AppDB].[Data].[HR_COV_Lynn];");
ArrayList startsheetname = new ArrayList();
startsheetname = getExcelSheetNames(fd.FileName);
string startname = startsheetname[0].ToString();
this.label1.Visible = true;
TransferData(fd.FileName, startname, "[AppDB].[Data].[HR_COV_Lynn]", connString);
this.label1.Visible = false;
}
}
private void button2_Click(object sender, EventArgs e)
{
string connString = setting;
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
sqlhelp.User_add_bom("truncate table [AppDB].[Reference].[HR_Master_Lynn];");
ArrayList startsheetname = new ArrayList();
startsheetname = getExcelSheetNames(fd.FileName);
string startname = startsheetname[0].ToString();
this.label1.Visible = true;
TransferData(fd.FileName, startname, "[AppDB].[Reference].[HR_Master_Lynn]", connString);
this.label1.Visible = false;
}
}
public ArrayList getExcelSheetNames(string filePath)
{
ArrayList arrayNames = new ArrayList();
string strConn = getExcelOleDBConnectStr(filePath);
DataTable tb = null;
try
{
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (System.Data.DataRow drow in tb.Rows)
{
string sheetName = drow["TABLE_NAME"].ToString().Trim();
int pos = sheetName.LastIndexOf('$');
if (pos != -1)
{
arrayNames.Add(sheetName.Substring(0, pos));
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
return arrayNames;
}
private string getExcelOleDBConnectStr(string filePath)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=" + @filePath + ";" + "Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
return strConn;
}
void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
this.Text = e.RowsCopied.ToString();
this.Update();
}
string HostName = System.Environment.UserName;
public void TransferData(string excelFile, string startsheetname, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
//try
//{
//获取全部数据
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", startsheetname);
//strExcel = "select * from [{0}$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//#region 判断料号是否8位或9位
//for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
//{
// if (ds.Tables[0].Rows[i][1].ToString().Length != 8 && ds.Tables[0].Rows[i][1].ToString().Length != 9)
// {
// MessageBox.Show("Excel " + (i + 2) + " row material No. error", "Tips");
// return;
// }
//}
//#endregion
//MessageBox.Show(ds.Tables[0].Rows[0][1].ToString());
//return;
//如果目标表不存在则创建
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]);
}
#region //加入username和datetime
DateTime now = new DateTime();
now = DateTime.Now;
sqlhelp.User_add_bom("update "+ sheetName+" set [Newin_user]='" + HostName + "';");
#endregion
MessageBox.Show("Success!");
//}
//catch (Exception ex)
//{
// System.Windows.Forms.MessageBox.Show(ex.Message);
//}
}
private void ExcelTosql_Load(object sender, EventArgs e)
{
this.label1.Visible = false;
}