package mes.basic;
|
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.openqa.selenium.*;
|
import org.openqa.selenium.chrome.ChromeDriver;
|
import org.openqa.selenium.interactions.Actions;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.concurrent.TimeUnit;
|
|
public class BomImportTest {
|
private final static String DATE_FORMAT = "yyyy/MM/dd";
|
private final static String URL = "http://192.168.7.48:8100"; //系统地址
|
private final static String USER_NAME = "admin"; //用户名
|
private final static String PASSWORD = "I&^J^zYq"; //用户密码
|
private final static String FILE_PATH = "D:/WDZCN-RYY23 BOM-2021.12.21.xlsx"; //文件
|
private final static int SLEEP_MILLIS = 1000; //等待时长
|
|
public static void main(String[] args) throws Exception {
|
WebDriver driver = login(URL, USER_NAME, PASSWORD);
|
//读取excel文件
|
File file = new File(FILE_PATH);
|
//获得Workbook工作薄对象
|
Workbook workbook = getWorkBook(file);
|
try {
|
if (workbook != null) {
|
bomImport(driver, workbook);
|
}
|
driver.quit();
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
driver.quit();
|
OutputStream outputStream = new FileOutputStream(FILE_PATH);
|
workbook.write(outputStream);
|
workbook.close();
|
}
|
}
|
|
public static WebDriver login(String url, String username, String password) {
|
System.setProperty("webdriver.chrome.driver", "D://chromedriver_win32//chromedriver.exe");//谷歌
|
WebDriver driver = new ChromeDriver();
|
|
driver.manage().window().maximize();
|
driver.manage().deleteAllCookies();
|
// 与浏览器同步非常重要，必须等待浏览器加载完毕
|
driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
|
|
//打开目标地址
|
driver.get(url);
|
|
//登录
|
driver.findElement(By.cssSelector(".el-form-item:nth-child(1) .el-input__inner")).sendKeys(username);
|
driver.findElement(By.cssSelector(".el-input--suffix > .el-input__inner")).sendKeys(password);
|
driver.findElement(By.cssSelector(".el-form-item:nth-child(1) .el-input__inner")).click();
|
driver.findElement(By.cssSelector(".login-new-weaper")).click();
|
driver.findElement(By.cssSelector(".el-input--suffix > .el-input__inner")).click();
|
{
|
WebElement element = driver.findElement(By.cssSelector(".login-submit"));
|
Actions builder = new Actions(driver);
|
builder.moveToElement(element).perform();
|
}
|
driver.findElement(By.cssSelector(".login-submit")).click();
|
|
return driver;
|
}
|
|
public static void bomImport(WebDriver driver, Workbook workbook) throws Exception {
|
//打开产品结构页面
|
driver.findElement(By.xpath("//li[3]/div")).click();
|
driver.findElement(By.xpath("//a[contains(.,'产品结构')]")).click();
|
|
//获取工作簿数量
|
int sheetNum = workbook.getNumberOfSheets();
|
System.out.println("sheet num:" + sheetNum);
|
//读取工作簿开始和结束的位置
|
for (int i = 0; i < sheetNum; i++) {
|
Sheet sheet = workbook.getSheetAt(i);
|
//显示工作簿名称
|
System.out.println("sheet name:" + sheet.getSheetName());
|
|
int lastRowNum = sheet.getLastRowNum();
|
System.out.println("row num:" + lastRowNum);
|
|
Row tile = sheet.getRow(0);
|
int lastCellNum = tile.getLastCellNum();
|
|
//循环所有行
|
for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
|
//获得当前行
|
Row row = sheet.getRow(rowNum);
|
if (row == null) {
|
continue;
|
}
|
|
//本行已经导入过就跳过
|
if (row.getCell(lastCellNum) != null) {
|
// System.out.println("row line:" + rowNum + "\t" + row.getCell(lastCellNum).toString());
|
// if (!row.getCell(lastCellNum).toString().equals(""))
|
continue;
|
}
|
|
//获取零件号
|
String partNo = getCellValue(row.getCell(0)).trim();
|
|
System.out.println("row line:" + rowNum + "\t" + partNo);
|
|
String partNoxpath = "//input[@type='text']";
|
String trElement = "#app > div > div.avue-layout > div.avue-main > div > div.el-scrollbar__wrap > div > div >" +
|
" div > div > div > div > div.common-table-div > div > div.el-table__body-wrapper.is-scrolling-none > table > tbody > tr";
|
|
if (sheet.getSheetName().equals("BOM")) {
|
driver.findElement(By.xpath("//a[contains(.,'BOM')]")).click();
|
partNoxpath = "(//input[@type='text'])[3]";
|
trElement = "#app > div > div.avue-layout > div.avue-main > div > div.el-scrollbar__wrap > div > " +
|
"div > div > div > div > div > div.common-table-div > div > div.el-table__body-wrapper" +
|
".is-scrolling-none > table > tbody > tr";
|
}
|
|
//判断该零件是否已经存在
|
// driver.findElement(By.xpath(partNoxpath)).clear();
|
// driver.findElement(By.xpath(partNoxpath)).sendKeys(partNo);
|
// driver.findElement(By.xpath(partNoxpath)).sendKeys(Keys.ENTER);
|
// Thread.sleep(SLEEP_MILLIS);
|
|
// if (doesWebElementExist(driver, By.cssSelector(trElement))) {
|
// row.createCell(lastCellNum).setCellValue("exists");
|
// } else {
|
//先创建单层结构数据，最后构建BOM
|
if (!sheet.getSheetName().equals("BOM")) {
|
// 填写产品结构数据
|
fillBomHead(driver, row);
|
fillBomBody(driver, tile, row);
|
} else {
|
//构建BOM
|
buildBom(driver, row);
|
}
|
row.createCell(lastCellNum).setCellValue("ok");
|
// }
|
}
|
}
|
}
|
|
public static void buildBom(WebDriver driver, Row row) throws Exception {
|
//获取零件号
|
String partNo = getCellValue(row.getCell(0)).trim();
|
|
//点击新增按钮
|
driver.findElement(By.xpath("//button[contains(.,'新增')]")).click();
|
|
//设置零件号
|
driver.findElement(By.xpath("(//button[@type='button'])[4]")).click();
|
driver.findElement(By.xpath("//*[@id=\"part-dialog\"]/div/div[2]/div/div[3]/div/div[2]/table/thead/tr[2]/th[2]/div/div/div/input")).sendKeys(partNo);
|
driver.findElement(By.xpath("//*[@id=\"part-dialog\"]/div/div[2]/div/div[3]/div/div[2]/table/thead/tr[2]/th[2]/div/div/div/input")).sendKeys(Keys.ENTER);
|
Thread.sleep(SLEEP_MILLIS);
|
driver.findElement(By.cssSelector(".el-table__row:nth-child(1) .el-checkbox__inner")).click();
|
driver.findElement(By.xpath("//div/div[3]/div/button[2]")).click();
|
|
//设置版本号
|
String value = getCellValue(row.getCell(2)).trim();
|
if (!value.equals("")) {
|
driver.findElement(By.xpath("(//input[@type='text'])[3]")).sendKeys(value);
|
}
|
|
//设置绝缘颜色
|
value = getCellValue(row.getCell(3)).trim();
|
if (!value.equals("")) {
|
driver.findElement(By.xpath("(//input[@type='text'])[4]")).sendKeys(value);
|
}
|
//设置护套颜色
|
value = getCellValue(row.getCell(4)).trim();
|
if (!value.equals("")) {
|
driver.findElement(By.xpath("(//input[@type='text'])[5]")).sendKeys(value);
|
}
|
|
//设置特性1
|
value = getCellValue(row.getCell(5)).trim();
|
if (!value.equals("")) {
|
driver.findElement(By.xpath("(//input[@type='text'])[6]")).sendKeys(value);
|
}
|
|
//勾选BOM
|
driver.findElement(By.cssSelector(".expanded .el-checkbox__inner")).click();
|
|
//点击保存
|
driver.findElement(By.xpath("(//button[@type='button'])[3]")).click();
|
|
//返回
|
driver.findElement(By.cssSelector(".icon-btn-back")).click();
|
|
Thread.sleep(SLEEP_MILLIS);
|
}
|
|
public static void fillBomHead(WebDriver driver, Row row) throws Exception {
|
//获取零件号
|
String partNo = getCellValue(row.getCell(0)).trim();
|
|
//点击新增按钮
|
driver.findElement(By.xpath("//button[contains(.,'新增')]")).click();
|
|
//设置零件号
|
driver.findElement(By.xpath("(//button[@type='button'])[4]")).click();
|
driver.findElement(By.xpath("(//input[@type='text'])[4]")).sendKeys(partNo);
|
driver.findElement(By.xpath("(//input[@type='text'])[4]")).sendKeys(Keys.ENTER);
|
Thread.sleep(SLEEP_MILLIS);
|
driver.findElement(By.cssSelector(".el-table__row:nth-child(1) .el-checkbox__inner")).click();
|
driver.findElement(By.xpath("//div/div[3]/div/button[2]")).click();
|
|
//获取版本号
|
String version = getCellValue(row.getCell(2)).trim();
|
//设置版本号
|
driver.findElement(By.xpath("(//input[@type='text'])[3]")).sendKeys(version);
|
|
Thread.sleep(SLEEP_MILLIS);
|
//点击保存按钮
|
driver.findElement(By.xpath("//button[contains(.,'保存')]")).click();
|
Thread.sleep(SLEEP_MILLIS);
|
}
|
|
public static void fillBomBody(WebDriver driver, Row tile, Row row) throws Exception {
|
int partNum = 1;
|
for (int i = 3; i < tile.getLastCellNum() - 1; i++) {
|
if (tile.getCell(i) == null) {
|
continue;
|
}
|
|
if (tile.getCell(i).toString().trim().equals("工序")) {
|
if (row.getCell(i).toString().equals("") && partNum != 1) {
|
i += 4;
|
continue;
|
}
|
fillChildPart(driver, row, i, partNum++);
|
}
|
}
|
|
driver.findElement(By.xpath("//*[@id=\"app\"]/div/div[2]/div[2]/div/div[1]/div/div/div[1]/div[1]/a/i")).click();
|
Thread.sleep(SLEEP_MILLIS);
|
}
|
|
public static void fillChildPart(WebDriver driver, Row row, int index, int partNum) throws Exception {
|
//点击添加按钮
|
driver.findElement(By.xpath("//button[contains(.,'添加')]")).click();
|
|
//获取工序
|
String procedure = getCellValue(row.getCell(index)).trim();
|
|
//选择工序
|
String xpath = "//*[@id=\"app\"]/div/div[2]/div[2]/div/div[1]/div/div/div[2]/div[2]/div/div[2]/div/div[3" +
|
"]/table/tbody/tr";
|
xpath += "/td[1]/div/div/div/button";
|
driver.findElement(By.xpath(xpath)).click();
|
driver.findElement(By.xpath("(//input[@type='text'])[24]")).clear();
|
|
//需要刷新下，不然相同工序不能选择
|
Thread.sleep(SLEEP_MILLIS);
|
driver.findElement(By.cssSelector("#operation-dialog > div > div.el-dialog__body > div > div.grid-header > form > div.el-form-item.fr.el-form-item--small > div > button:nth-child(1)")).click();
|
|
if (!procedure.equals("")) {
|
driver.findElement(By.xpath("(//input[@type='text'])[24]")).clear();
|
driver.findElement(By.xpath("(//input[@type='text'])[24]")).sendKeys(procedure);
|
driver.findElement(By.xpath("(//input[@type='text'])[24]")).sendKeys(Keys.ENTER);
|
Thread.sleep(SLEEP_MILLIS);
|
driver.findElement(By.cssSelector("#operation-dialog > div > div.el-dialog__body > div > div.common-table-div > div > div" +
|
".el-table__body-wrapper.is-scrolling-none > table > tbody > tr")).click();
|
}
|
|
driver.findElement(By.cssSelector("#operation-dialog > div > div.el-dialog__footer > div > button.el-button" +
|
".el-button--primary.el-button--small")).click();
|
|
//选择子零件
|
xpath = "//*[@id=\"app\"]/div/div[2]/div[2]/div/div[1]/div/div/div[2]/div[2]/div/div[2]/div/div[3]/table" +
|
"/tbody/tr";
|
xpath += "/td[2]/div/div/div/button";
|
driver.findElement(By.xpath(xpath)).click();
|
String partNo = getCellValue(row.getCell(index + 1)).trim();
|
//需要刷新下，不然选过的零件不能选
|
Thread.sleep(SLEEP_MILLIS);
|
driver.findElement(By.cssSelector("#part-dialog > div > div.el-dialog__body > div > div.grid-header > form > div.el-form-item.fr.el-form-item--small > div > button:nth-child(2)")).click();
|
driver.findElement(By.xpath("(//input[@type='text'])[20]")).clear();
|
driver.findElement(By.xpath("(//input[@type='text'])[20]")).sendKeys(partNo);
|
driver.findElement(By.xpath("(//input[@type='text'])[20]")).sendKeys(Keys.ENTER);
|
Thread.sleep(SLEEP_MILLIS);
|
driver.findElement(By.cssSelector("#part-dialog > div > div.el-dialog__body > div > div.common-table-div > " +
|
"div > div.el-table__body-wrapper.is-scrolling-none > table > tbody > tr")).click();
|
driver.findElement(By.cssSelector("#part-dialog > div > div.el-dialog__footer > div > button.el-button" +
|
".el-button--primary.el-button--small")).click();
|
|
//填写数量
|
String num = getCellValue(row.getCell(index + 3)).trim();
|
if (!num.equals("")) {
|
xpath = "//*[@id=\"app\"]/div/div[2]/div[2]/div/div[1]/div/div/div[2]/div[2]/div/div[2]/div/div[3]/table/tbody/tr";
|
if (partNum > 1) {
|
xpath += "[" + partNum + "]";
|
}
|
xpath += "/td[4]/div/div/input";
|
driver.findElement(By.xpath(xpath)).sendKeys(num);
|
}
|
|
//填写盘数
|
String discsNum = getCellValue(row.getCell(index + 5)).trim();
|
if (!discsNum.equals("")) {
|
xpath = "//*[@id=\"app\"]/div/div[2]/div[2]/div/div[1]/div/div/div[2]/div[2]/div/div[2]/div/div[3]/table/tbody/tr";
|
xpath += "/td[6]/div/div/input";
|
driver.findElement(By.xpath(xpath)).sendKeys(discsNum);
|
}
|
|
//点击保存按钮
|
xpath = "//*[@id=\"app\"]/div/div[2]/div[2]/div/div[1]/div/div/div[2]/div[2]/div/div[2]/div/div[4]/div[2]/table/tbody/tr";
|
if (partNum > 1) {
|
xpath += "[" + partNum + "]";
|
}
|
xpath += "/td[8]/div/button[1]";
|
driver.findElement(By.xpath(xpath)).click();
|
// driver.findElement(By.cssSelector("body")).sendKeys(Keys.F5);
|
Thread.sleep(SLEEP_MILLIS);
|
}
|
|
public static Workbook getWorkBook(File file) {
|
//创建Workbook工作薄对象，表示整个excel
|
Workbook workbook = null;
|
try {
|
//获取excel文件的io流
|
InputStream is = new FileInputStream(file);
|
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
|
workbook = new XSSFWorkbook(is);
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
return workbook;
|
}
|
|
public static String getCellValue(Cell cell) {
|
String cellValue = "";
|
if (cell == null) {
|
return cellValue;
|
}
|
//如果当前单元格内容为日期类型，需要特殊处理
|
String dataFormatString = cell.getCellStyle().getDataFormatString();
|
if (dataFormatString.equals("m/d/yy")) {
|
cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
|
return cellValue;
|
}
|
//把数字当成String来读，避免出现1读成1.0的情况
|
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
}
|
//判断数据的类型
|
switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_NUMERIC: //数字
|
cellValue = String.valueOf(cell.getNumericCellValue());
|
break;
|
case Cell.CELL_TYPE_STRING: //字符串
|
cellValue = String.valueOf(cell.getStringCellValue());
|
break;
|
case Cell.CELL_TYPE_BOOLEAN: //Boolean
|
cellValue = String.valueOf(cell.getBooleanCellValue());
|
break;
|
case Cell.CELL_TYPE_FORMULA: //公式
|
cellValue = String.valueOf(cell.getCellFormula());
|
break;
|
case Cell.CELL_TYPE_BLANK: //空值
|
cellValue = "";
|
break;
|
case Cell.CELL_TYPE_ERROR: //故障
|
cellValue = "非法字符";
|
break;
|
default:
|
cellValue = "未知类型";
|
break;
|
}
|
return cellValue;
|
}
|
|
public static boolean doesWebElementExist(WebDriver driver, By selector) {
|
try {
|
//元素存在且必须显示才可操作
|
driver.findElement(selector);
|
if (driver.findElement(selector).isDisplayed())
|
return true;
|
else
|
return false;
|
} catch (NoSuchElementException e) {
|
return false;
|
}
|
}
|
}
|
