Java用POI复制excel单元格条件格式规则

1.说明

pom就不列了，POI版本为4.1.0

2.程序功能

将源单元格的条件格式规则复制到目标区域，兼容03与07。

3.代码如下

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author sheromin
 * 
 */
public class ConditionalFormatMain {

    public static void main(String[] args) {
        try {
            FileInputStream inputStream=new FileInputStream("D:\\test.xlsx");
            XSSFWorkbook workbook=new XSSFWorkbook(inputStream);
            XSSFSheet sheet=workbook.getSheet("Sheet1");
            XSSFSheetConditionalFormatting scf =sheet.getSheetConditionalFormatting();
            XSSFRow row=sheet.getRow(0);
            XSSFCell cell=row.getCell(0);
            List<ConditionalFormattingRule> ruleList=getConditionalRule(sheet,cell);
            CellRangeAddress[] regions= {new CellRangeAddress(1,1,0,7)};
            if(ruleList!=null&& ruleList.size()>0) {
                for(ConditionalFormattingRule rule:ruleList) {
                    scf.addConditionalFormatting(regions,rule);//区域内添加规则
                }
            }
            workbook.write(new FileOutputStream("D:\\test.xlsx"));
            workbook.close();
            inputStream.close();
            System.out.print("Finish!!!");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    private static List<ConditionalFormattingRule> getConditionalRule(Sheet sheet,Cell cell){
        List<ConditionalFormattingRule> ruleList=new ArrayList<ConditionalFormattingRule>();
        if(cell!=null) {
            SheetConditionalFormatting scf=sheet.getSheetConditionalFormatting();//获取sheet中条件格式对象
            int countOfFormat=scf.getNumConditionalFormattings();//条件格式的数量
            for(int i=0;i<countOfFormat;i++) {
                ConditionalFormatting format=scf.getConditionalFormattingAt(i);//第countOfFormat个条件格式
                CellRangeAddress[] ranges=format.getFormattingRanges();//条件格式区域
                for(int r=0;r<ranges.length;r++) {
                    if(ranges[r].isInRange(cell)) {//cell是否在此区域
                        int numOfRule=format.getNumberOfRules();
                        for(int j=0;j<numOfRule;j++) {// 获取具体的规则
                            ConditionalFormattingRule rule=format.getRule(j);
                            ruleList.add(rule);
                        }

                    }
                }

            }
        }
        return ruleList;
    }
}

4.结果如下：
程序运行前

程序运行后

经测试对于03和07版本的excel都适用。