[置顶] (9) 如何用Apache POI操作Excel文件-----应用条件样式(ConditionalFormatting)

标签: poiexcelapachejavaConditionalFormattin
1281人阅读 评论(0) 收藏 举报
分类:

有的时候,我们可能想根据一些条件,把Excel工作表中的一些数据,根据某个特定的条件,修改一下满足条件的单元格的样式.

其中比较操作符对象ComparisonOperator支持除了BWTWEEN和NOT_BEWTEEN之外的其他的7个操作符。

package org.apache.poi.ss.usermodel;
public final class ComparisonOperator
{
  public static final byte NO_COMPARISON = 0;
  public static final byte BETWEEN = 1;
  public static final byte NOT_BETWEEN = 2;
  public static final byte EQUAL = 3;
  public static final byte NOT_EQUAL = 4;
  public static final byte GT = 5;
  public static final byte LT = 6;
  public static final byte GE = 7;
  public static final byte LE = 8;
  
  public ComparisonOperator() {}
}


示范代码如下:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ConditionFormatDemo {

	 public static void main(String[] args) throws Exception {
		   //1. 生成5行9列的数据
	    	XSSFWorkbook wb = new XSSFWorkbook(); 
	    	XSSFSheet sheet = wb.createSheet("Sheet1");
	        XSSFCellStyle style = wb.createCellStyle();
	        for(int i=0;i<5;i++){
	        	Row row = sheet.createRow((short)i);
	        	for(int j=0;j<9;j++){
		        Cell cell = row.createCell((short) j);
		        cell.setCellValue((i+1)*10+(j+1));
		        cell.setCellStyle(style); 
	        	}
	        }
	        //2.设置Condition Format
	        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "50");
	        PatternFormatting fill1 = rule1.createPatternFormatting();
	        fill1.setFillBackgroundColor(IndexedColors.RED.index);
	        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting  fontFormatting=rule1.createFontFormatting();
	        fontFormatting.setFontStyle(true, true);	        
	        CellRangeAddress[] regions = {
	                CellRangeAddress.valueOf("A1:I5")
	        };
            
	        sheetCF.addConditionalFormatting(regions, rule1);
	        // 3.输出Excel文件
	        FileOutputStream fileOut = new FileOutputStream("conditionFormatTest.xlsx");
	        wb.write(fileOut);
	        fileOut.close();

	    }

}

运行后的效果如下,


1
1

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:387249次
    • 积分:6364
    • 等级:
    • 排名:第4111名
    • 原创:228篇
    • 转载:0篇
    • 译文:5篇
    • 评论:157条
    博客专栏