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