/// <summary>
/// 导入Excel
/// </summary>
private bool ImportExcel(string projectNO, string ProjectName, string path)
{
DataSet ds = null;
System.Data.DataTable dt = null;
try
{
{
#region Part
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
OleDbDataAdapter myCommand = null;
string strExcel = "select * from [sheet1$]";//这里sheet1对应excel的工作表名称，一定要注意。
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
dt = ds.Tables[0];
List <Part> projects = new List <Part>();
foreach (DataRow dr in dt.Rows)
{
for (int i = 1; i < dt.Columns.Count - 1; i++)
{
if (dr[i].ToString().Trim().Length > 0 && dr[i].ToString().Trim().Length > 0)
{
Part part = new Part()
{
PartType = dr[0].ToString().Trim(), ProjectNO = projectNO, Circuit = dt.Columns[i].ToString().Trim()
};
projects.Add(part);
}
}
}
var names = projects.Select(x => x.PartType).Distinct().ToList();
foreach (var item in names)
{
if (SQliteDbContext.CheckPartNameExist(item))
{
System.Windows.MessageBox.Show($"{item}型号已经存在,请删除该零件新后再导入", "警告", MessageBoxButton.OK, MessageBoxImage.Warning);
return(false);
}
}
#endregion
#region Fixture
string sql2 = "select * from [sheet3$]";//这里sheet1对应excel的工作表名称，一定要注意。
myCommand = new OleDbDataAdapter(sql2, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
dt = ds.Tables[0];
if (dt.Columns.Count < 5)
{
System.Windows.MessageBox.Show($"Sheet3缺少列,列数必须为6列，包括第一列的空白列,请检测列信息是否完成", "警告", MessageBoxButton.OK, MessageBoxImage.Warning);
return(false);
}
bool IsChecked = true;
int index3 = 0;
dt.Rows.Cast <DataRow>().Where(x => x.ItemArray[1].ToString().Trim().Length > 0).ToList().ForEach(r =>
{
index3++;
if (r[2].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"模型编号不能为空,在sheet3中{index3+1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
int pinindex = 0;
if (!int.TryParse(r[2].ToString().Trim(), out pinindex))
{
IsChecked = false;
MessageBox.Show($"模型编号必须为数字,在sheet3中{index3 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (r[4].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"插接件编号不能为空,在sheet3中{index3 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (r[5].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"Pin编号不能为空,在sheet3中{index3 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (!int.TryParse(r[5].ToString().Trim(), out pinindex))
{
IsChecked = false;
MessageBox.Show($"Pin编号必须为数字,在sheet3中{index3 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
});
if (!IsChecked)
{
MessageBox.Show($"导入数据出错！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
var fixtures = dt.Rows.Cast <DataRow>().Where(x => x.ItemArray[1].ToString().Trim().Length > 0).Select(r => new FixtureInfo
{
ProjectNO = projectNO,
FixtureType = r[1].ToString().Trim(),
PinNO = r[2].ToString().Trim(),
FixtureNO = r[4].ToString().Trim(),
// PhysicalChannel = r[2].ToString().Trim().Length == 0 ? 0 : Convert.ToInt32(r[1].ToString()),
PinIndex = Convert.ToInt32(r[5].ToString()),
}
).ToList();
#endregion
//
#region Connect
string Sql3 = "select * from [sheet2$]";//这里sheet1对应excel的工作表名称，一定要注意。
myCommand = new OleDbDataAdapter(Sql3, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
dt = ds.Tables[0];
if (dt.Columns.Count < 7)
{
System.Windows.MessageBox.Show($"Sheet2缺少列,列数必须为7列，包括第一列的序号列,请检测列信息是否完成", "警告", MessageBoxButton.OK, MessageBoxImage.Warning);
return(false);
}
int index2 = 0;
dt.AsEnumerable().Where(x => x[0].ToString().Trim().Length > 0).ToList().ForEach(r =>
{
index2++;
if (r[1].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"Wire不能为空,在sheet2中{index2+1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
int pinindex = 0;
if (r[2].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"Option不能为空,在sheet2中{index2+1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (r[3].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"From不能为空,在sheet2中{index2 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (r[4].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"From-Pin不能为空,在sheet2中{index2 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (!int.TryParse(r[4].ToString().Trim(), out pinindex))
{
IsChecked = false;
MessageBox.Show("FromPin编号必须为数字", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (r[5].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"To不能为空,在sheet2中{index2 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (r[6].ToString().Trim().Length == 0)
{
IsChecked = false;
MessageBox.Show($"To-Pin不能为空,在sheet2中{index2 + 1}行", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (!int.TryParse(r[6].ToString().Trim(), out pinindex))
{
IsChecked = false;
MessageBox.Show("ToPin编号必须为数字", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
});
if (!IsChecked)
{
MessageBox.Show($"导入数据出错！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
var data = dt.AsEnumerable().Where(x => x[0].ToString().Trim().Length > 0).ToList().Select(x => new WireConnect()
{
Wire = x[1].ToString().Trim(),
Option = x[2].ToString().Trim(),
From = x[3].ToString().Trim(),
FromPin = x[4].ToString().Trim(),
To = x[5].ToString().Trim(),
ToPin = x[6].ToString().Trim(),
}).ToList();
List <HashSet <WireConnect> > wireConnects = new List <HashSet <WireConnect> >();
for (int i = 0; i < data.Count; i++)
{
List <WireConnect> wires = new List <WireConnect>();
if (data[i].IsDeal)
{
continue;
}
wires.AddRange(data.FindAll(x => x.Equals(data[i])));
data[i].IsDeal = true;
if (wires.Count > 0)
{
for (int j = 0; j < wires.Count; j++)
{
if (wires[j].IsDeal)
{
continue;
}
wires.AddRange(data.FindAll(x => x.Equals(wires[j])));
wires[j].IsDeal = true;
}
wires.ForEach(x => x.Num = data[i].Wire);
wireConnects.Add(new HashSet <WireConnect>(wires));
}
}
List <ConnectInfoEx> ctlst = new List <ConnectInfoEx>();
foreach (var item in wireConnects)
{
foreach (var subitem in item)
{
ctlst.Add(new ConnectInfoEx
{
ProjectNO = projectNO,
WireNum = subitem.Num.Trim(),
CirCuitName = subitem.Option.Trim(),
Pins = new List <BasePin>()
{
new BasePin()
{
PinIndex = Convert.ToInt32(subitem.FromPin.ToString().Trim()), FixtureNO = subitem.From.ToString().Trim()
},
new BasePin()
{
PinIndex = Convert.ToInt32(subitem.ToPin.ToString().Trim()), FixtureNO = subitem.To.ToString().Trim()
},
}
});
}
}
////将From to 拆分成单个的两个归属到治具里
//var ctlst = wireConnects.Select(r => new ConnectInfoEx
//{
// ProjectNO = projectNO,
// WireNum = r.Num.Trim(),
// CirCuitName=r.Option.Trim(),
// Pins=new List<BasePin>()
// {
// new BasePin() { PinIndex = Convert.ToInt32(r.FromPin.ToString().Trim()), FixtureNO = r.From.ToString().Trim()},
// new BasePin() { PinIndex = Convert.ToInt32(r.ToPin.ToString().Trim()), FixtureNO = r.To.ToString().Trim()},
// }
//});
//筛选出合并点的信息
List <ConnectionInfo> result = new List <ConnectionInfo>();
if (!rd.IsChecked.Value)
{
result = ctlst.SelectMany(p => p.Pins, (p, r) => new ConnectionInfo
{
CirCuitName = p.CirCuitName,
ProjectNO = p.ProjectNO,
WireNum = p.WireNum,
FixtureNO = r.FixtureNO,
PinIndex = r.PinIndex,
}).Where(n => !System.Text.RegularExpressions.Regex.IsMatch(n.FixtureNO, @"^[a-zA-Z]{3}")).ToList();
}
else if (rd.IsChecked.Value && rd.IsChecked.HasValue)
{
if (patter.Text.Trim().Length == 0)
{
MessageBox.Show("请输入需要匹配的合点字母", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
return(false);
}
result = ctlst.SelectMany(p => p.Pins, (p, r) => new ConnectionInfo
{
CirCuitName = p.CirCuitName,
ProjectNO = p.ProjectNO,
WireNum = p.WireNum,
FixtureNO = r.FixtureNO,
PinIndex = r.PinIndex,
}).Where(n => !n.FixtureNO.StartsWith(patter.Text.Trim())).ToList();
}
//给每个点赋与物理地址
result.ForEach(x =>
{
var rs = fixtures.ToList().Find(r => r.FixtureNO == x.FixtureNO && r.PinIndex == x.PinIndex);
if (rs != null)
{
x.PhysicalChannel = rs.PhysicalChannel;
x.FixtureType = rs.FixtureType;
x.PinNO = rs.PinNO;
}
else
{
MessageBox.Show($"导入数据出错:{x.FixtureNO}-{x.PinIndex}在表3中不存在！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
);
#region
////将From to 拆分成单个的两个归属到治具里
//var ctlst = dt.Rows.Cast<DataRow>().Where(x => x.ItemArray[1].ToString() != "").Select(r => new ConnectInfoEx
//{
// ProjectNO = projectNO,
// WireNum = r[1].ToString().Trim(),
// CirCuitName = r[2].ToString().Trim(),
// Pins = new List<BasePin>() { new BasePin() { PinIndex = Convert.ToInt32(r[4].ToString().Trim()), FixtureNO = r[3].ToString().Trim()},
// new BasePin() { PinIndex = Convert.ToInt32(r[6].ToString().Trim()), FixtureNO = r[5].ToString().Trim()},}
//}).ToList();
#endregion
#endregion
Dictionary <string, object> dict = new Dictionary <string, object>();
//实体线束（零件图号）
string sql = "Insert into ExcelPart(ProjectNO,PartType,Circuit)Values(@ProjectNO,@PartType,@Circuit)";
dict.Add(sql, projects);
int row1 = SQliteDbContext.MultiInsert <Part>(sql, projects);
if (row1 == 0)
{
MessageBox.Show($"导入数据出错,插入数据库失败！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
//治具信息
string sqlFixtureAdd = "Insert into ExcelFixturePinInfo(ProjectNO,FixtureType,FixtureNO,PinNO, PinIndex)Values(@ProjectNO,@FixtureType,@FixtureNO,@PinNO,@PinIndex)";
int row2 = SQliteDbContext.MultiInsert <FixtureInfo>(sqlFixtureAdd, fixtures);
if (row2 == 0)
{
MessageBox.Show($"导入数据出错,插入数据库失败！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
//连接点
string sqlconn = "Insert into ExcelConnect(WireNum,CirCuitName,ProjectNO,FixtureNO,FixtureType,PinIndex,PinNO)Values(@WireNum,@CirCuitName,@ProjectNO,@FixtureNO,@FixtureType,@PinIndex,@PinNO)";
int row3 = SQliteDbContext.MultiInsert <ConnectionInfo>(sqlconn, result);
if (row3 == 0)
{
MessageBox.Show($"导入数据出错,插入数据库失败！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show($"导入数据出错：{ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}