2020年1月6日星期一

excel poi

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;
  }
 }
}
发帖者 时间：

没有评论:

发表评论

订阅： 博文评论 (Atom)