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