/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public bool ExcelToDataTable(string filePath, string descPath, List <CPK_ResultItem> items)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
try
{
fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheet("CPK Data");//读取第一个sheet，当然也可以循环读取每个sheet
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
rowCount = 55;
short Head1Row = 23;
short Head2Row = 23;
for (int i = 0; i < items.Count; ++i)
{
CPK_ResultItem item = items[i];
if (item.UsedNozzle == 1)
{
if (Head1Row > 55)
{
continue;
}
row = sheet.GetRow(Head1Row);
if (row == null)
{
continue;
}
cell = row.GetCell(2);
cell.SetCellValue(item.X1);
cell = row.GetCell(3);
cell.SetCellValue(item.X2);
cell = row.GetCell(4);
cell.SetCellValue(item.Y1);
cell = row.GetCell(5);
cell.SetCellValue(item.Y2);
Head1Row++;
}
else
{
if (Head2Row > 55)
{
continue;
}
row = sheet.GetRow(Head2Row);
if (row == null)
{
continue;
}
cell = row.GetCell(6);
cell.SetCellValue(item.X1);
cell = row.GetCell(7);
cell.SetCellValue(item.X2);
cell = row.GetCell(8);
cell.SetCellValue(item.Y1);
cell = row.GetCell(9);
cell.SetCellValue(item.Y2);
Head2Row++;
}
}
}
}
}
fs.Close();
FileStream file = new FileStream(descPath, FileMode.Create);
workbook.Write(file);
file.Flush();
file.Close();
return(true);
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
return(false);
}
}
/// <summary>
/// 从贴附信息导入
/// </summary>
/// <param name="excelPath">excel 路径</param>
/// <param name="posList">PosList 拍照点</param>
/// <returns>true/false</returns>
public bool ExcelReadToCpkItem(string excelPath, ref List <CPK_ResultItem> posList)
{
if (posList != null)
{
posList.Clear();
ISheet sheet = null;
FileStream fs = null;
IWorkbook workbook = null;
fs = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (excelPath.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (excelPath.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheet("PASTE");
}
else
{
return(false);
}
if (sheet == null)
{
return(false);
}
IRow row;
//计算贴附点个数
int rowIndex = 0;
short PastePointCount = 0;
while (rowIndex < 1000000000) //默认小于1000000000
{
row = sheet.GetRow(rowIndex + 152); //76
try
{
if (row.Cells[4].StringCellValue == "")
{
break;
}
}
catch
{
break;
}
rowIndex++;
PastePointCount++;
}
for (int i = 0; i < PastePointCount; i++)
{
row = sheet.GetRow(i + 152);
CPK_ResultItem item = new CPK_ResultItem();
item.Pos = new PointF(float.Parse(row.Cells[1].StringCellValue), float.Parse(row.Cells[2].StringCellValue));
item.UsedNozzle = short.Parse(row.Cells[9].StringCellValue);
posList.Add(item);
}
return(true);
}
return(false);
}
/// <summary>
/// 获得一个位置的CPK数据
/// </summary>
/// <param name="point">位置</param>
/// <param name="cpkIni">配置信息</param>
/// <param name="variable">速度</param>
/// <param name="result">CPK 数据</param>
/// <returns></returns>
public static bool GetCPK(IniFile cpkIni, Variable.VelMode variable, double gain, double offset, int cycle, ref CPK_ResultItem result)
{
if (result == null)
{
return(false);
}
// 移动到目标位
Form_Main.Instance.XYGoPosTillStop(20 * 1000, result.Pos, variable);
Thread.Sleep(100);
// 获得上相机图像
VisionImage image = Form_Main.Instance.GrabImage2View(Camera.CAM.Top);
if (image == null)
{
return(false);
}
for (int i = 0; i < cycle; ++i)
{
image = Form_Main.Instance.GainOffset(image, gain, offset);
}
// 初定位 寻找模板
try
{
// NI 视觉检测方法
List <PointContour> crossList = new List <PointContour>();
List <LineContour> lineList = new List <LineContour>();
for (int i = 0; i < 4; ++i) // 找4个角
{
PointContour cross = new PointContour();
LineContour line1 = new LineContour(); // 水平线
LineContour line2 = new LineContour(); // 垂直线
bool rtn = false;
for (int retry = 0; retry < 3; retry++)
{
rtn = FindCorss(i, cpkIni, ref cross, ref line1, ref line2);
if (rtn)
{
break;
}
}
if (!rtn)
{
return(false);
}
//crossList.Add(cross);
lineList.Add(line1);
lineList.Add(line2);
PointContour center1 = new PointContour((line1.Start.X + line1.End.X) / 2.0, (line1.Start.Y + line1.End.Y) / 2.0);
PointContour center2 = new PointContour((line2.Start.X + line2.End.X) / 2.0, (line2.Start.Y + line2.End.Y) / 2.0);
crossList.Add(center1);
crossList.Add(center2);
}
if (lineList.Count != 8)
{
return(false);
}
LineContour orgHLine = lineList[0]; // 基准水平线
LineContour orgVLine = lineList[1]; // 基准垂直线
//PointContour top = crossList[1]; // 顶点
//PointContour leftBottom = crossList[2]; // 左下角
//PointContour rightTop = crossList[3]; // 右上角
PointContour topV = crossList[3];
PointContour topH = crossList[2];
PointContour leftBottom = crossList[5];
PointContour rightTop = crossList[6];
PointContour cross1 = Algorithms.FindIntersectionPoint(orgVLine, new LineContour(topV, new PointContour(topV.X + 10, topV.Y)));
PointContour cross2 = Algorithms.FindIntersectionPoint(orgHLine, new LineContour(topH, new PointContour(topH.X, topH.Y + 10)));
PointContour cross3 = Algorithms.FindIntersectionPoint(orgVLine, new LineContour(leftBottom, new PointContour(leftBottom.X + 10, leftBottom.Y)));
PointContour cross4 = Algorithms.FindIntersectionPoint(orgHLine, new LineContour(rightTop, new PointContour(rightTop.X, rightTop.Y + 10)));
PointF xy = new PointF();
xy.X = (float)Form_Main.Instance.X.Pos;
xy.Y = (float)Form_Main.Instance.Y.Pos;
PointF tv = Form_Main.Instance.Point2CCDCenter(xy, topV, CAM.Top, 0);
PointF th = Form_Main.Instance.Point2CCDCenter(xy, topH, CAM.Top, 0);
PointF l = Form_Main.Instance.Point2CCDCenter(xy, leftBottom, CAM.Top, 0);
PointF r = Form_Main.Instance.Point2CCDCenter(xy, rightTop, CAM.Top, 0);
PointF c1 = Form_Main.Instance.Point2CCDCenter(xy, cross1, CAM.Top, 0);
PointF c2 = Form_Main.Instance.Point2CCDCenter(xy, cross2, CAM.Top, 0);
PointF c3 = Form_Main.Instance.Point2CCDCenter(xy, cross3, CAM.Top, 0);
PointF c4 = Form_Main.Instance.Point2CCDCenter(xy, cross4, CAM.Top, 0);
result.X1 = Math.Abs(tv.X - c1.X);
result.Y1 = Math.Abs(th.Y - c2.Y);
result.X2 = Math.Abs(l.X - c3.X);
result.Y2 = Math.Abs(r.Y - c4.Y);
Form_Main.Instance.imageSet.Image.Overlays.Default.AddLine(new LineContour(cross1, topV));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddLine(new LineContour(cross2, topH));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddLine(new LineContour(cross3, leftBottom));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddLine(new LineContour(cross4, rightTop));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddText("X1: " + result.X1.ToString(), topV, Rgb32Value.BlueColor, new OverlayTextOptions("Consolas", 24));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddText("Y1: " + result.Y1.ToString(), topH, Rgb32Value.BlueColor, new OverlayTextOptions("Consolas", 24));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddText("X2: " + result.X2.ToString(), leftBottom, Rgb32Value.BlueColor, new OverlayTextOptions("Consolas", 24));
Form_Main.Instance.imageSet.Image.Overlays.Default.AddText("Y2: " + result.Y2.ToString(), rightTop, Rgb32Value.BlueColor, new OverlayTextOptions("Consolas", 24));
}
catch { }
return(false);
}
