Jaxcel/src/org/hanei/jaxcel/report/TLParser.java

   1 /**
   2  * Copyright 2014 Hanei Management Co.,Ltd.
   3  *
   4  * This file is part of Jaxcel
   5  *
   6  *  Jaxcel is free software: you can redistribute it and/or modify
   7  *  it under the terms of the GNU Lesser General Public License as published by
   8  *  the Free Software Foundation, either version 3 of the License, or
   9  *  (at your option) any later version.
  10  *
  11  *  Jaxcel is distributed in the hope that it will be useful,
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  *  GNU Lesser General Public License for more details.
  15  *
  16  *  You should have received a copy of the GNU Lesser General Public License
  17  *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
  18  */
  19 package org.hanei.jaxcel.report;
  20
  21 import java.text.ParseException;
  22 import java.util.ArrayList;
  23 import java.util.Map;
  24 import java.util.regex.Matcher;
  25 import java.util.regex.Pattern;
  26
  27
  28 import org.apache.commons.lang3.BooleanUtils;
  29 import org.apache.commons.lang3.StringUtils;
  30 import org.apache.commons.lang3.math.NumberUtils;
  31 import org.apache.commons.lang3.time.DateUtils;
  32 import org.apache.poi.POIXMLDocumentPart;
  33 import org.apache.poi.hssf.record.cf.CellRangeUtil;
  34 import org.apache.poi.ss.formula.FormulaParseException;
  35 import org.apache.poi.ss.usermodel.Cell;
  36 import org.apache.poi.ss.usermodel.Row;
  37 import org.apache.poi.ss.usermodel.Sheet;
  38 import org.apache.poi.ss.util.CellRangeAddress;
  39 import org.apache.poi.ss.util.CellReference;
  40 import org.apache.poi.xssf.usermodel.XSSFDrawing;
  41 import org.apache.poi.xssf.usermodel.XSSFSheet;
  42 import org.hanei.jaxcel.exception.JaxcelInputException;
  43 import org.hanei.jaxcel.util.ExcelUtil;
  44 import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTDrawing;
  45 import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
  46 import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;
  47 import org.slf4j.LoggerFactory;
  48 import org.slf4j.Logger;
  49
  50 /**
  51  * Excelテンプレートシートの指示子(Template Language)、EL式(Expression Language)の検索、パースを行う
  52  *
  53  * @author Noboru Saito
  54  *
  55  */
  56 public class TLParser {
  57
  58         private static final Logger log = LoggerFactory.getLogger(TLParser.class);
  59
  60         private static final String EL = "el";
  61         private static final String TL_IF = "if";
  62         private static final String TL_FOREACH = "foreach";
  63         private static final String TLP_EXPR = "${";
  64         private static final String TLP_DELETE = "delete";
  65         private static final String TLP_DIRECTION = "direction";
  66         private static final String TLP_SHIFT = "shift";
  67         private static final String TLP_BLOCK = "block";
  68         private static final String TLP_STYLE = "style";
  69         private static final String TLP_START = "start";
  70         private static final String TLP_END = "end";
  71         private static final String TLP_ROWS = "rows";
  72         private static final String TLP_COLS = "cols";
  73
  74         private static final String LEFT = "left";
  75         private static final String UP = "up";
  76         private static final String CLEAR = "clear";
  77         private static final String ROW = "row";
  78         private static final String COL = "col";
  79         private static final String COPY = "copy";
  80         private static final int SPAN_DEF = 1;
  81         private static final int IDX_BASE = 1;
  82         private static final int PARSE_MAX = 10;
  83
  84         private static final short FORMAT_GENERAL = 0;
  85
  86         private Sheet sheet = null;
  87         private JaxcelContext context = null;
  88         private ELManager elMgr = null;
  89         private Cell cell = null;
  90         private int startRowIdx;
  91         private int startColIdx;
  92
  93         private String type;            // TL式のタイプ
  94         private String expression;      // EL式
  95         private String delete;          // delete
  96         private int rowSpan;            // row
  97         private int colSpan;            // column
  98         private String direction;       // direction
  99         private boolean shift;          // shift
 100         private boolean block;          // block
 101         private String style;           // style
 102         private String start;           // start
 103         private String end;                     // end
 104         private String list;            // list
 105         private String object;          // object
 106         private String matchString;     // マッチした文字列保持用
 107         private boolean reParseFlg;     // 同一セル再パース要否フラグ
 108         private int parseCount;         // 同一セルパース回数
 109
 110         /**
 111          *  ${expression}
 112          */
 113         private final String  rgEl = "\\$\\{([^\\{\\}]+)\\}";
 114         private final Pattern ptEl = Pattern.compile(rgEl);
 115         private Matcher mtEl;
 116
 117     /**
 118      *  #if(${expression} delete:"up|left|clear" block:"true|false" rows:"number" cols:"number")
 119      */
 120         private final String  rgIf = "#if\\(\\s*(\\$\\{[^\\{\\}]+\\})(?:\\s+(?:(delete\\s*:\\s*\"[^\"]*\")|(block\\s*:\\s*\"[^\"]*\")|(rows\\s*:\\s*\"\\d+\")|(cols\\s*:\\s*\"\\d+\"))){0,4}\\s*\\)";
 121         private final Pattern ptIf = Pattern.compile(rgIf);
 122         private Matcher mtIf;
 123
 124     /**
 125      *  #foreach(${object in list} direction:"row|col" style:"copy|^copy" shift:"true|false" block:"true|false" start:"number" end:"number" rows:"number" cols:"number")
 126      */
 127         private final String  rgForeach = "#foreach\\(\\s*(\\$\\{\\s*\\S+\\s+in\\s+\\S+\\s*\\})(?:\\s+(?:(direction\\s*:\\s*\"[^\"]*\")|(style\\s*:\\s*\"[^\"]*\")|(shift\\s*:\\s*\"[^\"]*\")|(block\\s*:\\s*\"[^\"]*\")|(start\\s*:\\s*\"[^\"]*\")|(end\\s*:\\s*\"[^\"]*\")|(rows\\s*:\\s*\"\\d+\")|(cols\\s*:\\s*\"\\d+\"))){0,8}\\s*\\)";
 128         private final Pattern ptForeach = Pattern.compile(rgForeach);
 129         private Matcher mtForeach;
 130
 131         /**
 132          *  attribute
 133          */
 134         private final String rgAttrEL = "\\$\\{([^\\{\\}]*)\\}";
 135         private final String rgAttr = "\\s*:\\s*\"([^\"]*)\"";
 136         private final String $1 = "$1";
 137
 138         /**
 139          *  指示子・EL式一括
 140          */
 141         private final Pattern ptAll = Pattern.compile(rgIf + "|" + rgForeach + "|" + rgEl);
 142         private Matcher mtAll = null;
 143
 144         /**
 145          * 日付形式書式パターン
 146          */
 147         private final String[] DATE_FORMATS = {
 148                         "yyyy-MM-dd",
 149                         "yyyy/MM/dd",
 150                         "yyyy-MM-dd HH:mm:ss.SSS",
 151                         "yyyy-MM-dd HH:mm:ss",
 152                         "yyyy-MM-dd HH:mm",
 153                         "yyyy/MM/dd HH:mm:ss.SSS",
 154                         "yyyy/MM/dd HH:mm:ss",
 155                         "yyyy/MM/dd HH:mm",
 156                         "HH:mm:ss.SSS",
 157                         "HH:mm:ss",
 158                         "HH:mm"
 159         };
 160
 161         /**
 162          * コンストラクタ
 163          *
 164          * @param context       Jaxlsコンテキストオブジェクト
 165          */
 166         public TLParser(JaxcelContext context) {
 167                 this.context = context;
 168                 if(this.context != null) {
 169                         sheet = this.context.getCurrentSheet();
 170                         elMgr = this.context.getElManager();
 171                 }
 172         }
 173
 174         /**
 175          * EL式、If句、Foreach句のパース
 176          *
 177          * @param cell  対象セル
 178          *
 179          * @throws JaxcelInputException 入力例外発生時
 180          */
 181         public void parse(Cell cell) {
 182                 log.trace("parse start");
 183
 184                 // チェック
 185                 if(elMgr == null) {
 186                         log.error("ELManager is null");
 187                         log.trace("parse end");
 188                         throw new JaxcelInputException("ELManager is null");
 189                 }
 190                 if(cell == null) {
 191                         // 再パースフラグクリア
 192                         this.cell = null;
 193                         reParseFlg = false;
 194                         parseCount = 0;
 195                         log.debug("cell is null");
 196                         log.trace("parse end");
 197                         return;
 198                 }
 199
 200                 // 新規対象セルの場合、保持
 201                 if(this.cell == null) {
 202                         log.debug("start new cell parse");
 203                         this.cell = cell;
 204                         // 再パースフラグ・カウントクリア
 205                         reParseFlg = false;
 206                         parseCount = 0;
 207                 }
 208                 else if(!this.cell.equals(cell)) {
 209                         log.debug("start new cell parse");
 210                         this.cell = cell;
 211                         // 再パースフラグ・カウントクリア
 212                         reParseFlg = false;
 213                         parseCount = 0;
 214                 }
 215                 // 新規対象セルでない場合
 216                 else {
 217                         log.debug("start repeat cell parse");
 218                         // 再パースカウント加算
 219                         parseCount++;
 220                 }
 221
 222                 // 再パース回数チェック
 223                 if(parseCount >= PARSE_MAX) {
 224                         log.warn("parse repeat count over");
 225                         // 再パースフラグクリア
 226                         reParseFlg = false;
 227                         log.trace("parse end");
 228                         return;
 229                 }
 230
 231                 // 指示子,EL式検索
 232                 if(find()) {
 233                         // 検索ヒット
 234
 235                         // EL式
 236                         if(isEL()) {
 237                                 evaluate();
 238                         }
 239                         // EL式以外
 240                         else {
 241                                 // 指示子の消去
 242                                 switch (cell.getCellType()) {
 243                                 case Cell.CELL_TYPE_STRING:
 244                                         cell.setCellValue(replaceFirst(""));
 245                                         break;
 246                                 case Cell.CELL_TYPE_FORMULA:
 247                                         // まずはそのまま
 248                                         try {
 249                                                 cell.setCellFormula(replaceFirst(""));
 250                                         }
 251                                         catch(FormulaParseException e) {
 252                                                 log.debug("formula set error: {}", e.getMessage(), e);
 253                                                 cell.setCellType(Cell.CELL_TYPE_BLANK);
 254                                                 cell.setCellType(Cell.CELL_TYPE_STRING);
 255                                                 cell.setCellValue(replaceFirst(""));
 256                                                 log.debug("set value type: String");
 257                                         }
 258                                 }
 259                                 if(isIf()) {
 260                                         parseIf();
 261                                 }
 262                                 else if(isForeach()) {
 263                                         parseForeach();
 264                                 }
 265                                 // EL式以外は再パースフラグセット
 266                                 reParseFlg = true;
 267                         }
 268                 }
 269                 else {
 270                         // 検索ヒットせず
 271                         log.debug("not found TL");
 272                         // 再パースフラグクリア
 273                         reParseFlg = false;
 274                 }
 275
 276                 log.trace("parse end: reParseFlg: {} parseCount: {}", reParseFlg, parseCount);
 277         }
 278
 279         /**
 280          * テンプレート指示子の検索、データ保持
 281          *
 282          * @param cell  検索対象セル
 283          *
 284          * @return 検索結果
 285          */
 286         private boolean find() {
 287                 log.trace("matchTL start");
 288
 289                 boolean findFlg = false;
 290
 291                 // 初期化
 292                 type = null;            // TL式のタイプ
 293                 expression = null;      // EL式
 294                 rowSpan = SPAN_DEF;     // rows
 295                 colSpan = SPAN_DEF;     // cols
 296                 delete = LEFT;          // delete
 297                 list = null;            // list
 298                 object = null;          // object
 299                 direction = ROW;        // direction
 300                 shift = true;           // shift
 301                 block = true;           // block
 302                 style = COPY;           // style
 303                 start = null;           // start
 304                 end = null;                     // end
 305                 matchString = null;
 306                 String cellVal = null;
 307
 308                 // セルインデックス保持
 309                 startRowIdx = cell.getRowIndex();
 310                 startColIdx = cell.getColumnIndex();
 311
 312                 // セルタイプにより分岐
 313                 // 文字列・計算式の場合は値保持。以外は終了
 314                 switch(cell.getCellType()){
 315                 case Cell.CELL_TYPE_STRING:
 316                         cellVal = cell.getStringCellValue();
 317                         log.debug("cell[{}] cellType: string  value: {}", (new CellReference(cell)).formatAsString(), cellVal);
 318                         break;
 319                 case Cell.CELL_TYPE_FORMULA:
 320                         cellVal = cell.getCellFormula();
 321                         log.debug("cell[{}] cellType: formula  value: {}", (new CellReference(cell)).formatAsString(), cellVal);
 322                         break;
 323                 default:
 324                         log.debug("cell type is not string or formula");
 325                         log.trace("matchTL end");
 326                         return findFlg;
 327                 }
 328
 329                 // 指示子（TL）の検索
 330                 mtAll = ptAll.matcher(cellVal);
 331                 // 指示子ヒット
 332                 if(mtAll.find()) {
 333                         matchString = mtAll.group();
 334                         log.debug("match: {}", matchString);
 335
 336                         // IF文であるか
 337                         if((mtIf = ptIf.matcher(matchString)).find()) {
 338                                 log.debug("type: {}", TL_IF);
 339
 340                                 // タイプ保持
 341                                 type = TL_IF;
 342                                 findFlg = true;
 343
 344                                 // データ保持
 345                                 for(int i = 1; i <= mtIf.groupCount(); i++) {
 346                                         if(mtIf.group(i) == null) continue;
 347                                         // ${...}  式
 348                                         if(mtIf.group(i).startsWith(TLP_EXPR)) {
 349                                                 expression = mtIf.group(i).replaceAll(rgAttrEL, $1).trim();
 350                                                 log.debug("expression: {}", expression);
 351                                         }
 352                                         // delete:"up|left|clear"  削除動作 デフォルト left
 353                                         else if(mtIf.group(i).startsWith(TLP_DELETE)) {
 354                                                 delete = mtIf.group(i).replaceAll(TLP_DELETE + rgAttr, $1).trim();
 355                                                 log.debug("{}: {}", TLP_DELETE, delete);
 356                                                 if(!UP.equalsIgnoreCase(delete) && !LEFT.equalsIgnoreCase(delete) && !CLEAR.equalsIgnoreCase(delete)) {
 357                                                         log.debug("{} is illegal argument. set default: {}", TLP_DELETE, LEFT);
 358                                                         delete = LEFT;
 359                                                 }
 360                                         }
 361                                         // blick:"true|false"  ブロック範囲で削除操作をするかの指定 デフォルト true
 362                                         else if(mtIf.group(i).startsWith(TLP_BLOCK)) {
 363                                                 block = BooleanUtils.toBoolean(mtIf.group(i).replaceAll(TLP_BLOCK + rgAttr, $1).trim());
 364                                                 log.debug("{}: {}", TLP_BLOCK, block);
 365                                         }
 366                                         // rows:"number"  範囲 デフォルト 1
 367                                         else if(mtIf.group(i).startsWith(TLP_ROWS)) {
 368                                                 rowSpan = NumberUtils.toInt(mtIf.group(i).replaceAll(TLP_ROWS + rgAttr, $1).trim());
 369                                                 log.debug("{}: {}", TLP_ROWS, rowSpan);
 370                                                 if(rowSpan <= 0) {
 371                                                         log.warn("{} is illegal argument. set default: {}", TLP_ROWS, SPAN_DEF);
 372                                                         rowSpan = SPAN_DEF;
 373                                                 }
 374                                         }
 375                                         // cols:"number"  範囲 デフォルト 1
 376                                         else if(mtIf.group(i).startsWith(TLP_COLS)) {
 377                                                 colSpan = NumberUtils.toInt(mtIf.group(i).replaceAll(TLP_COLS + rgAttr, $1).trim());
 378                                                 log.debug("{}: {}", TLP_COLS, colSpan);
 379                                                 if(colSpan <= 0) {
 380                                                         log.warn("{} is illegal argument. set default: {}", TLP_COLS, SPAN_DEF);
 381                                                         colSpan = SPAN_DEF;
 382                                                 }
 383                                         }
 384                                 }
 385                         }
 386                         // Foreach文であるか
 387                         else if((mtForeach = ptForeach.matcher(matchString)).find()) {
 388                                 log.debug("type: {}", TL_FOREACH);
 389
 390                                 // タイプ保持
 391                                 type = TL_FOREACH;
 392                                 findFlg = true;
 393
 394                                 // データ保持
 395                                 String[] tmp;
 396                                 for(int i = 1; i <= mtForeach.groupCount(); i++) {
 397                                         if(mtForeach.group(i) == null) continue;
 398                                         // ${objct in list}
 399                                         if(mtForeach.group(i).startsWith(TLP_EXPR)) {
 400                                                 expression = mtForeach.group(i).replaceAll(rgAttrEL, $1).trim();
 401                                                 log.debug("expression: {}", expression);
 402                                                 tmp = expression.split("\\s+");
 403                                                 if(tmp != null && tmp.length == 3) {
 404                                                         object = tmp[0];
 405                                                         list = tmp[2];
 406                                                         if(log.isDebugEnabled()) {
 407                                                                 log.debug("object: {}", object);
 408                                                                 log.debug("list: {}", list);
 409                                                         }
 410                                                 }
 411                                                 else {
 412                                                         log.warn("format error: ex) object in list");
 413                                                 }
 414                                         }
 415                                     // direction:"row|col"  繰返し方向 デフォルト row
 416                                         else if(mtForeach.group(i).startsWith(TLP_DIRECTION)) {
 417                                                 direction = mtForeach.group(i).replaceAll(TLP_DIRECTION + rgAttr, $1);
 418                                                 log.debug("{}: {}", TLP_DIRECTION, direction);
 419                                                 if(!ROW.equalsIgnoreCase(direction) && !COL.equalsIgnoreCase(direction)) {
 420                                                         log.warn("{} is illegal argument. set default: {}", TLP_DIRECTION, ROW);
 421                                                         direction = ROW;
 422                                                 }
 423                                         }
 424                                         // shift:"true|false"  foreachレンジより後のセルに対するシフト指定 デフォルト false
 425                                         else if(mtForeach.group(i).startsWith(TLP_SHIFT)) {
 426                                                 shift = BooleanUtils.toBoolean(mtForeach.group(i).replaceAll(TLP_SHIFT + rgAttr, $1).trim());
 427                                                 log.debug("{}: {}", TLP_SHIFT, shift);
 428                                         }
 429                                         // blick:"true|false"  foreachレンジをブロック範囲で繰り返すかの指定 デフォルト true
 430                                         else if(mtForeach.group(i).startsWith(TLP_BLOCK)) {
 431                                                 block = BooleanUtils.toBoolean(mtForeach.group(i).replaceAll(TLP_BLOCK + rgAttr, $1).trim());
 432                                                 log.debug("{}: {}", TLP_BLOCK, block);
 433                                         }
 434                                         // style:"copy|^copy"  foreachレンジのセルスタイルのコピー指定 デフォルト copy
 435                                         else if(mtForeach.group(i).startsWith(TLP_STYLE)) {
 436                                                 style = mtForeach.group(i).replaceAll(TLP_STYLE + rgAttr, $1).trim();
 437                                                 log.debug("{}: {}", TLP_STYLE, style);
 438                                         }
 439                                     // start:"numberExpression"
 440                                         else if(mtForeach.group(i).startsWith(TLP_START)) {
 441                                                 start = mtForeach.group(i).replaceAll(TLP_START + rgAttr, $1);
 442                                                 log.debug("{}: {}", TLP_START, start);
 443                                         }
 444                                     // end:"numberExpression"
 445                                         else if(mtForeach.group(i).startsWith(TLP_END)) {
 446                                                 end = mtForeach.group(i).replaceAll(TLP_END + rgAttr, $1);
 447                                                 log.debug("{}: {}", TLP_END, end);
 448                                         }
 449                                         // rows:"number"  範囲 デフォルト 1
 450                                         else if(mtForeach.group(i).startsWith(TLP_ROWS)) {
 451                                                 rowSpan = NumberUtils.toInt(mtForeach.group(i).replaceAll(TLP_ROWS + rgAttr, $1).trim());
 452                                                 log.debug("{}: {}", TLP_ROWS, rowSpan);
 453                                                 if(rowSpan <= 0) {
 454                                                         log.warn("{} is illegal argument. set default: {}", TLP_ROWS, SPAN_DEF);
 455                                                         rowSpan = SPAN_DEF;
 456                                                 }
 457                                         }
 458                                         // cols:"number"  範囲 デフォルト 1
 459                                         else if(mtForeach.group(i).startsWith(TLP_COLS)) {
 460                                                 colSpan = NumberUtils.toInt(mtForeach.group(i).replaceAll(TLP_COLS + rgAttr, $1).trim());
 461                                                 log.debug("{}: {}", TLP_COLS, colSpan);
 462                                                 if(colSpan <= 0) {
 463                                                         log.warn("{} is illegal argument. set default: {}", TLP_COLS, SPAN_DEF);
 464                                                         colSpan = SPAN_DEF;
 465                                                 }
 466                                         }
 467                                 }
 468                         }
 469                         // オブジェクト（EL）のヒット
 470                         else if((mtEl = ptEl.matcher(matchString)).find()) {
 471                                 log.debug("type: {}", EL);
 472
 473                                 // タイプ保持
 474                                 type = EL;
 475                                 findFlg = true;
 476
 477                                 // ${...}
 478                                 expression = mtEl.group(1).trim();
 479                                 log.debug("expression: {}", expression);
 480                         }
 481                         // 不明
 482                         else {
 483                                 log.warn("TL type: ???");
 484                                 // 再パースフラグクリア
 485                                 reParseFlg = false;
 486                         }
 487                 }
 488                 else {
 489                         log.debug("unmatch");
 490                 }
 491
 492                 log.trace("matchTL end: {}", findFlg);
 493                 return findFlg;
 494         }
 495
 496         /**
 497          * EL式の評価
 498          */
 499         private void evaluate() {
 500                 log.trace("evaluate start");
 501
 502                 Object elResult;                // パース結果保持用
 503                 String newCellVal;              // セル値
 504
 505                 // evaluate
 506                 elResult = elMgr.evaluate(expression);
 507                 if(elResult == null) {
 508                         log.debug("evaluate result is null");
 509                 }
 510                 else {
 511                         log.debug("evaluate result: {}", elResult.toString());
 512                 }
 513                 // マッチ１件目をevaluate結果で置換
 514                 newCellVal = replaceFirst(elResult == null ? "" : elResult.toString());
 515                 log.debug("replace value: {}", newCellVal);
 516
 517                 // newCellValが空白でないなら
 518                 if(!"".equals(newCellVal.trim())) {
 519                         // 置換後の値の形式チェック
 520                         // 置換後の値が数値型
 521                         if(NumberUtils.isNumber(newCellVal)) {
 522                                 // もともと文字列セルなら数値セルに変更し値をセット
 523                                 if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
 524                                         cell.setCellType(Cell.CELL_TYPE_BLANK);
 525                                         cell.setCellType(Cell.CELL_TYPE_NUMERIC);
 526                                         // まずは整数変換
 527                                         try {
 528                                                 cell.setCellValue(Integer.parseInt(newCellVal));
 529                                                 log.debug("set value type: Integer");
 530                                         }
 531                                         // エラー発生で実数変換
 532                                         catch(NumberFormatException e) {
 533                                                 cell.setCellValue(NumberUtils.toDouble(newCellVal));
 534                                                 log.debug("set value type: Double");
 535                                         }
 536                                 }
 537                                 // もともと計算式セルの場合は計算式として値をセット
 538                                 else {
 539                                         // まずはそのまま
 540                                         try {
 541                                                 cell.setCellFormula(newCellVal);
 542                                                 log.debug("set value type: Formula");
 543                                         }
 544                                         // エラー発生で数値としてセット
 545                                         catch(FormulaParseException e) {
 546                                                 log.warn("formula set error: {}", e.getMessage(), e);
 547                                                 cell.setCellType(Cell.CELL_TYPE_BLANK);
 548                                                 cell.setCellType(Cell.CELL_TYPE_NUMERIC);
 549                                                 // まずは整数変換
 550                                                 try {
 551                                                         cell.setCellValue(Integer.parseInt(newCellVal));
 552                                                         log.debug("set value type: Integer");
 553                                                 }
 554                                                 // エラー発生で実数変換
 555                                                 catch(NumberFormatException e2) {
 556                                                         cell.setCellValue(NumberUtils.toDouble(newCellVal));
 557                                                         log.debug("set value type: Double");
 558                                                 }
 559                                         }
 560                                 }
 561                                 // 再パースフラグクリア
 562                                 reParseFlg = false;
 563                         }
 564                         // 置換後の値がBool型
 565                         else if(StringUtils.equalsIgnoreCase(newCellVal, "true") || StringUtils.equalsIgnoreCase(newCellVal, "false")) {
 566                                 // もともと文字列セルならBoolセルに変更し値をセット
 567                                 if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
 568                                         cell.setCellType(Cell.CELL_TYPE_BLANK);
 569                                         cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
 570                                         cell.setCellValue(BooleanUtils.toBoolean(newCellVal));
 571                                         log.debug("set value type: Boolean");
 572                                 }
 573                                 // もともと計算式セルの場合は計算式として値をセット
 574                                 else {
 575                                         // まずはそのまま
 576                                         try {
 577                                                 cell.setCellFormula(newCellVal);
 578                                                 log.debug("set value type: Formula");
 579                                         }
 580                                         // エラー発生でBoolとしてセット
 581                                         catch(FormulaParseException e) {
 582                                                 log.warn("formula set error: {}", e.getMessage(), e);
 583                                                 cell.setCellType(Cell.CELL_TYPE_BLANK);
 584                                                 cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
 585                                                 cell.setCellValue(BooleanUtils.toBoolean(newCellVal));
 586                                                 log.debug("set value type: Boolean");
 587                                         }
 588                                 }
 589                                 // 再パースフラグクリア
 590                                 reParseFlg = false;
 591                         }
 592                         // 置換後の値が上記以外
 593                         else {
 594                                 try {
 595                                         // 置換後の値が日付・時刻型
 596                                         if(DateUtils.parseDateStrictly(newCellVal, DATE_FORMATS) != null) {
 597                                                 // もともと文字列セルなら数値セルに変更し値をセット
 598                                                 if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
 599                                                         cell.setCellType(Cell.CELL_TYPE_BLANK);
 600                                                         cell.setCellType(Cell.CELL_TYPE_NUMERIC);
 601                                                         // 書式未設定なら文字列のまま挿入
 602                                                         if(FORMAT_GENERAL == cell.getCellStyle().getDataFormat()) {
 603                                                                 cell.setCellValue(newCellVal);
 604                                                                 log.debug("set value type: String");
 605                                                         }
 606                                                         else {
 607                                                                 cell.setCellValue(DateUtils.parseDateStrictly(newCellVal, DATE_FORMATS));
 608                                                                 log.debug("set value type: Date");
 609                                                         }
 610                                                 }
 611                                                 // もともと計算式セルの場合は計算式として値をセット
 612                                                 else {
 613                                                         // まずはそのまま
 614                                                         try {
 615                                                                 cell.setCellFormula(newCellVal);
 616                                                                 log.debug("set value type: Formula");
 617                                                         }
 618                                                         // エラー発生で数値としてセット
 619                                                         catch(FormulaParseException e) {
 620                                                                 log.warn("formula set error: {}", e.getMessage(), e);
 621                                                                 cell.setCellType(Cell.CELL_TYPE_BLANK);
 622                                                                 cell.setCellType(Cell.CELL_TYPE_NUMERIC);
 623                                                                 cell.setCellValue(DateUtils.parseDateStrictly(newCellVal, DATE_FORMATS));
 624                                                                 log.debug("set value type: Date");
 625                                                         }
 626                                                 }
 627                                                 // 再パースフラグクリア
 628                                                 reParseFlg = false;
 629                                         }
 630                                         else {
 631                                                 // 元のセルタイプのまま値をセット
 632                                                 throw new ParseException("", 0);
 633                                         }
 634                                 }
 635                                 catch(ParseException e) {
 636                                         // 元のセルタイプのまま値をセット
 637                                         if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
 638                                                 cell.setCellValue(newCellVal);
 639                                                 log.debug("set value type: String");
 640                                         }
 641                                         else {
 642                                                 try {
 643                                                         cell.setCellFormula(newCellVal);
 644                                                         log.debug("set value type: Formula");
 645                                                 }
 646                                                 catch(FormulaParseException e2) {
 647                                                         log.warn("formula set error: {}", e2.getMessage(), e2);
 648                                                         cell.setCellType(Cell.CELL_TYPE_BLANK);
 649                                                         cell.setCellType(Cell.CELL_TYPE_STRING);
 650                                                         cell.setCellValue(newCellVal);
 651                                                         log.debug("set value type: String");
 652                                                 }
 653                                         }
 654                                         // 再パースフラグON
 655                                         reParseFlg = true;
 656                                 }
 657                         }
 658                 }
 659                 // newCellValが空白
 660                 else {
 661                         cell.setCellType(Cell.CELL_TYPE_BLANK);
 662                         // 再パースフラグクリア
 663                         reParseFlg = false;
 664                 }
 665
 666                 log.trace("evaluate end");
 667         }
 668
 669         /**
 670          * Foreach句のパース
 671          */
 672         private void parseForeach() {
 673                 log.trace("parseForeach start");
 674
 675                 Row fromRow, toRow;
 676                 Cell fromCell, toCell;
 677                 Object evalObject;
 678                 Object listObject;
 679                 Object[] mapKeys = null;
 680                 int listSize;
 681                 ArrayList<CellRangeAddress> rangeList = new ArrayList<>();
 682                 CellRangeAddress fromRange, toRange, tmpBfRange, tmpAfRange;
 683
 684                 // リスト
 685                 listObject = elMgr.evaluate(list);
 686                 if(listObject == null) {
 687                         log.debug("list is null eval response.");
 688                         log.trace("parseForeach end");
 689                         return;
 690                 }
 691                 log.debug("list class: {}", listObject.getClass().getName());
 692
 693                 // listObject Mapならキー取得
 694                 if(listObject instanceof Map) {
 695                         mapKeys = ((Map<?, ?>)listObject).keySet().toArray();
 696                 }
 697
 698                 // listのサイズ取得
 699                 evalObject = elMgr.evaluate("size(" + list + ")");
 700                 // evalした結果がintでなければリスト不可なオブジェクトと判断
 701                 if(evalObject == null) {
 702                         log.error("list size unknown");
 703                         log.trace("parseForeach end");
 704                         return;
 705                 }
 706                 else if(!(evalObject instanceof Integer)) {
 707                         log.error("list size is not Integer instance");
 708                         log.trace("parseForeach end");
 709                         return;
 710                 }
 711                 listSize = (int) evalObject;
 712                 log.debug("list size: {}", listSize);
 713
 714                 // start インデックス
 715                 int startIdx;
 716                 if(start != null) {
 717                         log.debug("start: {}", start);
 718                         // size(list) 等 式の可能性もあるのでevalする
 719                         evalObject = elMgr.evaluate(start);
 720                         if(evalObject != null) {
 721                                 // evalした結果がintでなければデフォルト
 722                                 if(!(evalObject instanceof Integer)) {
 723                                         log.warn("start is not Integer instance. start set default: {}", IDX_BASE);
 724                                         startIdx = IDX_BASE;
 725                                 }
 726                                 else {
 727                                         startIdx = (int) evalObject;
 728                                         // startIdxの補正
 729                                         if(startIdx == 0) {
 730                                                 // startIdxが0の場合、1に補正
 731                                                 startIdx = IDX_BASE;
 732                                         }
 733                                         else if(startIdx < 0) {
 734                                                 // startIdxがマイナス値の場合、listのサイズからの逆順とする
 735                                                 // -1 の場合リストのサイズ
 736                                                 startIdx = listSize + startIdx + IDX_BASE;
 737                                         }
 738                                         else if(startIdx > listSize) {
 739                                                 // startIdxがリストのサイズより大きい場合、リストのサイズに切り詰める
 740                                                 startIdx = listSize;
 741                                         }
 742                                 }
 743                         }
 744                         // evalした結果がnullの場合はデフォルト
 745                         else {
 746                                 log.debug("start is null eval response. start set default: {}", IDX_BASE);
 747                                 startIdx = IDX_BASE;
 748                         }
 749                 }
 750                 else {
 751                         log.debug("start is null. start set default: {}", IDX_BASE);
 752                         startIdx = IDX_BASE;
 753                 }
 754                 log.debug("startIdx: {}", startIdx);
 755
 756                 // end インデックス
 757                 int endIdx;
 758                 if(end != null) {
 759                         log.debug("end: {}", end);
 760                         // size(list) 等 式の可能性もあるのでevalする
 761                         evalObject = elMgr.evaluate(end);
 762                         if(evalObject != null) {
 763                                 // evalした結果がintでなければリストサイズ
 764                                 if(!(evalObject instanceof Integer)) {
 765                                         log.warn("end is not Integer instance. end set list size: {}", listSize);
 766                                         endIdx = listSize;
 767                                 }
 768                                 else {
 769                                         endIdx = (int) evalObject;
 770                                         // endIdxの補正
 771                                         if(endIdx == 0) {
 772                                                 // endIdxが0の場合、リストサイズ
 773                                                 endIdx = listSize;
 774                                         }
 775                                         else if(endIdx < 0) {
 776                                                 // endIdxがマイナス値の場合、listのサイズからの逆順とする
 777                                                 // -1 の場合リストのサイズ
 778                                                 endIdx = listSize + endIdx + IDX_BASE;
 779                                         }
 780                                         else if(endIdx > listSize) {
 781                                                 // endIdxがリストのサイズより大きい場合、リストのサイズに切り詰める
 782                                                 endIdx = listSize;
 783                                         }
 784                                 }
 785                         }
 786                         // evalした結果がnullの場合はリストサイズ
 787                         else {
 788                                 log.debug("end is null eval response. end set list size: {}", listSize);
 789                                 endIdx = listSize;
 790                         }
 791                 }
 792                 else {
 793                         log.debug("end is null. end set list size: {}", listSize);
 794                         endIdx = listSize;
 795                 }
 796
 797                 if(log.isDebugEnabled()) {
 798                         log.debug("endIdx: {}", endIdx);
 799                         log.debug("direction: {}", direction);
 800                         log.debug("shift: {}", shift);
 801                         log.debug("block: {}", block);
 802                         log.debug("style: {}", style);
 803                 }
 804
 805                 // コピー元範囲 block:falseの場合、行・列全体に範囲を広げる
 806                 if(!block) {
 807                         // 繰返し方向横（列）（行がデフォルト）
 808                         if(COL.equalsIgnoreCase(direction)) {
 809                                 fromRange = new CellRangeAddress(sheet.getFirstRowNum(), sheet.getLastRowNum(), startColIdx, startColIdx + colSpan - 1);
 810                         }
 811                         // 繰返し方向縦（行）の場合
 812                         else {
 813                                 fromRange = new CellRangeAddress(startRowIdx, startRowIdx + rowSpan - 1, 0, ExcelUtil.getLastColNum(sheet));
 814                         }
 815                 }
 816                 else {
 817                         fromRange = new CellRangeAddress(startRowIdx, startRowIdx + rowSpan - 1, startColIdx, startColIdx + colSpan - 1);
 818                 }
 819                 // シートに収まる範囲にリサイズ
 820                 fromRange = ExcelUtil.getIntersectRange(sheet, fromRange);
 821
 822                 // 繰返しコピー回数１以上なら、コピー先範囲シフト・クリア等実施
 823                 if(startIdx != endIdx) {
 824                         // コピー先範囲の範囲特定
 825                         int copyCount = startIdx < endIdx ? endIdx - startIdx : startIdx - endIdx;
 826                         // 繰返し方向横（列）（行がデフォルト）
 827                         if(COL.equalsIgnoreCase(direction)) {
 828                                 toRange = new CellRangeAddress(
 829                                                 fromRange.getFirstRow(),
 830                                                 fromRange.getLastRow(),
 831                                                 fromRange.getLastColumn() + 1,
 832                                                 fromRange.getLastColumn() + (fromRange.getLastColumn() - fromRange.getFirstColumn() + 1) * copyCount);
 833                         }
 834                         // 繰返し方向縦（行）の場合
 835                         else {
 836                                 toRange = new CellRangeAddress(
 837                                                 fromRange.getLastRow() + 1,
 838                                                 fromRange.getLastRow() + (fromRange.getLastRow() - fromRange.getFirstRow() + 1) * copyCount,
 839                                                 fromRange.getFirstColumn(),
 840                                                 fromRange.getLastColumn());
 841                         }
 842
 843                         // shift:trueの場合、コピー先範囲の後続範囲をシフト
 844                         if(shift) {
 845                                 // 移動量
 846                                 int distance = (startIdx > endIdx ? startIdx - endIdx : endIdx - startIdx);
 847
 848                                 // 繰返し方向横（列）（行がデフォルト）
 849                                 if(COL.equalsIgnoreCase(direction)) {
 850                                         distance *= colSpan;
 851                                         // 繰返し範囲以降をずらす
 852                                         ExcelUtil.shift(sheet, new CellRangeAddress(fromRange.getFirstRow(), fromRange.getLastRow(), fromRange.getLastColumn() + 1, fromRange.getLastColumn() + 1), COL, distance, block);
 853                                 }
 854                                 // 繰返し方向縦（行）の場合
 855                                 else {
 856                                         distance *= rowSpan;
 857                                         // 繰返し範囲以降をずらす
 858                                         ExcelUtil.shift(sheet, new CellRangeAddress(fromRange.getLastRow() + 1, fromRange.getLastRow() + 1, fromRange.getFirstColumn(), fromRange.getLastColumn()), ROW, distance, block);
 859                                 }
 860                         }
 861                         // shift:falseの場合、コピー先範囲をクリア
 862                         else {
 863                                 ExcelUtil.clearRange(sheet, toRange, COPY.equalsIgnoreCase(style), COPY.equalsIgnoreCase(style));
 864                         }
 865                         // style:copyの場合、コピー元範囲の結合保持・解除
 866                         if(COPY.equalsIgnoreCase(style)) {
 867                                 // コピー元範囲の結合状態チェック・保持。コピー先範囲の結合解除
 868                                 CellRangeAddress tmpRange;
 869                                 for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
 870                                         tmpRange = sheet.getMergedRegion(i);
 871                                         // コピー元範囲の結合状態保持・解除
 872                                         switch(CellRangeUtil.intersect(fromRange, tmpRange)) {
 873                                         case CellRangeUtil.INSIDE:
 874                                                 rangeList.add(tmpRange);
 875                                                 log.debug("from range inside mergedRegion. save: {}", tmpRange.formatAsString());
 876                                                 break;
 877                                         case CellRangeUtil.ENCLOSES:
 878                                                 log.warn("from range encloses mergedRegion. remove: {}", tmpRange.formatAsString());
 879                                                 sheet.removeMergedRegion(i);
 880                                                 break;
 881                                         case CellRangeUtil.OVERLAP:
 882                                                 log.warn("from range overlap mergedRegion. remove: {}", tmpRange.formatAsString());
 883                                                 sheet.removeMergedRegion(i);
 884                                                 break;
 885                                         }
 886                                 }
 887                         }
 888                 }
 889
 890                 // 繰返し回数でループしコピー
 891                 boolean copyFlg = true;
 892                 for(int i = startIdx, cpCount = 0; (startIdx <= endIdx && i <= endIdx || startIdx > endIdx && i >= endIdx); i = (startIdx <= endIdx ? i + 1 : i - 1), cpCount++) {
 893                         // 範囲行でループ
 894                         for(int r = fromRange.getFirstRow(); r <= fromRange.getLastRow(); r++) {
 895                                 // 繰返し方向縦（行）の場合、コピー先の範囲チェック
 896                                 if(ROW.equalsIgnoreCase(direction) && (r + rowSpan * cpCount < 0 || r + rowSpan * cpCount > ExcelUtil.getMaxRowIndex(sheet))) {
 897                                         // コピー先行がシート範囲外
 898                                         log.warn("to row outside sheet");
 899                                         // 繰返しコピーフラグfalse
 900                                         copyFlg = false;
 901                                         break;
 902                                 }
 903
 904                                 // コピー元行
 905                                 fromRow = sheet.getRow(r);
 906                                 // コピー先行
 907                                 toRow = ROW.equalsIgnoreCase(direction) ? sheet.getRow(r + rowSpan * cpCount) : fromRow;
 908
 909                                 // コピー元行がnullならコピー先行もnull。continue
 910                                 if(fromRow == null) {
 911                                         if(toRow != null) {
 912                                                 log.debug("continue. from row [{}] is null. to row [{}] remove", (r + 1), (toRow.getRowNum() + 1));
 913                                                 sheet.removeRow(toRow);
 914                                         }
 915                                         else {
 916                                                 log.debug("continue. from row [{}] and to row [{}] is null", (r + 1), ((ROW.equalsIgnoreCase(direction) ? (r + rowSpan * cpCount) : r) + 1));
 917                                         }
 918                                         continue;
 919                                 }
 920
 921                                 // コピー先行がnullなら生成
 922                                 if(toRow == null) toRow = sheet.createRow(r + rowSpan * cpCount);
 923
 924                                 // コピー元行・コピー先行 行番号が異なり、block falseなら行情報(高さ)もコピー
 925                                 if(fromRow.getRowNum() != toRow.getRowNum() && !block) toRow.setHeight(fromRow.getHeight());
 926
 927                                 // 範囲列でループ
 928                                 for(int c = fromRange.getFirstColumn(); c <= fromRange.getLastColumn(); c++) {
 929                                         // 繰返し方向横（列）の場合、コピー先の範囲チェック
 930                                         if(COL.equalsIgnoreCase(direction) && (c + colSpan * cpCount < 0 || c + colSpan * cpCount > ExcelUtil.getMaxColumnIndex(sheet))) {
 931                                                 // コピー先列がシート範囲外
 932                                                 log.warn("to cell outside sheet");
 933                                                 break;
 934                                         }
 935
 936                                         // コピー元セル
 937                                         fromCell = fromRow.getCell(c);
 938                                         // コピー先セル
 939                                         toCell = toRow.getCell(COL.equalsIgnoreCase(direction) ? c + colSpan * cpCount : c);
 940
 941                                         // コピー元セルがnullならコピー先セルもnull
 942                                         if(fromCell == null) {
 943                                                 if(toCell != null) {
 944                                                         log.debug("continue. from cell [{}] is null. to cell [{}] remove", (new CellReference(fromRow.getRowNum(), c)).formatAsString(), (new CellReference(toCell.getRowIndex(), toCell.getColumnIndex())).formatAsString());
 945                                                         toRow.removeCell(toCell);
 946                                                 }
 947                                                 else {
 948                                                         log.debug("continue. from cell [{}] and to cell [{}] is null", (new CellReference(fromRow.getRowNum(), c)).formatAsString(), (new CellReference(toRow.getRowNum(), COL.equalsIgnoreCase(direction) ? c + colSpan * cpCount : c)).formatAsString());
 949                                                 }
 950                                                 continue;
 951                                         }
 952
 953                                         // コピー先セルがnullなら生成
 954                                         if(toCell == null) toCell = toRow.createCell(COL.equalsIgnoreCase(direction) ? c + colSpan * cpCount : c);
 955
 956                                         // コピー元列・コピー先列 列番号が異なり、block falseなら列情報(幅)もコピー  （ 繰返しループ１回目のみ）
 957                                         if(cpCount == 0 && fromCell.getColumnIndex() != toCell.getColumnIndex() && !block)
 958                                                 sheet.setColumnWidth(toCell.getColumnIndex(), sheet.getColumnWidth(fromCell.getColumnIndex()));
 959
 960                                         // スタイルコピーなら
 961                                         if(cpCount > 0 && COPY.equalsIgnoreCase(style)) {
 962                                                 // スタイルのコピー
 963                                                 toCell.setCellStyle(fromCell.getCellStyle());
 964                                         }
 965                                         // 値のコピー
 966                                         switch(fromCell.getCellType()) {
 967                                         case Cell.CELL_TYPE_BLANK:
 968                                                 toCell.setCellType(Cell.CELL_TYPE_BLANK);
 969                                                 break;
 970                                         case Cell.CELL_TYPE_BOOLEAN:
 971                                                 toCell.setCellType(Cell.CELL_TYPE_BOOLEAN);
 972                                                 toCell.setCellValue(fromCell.getBooleanCellValue());
 973                                                 break;
 974                                         case Cell.CELL_TYPE_ERROR:
 975                                                 toCell.setCellType(Cell.CELL_TYPE_ERROR);
 976                                                 toCell.setCellErrorValue(fromCell.getErrorCellValue());
 977                                                 break;
 978                                         case Cell.CELL_TYPE_FORMULA:
 979                                                 toCell.setCellType(Cell.CELL_TYPE_FORMULA);
 980                                                 // 数式のパース・移動先に合わせる
 981                                                 toCell.setCellFormula(
 982                                                                 ExcelUtil.getMoveFormula(
 983                                                                                 sheet,
 984                                                                                 fromCell.getCellFormula().replace(
 985                                                                                                 (cpCount == 0 ? object : list + "[" + (mapKeys == null ? (startIdx - 1) : "'" + mapKeys[startIdx - 1] + "'") + "]"), list + "[" + (mapKeys == null ? (i - 1) : "'" + mapKeys[i - 1] + "'") + "]"),
 986                                                                                 toCell.getRowIndex() - fromCell.getRowIndex(),
 987                                                                                 toCell.getColumnIndex() - fromCell.getColumnIndex()
 988                                                                 )
 989                                                 );
 990                                                 log.debug("to cell Formula: {}", toCell.getCellFormula());
 991                                                 break;
 992                                         case Cell.CELL_TYPE_NUMERIC:
 993                                                 toCell.setCellType(Cell.CELL_TYPE_NUMERIC);
 994                                                 toCell.setCellValue(fromCell.getNumericCellValue());
 995                                                 break;
 996                                         case Cell.CELL_TYPE_STRING:
 997                                                 toCell.setCellType(Cell.CELL_TYPE_STRING);
 998                                                 toCell.setCellValue(fromCell.getStringCellValue().replace((cpCount == 0 ? object : list + "[" + (mapKeys == null ? (startIdx - 1) : "'" + mapKeys[startIdx - 1] + "'") + "]"), list + "[" + (mapKeys == null ? (i - 1) : "'" + mapKeys[i - 1] + "'") + "]"));
 999                                                 log.debug("to cell string: {}", toCell.getStringCellValue());
1000                                                 break;
1001                                         }
1002                                 }
1003                         }
1004                         // 繰返しコピーフラグfalseならbreak
1005                         if(!copyFlg) break;
1006                 }
1007 //              // コピー元範囲を繰返しコピー
1008 //              // 範囲行でループ
1009 //              for(int r = fromRange.getFirstRow(); r <= fromRange.getLastRow(); r++) {
1010 //                      row = sheet.getRow(r);
1011 //                      // コピー元行がnullで繰返し方向横（列）の場合
1012 //                      if(row == null && COL.equalsIgnoreCase(direction)) {
1013 //                              // コピー先行もnullとなるのでセルの処理はせず終了
1014 //                              log.debug("continue. direction: col from row [{}] is null", (r + 1));
1015 //                              continue;
1016 //                      }
1017 //
1018 //                      // 範囲列でループ
1019 //                      for(int c = fromRange.getFirstColumn(); c <= fromRange.getLastColumn(); c++) {
1020 //                              // コピー元行がnullの場合セルもnull
1021 //                              if(row == null) {
1022 //                                      cell = null;
1023 //                              } else {
1024 //                                      cell = row.getCell(c);
1025 //                              }
1026 //
1027 //                              // 繰返し回数でループ
1028 //                              for(int i = startIdx, cpCount = 0; (startIdx <= endIdx && i <= endIdx || startIdx > endIdx && i >= endIdx); i = (startIdx <= endIdx ? i + 1 : i - 1), cpCount++) {
1029 //                                      // 繰返し方向横（列）（行がデフォルト）
1030 //                                      if(COL.equalsIgnoreCase(direction)) {
1031 //                                              // 範囲チェック
1032 //                                              if(c + colSpan * cpCount < 0 || c + colSpan * cpCount > ExcelUtil.getMaxColumnIndex(sheet)) {
1033 //                                                      log.warn("to cell outside sheet");
1034 //                                                      break;
1035 //                                              }
1036 //                                              toRow = row;
1037 //                                              toCell = row.getCell(c + colSpan * cpCount);
1038 //                                              // コピー元セルがnullならコピー先セルもnull
1039 //                                              if(cell == null) {
1040 //                                                      if(toCell != null) {
1041 //                                                              toRow.removeCell(toCell);
1042 //                                                              log.debug("continue. from cell [{}] is null. to cell [{}] remove", (new CellReference(r, c)).formatAsString(), (new CellReference(r, c + colSpan * cpCount)).formatAsString());
1043 //                                                      }
1044 //                                                      else {
1045 //                                                              log.debug("continue. from cell [{}] and to cell [{}] is null", (new CellReference(r, c)).formatAsString(), (new CellReference(r, c + colSpan * cpCount)).formatAsString());
1046 //                                                      }
1047 //                                                      continue;
1048 //                                              }
1049 //                                              else {
1050 //                                                      if(toCell == null) toCell = row.createCell(c + colSpan * cpCount);
1051 //                                                      // block falseなら列情報(幅)もコピー
1052 //                                                      if(!block) {
1053 //                                                              sheet.setColumnWidth(toCell.getColumnIndex(), sheet.getColumnWidth(cell.getColumnIndex()));
1054 //                                                      }
1055 //                                              }
1056 //                                      }
1057 //                                      // 繰返し方向縦（行）の場合
1058 //                                      else {
1059 //                                              // 範囲チェック
1060 //                                              if(r + rowSpan * cpCount < 0 || r + rowSpan * cpCount > ExcelUtil.getMaxRowIndex(sheet)) {
1061 //                                                      log.warn("to row outside sheet");
1062 //                                                      break;
1063 //                                              }
1064 //                                              // 繰返し先の行がなければ生成
1065 //                                              toRow = sheet.getRow(r + rowSpan * cpCount);
1066 //                                              toCell = null;
1067 //                                              // コピー元行がnullならコピー先行もnull
1068 //                                              if(row == null) {
1069 //                                                      if(toRow != null) {
1070 //                                                              sheet.removeRow(toRow);
1071 //                                                              log.debug("continue. from row [{}] is null. to row [{}] remove", (r + 1));
1072 //                                                      }
1073 //                                                      else {
1074 //                                                              log.debug("continue. from and to row is null");
1075 //                                                      }
1076 //                                                      continue;
1077 //                                              }
1078 //                                              else {
1079 //                                                      if(toRow == null) toRow = sheet.createRow(r + rowSpan * cpCount);
1080 //                                                      // block falseなら行情報(高さ)もコピー
1081 //                                                      if(!block) {
1082 //                                                              toRow.setHeight(row.getHeight());
1083 //                                                      }
1084 //                                                      toCell = toRow.getCell(c);
1085 //                                                      // コピー元セルがnullならコピー先セルもnull
1086 //                                                      if(cell == null) {
1087 //                                                              if(toCell != null) {
1088 //                                                                      toRow.removeCell(toCell);
1089 //                                                                      log.debug("continue. from cell is null. to cell remove");
1090 //                                                              }
1091 //                                                              else {
1092 //                                                                      log.debug("continue. from cell [{}] and to cell [{}] is null", (new CellReference(r, c)).formatAsString(), (new CellReference(r + rowSpan * cpCount, c)).formatAsString());
1093 //                                                              }
1094 //                                                              continue;
1095 //                                                      }
1096 //                                                      else {
1097 //                                                              if(toCell == null) toCell = toRow.createCell(c);
1098 //                                                      }
1099 //                                              }
1100 //                                      }
1101 //
1102 //                                      // スタイルコピーなら
1103 //                                      if(cpCount > 0 && COPY.equalsIgnoreCase(style)) {
1104 //                                              // スタイルのコピー
1105 //                                              toCell.setCellStyle(cell.getCellStyle());
1106 //                                      }
1107 //                                      // 値のコピー
1108 //                                      switch(cell.getCellType()) {
1109 //                                      case Cell.CELL_TYPE_BLANK:
1110 //                                              toCell.setCellType(Cell.CELL_TYPE_BLANK);
1111 //                                              break;
1112 //                                      case Cell.CELL_TYPE_BOOLEAN:
1113 //                                              toCell.setCellType(Cell.CELL_TYPE_BOOLEAN);
1114 //                                              toCell.setCellValue(cell.getBooleanCellValue());
1115 //                                              break;
1116 //                                      case Cell.CELL_TYPE_ERROR:
1117 //                                              toCell.setCellType(Cell.CELL_TYPE_ERROR);
1118 //                                              toCell.setCellErrorValue(cell.getErrorCellValue());
1119 //                                              break;
1120 //                                      case Cell.CELL_TYPE_FORMULA:
1121 //                                              toCell.setCellType(Cell.CELL_TYPE_FORMULA);
1122 //                                              // 数式のパース・移動先に合わせる
1123 //                                              toCell.setCellFormula(
1124 //                                                              ExcelUtil.getMoveFormula(
1125 //                                                                              sheet,
1126 //                                                                              cell.getCellFormula().replace(
1127 //                                                                                              (i == startIdx ? object : list + "[" + (mapKeys == null ? (startIdx - 1) : "'" + mapKeys[startIdx - 1] + "'") + "]"), list + "[" + (mapKeys == null ? (i - 1) : "'" + mapKeys[i - 1] + "'") + "]"),
1128 //                                                                              toCell.getRowIndex() - cell.getRowIndex(),
1129 //                                                                              toCell.getColumnIndex() - cell.getColumnIndex()
1130 //                                                              )
1131 //                                              );
1132 //                                              log.debug("to cell Formula: {}", toCell.getCellFormula());
1133 //                                              break;
1134 //                                      case Cell.CELL_TYPE_NUMERIC:
1135 //                                              toCell.setCellType(Cell.CELL_TYPE_NUMERIC);
1136 //                                              toCell.setCellValue(cell.getNumericCellValue());
1137 //                                              break;
1138 //                                      case Cell.CELL_TYPE_STRING:
1139 //                                              toCell.setCellType(Cell.CELL_TYPE_STRING);
1140 //                                              toCell.setCellValue(cell.getStringCellValue().replace((i == startIdx ? object : list + "[" + (mapKeys == null ? (startIdx - 1) : "'" + mapKeys[startIdx - 1] + "'") + "]"), list + "[" + (mapKeys == null ? (i - 1) : "'" + mapKeys[i - 1] + "'") + "]"));
1141 //                                              log.debug("to cell string: {}", toCell.getStringCellValue());
1142 //                                              break;
1143 //                                      }
1144 //                              }
1145 //                      }
1146 //              }
1147                 // スタイルコピーなら
1148                 if(COPY.equalsIgnoreCase(style)) {
1149                         // 行列加算用
1150                         int addRow = 0;
1151                         int addCol = 0;
1152                         if(COL.equalsIgnoreCase(direction)) {
1153                                 addCol = colSpan;
1154                         }
1155                         else {
1156                                 addRow = rowSpan;
1157                         }
1158
1159                         // コピー元範囲の結合保持なら
1160                         if(!rangeList.isEmpty()) {
1161                                 // 結合をコピー
1162                                 // 結合保持数でループ
1163                                 for(int c = 0; c < rangeList.size(); c++) {
1164                                         tmpBfRange = rangeList.get(c);
1165                                         // 繰返し回数でループ
1166                                         for(int i = startIdx, cpCount = 0; (startIdx <= endIdx && i <= endIdx || startIdx > endIdx && i >= endIdx); i = (startIdx <= endIdx ? i + 1 : i - 1), cpCount++) {
1167                                                 // 初回はコピーしない（コピー元だから）
1168                                                 if(i == startIdx) continue;
1169                                                 // コピー先レンジ
1170                                                 tmpAfRange = new CellRangeAddress(
1171                                                                 tmpBfRange.getFirstRow() + (addRow * cpCount),
1172                                                                 tmpBfRange.getLastRow() + (addRow * cpCount),
1173                                                                 tmpBfRange.getFirstColumn() + (addCol * cpCount),
1174                                                                 tmpBfRange.getLastColumn() + (addCol * cpCount));
1175                                                 if(ExcelUtil.validateRange(sheet, tmpAfRange)) {
1176                                                         sheet.addMergedRegion(tmpAfRange);
1177                                                         log.debug("mergedRegion copy. from: [{}] to: [{}]", tmpBfRange.formatAsString(), tmpAfRange.formatAsString());
1178                                                 }
1179                                         }
1180                                 }
1181                         }
1182 /**
1183                         // オブジェクトのコピー
1184                         xls形式非対応
1185                         if(sheet instanceof HSSFSheet) {
1186                                 CellRangeAddress cpRange;
1187                                 HSSFShape cpShape;
1188                                 HSSFPatriarch patriarch = ((HSSFSheet)sheet).getDrawingPatriarch();
1189                                 if(patriarch != null) {
1190                                         List<HSSFShape> shapes = ((HSSFSheet)sheet).getDrawingPatriarch().getChildren();
1191                                         if(shapes != null) {
1192                                                 HSSFClientAnchor fAnchor, tAnchor;
1193                                                 HSSFShape tShape;
1194                                                 int r1, r2;
1195                                                 short c1, c2;
1196                                                 for (int i = 0; i < shapes.size(); i++) {
1197                                                         fAnchor = (HSSFClientAnchor)shapes.get(i).getAnchor();
1198                                                         if(fAnchor != null) {
1199                                                                 r1 = fAnchor.getRow1();
1200                                                                 c1 = fAnchor.getCol1();
1201                                                                 r2 = fAnchor.getRow2();
1202                                                                 c2 = fAnchor.getCol2();
1203                                                                 tmpRange = new CellRangeAddress(r1, r2, c1, c2);
1204                                                                 switch(CellRangeUtil.intersect(fRange, tmpRange)) {
1205                                                                 case CellRangeUtil.INSIDE:
1206                                                                 case CellRangeUtil.OVERLAP:
1207                                                                         // 繰返し方向縦（行）でない場合
1208                                                                         if(!ROW.equalsIgnoreCase(direction)) {
1209                                                                                 tAnchor = new HSSFClientAnchor(
1210                                                                                                 fAnchor.getDx1(), fAnchor.getDy1(),
1211                                                                                                 fAnchor.getDx2(), fAnchor.getDy2(),
1212                                                                                                 (short) (c1 + (colSpan * cpCount)), r1,
1213                                                                                                 (short) (c2 + (colSpan * cpCount)), r2);
1214                                                                                 cpRange = new CellRangeAddress(r1, r2, c1 + (colSpan * cpCount), c2 + (colSpan * cpCount));
1215                                                                         }
1216                                                                         // 繰返し方向縦（行）の場合
1217                                                                         else {
1218                                                                                 tAnchor = new HSSFClientAnchor(
1219                                                                                                 fAnchor.getDx1(), fAnchor.getDy1(),
1220                                                                                                 fAnchor.getDx2(), fAnchor.getDy2(),
1221                                                                                                 c1, r1 + (rowSpan * cpCount),
1222                                                                                                 c2, r2 + (rowSpan * cpCount));
1223                                                                                 cpRange = new CellRangeAddress(r1 + (rowSpan * cpCount), r2 + (rowSpan * cpCount), c1, c2);
1224                                                                         }
1225                                                                         if(log.isDebugEnabled()) log.debug("object copy from: " + tmpRange.formatAsString() + " to: "  + cpRange.formatAsString());
1226                                                                         HSSFShape = new
1227                                                                         patriarch.addShape(new shapes.get(i),)
1228                                                                 }
1229                                                         }
1230                                                 }
1231                                         }
1232                                 }
1233                         }
1234 */
1235                         // オブジェクトのコピー Excel2007以降 ooxml形式のみ対応
1236                         if(sheet instanceof XSSFSheet) {
1237                                 int r1, c1, r2, c2;
1238                                 CTDrawing ctDrawing;
1239                                 CTTwoCellAnchor fAnchor, cpAnchor;
1240                                 CTMarker from, to;
1241                                 try {
1242                                         // DocumentPartでループ
1243                                         for(POIXMLDocumentPart part : ((XSSFSheet)sheet).getRelations()) {
1244                                                 if(part == null) continue;
1245                                                 log.debug("DocumentPart class: {}", part.getClass().getName());
1246                                                 // DocumentPartがDrawingオブジェクトの場合
1247                                                 if(part instanceof XSSFDrawing) {
1248                                                         ctDrawing = ((XSSFDrawing) part).getCTDrawing();
1249                                                         if(ctDrawing != null) {
1250                                                                 // アンカーでループ
1251                                                                 int alSize = ctDrawing.getTwoCellAnchorList().size();
1252                                                                 for (int i = 0; i < alSize; i++) {
1253                                                                         fAnchor = ctDrawing.getTwoCellAnchorList().get(i);
1254                                                                         // GraphicFrameをもつグラフ、スマートアートは簡単にはコピーできないので非対応
1255                                                                         if(fAnchor.isSetGraphicFrame()) continue;
1256
1257                                                                         // アンカーの位置情報
1258                                                                         from = fAnchor.getFrom();
1259                                                                         r1 = from.getRow();
1260                                                                         c1 = from.getCol();
1261                                                                         to = fAnchor.getTo();
1262                                                                         r2 = to.getRow();
1263                                                                         c2 = to.getCol();
1264                                                                         tmpBfRange = new CellRangeAddress(r1, r2, c1, c2);
1265
1266                                                                         // コピー元レンジに含まれている、掛かっているならコピー
1267                                                                         switch(CellRangeUtil.intersect(fromRange, tmpBfRange)) {
1268                                                                         case CellRangeUtil.INSIDE:
1269                                                                         case CellRangeUtil.OVERLAP:
1270                                                                                 // 繰返し回数でループ
1271                                                                                 for(int j = startIdx, cpCount = 0; (startIdx <= endIdx && j <= endIdx || startIdx > endIdx && j >= endIdx); j = (startIdx <= endIdx ? j + 1 : j - 1), cpCount++) {
1272                                                                                         // 初回はコピーしない（コピー元だから）
1273                                                                                         if(j == startIdx) continue;
1274                                                                                         // TODO これでオブジェクト（アンカー）のコピーができる！！
1275                                                                                         cpAnchor = ctDrawing.addNewTwoCellAnchor();
1276                                                                                         cpAnchor.set(fAnchor.copy());
1277                                                                                         from = cpAnchor.getFrom();
1278                                                                                         from.setRow(from.getRow() + (addRow * cpCount));
1279                                                                                         from.setCol(from.getCol() + (addCol * cpCount));
1280                                                                                         to = cpAnchor.getTo();
1281                                                                                         to.setRow(to.getRow() + (addRow * cpCount));
1282                                                                                         to.setCol(to.getCol() + (addCol * cpCount));
1283                                                                                         if(log.isDebugEnabled()) {
1284                                                                                                 tmpAfRange = new CellRangeAddress(from.getRow(), to.getRow(), from.getCol(), to.getCol());
1285                                                                                                 log.debug("object copy from: [{}] to: [{}]", tmpBfRange.formatAsString(), tmpAfRange.formatAsString());
1286                                                                                         }
1287                                                                                 }
1288                                                                         }
1289                                                                 }
1290                                                         }
1291                                                 }
1292                                         }
1293                                 }
1294                                 catch(Exception e) {
1295                                         log.error("object copy error: " + e.getMessage(), e);
1296                                 }
1297                         }
1298                 }
1299                 log.trace("parseForeach end");
1300         }
1301
1302         /**
1303          * If句のパース
1304          */
1305         private void parseIf() {
1306                 log.trace("parseIf start");
1307
1308                 // 判定
1309                 boolean result;
1310                 Object elResult = elMgr.evaluate(expression);
1311                 if(elResult == null) {
1312                         log.debug("evaluate result is null");
1313                         result = false;
1314                 }
1315                 else if(elResult instanceof Boolean) {
1316                         result = (boolean) elResult;
1317                 }
1318                 else {
1319                         result = true;
1320                 }
1321                 log.debug("evaluate result: {}", result);
1322
1323                 // 判定falseの場合、範囲の操作
1324                 if(!result) {
1325                         if(delete == null) {
1326                                 log.debug("{} is null set default: {}", TLP_DELETE, LEFT);
1327                                 delete = LEFT;
1328                         }
1329                         else if(!LEFT.equalsIgnoreCase(delete) && !UP.equalsIgnoreCase(delete) && !CLEAR.equalsIgnoreCase(delete)) {
1330                                 log.warn("{} is illegal argument. set default: {}", TLP_DELETE, LEFT);
1331                                 delete = LEFT;
1332                         }
1333                         else {
1334                                 log.debug("delete: {}", delete);
1335                                 delete = delete.toLowerCase().trim();
1336                         }
1337
1338                         switch(delete) {
1339                         // クリア
1340                         case CLEAR:
1341 //                              ExcelUtil.clearRange(sheet, (new CellRangeAddress(startRowIdx, startRowIdx + rowSpan - 1, startColIdx, startColIdx + colSpan - 1)), true, true);
1342                                 ExcelUtil.clearRange(sheet, (new CellRangeAddress(startRowIdx, startRowIdx + rowSpan - 1, startColIdx, startColIdx + colSpan - 1)), false, false);
1343                                 break;
1344                         // 左詰め
1345                         case LEFT:
1346                                 // IF範囲以降をずらす
1347                                 ExcelUtil.shift(sheet, new CellRangeAddress(startRowIdx, startRowIdx + rowSpan - 1, startColIdx + colSpan, startColIdx + colSpan), COL, -colSpan, block);
1348                                 break;
1349                         // 上詰め
1350                         case UP:
1351                                 // IF範囲以降をずらす
1352                                 ExcelUtil.shift(sheet, new CellRangeAddress(startRowIdx + rowSpan, startRowIdx + rowSpan, startColIdx, startColIdx + colSpan - 1), ROW, -rowSpan, block);
1353                         }
1354                 }
1355                 log.trace("parseIf end");
1356         }
1357
1358         /**
1359          * マッチした結果がEL式であるかを返却
1360          *
1361          * @return      マッチした結果がEL式であればtrue。以外の場合false
1362          */
1363         private boolean isEL() {
1364                 return EL.equalsIgnoreCase(type);
1365         }
1366
1367         /**
1368          * マッチした結果がIf句であるかを返却
1369          *
1370          * @return      マッチした結果がIf句であればtrue。以外の場合false
1371          */
1372         private boolean isIf() {
1373                 return TL_IF.equalsIgnoreCase(type);
1374         }
1375
1376         /**
1377          * マッチした結果がForeach句であるかを返却
1378          *
1379          * @return      マッチした結果がForeach句であればtrue。以外の場合false
1380          */
1381         private boolean isForeach() {
1382                 return TL_FOREACH.equalsIgnoreCase(type);
1383         }
1384
1385         /**
1386          * 指示子全体検索結果から１つ目の指示子を置換
1387          *
1388          * @param replacement   置換文字列
1389          *
1390          * @return      置換後の文字列
1391          */
1392         private String replaceFirst(String replacement) {
1393                 return mtAll.replaceFirst(replacement == null ? "" : replacement);
1394         }
1395
1396         /**
1397          * 同一セルの再パース要否を返却
1398          *
1399          * @return 再パース必要であればはtrue。不要であればfalse
1400          */
1401         public boolean isReParseCell() {
1402                 return reParseFlg;
1403         }
1404 }