/// <summary>
/// 选择Excel文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFileNamePath_Click(object sender, EventArgs e)
{
//openFileDialog1.FileName = "";
openFileDialog1.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
openFileDialog1.FilterIndex = 1;
openFileDialog1.RestoreDirectory = true;
//openFileDialog1.Multiselect = true;
if (openFileDialog1.ShowDialog(this) == DialogResult.OK)
{
this.txtFilePath.Text = openFileDialog1.FileName;
}
}
/// <summary>
/// 导入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImport_Click(object sender, EventArgs e)
{
string FilePath = this.txtFilePath.Text;
if (FilePath == null || FilePath == "")
{
MessageBox.Show("没有选择Excel文件,无法进行数据导入!","导入提示");
return;
}
FileType = FilePath.Split('.')[1];
ExcelToDataBase(FilePath);
}
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public void ExcelToDataBase(string FilePath)
{
SmhandOverBLL smhandOverBLL = new SmhandOverBLL();
string strConn = "";
if (FileType.Equals("xlsx"))
{
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
}
else if (FileType.Equals("xls"))
{
strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info = False;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";
}
OleDbConnection con = new OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataSet dss = new DataSet();
//string strExcel = "select * from [单头$]";
OleDbCommand com = new OleDbCommand("SELECT * FROM [单头$]", con);
OleDbDataAdapter da = new OleDbDataAdapter(com);
da.Fill(ds, "table1");
com.Dispose();
da.Dispose();
List<SmhandOverModel> smhandOverModellist = new List<SmhandOverModel>();
int dsCount = ds.Tables[0].Rows.Count;
SqlBulkCopy方法导入
//DateTime startTime = DateTime.Now;
//DataTable table = new DataTable();
//table.Columns.Add("uuId");
//table.Columns.Add("Orgcode");
//table.Columns.Add("Orgname");
//table.Columns.Add("Tagcode");
//table.Columns.Add("Tagname");
//table.Columns.Add("TagAddress");
//table.Columns.Add("contactor");
//table.Columns.Add("mobile");
//table.Columns.Add("arriveCode");
//table.Columns.Add("arriveName");
//table.Columns.Add("aggqty");
//table.Columns.Add("remark");
//table.Columns.Add("state");
//table.Columns.Add("num");
//table.Columns.Add("lastModified");
//table.Columns.Add("createDate");
//table.Columns.Add("type");
//SqlBulkCopy方法导入
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
SmhandOverModel smhandOverModel = new SmhandOverModel();
string uuidNull = ds.Tables[0].Rows[i][0].ToString();
if (uuidNull == "" || uuidNull == null)
{
//continue //退出本次循环,进行下一次的循环
//goto; //1,在多重循环时需要退出循环时,break只能退出当前循环,而使用goto可以直接跳出。2,作为一种条件,直接跳转。
break; //退出该循环
}
//SqlBulkCopy方法导入
//DataRow row = table.NewRow();
//row["uuId"] = ds.Tables[0].Rows[i][0].ToString(); //订单号
//row["Orgcode"] = ds.Tables[0].Rows[i][1].ToString(); //仓库代码
//row["Orgname"] = ds.Tables[0].Rows[i][2].ToString(); //仓库名称
//row["Tagcode"] = ds.Tables[0].Rows[i][3].ToString(); //售达方代码(供应商代码)
//row["Tagname"] = ds.Tables[0].Rows[i][4].ToString(); //售达方名称(供应商名称)
//row["TagAddress"] = ds.Tables[0].Rows[i][5].ToString(); //地址
//row["contactor"] = ds.Tables[0].Rows[i][6].ToString(); //联系人
//row["mobile"] = ds.Tables[0].Rows[i][7].ToString(); //电话
//row["arriveCode"] = ds.Tables[0].Rows[i][8].ToString(); //送达方代码
//row["arriveName"] = ds.Tables[0].Rows[i][9].ToString(); //送达方名称
//row["aggqty"] = Convert.ToInt32(ds.Tables[0].Rows[i][10].ToString()); //总出货量
//row["remark"] = ds.Tables[0].Rows[i][11].ToString(); //备注
//row["state"] = "已导入";
//row["num"] = row["uuId"].ToString();
//row["lastModified"] = System.DateTime.Now.Date;
//row["createDate"] = System.DateTime.Now.Date;
//row["type"] = 0;
//table.Rows.Add(row);
/SqlBulkCopy方法导入
smhandOverModel.uuId = ds.Tables[0].Rows[i][0].ToString(); //订单号
smhandOverModel.Orgcode = ds.Tables[0].Rows[i][1].ToString(); //仓库代码
smhandOverModel.Orgname = ds.Tables[0].Rows[i][2].ToString(); //仓库名称
smhandOverModel.Tagcode = ds.Tables[0].Rows[i][3].ToString(); //售达方代码(供应商代码)
smhandOverModel.Tagname = ds.Tables[0].Rows[i][4].ToString(); //售达方名称(供应商名称)
smhandOverModel.TagAddress = ds.Tables[0].Rows[i][5].ToString(); //地址
smhandOverModel.contactor = ds.Tables[0].Rows[i][6].ToString(); //联系人
smhandOverModel.mobile = ds.Tables[0].Rows[i][7].ToString(); //电话
smhandOverModel.arriveCode = ds.Tables[0].Rows[i][8].ToString(); //送达方代码
smhandOverModel.arriveName = ds.Tables[0].Rows[i][9].ToString(); //送达方名称
smhandOverModel.aggqty = Convert.ToInt32(ds.Tables[0].Rows[i][10].ToString()); //总出货量
smhandOverModel.remark = ds.Tables[0].Rows[i][11].ToString(); //备注
smhandOverModel.state = "已导入";
smhandOverModel.num = smhandOverModel.uuId;
smhandOverModel.lastModified = System.DateTime.Now.Date;
smhandOverModel.createDate = System.DateTime.Now.Date;
#region //查询订单明细表UUid存在不,存在则给出提示,不能导入
int i1 = smhandOverBLL.ExistSmhandOverModelByUuid(smhandOverModel.uuId);
if (i1 >= 1)
{
MessageBox.Show("提示:该订单(" + smhandOverModel.uuId + ")已存在,导入失败!", "导入提示");
return;
}
#endregion
smhandOverModellist.Add(smhandOverModel);
}
OleDbCommand coms = new OleDbCommand("SELECT * FROM [明细行$]", con);
OleDbDataAdapter das = new OleDbDataAdapter(coms);
das.Fill(dss, "table1");
coms.Dispose();
das.Dispose();
List<SmhandOverLineModel> smhandOverLineModellist = new List<SmhandOverLineModel>();
for (int i1 = 0; i1 < dss.Tables[0].Rows.Count; i1++)
{
string uuidNulls = dss.Tables[0].Rows[i1][0].ToString();
if (uuidNulls == "" || uuidNulls == null)
{
break;
}
SmhandOverLineModel smhandOverLineModel = new SmhandOverLineModel();
//smhandOverLineModel.uuId = dss.Tables[0].Rows[i1][0].ToString(); //行项目号
smhandOverLineModel.uuId = System.Guid.NewGuid().ToString(); //行项目号
smhandOverLineModel.master = dss.Tables[0].Rows[i1][1].ToString(); //订单号
smhandOverLineModel.productSKU = dss.Tables[0].Rows[i1][2].ToString(); //SKU
smhandOverLineModel.hscode = dss.Tables[0].Rows[i1][3].ToString(); //国际码
smhandOverLineModel.productCode = dss.Tables[0].Rows[i1][4].ToString(); //商品代码
smhandOverLineModel.productName = dss.Tables[0].Rows[i1][5].ToString(); //名称
smhandOverLineModel.coclor = dss.Tables[0].Rows[i1][6].ToString(); //颜色代码
smhandOverLineModel.coclorName = dss.Tables[0].Rows[i1][7].ToString(); //颜色名称
smhandOverLineModel.productsize = dss.Tables[0].Rows[i1][8].ToString(); //尺码代码
smhandOverLineModel.sizeName = dss.Tables[0].Rows[i1][9].ToString(); //尺码名称
smhandOverLineModel.qty = Convert.ToInt32(dss.Tables[0].Rows[i1][10]); //数量
smhandOverLineModel.remark = dss.Tables[0].Rows[i1][11].ToString(); //备注
smhandOverLineModel.lastModified = System.DateTime.Now.Date;
smhandOverLineModel.state = "已导入";
smhandOverLineModel.packQty = 99999;
#region 查询订单明细表UUid存在不,存在则给出提示,不能导入
int j1 = smhandOverLineBLL.ExistSmhandOverLineModelByUuid(smhandOverLineModel.uuId);
if (j1 >= 1)
{
MessageBox.Show("提示:该订单(" + smhandOverLineModel.uuId + ")已存在,导入失败!", "导入提示");
return;
}
#endregion
smhandOverLineModellist.Add(smhandOverLineModel);
}
//SqlBulkCopy方法导入
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQL2005))
//{
// bulkCopy.DestinationTableName = "SMHandOver";
// bulkCopy.ColumnMappings.Add("uuId", "uuId");
// bulkCopy.ColumnMappings.Add("Orgcode", "Orgcode");
// bulkCopy.ColumnMappings.Add("Orgname", "Orgname");
// bulkCopy.ColumnMappings.Add("Tagcode", "Tagcode");
// bulkCopy.ColumnMappings.Add("Tagname", "Tagname");
// bulkCopy.ColumnMappings.Add("TagAddress", "TagAddress");
// bulkCopy.ColumnMappings.Add("contactor", "contactor");
// bulkCopy.ColumnMappings.Add("mobile", "mobile");
// bulkCopy.ColumnMappings.Add("arriveCode", "arriveCode");
// bulkCopy.ColumnMappings.Add("arriveName", "arriveName");
// bulkCopy.ColumnMappings.Add("aggqty", "aggqty");
// bulkCopy.ColumnMappings.Add("remark", "remark");
// bulkCopy.ColumnMappings.Add("state", "state");
// bulkCopy.ColumnMappings.Add("num", "num");
// bulkCopy.ColumnMappings.Add("lastModified", "lastModified");
// bulkCopy.ColumnMappings.Add("createDate", "createDate");
// bulkCopy.ColumnMappings.Add("type","type");
// bulkCopy.WriteToServer(table);
// bulkCopy.Close();
//}
//table.Clear();
//table.Dispose();
//TimeSpan ts = DateTime.Now - startTime;
//MessageBox.Show(ts.ToString());
/SqlBulkCopy方法导入
int counts = smhandOverBLL.InsertSmhanOverModelAndSmhandOverModelLineAndImport(smhandOverModellist, smhandOverLineModellist);
if (counts >= 1)
{
MessageBox.Show("导入成功!","导入订单数据提示");
Select();
}
else
{
MessageBox.Show("导入失败!", "导入订单数据提示");
}
}
/// <summary>
/// 验证uuid是否有重复
/// </summary>
/// <param name="Uuid"></param>
/// <returns></returns>
public int ExistSmhandOverLineModelByUuid(string Uuid)
{
string sql = string.Format("select count(uuid) from SmhandOverLine where uuid = '{0}'", Uuid);
return base.Exists(sql);
}
/// <summary>
/// 导入多条数据
/// </summary>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public int InsertSmhanOverModelAndSmhandOverModelLineAndImport(List<SmhandOverModel> list1, List<SmhandOverLineModel> list2)
{
int count = 0;
using (SqlConnection connection = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
//导入订单数据到SmhandOver
foreach (SmhandOverModel model1 in list1)
{
cmd.CommandText = string.Format("insert into SmhandOver (uuId,lastModified,lastModifier,state,createDate,num,orgcode,orgname,tagcode,tagname,tagaddress,arrivecode,arrivename,contactor,mobile,aggqty,aggRealQty,type,remark) values(" + "'" + model1.uuId + "','" + model1.lastModified + "','" + model1.lastModifier + "','" + model1.state + "','" + model1.createDate + "','" + model1.num + "','" + model1.Orgcode + "','" + model1.Orgname + "','" + model1.Tagcode + "','" + model1.Tagname + "','" + model1.TagAddress + "','" + model1.arriveCode + "','" + model1.arriveName + "','" + model1.contactor + "','" + model1.mobile + "'," + model1.aggqty + "," + model1.aggRealQty + "," + 0 + ",'" + model1.remark + "'" + ")");
count += cmd.ExecuteNonQuery();
}
if (count <= 0)
{
tx.Rollback();
connection.Close();
return -1;
}
//导入订单明细数据到SmhandOverLine
foreach (SmhandOverLineModel model2 in list2)
{
cmd.CommandText = string.Format("insert into SmhandOverLine (uuId,lastModified,lastModifier,state,master,hsCode,productSKU,productCode,productName,coclor,productsize,qty,packQty,realQty,diffQty,coclorName,sizeName,remark) values(" + "'" + model2.uuId + "','" + model2.lastModified + "','" + model2.lastModifier + "','" + model2.state + "','" + model2.master + "','" + model2.hscode + "','" + model2.productSKU + "','" + model2.productCode + "','" + model2.productName + "','" + model2.coclor + "','" + model2.productsize + "'," + model2.qty + "," + model2.packQty + "," + model2.realQty + "," + model2.diffQty + ",'" + model2.coclorName + "','" + model2.sizeName + "','" + model2.remark + "'" + ")");
count += cmd.ExecuteNonQuery();
}
if (count <= 1)
{
tx.Rollback();
connection.Close();
return -1;
}
tx.Commit();
return 1;
}
catch (Exception)
{
tx.Rollback();
connection.Close();
return -1;
}
}
}
}