Jaxcel/src/org/hanei/jaxcel/util/ExcelUtil.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.util;
  20
  21 import java.lang.reflect.Field;
  22 import java.util.ArrayList;
  23 import java.util.List;
  24 import java.util.regex.Matcher;
  25 import java.util.regex.Pattern;
  26 import java.util.regex.PatternSyntaxException;
  27
  28
  29 import org.apache.poi.POIXMLDocumentPart;
  30 import org.apache.poi.hssf.record.cf.CellRangeUtil;
  31 import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;
  32 import org.apache.poi.hssf.usermodel.HSSFSheet;
  33 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  34 import org.apache.poi.ss.SpreadsheetVersion;
  35 import org.apache.poi.ss.formula.FormulaParser;
  36 import org.apache.poi.ss.formula.FormulaParsingWorkbook;
  37 import org.apache.poi.ss.formula.FormulaRenderer;
  38 import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
  39 import org.apache.poi.ss.formula.FormulaType;
  40 import org.apache.poi.ss.formula.ptg.AreaPtg;
  41 import org.apache.poi.ss.formula.ptg.Ptg;
  42 import org.apache.poi.ss.formula.ptg.RefPtg;
  43 import org.apache.poi.ss.usermodel.Cell;
  44 import org.apache.poi.ss.usermodel.Row;
  45 import org.apache.poi.ss.usermodel.Sheet;
  46 import org.apache.poi.ss.util.CellRangeAddress;
  47 import org.apache.poi.ss.util.CellReference;
  48 import org.apache.poi.xssf.streaming.SXSSFSheet;
  49 import org.apache.poi.xssf.usermodel.XSSFDrawing;
  50 import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
  51 import org.apache.poi.xssf.usermodel.XSSFHyperlink;
  52 import org.apache.poi.xssf.usermodel.XSSFSheet;
  53 import org.apache.poi.xssf.usermodel.XSSFTable;
  54 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  55 import org.hanei.jaxcel.exception.JaxcelInputException;
  56 import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTDrawing;
  57 import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
  58 import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;
  59 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter;
  60 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
  61 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTablePart;
  62 import org.slf4j.LoggerFactory;
  63 import org.slf4j.Logger;
  64
  65 /**
  66  * Excelユーティリティクラス
  67  *
  68  * @since 1.00.00
  69  * @author Noboru Saito
  70  */
  71 public class ExcelUtil {
  72
  73         private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
  74
  75         /** 範囲判定 */
  76         public static final String INSIDE = "inside";
  77         public static final String OVERLAP = "overlap";
  78         public static final String ENCLOSES = "encloses";
  79         public static final String NO_INTERSECTION = "no_intersection";
  80
  81         /**
  82          * シートの最大行数を返却
  83          *
  84          * @param sheet Worksheetオブジェクト
  85          *
  86          * @return シートの最大行数
  87          *
  88          * @throws JaxcelInputException 入力例外発生時
  89          */
  90         public static int getMaxRowIndex(Sheet sheet) {
  91                 log.trace("getMaxRowIndex start");
  92
  93                 if(sheet == null) {
  94                         log.error("sheet is null");
  95                         throw new JaxcelInputException("sheet is null");
  96                 }
  97                 int max;
  98                 if(sheet instanceof HSSFSheet) {
  99                         max = SpreadsheetVersion.EXCEL97.getLastRowIndex();
 100                 }
 101                 else if(sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet){
 102                         max = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
 103                 }
 104                 else {
 105                         log.error("sheet is unsupported type");
 106                         throw new JaxcelInputException("sheet is unsupported type");
 107                 }
 108
 109                 log.trace("getMaxRowIndex end: {}", max);
 110                 return max;
 111         }
 112
 113         /**
 114          * シートの最大列数を返却
 115          *
 116          * @param sheet Worksheetオブジェクト
 117          *
 118          * @return シートの最大行数
 119          *
 120          * @throws JaxcelInputException 入力例外発生時
 121          */
 122         public static int getMaxColumnIndex(Sheet sheet) {
 123                 log.trace("getMaxColumnIndex start");
 124
 125                 if(sheet == null) {
 126                         log.error("sheet is null");
 127                         throw new JaxcelInputException("sheet is null");
 128                 }
 129                 int max;
 130                 if(sheet instanceof HSSFSheet) {
 131                         max = SpreadsheetVersion.EXCEL97.getLastColumnIndex();
 132                 }
 133                 else if(sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet){
 134                         max = SpreadsheetVersion.EXCEL2007.getLastColumnIndex();
 135                 }
 136                 else {
 137                         log.error("sheet is unsupported type");
 138                         throw new JaxcelInputException("sheet is null");
 139                 }
 140
 141                 log.trace("getMaxColumnIndex end: {}", max);
 142                 return max;
 143         }
 144
 145         /**
 146          * 範囲の妥当性チェック
 147          *
 148          * @param sheet Worksheetオブジェクト
 149          * @param range 対象範囲
 150          *
 151          * @return 判定結果
 152          *
 153          * @throws JaxcelInputException 入力例外発生時
 154          */
 155         public static boolean validateRange(Sheet sheet, CellRangeAddress range) {
 156                 log.trace("validateRange start");
 157
 158                 boolean ret = validateColumn(sheet, range) && validateRow(sheet, range);
 159
 160                 log.trace("validateRange end: {}", ret);
 161                 return ret;
 162         }
 163
 164         /**
 165          * 範囲の列方向妥当性チェック
 166          *
 167          * @param sheet Worksheetオブジェクト
 168          * @param range 対象範囲
 169          *
 170          * @return 判定結果
 171          *
 172          * @throws JaxcelInputException 入力例外発生時
 173          */
 174         public static boolean validateColumn(Sheet sheet, CellRangeAddress range) {
 175                 log.trace("validateColumn start");
 176
 177                 if(range == null) {
 178                         log.error("range is null");
 179                         throw new JaxcelInputException("range is null");
 180                 }
 181                 boolean ret = false;
 182                 int max = getMaxColumnIndex(sheet);
 183                 int firstColumn = range.getFirstColumn();
 184                 int lastColumn = range.getLastColumn();
 185
 186                 if((isFullRowRange(sheet, range)) || (firstColumn <= lastColumn && firstColumn >= 0 && firstColumn <= max && lastColumn >= 0 && lastColumn <= max)){
 187                         ret = true;
 188                 }
 189
 190                 log.trace("validateColumn end: {}", ret);
 191                 return ret;
 192         }
 193
 194         /**
 195          * 範囲の行方向妥当性チェック
 196          *
 197          * @param sheet Worksheetオブジェクト
 198          * @param range 対象範囲
 199          *
 200          * @return 判定結果
 201          *
 202          * @throws JaxcelInputException 入力例外発生時
 203          */
 204         public static boolean validateRow(Sheet sheet, CellRangeAddress range) {
 205                 log.trace("validateRow start");
 206
 207                 if(range == null) {
 208                         log.error("range is null");
 209                         throw new JaxcelInputException("range is null");
 210                 }
 211                 boolean ret = false;
 212                 int max = getMaxRowIndex(sheet);
 213                 int firstRow = range.getFirstRow();
 214                 int lastRow = range.getLastRow();
 215
 216                 if((isFullColumnRange(sheet, range)) || (firstRow <= lastRow && firstRow >= 0 && firstRow <= max && lastRow >= 0 && lastRow <= max)){
 217                         ret = true;
 218                 }
 219
 220                 log.trace("validateRow end: {}", ret);
 221                 return ret;
 222         }
 223
 224         /**
 225          * 行全体を指定している範囲であるかの判定
 226          *
 227          * @param sheet Worksheetオブジェクト
 228          * @param range 対象範囲
 229          *
 230          * @return 判定結果
 231          *
 232          * @throws JaxcelInputException 入力例外発生時
 233          */
 234         public static boolean isFullRowRange(Sheet sheet, CellRangeAddress range) {
 235                 log.trace("isFullRowRange start");
 236
 237                 if(range == null) {
 238                         log.error("range is null");
 239                         throw new JaxcelInputException("range is null");
 240                 }
 241                 boolean ret = false;
 242                 int max = getMaxColumnIndex(sheet);
 243                 int firstColumn = range.getFirstColumn();
 244                 int lastColumn = range.getLastColumn();
 245
 246                 if(log.isDebugEnabled()) {
 247                         log.debug("sheet max column: {}", max);
 248                         log.debug("range first column: {}", firstColumn);
 249                         log.debug("range last column: {}", lastColumn);
 250                 }
 251
 252                 if((firstColumn == 0 && lastColumn == max) || (firstColumn == -1 && lastColumn == -1)) {
 253                         ret = true;
 254                 }
 255
 256                 log.trace("isFullRowRange end: {}", ret);
 257                 return ret;
 258         }
 259
 260         /**
 261          * 列全体を指定している範囲であるかの判定
 262          *
 263          * @param sheet Worksheetオブジェクト
 264          * @param range 対象範囲
 265          *
 266          * @return 判定結果
 267          */
 268         public static boolean isFullColumnRange(Sheet sheet, CellRangeAddress range) {
 269                 log.trace("isFullColumnRange start");
 270
 271                 if(range == null) {
 272                         log.error("range is null");
 273                         throw new JaxcelInputException("range is null");
 274                 }
 275                 boolean ret = false;
 276                 int max = getMaxRowIndex(sheet);
 277                 int firstRow = range.getFirstRow();
 278                 int lastRow = range.getLastRow();
 279
 280                 if(log.isDebugEnabled()) {
 281                         log.debug("sheet max row: {}", max);
 282                         log.debug("range first row: {}", firstRow);
 283                         log.debug("range last row: {}", lastRow);
 284                 }
 285
 286                 if((firstRow == 0 && lastRow == max) || (firstRow == -1 && lastRow == -1)) {
 287                         ret = true;
 288                 }
 289
 290                 log.trace("isFullColumnRange end: {}", ret);
 291                 return ret;
 292         }
 293
 294         /**
 295          * シートの最大列数を返却
 296          *
 297          * @param sheet Worksheetオブジェクト
 298          *
 299          * @return シートの最大列数。<br>
 300          * sheetに行データが存在しない場合、または、すべての行にセルが存在しない場合は -1
 301          *
 302          * @throws JaxcelInputException 入力例外発生時
 303          */
 304         public static int getLastColNum(Sheet sheet) {
 305                 log.trace("getLastColNum start");
 306
 307                 if(sheet == null) {
 308                         log.error("sheet is null");
 309                         throw new JaxcelInputException("sheet is null");
 310                 }
 311                 int maxColNum = getLastColNum(sheet, sheet.getFirstRowNum(), sheet.getLastRowNum());
 312
 313                 log.trace("getLastColNum end: {}", maxColNum);
 314                 return maxColNum;
 315         }
 316
 317         /**
 318          * シート指定行範囲の最大列数を返却
 319          *
 320          * @param sheet Worksheetオブジェクト
 321          * @param firstRow 範囲始点行番号（０起点）
 322          * @param lastRow 範囲終点行番号（０起点）
 323          *
 324          * @return シート指定列範囲の最大列数。<br>
 325          * sheetの指定範囲に行データが存在しない場合は -1
 326          * sheetの指定範囲に行データが存在しない場合、または、指定範囲のすべての行にセルが存在しない場合は -1
 327          *
 328          * @throws JaxcelInputException 入力例外発生時
 329          */
 330         public static int getLastColNum(Sheet sheet, int firstRow, int lastRow) {
 331                 log.trace("getLastColNum start");
 332
 333                 if(sheet == null) {
 334                         log.error("sheet is null");
 335                         throw new JaxcelInputException("sheet is null");
 336                 }
 337
 338                 int fRow = firstRow;
 339                 int lRow = lastRow;
 340
 341                 if(fRow == -1 && lRow == -1) {
 342                         log.info("firstRow and lastRow is -1. set full row range");
 343                         fRow = 0;
 344                         lRow = sheet.getLastRowNum();
 345                 }
 346                 else if(fRow < 0) {
 347                         log.error("firstRow is lt 0: {}", fRow);
 348                         throw new JaxcelInputException("firstRow is lt 0");
 349                 }
 350                 else if(lRow < 0) {
 351                         log.error("lastRow is lt 0: {}", lRow);
 352                         throw new JaxcelInputException("lastRow is lt 0");
 353                 }
 354                 if(fRow > lRow) {
 355                         log.warn("firstRow gt lastRow: {}, {}. swap", fRow, lRow);
 356                         int tmp = fRow;
 357                         fRow = lRow;
 358                         lRow = tmp;
 359                 }
 360
 361                 Row row;
 362                 int maxColNum = -1;
 363                 for(int i = fRow; i <= lRow; i++) {
 364                         row = sheet.getRow(i);
 365                         if(row == null) continue;
 366                         maxColNum = (row.getLastCellNum() > maxColNum ? row.getLastCellNum() : maxColNum);
 367                 }
 368
 369                 log.trace("getLastColNum end: {}", maxColNum);
 370                 return maxColNum;
 371         }
 372
 373         /**
 374          * 対象範囲をブロック指定でシフトする
 375          *
 376          * @param sheet         Worksheetオブジェクト
 377          * @param range         シフト対象範囲
 378          * @param direction     シフト方向。row：行方向(デフォルト)　col：列方向
 379          * @param distance      シフト距離。正数：右・下　負数：左・上
 380          *
 381          * @throws JaxcelInputException 入力例外発生時
 382          */
 383         public static void shift(Sheet sheet, CellRangeAddress range, String direction, int distance) {
 384                 shift(sheet, range, direction, distance, true);
 385         }
 386
 387         /**
 388          * 対象範囲をシフトする
 389          *
 390          * @param sheet         Worksheetオブジェクト
 391          * @param range         シフト対象範囲
 392          * @param direction     シフト方向。row：行方向(デフォルト)　col：列方向
 393          * @param distance      シフト距離。正数：右・下　負数：左・上
 394          * @param block         ブロック指定。true：ブロック指定　false：列・行単位指定
 395          *
 396          * @throws JaxcelInputException 入力例外発生時
 397          */
 398         public static void shift(Sheet sheet, CellRangeAddress range, String direction, int distance, boolean block) {
 399                 log.trace("shift start");
 400
 401                 final String ROW = "row";
 402                 final String COL = "col";
 403
 404                 String _direction;      // シフト方向
 405                 CellRangeAddress _range;
 406
 407                 // チェック
 408                 if(sheet == null) {
 409                         log.error("sheet is null");
 410                         throw new JaxcelInputException("sheet is null");
 411                 }
 412                 if(range == null) {
 413                         log.error("range is null");
 414                         throw new JaxcelInputException("range is null");
 415                 }
 416                 _range = range;
 417                 if(!validateRange(sheet, range)) {
 418                         _range = getIntersectRange(sheet, range);
 419                         if(_range == null) {
 420                                 log.warn("range is illegal: [{}]", range.formatAsString());
 421                                 log.trace("shift end");
 422                                 return;
 423                         }
 424                         else {
 425                                 log.info("resize range: [{}]", _range.formatAsString());
 426                         }
 427                 }
 428                 if(distance == 0) {
 429                         log.debug("distance is 0");
 430                         log.trace("shift end");
 431                         return;
 432                 }
 433                 if(direction == null) {
 434                         log.debug("direction is null. set default: {}", ROW);
 435                         _direction = ROW;
 436                 }
 437                 else if(!COL.equalsIgnoreCase(direction) && !ROW.equalsIgnoreCase(direction)) {
 438                         log.debug("direction is illegal argument. set default: {}", ROW);
 439                         _direction = ROW;
 440                 }
 441                 else {
 442                         _direction = direction.toLowerCase();
 443                 }
 444
 445                 if(log.isDebugEnabled()) {
 446                         log.debug("sheet: {}", sheet.getSheetName());
 447                         log.debug("range: [{}]", _range.formatAsString());
 448                         log.debug("distance: {}", distance);
 449                         log.debug("direction: {}", _direction);
 450                         log.debug("block: {}", block);
 451                 }
 452
 453                 // シフト範囲の特定　引数の範囲以降を移動対象に含める
 454                 CellRangeAddress fromRange;
 455                 int rowDistance, colDistance;
 456                 int firstIdx, lastIdx;
 457
 458                 // 列方向の場合
 459                 if(COL.equals(_direction)) {
 460                         // 先頭列
 461                         firstIdx = _range.getFirstColumn();
 462                         // 最終列
 463                         // block範囲でなければ範囲拡大
 464                         lastIdx = block ? getLastColNum(sheet, _range.getFirstRow(), _range.getLastRow()) : getLastColNum(sheet);
 465                         lastIdx = lastIdx > firstIdx ? lastIdx : firstIdx;
 466
 467                         // 移動元範囲決定
 468                         // block範囲指定でなければ行範囲拡大
 469                         if(block) {
 470                                 fromRange = new CellRangeAddress(
 471                                                 _range.getFirstRow(), _range.getLastRow(),
 472                                                 firstIdx, lastIdx);
 473                         }
 474                         else {
 475                                 fromRange = new CellRangeAddress(
 476                                                 sheet.getFirstRowNum(), sheet.getLastRowNum(),
 477                                                 firstIdx, lastIdx);
 478                         }
 479
 480                         // 移動量
 481                         rowDistance = 0;
 482                         colDistance = distance;
 483                 }
 484                 // 行方向の場合
 485                 else {
 486                         // 先頭行
 487                         firstIdx = _range.getFirstRow();
 488                         // 最終行
 489                         lastIdx = sheet.getLastRowNum() > firstIdx ? sheet.getLastRowNum() : firstIdx;
 490
 491                         // 移動元範囲決定
 492                         // block範囲でなければ列範囲拡大
 493                         if(block) {
 494                                 fromRange = new CellRangeAddress(
 495                                                 firstIdx, lastIdx,
 496                                                 _range.getFirstColumn(), _range.getLastColumn());
 497                         }
 498                         else {
 499                                 fromRange = new CellRangeAddress(
 500                                                 firstIdx, lastIdx,
 501                                                 0, getLastColNum(sheet));
 502                         }
 503
 504                         // 移動量
 505                         rowDistance = distance;
 506                         colDistance = 0;
 507                 }
 508
 509                 // 範囲移動
 510                 moveRange(sheet, fromRange, rowDistance, colDistance, block);
 511
 512                 log.trace("shift end");
 513         }
 514
 515         /**
 516          * 数式に使用されている相対参照を指定距離で移動した結果を返却<br>
 517          * 配列数式はPOIの制約上使用できません
 518          *
 519          * @param sheet         Worksheetオブジェクト
 520          * @param formula       数式
 521          * @param rowDistance   行方向移動距離。正数：下　負数：上
 522          * @param colDistance   列方向移動距離。正数：右　負数：左
 523          *
 524          * @return      相対参照を指定距離で移動した結果の数式<br>
 525          * 移動した結果、数式内の参照がシート範囲外を指定したとしてもワークシートとしては異常とならない為、移動距離の妥当性チェックは行わない<br>
 526          * 配列数式等、POIの制約上解析不可能な数式の場合は引数のまま返却する
 527          *
 528          * @throws JaxcelInputException 入力例外発生時
 529          */
 530         public static String getMoveFormula(Sheet sheet, String formula, int rowDistance, int colDistance) {
 531                 log.trace("getMoveFormula start");
 532
 533                 // チェック
 534                 if(sheet == null) {
 535                         log.warn("sheet is null");
 536                         return formula;
 537                 }
 538                 if(formula == null) {
 539                         log.warn("formula is null");
 540                         return formula;
 541                 }
 542                 if(rowDistance == 0 && colDistance == 0) {
 543                         log.debug("rowDistance and colDistance is 0");
 544                         log.trace("getMoveFormula end");
 545                         return formula;
 546                 }
 547
 548                 if(log.isDebugEnabled()) {
 549                         log.debug("sheet: {}", sheet.getSheetName());
 550                         log.debug("formula: {}", formula);
 551                         log.debug("rowDistance: {}", rowDistance);
 552                         log.debug("colDistance: {}", colDistance);
 553                 }
 554
 555                 // 数式パースの準備
 556                 FormulaParsingWorkbook fpBook;
 557                 // xls形式
 558                 if(sheet instanceof HSSFSheet) {
 559                         fpBook = HSSFEvaluationWorkbook.create((HSSFWorkbook) sheet.getWorkbook());
 560                 }
 561                 // 2007以降 ooxml形式
 562                 else if(sheet instanceof XSSFSheet) {
 563                         fpBook = XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
 564                 }
 565                 else {
 566                         log.warn("sheet is unsupported type");
 567                         log.trace("getMoveFormula end");
 568                         return formula;
 569                 }
 570
 571                 // 数式のパース
 572                 String returnFormula;
 573                 boolean parseFlg = false;
 574
 575                 try {
 576                         Ptg[]   ptg;
 577                         AreaPtg aPtg;
 578                         RefPtg  rPtg;
 579
 580                         // 数式のパートに分解
 581                         ptg = FormulaParser.parse(formula, fpBook, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));         // TODO: FormulaType.CELL とは？
 582                         for (int i = 0; i < ptg.length; i++){
 583                                 log.trace("from ptg: {}", ptg[i]);
 584                                 // パートがエリア（range）の場合
 585                                 if(ptg[i] instanceof AreaPtg) {
 586                                         aPtg = (AreaPtg) ptg[i];
 587                                         // 各行・列が相対参照であれば移動量を加算し書き換え
 588                                         if(aPtg.isFirstRowRelative()) {
 589                                                 aPtg.setFirstRow(aPtg.getFirstRow() + rowDistance);
 590                                                 parseFlg = true;
 591                                         }
 592                                         if(aPtg.isFirstColRelative()) {
 593                                                 aPtg.setFirstColumn(aPtg.getFirstColumn() + colDistance);
 594                                                 parseFlg = true;
 595                                         }
 596                                         if(aPtg.isLastRowRelative()) {
 597                                                 aPtg.setLastRow(aPtg.getLastRow() + rowDistance);
 598                                                 parseFlg = true;
 599                                         }
 600                                         if(aPtg.isLastColRelative()) {
 601                                                 aPtg.setLastColumn(aPtg.getLastColumn() + colDistance);
 602                                                 parseFlg = true;
 603                                         }
 604                                 }
 605                                 // パートがセルの場合
 606                                 else if(ptg[i] instanceof RefPtg) {
 607                                         rPtg = (RefPtg) ptg[i];
 608                                         // 行・列が相対参照であれば移動量を加算し書き換え
 609                                         if(rPtg.isRowRelative()) {
 610                                                 rPtg.setRow(rPtg.getRow() + rowDistance);
 611                                                 parseFlg = true;
 612                                         }
 613                                         if(rPtg.isColRelative()) {
 614                                                 rPtg.setColumn(rPtg.getColumn() + colDistance);
 615                                                 parseFlg = true;
 616                                         }
 617                                 }
 618                                 log.trace("to ptg: {}", ptg[i]);
 619                         }
 620                         // 相対参照が存在し移動させた場合は数式の再構築
 621                         returnFormula = parseFlg ? FormulaRenderer.toFormulaString((FormulaRenderingWorkbook) fpBook, ptg) : formula;
 622                 }
 623                 catch(Exception e) {
 624                         log.error("formula parse error: {}", e.getMessage(), e);
 625                         returnFormula = formula;
 626                 }
 627
 628                 log.trace("getMoveFormula end: {}", returnFormula);
 629                 return returnFormula;
 630         }
 631
 632         /**
 633          * 対象範囲をクリアする
 634          *
 635          * @param sheet         Worksheetオブジェクト
 636          * @param range         移動対象範囲
 637          * @param clearStyle    セルスタイルのクリア指定。trueでスタイルをクリアする
 638          * @param clearMerge    セル結合のクリア指定。trueで結合をクリアする
 639          *
 640          * @throws JaxcelInputException 入力例外発生時
 641          */
 642         public static void clearRange(Sheet sheet, CellRangeAddress range, boolean clearStyle, boolean clearMerge) {
 643                 log.trace("clearRange start");
 644
 645                 CellRangeAddress tmpRange;
 646                 CellRangeAddress _range;
 647
 648                 // チェック
 649                 if(sheet == null) {
 650                         log.error("sheet is null");
 651                         throw new JaxcelInputException("sheet is null");
 652                 }
 653                 if(range == null) {
 654                         log.error("range is null");
 655                         throw new JaxcelInputException("range is null");
 656                 }
 657                 _range = range;
 658                 if(!validateRange(sheet, range)) {
 659                         _range = getIntersectRange(sheet, range);
 660                         if(_range == null) {
 661                                 log.warn("range is illegal: [{}]", range.formatAsString());
 662                                 log.trace("clearRange end");
 663                                 return;
 664                         }
 665                         else {
 666                                 log.info("resize range: [{}]", _range.formatAsString());
 667                         }
 668                 }
 669                 if(log.isDebugEnabled()) {
 670                         log.debug("sheet: {}", sheet.getSheetName());
 671                         log.debug("range: [{}]", _range.formatAsString());
 672                         log.debug("clearStyle: {}", clearStyle);
 673                         log.debug("clearMerge: {}", clearMerge);
 674                 }
 675
 676                 // 結合の解除
 677                 if(clearMerge && sheet.getNumMergedRegions() > 0) {
 678                         for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
 679                                 // 結合範囲取得
 680                                 tmpRange = sheet.getMergedRegion(i);
 681                                 log.trace("mergedRegion renge: [{}]", tmpRange.formatAsString());
 682
 683                                 // 範囲内・範囲に掛かっている結合の解除
 684                                 if(CellRangeUtil.NO_INTERSECTION != CellRangeUtil.intersect(_range, tmpRange)) {
 685                                         log.debug("mergedRegion {}. clear: [{}]", getIntersectString(_range, tmpRange), tmpRange.formatAsString());
 686                                         sheet.removeMergedRegion(i);
 687                                         i = -1;
 688                                 }
 689                         }
 690                 }
 691
 692                 // オブジェクトの削除・クリア Excel2007以降 ooxml形式のみ対応
 693                 if(sheet instanceof XSSFSheet) {
 694                         XSSFSheet xSheet = (XSSFSheet) sheet;
 695                         String ref;
 696
 697                         // オートフィルタ
 698                         CTAutoFilter af = xSheet.getCTWorksheet().getAutoFilter();
 699                         if(af != null) {
 700                                 ref = af.getRef();
 701                                 log.trace("auto filter renge: [{}]", ref);
 702
 703                                 // CellRangeAddressに変換し位置情報設定
 704                                 tmpRange = CellRangeAddress.valueOf(ref);
 705                                 // 範囲内に含まれている or 掛かっている オートフィルタクリア
 706                                 switch(CellRangeUtil.intersect(_range, tmpRange)) {
 707                                 case CellRangeUtil.INSIDE:
 708                                 case CellRangeUtil.OVERLAP:
 709                                         xSheet.getCTWorksheet().unsetAutoFilter();
 710                                         log.debug("auto filter {}. clear: [{}]", getIntersectString(_range, tmpRange), ref);
 711                                 }
 712                         }
 713
 714                         // DocumentPartでループ
 715                         CTDrawing drawing;
 716                         CTTable table;
 717                         CTTwoCellAnchor anchor;
 718                         List<CTTablePart> tableList;
 719
 720                         for(POIXMLDocumentPart part : xSheet.getRelations()) {
 721                                 if(part == null) continue;
 722                                 log.debug("documentPart class: {}", part.getClass().getName());
 723
 724                                 // DocumentPartがテーブルの場合
 725                                 if(part instanceof XSSFTable) {
 726                                         // テーブルオブジェクト取得
 727                                         table = ((XSSFTable) part).getCTTable();
 728                                         // テーブル範囲取得
 729                                         ref = table.getRef();
 730                                         log.trace("table range: [{}]", ref);
 731                                         tmpRange = CellRangeAddress.valueOf(ref);
 732
 733                                         // 範囲内に含まれている or 掛かっている テーブルクリア
 734                                         switch(CellRangeUtil.intersect(_range, tmpRange)) {
 735                                         case CellRangeUtil.INSIDE:
 736                                         case CellRangeUtil.OVERLAP:
 737                                                 // シート上に存在するテーブルと対象テーブルのIDが一致するものを検索しクリアする
 738                                                 tableList = xSheet.getCTWorksheet().getTableParts().getTablePartList();
 739                                                 for(int i = 0; i < tableList.size(); i++) {
 740                                                         if(tableList.get(i).getId() != null && tableList.get(i).getId().equalsIgnoreCase(part.getPackageRelationship().getId())) {
 741                                                                 xSheet.getCTWorksheet().getTableParts().removeTablePart(i);
 742                                                                 log.debug("table {}. clear: [{}]", getIntersectString(_range, tmpRange), ref);
 743                                                                 break;
 744                                                         }
 745                                                 }
 746                                         }
 747                                 }
 748                                 // DocumentPartがDrawingオブジェクトの場合
 749                                 else if(part instanceof XSSFDrawing) {
 750                                         // Drawingオブジェクト取得
 751                                         drawing = ((XSSFDrawing) part).getCTDrawing();
 752                                         // 画像やシェイプ等のアンカーでループ
 753                                         for(int i = 0; i < drawing.getTwoCellAnchorList().size(); i++) {
 754                                                 // 位置情報取得
 755                                                 anchor = drawing.getTwoCellAnchorList().get(i);
 756                                                 tmpRange = new CellRangeAddress(anchor.getFrom().getRow(), anchor.getTo().getRow(), anchor.getFrom().getCol(), anchor.getTo().getCol());
 757                                                 log.debug("object range: [{}]", tmpRange.formatAsString());
 758
 759                                                 // 範囲内に掛かっていない以外 オブジェクトの削除
 760                                                 if(CellRangeUtil.NO_INTERSECTION != CellRangeUtil.intersect(_range, tmpRange)) {
 761                                                         drawing.removeTwoCellAnchor(i);
 762                                                         log.debug("object {}. delete: [{}]", getIntersectString(_range, tmpRange), tmpRange.formatAsString());
 763                                                         i = -1;
 764                                                 }
 765                                         }
 766                                 }
 767                         }
 768                 }
 769                 // セルのクリア
 770                 Row row;
 771                 Cell cell;
 772                 // 行でループ
 773                 for(int i = range.getFirstRow() == -1 ? 0 : range.getFirstRow(), ii = range.getLastRow() == - 1 ? getMaxRowIndex(sheet) : range.getLastRow(); i <= ii; i++) {
 774                         row = sheet.getRow(i);
 775                         if(row == null) continue;
 776                         // 列でループ
 777                         for(int c = range.getFirstColumn() == -1 ? 0 : range.getFirstColumn(), cc = range.getLastColumn() == -1 ? getMaxColumnIndex(sheet) : range.getLastColumn(); c <= cc; c++) {
 778                                 cell = row.getCell(c);
 779                                 if(cell == null) continue;
 780                                 // ハイパーリンクのクリア
 781                                 clearHyperlink(sheet, cell);
 782                                 // コメントクリア
 783                                 cell.removeCellComment();
 784                                 // スタイルクリアならセルの削除
 785                                 if(clearStyle) {
 786                                         row.removeCell(cell);
 787                                 }
 788                                 // スタイルクリアでないならブランクセル
 789                                 else {
 790                                         cell.setCellType(Cell.CELL_TYPE_BLANK);
 791                                 }
 792                                 log.debug("cell clear: [{}]", (new CellReference(cell).formatAsString()));
 793                         }
 794                 }
 795                 log.trace("clearRange end");
 796         }
 797
 798         /**
 799          * 指定された範囲の交差状態を定数文字列で返却する<br>
 800          * ブック、シートは意識しません。
 801          *
 802          * @param rangeA        対象範囲Ａ
 803          * @param rangeB        対象範囲Ｂ
 804          *
 805          * @return      ExcelUtil.INSIDE：対象範囲Ａに対象範囲Ｂが含まれている<br>
 806          * ExcelUtil.OVERLAP：対象範囲Ａに対象範囲Ｂの一部が交差している<br>
 807          * ExcelUtil.ENCLOSES：対象範囲Ｂに対象範囲Ａが含まれている<br>
 808          * ExcelUtil.NO_INTERSECTION：対象範囲Ａに対象範囲Ｂは一部の含まれていない
 809          */
 810         public static String getIntersectString(CellRangeAddress rangeA, CellRangeAddress rangeB) {
 811                 // 範囲内に含まれている or 掛かっている テーブルクリア
 812                 switch(CellRangeUtil.intersect(rangeA, rangeB)) {
 813                 case CellRangeUtil.NO_INTERSECTION:
 814                         return NO_INTERSECTION;
 815                 case CellRangeUtil.INSIDE:
 816                         return INSIDE;
 817                 case CellRangeUtil.ENCLOSES:
 818                         return ENCLOSES;
 819                 default:
 820                         return OVERLAP;
 821                 }
 822         }
 823
 824         /**
 825          * 指定された範囲がシートに収まらない場合、収まるサイズにリサイズした範囲を返却する
 826          *
 827          * @param sheet         Worksheetオブジェクト
 828          * @param range         対象範囲
 829          *
 830          * @return      シートに収まる範囲<br>
 831          * シートに収まる範囲が存在しない場合はnull
 832          *
 833          * @throws JaxcelInputException 入力例外発生時
 834          */
 835         public static CellRangeAddress getIntersectRange(Sheet sheet, CellRangeAddress range) {
 836                 log.trace("getIntersectRange start");
 837
 838                 // チェック
 839                 if(sheet == null) {
 840                         log.error("sheet is null");
 841                         throw new JaxcelInputException("sheet is null");
 842                 }
 843                 CellRangeAddress tmpRange = getIntersectRange(sheet, (new CellRangeAddress(0, getMaxRowIndex(sheet), 0, getMaxColumnIndex(sheet))), range);
 844
 845                 log.trace("getIntersectRange end : [{}]", (tmpRange == null ? null : tmpRange.formatAsString()));
 846                 return tmpRange;
 847         }
 848
 849         /**
 850          * 指定された範囲の交差する範囲を返却する<br>
 851          *
 852          * @param sheet         Worksheetオブジェクト
 853          * @param rangeA        対象範囲Ａ
 854          * @param rangeB        対象範囲Ｂ
 855          *
 856          * @return      交差する範囲<br>
 857          * 交差する範囲が存在しない場合はnull
 858          *
 859          * @throws JaxcelInputException 入力例外発生時
 860          */
 861         public static CellRangeAddress getIntersectRange(Sheet sheet, CellRangeAddress rangeA, CellRangeAddress rangeB) {
 862                 log.trace("getIntersectRange start");
 863
 864                 // チェック
 865                 if(sheet == null) {
 866                         log.error("sheet is null");
 867                         throw new JaxcelInputException("sheet is null");
 868                 }
 869                 if(rangeA == null) {
 870                         log.error("rangeA is null");
 871                         throw new JaxcelInputException("rangeA is null");
 872                 }
 873                 if(rangeB == null) {
 874                         log.error("rangeB is null");
 875                         throw new JaxcelInputException("rangeA is null");
 876                 }
 877 //              if(!validateRange(sheet, rangeA)) {
 878 //                      log.error("rangeA is illegal: [{}]", rangeA.formatAsString());
 879 //                      throw new JaxcelInputException("rangeA is illegal");
 880 //              }
 881 //              if(!validateRange(sheet, rangeB)) {
 882 //                      log.error("rangeB is illegal: [{}]", rangeB.formatAsString());
 883 //                      throw new JaxcelInputException("rangeB is illegal");
 884 //              }
 885                 if(log.isDebugEnabled()) {
 886                         log.debug("sheet: {}", sheet.getSheetName());
 887                         log.debug("rangeA: [{}]", rangeA.formatAsString());
 888                         log.debug("rangeB: [{}]", rangeB.formatAsString());
 889                 }
 890
 891                 // 交差範囲生成
 892                 CellRangeAddress tmpRange = new CellRangeAddress(
 893                                 (rangeA.getFirstRow() >= rangeB.getFirstRow() ? rangeA.getFirstRow() : rangeB.getFirstRow()),
 894                                 (rangeA.getLastRow() <= rangeB.getLastRow() ? rangeA.getLastRow() : rangeB.getLastRow()),
 895                                 (rangeA.getFirstColumn() >= rangeB.getFirstColumn() ? rangeA.getFirstColumn() : rangeB.getFirstColumn()),
 896                                 (rangeA.getLastColumn() <= rangeB.getLastColumn() ? rangeA.getLastColumn() : rangeB.getLastColumn()));
 897
 898
 899                 // 交差範囲チェック
 900                 if(!validateRange(sheet, tmpRange)) {
 901                         log.debug("rangeA rangeB is not intersect");
 902                         log.trace("getIntersectRange end");
 903                         return null;
 904                 }
 905
 906                 log.trace("getIntersectRange end : {}", tmpRange.formatAsString());
 907                 return tmpRange;
 908         }
 909
 910
 911         /**
 912          * 対象範囲を移動する
 913          *
 914          * @param sheet         Worksheetオブジェクト
 915          * @param range         移動対象範囲
 916          * @param rowDistance   行方向移動距離。正数：下　負数：上
 917          * @param colDistance   列方向移動距離。正数：右　負数：左
 918          * @param block         ブロック指定。true：ブロック指定　false：列・行単位指定
 919          *
 920          * @throws JaxcelInputException 入力例外発生時
 921          */
 922         public static void moveRange(Sheet sheet, CellRangeAddress range, int rowDistance, int colDistance, boolean block) {
 923                 log.trace("moveRange start");
 924
 925                 // チェック
 926                 if(sheet == null) {
 927                         log.error("sheet is null");
 928                         throw new JaxcelInputException("sheet is null");
 929                 }
 930                 if(range == null) {
 931                         log.error("range is null");
 932                         throw new JaxcelInputException("range is null");
 933                 }
 934                 if(!validateRange(sheet, range)) {
 935                         log.error("range is illegal: [{}]", range.formatAsString());
 936                         throw new JaxcelInputException("range is illegal");
 937                 }
 938                 if(rowDistance == 0 && colDistance == 0) {
 939                         log.debug("distance is 0");
 940                         log.trace("moveRange end");
 941                         return;
 942                 }
 943                 // 斜め移動（rowDistance、colDistanceのいずれも０でない）の場合はblock指定はtrueとする
 944                 else if(!block && rowDistance != 0 && colDistance != 0) {
 945                         log.info("change block mode");
 946                         block = true;
 947                 }
 948
 949                 if(log.isDebugEnabled()) {
 950                         log.debug("sheet: {}", sheet.getSheetName());
 951                         log.debug("range: {}", range.formatAsString());
 952                         log.debug("rowDistance: {}", rowDistance);
 953                         log.debug("colDistance: {}", colDistance);
 954                         log.debug("block: {}", block);
 955                 }
 956
 957                 // 移動先範囲シートに収まる範囲を取得
 958                 CellRangeAddress toRange = getIntersectRange(sheet, new CellRangeAddress(
 959                                 (range.getFirstRow() == -1 ? 0 : range.getFirstRow()) + rowDistance,
 960                                 (range.getLastRow() == -1 ? sheet.getLastRowNum() : range.getLastRow()) + rowDistance,
 961                                 (range.getFirstColumn() == - 1 ? 0 : range.getFirstColumn()) + colDistance,
 962                                 (range.getLastColumn() == -1 ? getLastColNum(sheet) : range.getLastColumn()) + colDistance));
 963                 // シートに収まる範囲がなければ
 964                 if(toRange == null) {
 965                         // 移動元範囲のクリア
 966                         log.debug("toRange outside sheet. fromRange clear");
 967                         clearRange(sheet, range, true, true);
 968                         log.trace("moveRange end");
 969                         return;
 970                 }
 971                 log.debug("toRange: {}", toRange.formatAsString());
 972
 973                 // 移動元範囲内の接合の保持・削除
 974                 ArrayList<CellRangeAddress> mergedRegionList = new ArrayList<>();
 975                 CellRangeAddress tmpRange;
 976                 if(sheet.getNumMergedRegions() > 0) {
 977                         log.trace("fromRange mergedRegion check");
 978
 979                         for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
 980                                 tmpRange = sheet.getMergedRegion(i);
 981                                 log.trace("mergedRegion renge: [{}]", tmpRange.formatAsString());
 982
 983                                 // 移動元範囲内に含まれている 接合の保持
 984                                 // 移動元範囲に掛かっている or 移動元範囲を囲っている 接合の削除
 985                                 switch(CellRangeUtil.intersect(range, tmpRange)) {
 986                                 case CellRangeUtil.INSIDE:
 987                                         log.debug("mergedRegion {}. save: [{}]", getIntersectString(range, tmpRange), tmpRange.formatAsString());
 988                                         mergedRegionList.add(tmpRange);
 989                                         sheet.removeMergedRegion(i);
 990                                         i = -1;
 991                                         break;
 992                                 case CellRangeUtil.ENCLOSES:
 993                                 case CellRangeUtil.OVERLAP:
 994                                         log.debug("mergedRegion {}. remove: [{}]", getIntersectString(range, tmpRange), tmpRange.formatAsString());
 995                                         sheet.removeMergedRegion(i);
 996                                         i = -1;
 997                                         break;
 998                                 }
 999                         }
1000                 }
1001
1002                 // 移動先範囲内の接合の削除
1003                 if(sheet.getNumMergedRegions() > 0) {
1004                         log.trace("toRange mergedRegion check");
1005
1006                         for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
1007                                 tmpRange = sheet.getMergedRegion(i);
1008                                 log.trace("mergedRegion renge: [{}]", tmpRange.formatAsString());
1009
1010                                 // 移動先範囲内に掛かっていない以外 接合の削除
1011                                 if(CellRangeUtil.NO_INTERSECTION != CellRangeUtil.intersect(toRange, tmpRange)) {
1012                                         log.debug("mergedRegion {}. remove: [{}]", getIntersectString(toRange, tmpRange), tmpRange.formatAsString());
1013                                         sheet.removeMergedRegion(i);
1014                                         i = -1;
1015                                 }
1016                         }
1017                 }
1018
1019                 // 保持した結合があれば移動先で追加
1020                 if(!mergedRegionList.isEmpty()) {
1021                         for(CellRangeAddress mergedRegion : mergedRegionList) {
1022                                 // シートに収まる範囲を取得
1023                                 tmpRange = getIntersectRange(sheet, new CellRangeAddress(
1024                                                 mergedRegion.getFirstRow() + rowDistance,
1025                                                 mergedRegion.getLastRow() + rowDistance,
1026                                                 mergedRegion.getFirstColumn() + colDistance,
1027                                                 mergedRegion.getLastColumn() + colDistance));
1028                                 // シートに収まれば
1029                                 if(tmpRange != null) {
1030                                         // 移動先で追加
1031                                         sheet.addMergedRegion(tmpRange);
1032                                         log.debug("mergedRegion move. from: [{}] to: [{}]", mergedRegion.formatAsString(), tmpRange.formatAsString());
1033                                 }
1034                                 else {
1035                                         log.debug("mergedRegion move to outside sheet. clear: [{}]", mergedRegion.formatAsString());
1036                                 }
1037                         }
1038                 }
1039
1040                 // シェイプ等のオブジェクトの削除・移動 Excel2007以降 ooxml形式のみ対応
1041                 if(sheet instanceof XSSFSheet) {
1042                         XSSFSheet xSheet = (XSSFSheet) sheet;
1043                         int r1, c1, r2, c2;
1044                         String ref;
1045                         CTDrawing ctDrawing;
1046                         CTTable ctTable;
1047                         CTTwoCellAnchor fAnchor;
1048                         CTMarker from, to;
1049                         // DocumentPartでループ
1050                         try {
1051                                 // オートフィルタ
1052                                 CTAutoFilter af = xSheet.getCTWorksheet().getAutoFilter();
1053                                 if(af != null) {
1054                                         // 位置情報取得
1055                                         ref = af.getRef();
1056                                         log.trace("auto filter range: [{}]", ref);
1057                                         tmpRange = CellRangeAddress.valueOf(ref);
1058
1059                                         // オートフィルタの移動・クリア
1060                                         switch(CellRangeUtil.intersect(range, tmpRange)) {
1061                                         case CellRangeUtil.OVERLAP:
1062                                                 // 移動元レンジに掛かっている オートフィルタクリア
1063                                                 xSheet.getCTWorksheet().unsetAutoFilter();
1064                                                 log.debug("auto filter clear: [{}]", ref);
1065                                                 break;
1066
1067                                         case CellRangeUtil.INSIDE:
1068                                                 // 移動元レンジに含まれている
1069                                                 tmpRange.setFirstRow(tmpRange.getFirstRow() + rowDistance);
1070                                                 tmpRange.setLastRow(tmpRange.getLastRow() + rowDistance);
1071                                                 tmpRange.setFirstColumn(tmpRange.getFirstColumn() + colDistance);
1072                                                 tmpRange.setLastColumn(tmpRange.getLastColumn() + colDistance);
1073                                                 // 移動先範囲妥当性チェック（シートに収まっているか）
1074                                                 if(validateRange(xSheet, tmpRange)) {
1075                                                         // オートフィルタ移動
1076                                                         xSheet.setAutoFilter(tmpRange);
1077                                                         log.debug("auto filter move from: [{}] to: [{}]", ref, tmpRange.formatAsString());
1078                                                 }
1079                                                 else {
1080                                                         // オートフィルタクリア
1081                                                         xSheet.getCTWorksheet().unsetAutoFilter();
1082                                                         log.debug("auto filter move to outside sheet. clear: [{}]", ref);
1083                                                 }
1084                                                 break;
1085
1086                                         case CellRangeUtil.NO_INTERSECTION:
1087                                                 // 移動元レンジに含まれていない and (移動先レンジに含まれている or 掛かっている) オートフィルタクリア
1088                                                 switch(CellRangeUtil.intersect(toRange, tmpRange)) {
1089                                                 case CellRangeUtil.INSIDE:
1090                                                 case CellRangeUtil.OVERLAP:
1091                                                         xSheet.getCTWorksheet().unsetAutoFilter();
1092                                                         log.debug("auto filter clear: [{}]", ref);
1093                                                 }
1094                                         }
1095                                 }
1096
1097                                 // DocumentPartでループ
1098                                 for(POIXMLDocumentPart part : xSheet.getRelations()) {
1099                                         if(part == null) continue;
1100                                         log.debug("DocumentPart class: {}", part.getClass().getName());
1101
1102                                         // DocumentPartがテーブルの場合
1103                                         if(part instanceof XSSFTable) {
1104                                                 // テーブルオブジェクト取得
1105                                                 ctTable = ((XSSFTable) part).getCTTable();
1106
1107                                                 // 位置情報取得
1108                                                 ref = ctTable.getRef();
1109                                                 log.debug("table ref: [{}]", ref);
1110                                                 tmpRange = CellRangeAddress.valueOf(ref);
1111
1112                                                 switch(CellRangeUtil.intersect(range, tmpRange)) {
1113                                                 case CellRangeUtil.OVERLAP:
1114                                                         // 移動元レンジに掛かっている テーブルクリア
1115                                                         clearTable(xSheet, part.getPackageRelationship().getId());
1116                                                         log.debug("table clear: [{}]", ref);
1117                                                         break;
1118
1119                                                 case CellRangeUtil.INSIDE:
1120                                                         // 移動元レンジに含まれている
1121                                                         tmpRange.setFirstRow(tmpRange.getFirstRow() + rowDistance);
1122                                                         tmpRange.setLastRow(tmpRange.getLastRow() + rowDistance);
1123                                                         tmpRange.setFirstColumn(tmpRange.getFirstColumn() + colDistance);
1124                                                         tmpRange.setLastColumn(tmpRange.getLastColumn() + colDistance);
1125                                                         // 移動先範囲妥当性チェック（シートに収まっているか）
1126                                                         if(validateRange(xSheet, tmpRange)) {
1127                                                                 // テーブル移動
1128                                                                 ctTable.setRef(tmpRange.formatAsString());
1129                                                                 log.debug("table move from: [{}] to: [{}]", ref, tmpRange.formatAsString());
1130                                                         }
1131                                                         else {
1132                                                                 // テーブルクリア
1133                                                                 clearTable(xSheet, part.getPackageRelationship().getId());
1134                                                                 log.debug("table clear: [{}]", ref);
1135                                                         }
1136                                                         break;
1137
1138                                                 case CellRangeUtil.NO_INTERSECTION:
1139                                                         // 移動元レンジに含まれていない and
1140                                                         // (移動先レンジに含まれている or 掛かっている) テーブルクリア
1141                                                         switch(CellRangeUtil.intersect(toRange, tmpRange)) {
1142                                                         case CellRangeUtil.INSIDE:
1143                                                         case CellRangeUtil.OVERLAP:
1144                                                                 clearTable(xSheet, part.getPackageRelationship().getId());
1145                                                                 log.debug("table clear: [{}]", ref);
1146                                                         }
1147                                                 }
1148                                         }
1149                                         // DocumentPartがDrawingオブジェクトの場合
1150                                         else if(part instanceof XSSFDrawing) {
1151                                                 ctDrawing = ((XSSFDrawing) part).getCTDrawing();
1152                                                 if(ctDrawing != null) {
1153                                                         // 画像やシェイプ等のアンカーでループ
1154                                                         for (int i = 0; i < ctDrawing.getTwoCellAnchorList().size(); i++) {
1155                                                                 // 位置情報取得
1156                                                                 fAnchor = ctDrawing.getTwoCellAnchorList().get(i);
1157                                                                 from = fAnchor.getFrom();
1158                                                                 r1 = from.getRow();
1159                                                                 c1 = from.getCol();
1160                                                                 to = fAnchor.getTo();
1161                                                                 r2 = to.getRow();
1162                                                                 c2 = to.getCol();
1163                                                                 tmpRange = new CellRangeAddress(r1, r2, c1, c2);
1164
1165                                                                 // (移動先レンジに含まれている or 掛かっている) and
1166                                                                 // (移動元レンジに含まれていない and 掛かっていない) 削除
1167                                                                 switch(CellRangeUtil.intersect(toRange, tmpRange)) {
1168                                                                 case CellRangeUtil.INSIDE:
1169                                                                 case CellRangeUtil.OVERLAP:
1170                                                                         if(CellRangeUtil.intersect(range, tmpRange) == CellRangeUtil.NO_INTERSECTION) {
1171                                                                                 ctDrawing.removeTwoCellAnchor(i);
1172                                                                                 log.debug("object delete : [{}]", tmpRange.formatAsString());
1173                                                                                 i--;
1174                                                                                 continue;
1175                                                                         }
1176                                                                 }
1177
1178                                                                 // 移動元レンジに含まれている、掛かっているなら移動
1179                                                                 switch(CellRangeUtil.intersect(range, tmpRange)) {
1180                                                                 case CellRangeUtil.INSIDE:
1181                                                                 case CellRangeUtil.OVERLAP:
1182                                                                         from.setRow(r1 + rowDistance);
1183                                                                         to.setRow(r2 + rowDistance);
1184                                                                         from.setCol(c1 + colDistance);
1185                                                                         to.setCol(c2 + colDistance);
1186
1187                                                                         // 移動先範囲妥当性チェック
1188                                                                         if(!validateRange(xSheet, (new CellRangeAddress(from.getRow(), to.getRow(), from.getCol(), to.getCol())))) {
1189                                                                                 // シートに収まっていない 削除
1190                                                                                 ctDrawing.removeTwoCellAnchor(i);
1191                                                                                 log.debug("object delete : [{}]", tmpRange.formatAsString());
1192                                                                                 i--;
1193                                                                                 continue;
1194                                                                         }
1195
1196                                                                         log.debug("object move from: [{}] to: [{}]", tmpRange.formatAsString(), (new CellRangeAddress(from.getRow(), to.getRow(), from.getCol(), to.getCol())).formatAsString());
1197                                                                         break;
1198                                                                 default:
1199                                                                         log.debug("object not move: [{}]", tmpRange.formatAsString());
1200                                                                 }
1201                                                         }
1202                                                 }
1203                                         }
1204                                 }
1205                         }
1206                         catch(Exception e) {
1207                                 log.error("object move error: {}", e.getMessage(), e);
1208                         }
1209                 }
1210
1211                 // セル移動
1212                 Row fRow, tRow;
1213                 Cell fCell, tCell;
1214                 // 移動元範囲行でループ　上へ移動なら正順、下へ移動なら逆順
1215                 for(
1216                         int r = (rowDistance < 0 ? range.getFirstRow() : range.getLastRow());
1217                         (rowDistance < 0 && r <= range.getLastRow()) ||
1218                         (rowDistance >= 0 && r >= range.getFirstRow());
1219                         r = (rowDistance < 0 ? r + 1 : r - 1))
1220                 {
1221                         // 移動元行
1222                         fRow = sheet.getRow(r) == null ? sheet.createRow(r) : sheet.getRow(r);
1223
1224                         // 移動先行
1225                         if(r + rowDistance < 0 || r + rowDistance > getMaxRowIndex(sheet)) {
1226                                 // シート範囲外ならnull
1227                                 tRow = null;
1228                         }
1229                         else {
1230                                 // シート範囲内
1231                                 tRow = sheet.getRow(r + rowDistance) == null ? sheet.createRow(r + rowDistance) : sheet.getRow(r + rowDistance);
1232                                 // 縦（行）移動でブロック範囲でないなら行情報（高さ）もコピー
1233                                 if(!block && rowDistance != 0) {
1234                                         tRow.setHeight(fRow.getHeight());
1235                                 }
1236                         }
1237
1238                         // 移動元範囲列でループ　左へ移動なら正順、右へ移動なら逆順
1239                         for(
1240                                 int c = (colDistance < 0 ? range.getFirstColumn() : range.getLastColumn());
1241                                 (colDistance < 0 && c <= range.getLastColumn()) ||
1242                                 (colDistance >= 0 && c >= range.getFirstColumn());
1243                                 c = (colDistance < 0 ? c + 1 : c - 1))
1244                         {
1245                                 // 移動元セル
1246                                 fCell = fRow.getCell(c);
1247
1248                                 // 移動先セル
1249                                 if(tRow == null || c + colDistance < 0 || c + colDistance > getMaxColumnIndex(sheet)) {
1250                                         // シート範囲外ならnull
1251                                         tCell = null;
1252                                 }
1253                                 else {
1254                                         tCell = tRow.getCell(c + colDistance) == null ? tRow.createCell(c + colDistance) : tRow.getCell(c + colDistance);
1255                                         // 横（列）移動でブロック範囲でないなら列情報（幅）もコピー(１行目でのみ行う)
1256                                         if(!block && colDistance != 0 && c == (colDistance < 0 ? range.getFirstColumn() : range.getLastColumn())) {
1257                                                 sheet.setColumnWidth(tCell.getColumnIndex(), sheet.getColumnWidth(c));
1258                                         }
1259                                 }
1260
1261                                 // 移動
1262                                 // 移動元セルがnull、移動先がシート範囲外ならcontinue
1263                                 if(fCell == null && tCell == null) {
1264                                         continue;
1265                                 }
1266                                 // 移動元セルがnull、移動先がシート範囲内
1267                                 if(fCell == null && tCell != null) {
1268                                         log.debug("clear cell: [{}]", (new CellReference(tCell)).formatAsString());
1269                                         // 移動先セルのハイパーリンク クリア
1270                                         clearHyperlink(sheet, tCell);
1271                                         // 移動先セル クリア
1272                                         tRow.removeCell(tCell);
1273                                 }
1274                                 // 移動元セルがnullでない、移動先がシート範囲外
1275                                 else if(fCell != null && tCell == null) {
1276                                         log.debug("cell move to outside sheet. clear cell: [{}]", (new CellReference(fCell)).formatAsString());
1277                                         // コメントクリア
1278                                         fCell.removeCellComment();
1279                                         // 移動元セルのハイパーリンク クリア
1280                                         clearHyperlink(sheet, fCell);
1281                                         // 移動元セル クリア
1282                                         fRow.removeCell(fCell);
1283                                 }
1284                                 // 移動元セルがnullでない、移動先がシート範囲内 セル移動
1285                                 else {
1286                                         log.debug("move cell. from: [{}] to: [{}]", (new CellReference(fCell)).formatAsString(), (new CellReference(tCell)).formatAsString());
1287
1288                                         // 移動先セルクリア
1289                                         tCell.setCellType(Cell.CELL_TYPE_BLANK);
1290                                         // スタイルのコピー
1291                                         tCell.setCellStyle(fCell.getCellStyle());
1292                                         // コメントのコピー
1293                                         tCell.removeCellComment();
1294                                         if(fCell.getCellComment() != null) {
1295                                                 tCell.setCellComment(fCell.getCellComment());
1296                                         }
1297                                         // 値
1298                                         switch(fCell.getCellType()) {
1299                                         case Cell.CELL_TYPE_BOOLEAN:
1300                                                 tCell.setCellType(Cell.CELL_TYPE_BOOLEAN);
1301                                                 tCell.setCellValue(fCell.getBooleanCellValue());
1302                                                 break;
1303                                         case Cell.CELL_TYPE_ERROR:
1304                                                 tCell.setCellType(Cell.CELL_TYPE_ERROR);
1305                                                 tCell.setCellErrorValue(fCell.getErrorCellValue());
1306                                                 break;
1307                                         case Cell.CELL_TYPE_FORMULA:
1308                                                 tCell.setCellType(Cell.CELL_TYPE_FORMULA);
1309                                                 // 数式のパース・移動先に合わせる
1310                                                 tCell.setCellFormula(getMoveFormula(sheet, fCell.getCellFormula(), rowDistance, colDistance));
1311                                                 break;
1312                                         case Cell.CELL_TYPE_NUMERIC:
1313                                                 tCell.setCellType(Cell.CELL_TYPE_NUMERIC);
1314                                                 tCell.setCellValue(fCell.getNumericCellValue());
1315                                                 break;
1316                                         case Cell.CELL_TYPE_STRING:
1317                                                 tCell.setCellType(Cell.CELL_TYPE_STRING);
1318                                                 tCell.setCellValue(fCell.getRichStringCellValue());
1319                                                 break;
1320                                         }
1321                                         // ハイパーリンク 移動
1322                                         moveHyperlink(sheet, fCell, tCell);
1323                                         // 移動元セル コメントクリア
1324                                         fCell.removeCellComment();
1325                                         // 移動元セル クリア
1326                                         fRow.removeCell(fCell);
1327                                 }
1328                         }
1329                 }
1330                 log.trace("moveRange end");
1331         }
1332
1333         /**
1334          * テーブルのクリア<br>
1335          * Excel2007以降 ooxml形式のみ対応
1336          *
1337          * @param sheet Worksheetオブジェクト
1338          * @param tableId テーブルID
1339          */
1340         private static void clearTable(Sheet sheet, String tableId) {
1341                 if(sheet instanceof XSSFSheet) {
1342                         XSSFSheet xSheet = (XSSFSheet) sheet;
1343                         List<CTTablePart> tableList = xSheet.getCTWorksheet().getTableParts().getTablePartList();
1344                         for(int i = 0; i < tableList.size(); i++) {
1345                                 if(tableList.get(i).getId() != null && tableList.get(i).getId().equalsIgnoreCase(tableId)) {
1346                                         xSheet.getCTWorksheet().getTableParts().removeTablePart(i);
1347                                         break;
1348                                 }
1349                         }
1350                 }
1351         }
1352
1353         /**
1354          * ハイパーリンクの移動<br>
1355          * Excel2007以降 ooxml形式のみ対応
1356          *
1357          * @param sheet Worksheetオブジェクト
1358          * @param fromCell 移動元セル
1359          * @param toCell 移動先セル
1360          */
1361         private static void moveHyperlink(Sheet sheet, Cell fromCell, Cell toCell) {
1362                 if(fromCell.getHyperlink() != null && sheet instanceof XSSFSheet) {
1363                         // ハイパーリンクの移動
1364                         toCell.setHyperlink(fromCell.getHyperlink());
1365                         log.debug("hyperlink move. from: [{}] to: [{}]", (new CellReference(fromCell)).formatAsString(), (new CellReference(toCell)).formatAsString());
1366
1367                         // 移動元セルのハイパーリンクのクリア
1368                         clearHyperlink(sheet, fromCell);
1369                 }
1370         }
1371
1372         /**
1373          * ハイパーリンクのクリア<br>
1374          * Excel2007以降 ooxml形式のみ対応
1375          *
1376          * @param sheet Worksheetオブジェクト
1377          * @param cell クリア対象セル
1378          */
1379         private static void clearHyperlink(Sheet sheet, Cell cell) {
1380                 // ハイパーリンクのクリア Excel2007以降 ooxml形式のみ対応
1381                 if(sheet instanceof XSSFSheet && cell.getHyperlink() != null) {
1382                         try {
1383                                 Field field = XSSFSheet.class.getDeclaredField("hyperlinks");
1384                                 field.setAccessible(true);
1385                                 @SuppressWarnings("unchecked")
1386                                 List<XSSFHyperlink> hyperlinks = (List<XSSFHyperlink>)field.get(sheet);
1387                                 hyperlinks.remove(cell.getHyperlink());
1388                                 log.debug("hyperlink clear: [{}]", (new CellReference(cell)).formatAsString());
1389                         } catch (Exception e) {
1390                                 log.error("hyperlink clear error: {}", e.getMessage(), e);
1391                         }
1392                 }
1393         }
1394
1395         /**
1396          * セルのアドレス比較<br>
1397          *
1398          * @param cellA 対象セルＡ
1399          * @param cellB 対象セルＢ
1400          * @param isCheckSheet  trueの場合、シート名を含め比較する
1401          *
1402          * @return 判定結果<br>
1403          * 対象セルのいずれかでもnullの場合はfalse
1404          *
1405          * @since 1.01.00
1406          */
1407         public static boolean equalsCellAddress(Cell cellA, Cell cellB, boolean isCheckSheet) {
1408
1409                 if(cellA == null || cellB == null) return false;
1410
1411                 if(isCheckSheet) {
1412                         if(!(cellA.getSheet().getSheetName().equals(cellB.getSheet().getSheetName()))) {
1413                                 return false;
1414                         }
1415                 }
1416                 return cellA.getColumnIndex() == cellB.getColumnIndex() &&
1417                                 cellA.getRowIndex() == cellB.getRowIndex();
1418
1419         }
1420
1421         /**
1422          * セルの文字列・数式から指定された正規表現の最初の部分を指定された文字列に置換した結果を返却<br>
1423          *
1424          * replacement内でのバックスラッシュ (\) とドル記号 ($) は、String.replaceFirstと同様に作用します。
1425          *
1426          * @param cell  対象セル
1427          * @param target        置換される文字列
1428          * @param replacement   置き換える文字列
1429          * @param regex trueの場合、targetを正規表現として扱う
1430          *
1431          * @return 置換後のセル文字列・計算式<br>
1432          * 対象セルがnullの場合はnull<br>
1433          * 対象セルのセルタイプが文字列・数式以外の場合はnull
1434          *
1435          * @since 1.01.00
1436          */
1437         public static String replaceFirstCellValue(Cell cell, String target, String replacement, boolean regex) {
1438                 // チェック
1439                 if(cell == null) return null;
1440                 String _replacement = replacement == null ? "" : replacement;
1441                 String _return = null;
1442                 // セルタイプ毎処理
1443                 switch(cell.getCellType()) {
1444                 case Cell.CELL_TYPE_STRING:
1445                         if(target == null) _return = cell.getStringCellValue();
1446                         try {
1447                                 _return = cell.getStringCellValue().replaceFirst(regex ? target : Pattern.quote(target), _replacement);
1448                         }
1449                         catch(PatternSyntaxException e) {
1450                                 log.warn("replaceFirst error. {}", e.getMessage());
1451                         }
1452                         break;
1453                 case Cell.CELL_TYPE_FORMULA:
1454                         if(target == null) _return = cell.getCellFormula();
1455                         try {
1456                                 _return = cell.getCellFormula().replaceFirst(regex ? target : Pattern.quote(target), _replacement);
1457                         }
1458                         catch(PatternSyntaxException e) {
1459                                 log.warn("replaceFirst error. {}", e.getMessage());
1460                         }
1461                         break;
1462                 default:
1463                         // セルタイプが上記以外の場合はnull返却
1464                         log.warn("cell type is not strng or formula");
1465                         return null;
1466                 }
1467                 if(_return == null) _return = "";
1468                 return _return;
1469         }
1470
1471         /**
1472          * セルの文字列・数式から指定された文字列の最初の部分を指定された文字列に置換した結果を返却<br>
1473          * 引数のtarget, replacementともに正規表現として扱いません。
1474          *
1475          * @param cell  対象セル
1476          * @param target        置換される文字列
1477          * @param replacement   置き換える文字列
1478          *
1479          * @return 置換後のセル文字列・計算式<br>
1480          * 対象セルがnullの場合はnull<br>
1481          * 対象セルのセルタイプが文字列・数式以外の場合はnull
1482          *
1483          * @since 1.01.00
1484          */
1485         public static String replaceFirstCellValue(Cell cell, String target, String replacement) {
1486                 return replaceFirstCellValue(cell, target, Matcher.quoteReplacement(replacement), false);
1487         }
1488 }