/// <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); }