POIの小ネタ - セルの入力規則の修正

以前に、POIの小ネタ - セルの入力規則の取得 - タツノオトシゴの日記で記載した、セルの入力規則の続き。

ver3.11から、入力規則が取得できるようになりました。

ただし、削除者、修正のメソッドが準備されていませんでした。

今回は、リフレクションで無理矢理、範囲を変更する方法の紹介。

削除については、どうも上手くいきませんでした。

セルの入力規則の範囲を変更する処理

【XSSF(Excel2007、xlsx)形式の場合】

• OpenXMLのデータ型「CTDataValidations」に入力規則のデータ型を持ちます。
  • CTDataValidationsは、XSSFSheetのプライベートなフィールド「CTWorksheet worksheet」で保持している、リフレクションで取得します。
• 各入力規則は、「CTDataValidation」で取得でき、設定されている範囲を持ちます。
• 入力規則の範囲は、「List CTDataValiadtion#getSqref()」から取得できます。
  • これらは、CellRangeAddressListの各要素を文字列(CellRaggeAddress#formatAsString())にした形式です。
• 書き換える範囲が見つかったら、新しい範囲を追加します。

【HSSF（Excel2000/2003、xls）形式の場合】

• 「DataValidityTable」に入力規則のデータ型を持ちます。
  • DataValidityTableは、HSSFSheetのプライベートなフィールド「InternalSheet _sheet」で保持しているため、リフレクションで取得します。
• 各入力規則は、レコードの一種であるため、RecordVisitorを実装して、取得します。
  • 「DVRecord」が実際の入力規則を保持するレコードです。
• 「DvRecord#getCellRangeAddress()」で範囲を取得できます。
• 書き換える範囲が見つかったら、既存の範囲を削除し、新しい範囲を追加します。

/**
 * 入力規則の範囲を更新する。
 * @param sheet シート
 * @param oldRegion 更新対象の範囲。
 * @param newRegion 新しい範囲。
 * @return true:更新完了。false:指定した範囲を持つ入力規則が見つからなかった場合。
 */
public static boolean updateDataValidationRegion(final Sheet sheet,
        final CellRangeAddressList oldRegion, final CellRangeAddressList newRegion) {
    
    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notNull(oldRegion, "oldRegion");
    ArgUtils.notNull(newRegion, "newRegion");
    
    if(sheet instanceof XSSFSheet) {
        
        final List<String> oldSqref = convertSqref(oldRegion);
        
        try {
            final XSSFSheet xssfSheet = (XSSFSheet) sheet;
            Field fWorksheet = XSSFSheet.class.getDeclaredField("worksheet");
            fWorksheet.setAccessible(true);
            CTWorksheet worksheet = (CTWorksheet) fWorksheet.get(xssfSheet);
            
            CTDataValidations dataValidations = worksheet.getDataValidations();
            if(dataValidations == null) {
                return false;
            }
            
            for(int i=0; i < dataValidations.getCount(); i++) {
                CTDataValidation dv = dataValidations.getDataValidationArray(i);
                
                // 規則の範囲を比較し、同じならば範囲を書き換える。
                @SuppressWarnings("unchecked")
                List<String> sqref = new ArrayList<>(dv.getSqref());
                if(equalsSqref(sqref, oldSqref)) {
                    List<String> newSqref = convertSqref(newRegion);
                    dv.setSqref(newSqref);
                    
                    // 設定し直す
                    dataValidations.setDataValidationArray(i, dv);
                
                    return true;
                }
                
            }
            
            return false;
            
        } catch(Exception e) {
            throw new RuntimeException("fail update DataValidation's Regsion.", e);
        }
        
    } else if(sheet instanceof HSSFSheet) {
        
        final HSSFSheet hssfSheet = (HSSFSheet) sheet;
        try {
            Field fWorksheet = HSSFSheet.class.getDeclaredField("_sheet");
            fWorksheet.setAccessible(true);
            InternalSheet worksheet = (InternalSheet) fWorksheet.get(hssfSheet);
            
            DataValidityTable dvt = worksheet.getOrCreateDataValidityTable();
            
            // シート内の入力規則のデータを検索して、一致するものがあれば書き換える。
            final AtomicBoolean updated = new AtomicBoolean(false);
            RecordVisitor visitor = new RecordVisitor() {
                
                @Override
                public void visitRecord(final Record r) {
                    if (!(r instanceof DVRecord)) {
                        return;
                    }
                    
                    final DVRecord dvRecord = (DVRecord) r;
                    final CellRangeAddressList region = dvRecord.getCellRangeAddress();
                    if(equalsRegion(region, oldRegion)) {
                        
                        // 一旦既存の範囲を削除する。
                        while(region.countRanges() != 0) {
                            region.remove(0);
                        }
                        
                        // 新しい範囲を追加する。
                        for(CellRangeAddress newRange : newRegion.getCellRangeAddresses()) {
                            region.addCellRangeAddress(newRange);
                        }
                        
                        updated.set(true);
                        return;
                    }
                }
            };
            
            dvt.visitContainedRecords(visitor);
            
            return updated.get();
            
        } catch(Exception e) {
            throw new RuntimeException("fail update DataValidation's Regsion.", e);
        }
    } else {
        throw new UnsupportedOperationException("not supported update dava validation's region for type " + sheet.getClass().getName());
    }
    
}

/**
 * CellRangeAddressを文字列形式のリストに変換する。
 * @since 0.5
 * @param region
 * @return
 */
private static List<String> convertSqref(final CellRangeAddressList region) {
    
    List<String> sqref = new ArrayList<>();
    for(CellRangeAddress range : region.getCellRangeAddresses()) {
        sqref.add(range.formatAsString());
    }
    
    return sqref;
    
}

/**
 * 文字列形式のセルの範囲が同じかどうか比較する。
 * @since 0.5
 * @param sqref1
 * @param sqref2
 * @return
 */
public static boolean equalsSqref(final List<String> sqref1, final List<String> sqref2) {
    
    if(sqref1.size() != sqref2.size()) {
        return false;
    }
    
    Collections.sort(sqref1);
    Collections.sort(sqref2);
    
    final int size = sqref1.size();
    for(int i=0; i < size; i++) {
        if(!sqref1.get(i).equals(sqref2.get(i))) {
            return false;
        }
    }
    
    return true;
    
}

/**
 * 文字列形式のセルの範囲が同じかどうか比較する。
 * @since 0.5
 * @param region1
 * @param region2
 * @return
 */
public static boolean equalsRegion(final CellRangeAddressList region1, final CellRangeAddressList region2) {
    
    return equalsSqref(convertSqref(region1), convertSqref(region2));
    
}

セルの入力規則の範囲を変更する処理の呼び出し方

JUnitのテスタ形式ですが、下記のようになります。

実際には、既存の範囲であるCellRangeAddressListの値をそのまま書き換えるようになると思います。

その場合、既存の範囲を残しておく必要があるため、CellRangeAddressList#copy()でインスタンスをコピーしておくと便利だと思います。

/**
 * {@link POIUtils#updateDataValidationRegion(Sheet, CellRangeAddressList, CellRangeAddressList)}
 * ・XSSF形式
 * @since 0.5
 */
@Test
public void testUpdateDataValidationRegion_xssf() throws Exception {
    
    Workbook workbook = WorkbookFactory.create(new FileInputStream("src/test/data/utils.xlsx"));
    Sheet sheet = workbook.getSheet("入力規則");
    
    // 範囲の定義
    CellRangeAddressList oldRegion = new CellRangeAddressList();
    oldRegion.addCellRangeAddress(new CellRangeAddress(4, 5, 2, 2));
    
    CellRangeAddressList newRegion = new CellRangeAddressList();
    newRegion.addCellRangeAddress(new CellRangeAddress(4, 7, 2, 2));
    
    // 既存の入力規則を取得し、範囲が同じものを探索し、書き換えます。
    boolean updated = false;
    List<? extends DataValidation> validations = sheet.getDataValidations();
    for(DataValidation dv : validations) {
        
        // 既存のCellRangeAddressListを書き換えるときは、copy()メソッドでクローンしておく。
        CellRangeAddressList region = dv.getRegions().copy();
        if(POIUtils.equalsRegion(region, oldRegion)) {
            updated = POIUtils.updateDataValidationRegion(sheet, region, newRegion);
            break;
        }
    }
    
    assertThat(updated, is(true));
    
    // 書き換わったかどうか確認する
    boolean found = false;
    List<? extends DataValidation> updatedvalidations = sheet.getDataValidations();
    for(DataValidation dv : updatedvalidations) {
        CellRangeAddressList region = dv.getRegions();
        if(POIUtils.equalsRegion(region, newRegion)) {
            found = true;
            break;
        }
    }
    
    assertThat(found, is(true));
    
    workbook.write(new FileOutputStream("src/test/out/utils_out.xlsx"));
    
}