package common;
public class Const {
public final static String XL5_DIR = "";
public final static String XLS_FILE = "autolest.×1sm";
public final static String MAIN_SHEET = "";
public final static String CHROME = "CHROME";
public final static String IE11 = "IE11";
public final static String SAFARI = "SAFARI";
// excelの定数
public final static int MAX_COL = 1000;
public final static int MAX_ROW = 1000;
// シナリオ定義書関連
// ①シートのシナリオの名
public final static String SCN_SHTNAME_1 = "シナリオ";// シナリオのシート名
public final static int SCN_SHTSCN_TITLE_ROW = 3;// シナリオのシートのシナリオタイトル所属行番号
// No.
public final static String SCN_TITLE_NO = "No.";
// 実行
public final static String SCN_TITLE_RUN = "実行.";
// 画面ID
public final static String SCN_TITLE_GAMENID = " 画面ID";
// 画面名
public final static String SCN_TITLE_GAMENNAME = "画面名";
// 入力パターン
public final static String SCN_TITLE_INPUTPTN = "入力パターン";
// 確認パターン
public final static String SCN_TITLE_CONFIRMID = "確認パターン";
// イベント
public final static String SCN_TITLE_EVENT = "イベント";
// イベントID
public final static String SCN_TITLE_EVENTID = "イベントID";
// URL
public final static String SCN_TITLE_URL = "URL";
// ユーザー
public final static String SCN_TITLE_USER = "ユーザー";
// パスワード
public final static String SCN_TITLE_PWD = "パスワード";
// 備考
public final static String SCN_TITLE_BIKO = "備考";
public final static int SCN_SHTSCN_VALUE_STARTROW = 4;// No.1の所属列番 1から
// ②シナリオ定義書の画面投入データのシート
public final static int SCN_SHTINPUT_PATTENNO_ROW = 2;// No.xパターン番号所属した行番号 1から
public final static int SCN_SHTINPUT_PATTENNO_STARTCOL = 18;// No.1の所属列番 1から
public final static int SCN_SHTINPUT_VALUE_STARTROW = 3;// No.1の所属列番 1から
public final static int SCN_SHTINPUT_NOTITLE_COL = 1;// ID所属列番 1から
// 画面定義書
public final static String HTMLDEF_TITLE_ROW = "1,2";// 画面定義書のタイトル所属行番号
// NO.
public final static String HTMLDEF_TITLE_TITLE_NO = "NO.";
// 項目名
public final static String HTMLDEF_TITLE_ITEMNAME = "項目名";
// HtmlType
public final static String HTMLDEF_TITLE_HTMLTYPE = " HtmlType";
// Element
public final static String HTMLDEF_TITLE_ELEMENT = "Element";
// IDX
public final static String HTMLDEF_TITLE_IDX = "IDX";
// Frame
public final static String HTMLDEF_TITLE_FRAME = "Frame";
// 値の開始行番号
public final static int HTMLDEF_VALUE_STARTROW = 3;// No.1の所属列番 1から
public final static String V8A_I = "I";
public final static String VBA_O = "O";
public final static String VBA_BATU = "x";
public final static long TIME_OUT = 20;
}
package excel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import Dto.HtmlItem;
import Dto.InputDto;
import Dto.SceneDto;
import common.Const;
import common.Global;
public class ExcelUtil {
// シナリオファイルの全パスファイル名
private String _filePathNameScenarioInfo;
// シナリオブック
private XSSFWorkbook _workBookScenarioInfo;
// 当シナリオブック関連する画面定義書のマック
//private Map<String, XSSFWorkbook> htmlDefBookMap = new HashMap<String, XSSFWorkbook>();
private Map<String, Map<String, HtmlItem>> htmlDefMap = new HashMap<String, Map<String, HtmlItem>>();
private List<SceneDto> sceneList = new ArrayList<SceneDto>();
public void init(String filePathNameScenarioInfo) {
//シナリオブックを初期化
_workBookScenarioInfo = this.getBook(filePathNameScenarioInfo);
}
/**
* Excelファイルをクローズ TODO
*/
public void close() {
try {
_workBookScenarioInfo.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//１つのシナリオ定義書、複数の画面定義書から、シナリオリスト、画面定義のマップを取得。
private void setSceneList_htmlDefMap() {
XSSFSheet scnSheet = _workBookScenarioInfo.getSheet(Const.SCN_SHTNAME_1);
//シナリオ定義書の「シナリオ」シートにタイトルのcell値とcellの列番のマップ
Map<String, Integer> colNameNumMap = getColNameNumMap(scnSheet, String.valueOf(Const.SCN_SHTSCN_TITLE_ROW));
String no = "";
//merge cellを判断必要ため
String preNo = "";
String preRun = "";
for(int r = Const.SCN_SHTSCN_VALUE_STARTROW; r< Const.MAX_ROW;r++) {
SceneDto scene = new SceneDto();
// No.
int col_NO = colNameNumMap.get(Const.SCN_TITLE_NO);
String val_NO = this.cell(scnSheet, r, col_NO);
if(val_NO.equals("")) {
val_NO = preNo;
}
scene.setNo(val_NO);
preNo = val_NO;//NOを退避、次のループを使う
// 実行
int col_RUN = colNameNumMap.get(Const.SCN_TITLE_RUN);
String val_RUN = this.cell(scnSheet, r, col_RUN);
if(preRun.equals("")) {
val_RUN = preRun;
}
scene.setRunFlg(val_RUN);
preRun = val_RUN;//NOを退避、次のループを使う
if(val_RUN.equals("×")) {
//実行フラグが×の場合、当ケースを実行しない
continue;
}
// 画面ID
int col_GAMENID = colNameNumMap.get(Const.SCN_TITLE_GAMENID);
String val_GAMENID = this.cell(scnSheet, r, col_GAMENID);
if(val_NO.equals("") && val_RUN.equals("") && val_GAMENID.equals("") ) {
break;
}
scene.setGamenID(val_GAMENID);
// 画面名
int col_GAMENNAME = colNameNumMap.get(Const.SCN_TITLE_GAMENNAME);
String val_GAMENNAME = this.cell(scnSheet, r, col_GAMENNAME);
scene.setGamenName(val_GAMENNAME);
// 入力パターン
int col_INPUTPTN = colNameNumMap.get(Const.SCN_TITLE_INPUTPTN);
String val_INPUTPTN = this.cell(scnSheet, r, col_INPUTPTN);
scene.setInputPTN(val_INPUTPTN);
// 確認パターン
int col_CONFIRMID = colNameNumMap.get(Const.SCN_TITLE_CONFIRMID);
String val_CONFIRMID = this.cell(scnSheet, r, col_CONFIRMID);
scene.setConfirmID(val_CONFIRMID);
// イベント
int col_EVENT = colNameNumMap.get(Const.SCN_TITLE_EVENT);
String val_EVENT = this.cell(scnSheet, r, col_EVENT);
scene.setEventName(val_EVENT);
// イベントID
int col_EVENTID = colNameNumMap.get(Const.SCN_TITLE_EVENTID);
String val_EVENTID = this.cell(scnSheet, r, col_EVENTID);
scene.setEventID(val_EVENTID);
// URL
int col_URL = colNameNumMap.get(Const.SCN_TITLE_URL);
String val_URL = this.cell(scnSheet, r, col_URL);
scene.setUrl(val_URL);
sceneList.add(scene);
String htmlDefBookPathName = getHtmlDefBookPathName(val_GAMENID, val_GAMENNAME);
Map<String, HtmlItem> tmpHtmlDefMap = getHtmlDefBookMap(htmlDefBookPathName);
htmlDefMap.put(htmlDefBookPathName,tmpHtmlDefMap);
}
}
//画面定義書の全パス名を取得
private String getHtmlDefBookPathName(String gamenID, String gamenName) {
return Global.getHtmlDefFolder() + "\\" + "画面定義書_(" + gamenID + ").xlsx";
}
/**
* シナリオ定義書から投入データを取得
* @param gamenId
* @param gamenName
* @param patternID
* @return
*/
private InputDto getInutValuesByNoID(String gamenId, String gamenName, String patternID) {
InputDto ret = new InputDto();
ret.setGamenId(gamenId);
ret.setGamenName(gamenName);
ret.setPatternID(patternID);
if (StringUtils.isBlank(gamenName) || gamenName.equals("-") || StringUtils.isBlank(gamenName)
|| gamenName.equals("-")) {
return null;
}
XSSFSheet inputSheet = _workBookScenarioInfo.getSheet(gamenName);
Map<String, String> valueMap = new LinkedHashMap<String, String>();
int dataCol = 0;
for (int c = Const.SCN_SHTINPUT_PATTENNO_STARTCOL; c < Const.MAX_COL; c++) {
String tmpCell = this.cell(inputSheet, Const.SCN_SHTINPUT_PATTENNO_ROW, c).trim();
if (tmpCell.equals(patternID)) {
dataCol = c;
break;
}
}
if (dataCol == 0) {
// TODO err
return null;
}
String id;
String val;
for (int r = Const.SCN_SHTINPUT_VALUE_STARTROW; r < Const.MAX_ROW; r++) {
id = this.cell(inputSheet, r, Const.SCN_SHTINPUT_NOTITLE_COL).trim();
val = this.cell(inputSheet, r, dataCol).trim();
if (StringUtils.isEmpty(id)) {
break;
}
valueMap.put(id, val);
}
ret.setValueMap(valueMap);
return ret;
}
/**
* 一つの画面定義書のマップ Map(項目ＩＤ（String）, HTML特定用定義（HtmlItem）)
* @param bookPathName 画面定義書のファイルパス名
* @return マップ
*/
private Map<String, HtmlItem> getHtmlDefBookMap(String bookPathName) {
//返却用
Map<String, HtmlItem> retMap = new HashMap<String, HtmlItem>();
XSSFWorkbook htmlDefBook = getBook(bookPathName);
XSSFSheet sht = htmlDefBook.getSheetAt(0);
Map<String, Integer> colNameNumMap = getColNameNumMap(sht, Const.HTMLDEF_TITLE_ROW);
for (int r = Const.HTMLDEF_VALUE_STARTROW; r < Const.MAX_ROW; r++) {
HtmlItem item = new HtmlItem();
//NO.
int col_NO = colNameNumMap.get(Const.HTMLDEF_TITLE_TITLE_NO);
String val_NO = this.cell(sht, r, col_NO);
item.setNO(val_NO);
//項目名
int col_ITEMNAME = colNameNumMap.get(Const.HTMLDEF_TITLE_ITEMNAME);
String val_ITEMNAME = this.cell(sht, r, col_ITEMNAME);
item.setItemName(val_ITEMNAME);
if(StringUtils.isBlank(val_NO) && StringUtils.isBlank(val_ITEMNAME)) {
break;
}
//HtmlType
int col_HTMLTYPE = colNameNumMap.get(Const.HTMLDEF_TITLE_HTMLTYPE);
String val_HTMLTYPE = this.cell(sht, r, col_HTMLTYPE);
item.setHtmlType(val_HTMLTYPE);
//Element
int col_ELEMENT= colNameNumMap.get(Const.HTMLDEF_TITLE_ELEMENT);
String val_ELEMENT = this.cell(sht, r, col_ELEMENT);
item.setCssSel(val_ELEMENT);
//IDX
int col_IDX = colNameNumMap.get(Const.HTMLDEF_TITLE_IDX);
String val_IDX = this.cell(sht, r, col_IDX);
item.setIdx(val_IDX);
//Frame
int col_FRAME = colNameNumMap.get(Const.HTMLDEF_TITLE_FRAME);
String val_FRAME = this.cell(sht, r, col_FRAME);
item.setFrame(val_FRAME);
retMap.put(val_NO, item);
}
return retMap;
}
public XSSFWorkbook getBook(String bookPathName) {
XSSFWorkbook retBook = null;
FileInputStream fileInputStream;
try {
fileInputStream = new FileInputStream(bookPathName);
retBook = new XSSFWorkbook(fileInputStream);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return retBook;
}
/**
* ファイル全パス名でmapからブックを取得する,mapにない場合、フォルダから取得、mapにputして、返却する。
*
* @param filePathName
* @param map
* @return
*/
private XSSFWorkbook getBookFromBookMap(String bookPathName, Map<String, XSSFWorkbook> map) {
XSSFWorkbook book = null;
// file mapにある場合、get、なければ、pathNameで取得する、mapにputする
if (map.containsKey(bookPathName)) {
book = map.get(bookPathName);
} else {
book = this.getBook(bookPathName);
map.put(bookPathName, book);
}
return book;
}
/**
* cellはmerged cellsの一部か
*
* @param sheet
* @param r
* @param c
* @return
*/
private boolean isMergedCell(XSSFSheet sheet, int r, int c) {
// sheetは、処理対象のシート。
final int mergedCellCount = sheet.getNumMergedRegions();
for (int i = 0; i < mergedCellCount; i++) {
// シート内の各結合セルについて、左上のセルの行・列のインデックスを取得する。
final CellRangeAddress range = sheet.getMergedRegion(i);
final int firstRow = range.getFirstRow();
final int lastRow = range.getLastRow();
final int firstCol = range.getFirstColumn();
final int lastCol = range.getLastRow();
if (r >= firstRow && r <= lastRow && c >= firstCol && c <= lastCol) {
return true;
} else {
return false;
}
}
return false;
}
/**
* excelの文字にある列番（１～）マップを取得（文字、文字所属されたExcelの列番号）
*
* @param sht
* @param rows 対象文字列あるの行番号、複数の場合、カンマ区切りで
* @return マップ
*/
private Map<String, Integer> getColNameNumMap(XSSFSheet sht, String rowNum) {
// 返却マップ
Map<String, Integer> retMap = new HashMap<String, Integer>();
String[] rowsArrStr;
if (rowNum.contains(",")) {
rowsArrStr = rowNum.split(",");
} else {
rowsArrStr = new String[1];
rowsArrStr[0] = rowNum;
}
for (String strR : rowsArrStr) {
int r = Integer.valueOf(strR);
String tmpStr;
for (int c = 1; c < Const.MAX_COL; c++) {
tmpStr = this.cell(sht, r, c).trim();
if (StringUtils.isNotEmpty(tmpStr)) {
retMap.put(tmpStr, c);
}
}
}
return retMap;
}
/**
* Cellのvalueを取得
*
* @param sht
* @param rowNo 1から
* @param colNo 1から
* @return cellのvalue
*/
private String cell(XSSFSheet sht, int rowNo, int colNo) {
rowNo = rowNo - 1;
colNo = colNo - 1;
XSSFRow xSSFRow = sht.getRow(rowNo);
XSSFCell cell = xSSFRow.getCell(colNo);
Object object;
Objects.requireNonNull(cell, "cell is null");
CellType cellType = cell.getCellType();
if (cellType == CellType.BLANK) {
object = null;
} else if (cellType == CellType.BOOLEAN) {
object = cell.getBooleanCellValue();
} else if (cellType == CellType.FORMULA) {
switch (cell.getCachedFormulaResultType()) {
case NUMERIC:
double dbl = cell.getNumericCellValue();
int intVal = (int) dbl;
if (dbl == intVal) {
object = intVal;
} else {
object = dbl;
}
break;
case STRING:
object = cell.getRichStringCellValue();
break;
default:
throw new RuntimeException("Error cell is unsupported");
}
} else if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
object = cell.getDateCellValue();
} else {
double dbl = cell.getNumericCellValue();
int intVal = (int) dbl;
if (dbl == intVal) {
object = intVal;
} else {
object = dbl;
}
}
} else if (cellType == CellType.STRING) {
object = cell.getStringCellValue();
} else {
throw new RuntimeException("Unknow type cell");
}
String cellValue = String.valueOf(object);
if (cellValue == null || cellValue.isEmpty()) {
return "";
} else {
return cellValue;
}
}
}
