spire.xls for java支持为excel设置三色及四色交通灯。该文将介绍如何在excel工作表中设置三色交通灯。
import com.spire.xls.*;
import com.spire.xls.core.iconditionalformat;
import com.spire.xls.core.spreadsheet.collections.xlsconditionalformats;
import java.awt.*;
public class settrafficlightsicons {
public static void main(string[] args) {
//新建实例
workbook workbook = new workbook();
//添加工作表(默认3个)
worksheet sheet = workbook.getworksheets().get(0);
//添加数据并设置样式
sheet.getcellrange("a1").settext("traffic lights");
sheet.getcellrange("a2").setnumbervalue(0.95);
sheet.getcellrange("a2").setnumberformat("0%");
sheet.getcellrange("a3").setnumbervalue(0.5);
sheet.getcellrange("a3").setnumberformat("0%");
sheet.getcellrange("a4").setnumbervalue(0.1);
sheet.getcellrange("a4").setnumberformat("0%");
sheet.getcellrange("a5").setnumbervalue(0.9);
sheet.getcellrange("a5").setnumberformat("0%");
sheet.getcellrange("a6").setnumbervalue(0.7);
sheet.getcellrange("a6").setnumberformat("0%");
sheet.getcellrange("a7").setnumbervalue(0.6);
sheet.getcellrange("a7").setnumberformat("0%");
//设置高度和宽度
sheet.getallocatedrange().setrowheight(20);
sheet.getallocatedrange().setcolumnwidth(25);
//添加条件格式
xlsconditionalformats conditional = sheet.getconditionalformats().add();
conditional.addrange(sheet.getallocatedrange());
iconditionalformat format1 = conditional.addcondition();
//设置背景色
format1.setformattype(conditionalformattype.cellvalue);
format1.setfirstformula("300");
format1.setoperator(comparisonoperatortype.less);
format1.setfontcolor(color.black);
format1.setbackcolor(color.lightgray);
//使用条件格式设置交通灯
conditional.addrange(sheet.getallocatedrange());
iconditionalformat format = conditional.addcondition();
format.setformattype(conditionalformattype.iconset);
format.geticonset().seticonsettype(iconsettype.threetrafficlights1);
//保存文档
string result = "output/settrafficlightsicons_result.xlsx";
workbook.savetofile(result, excelversion.version2013);
}
}
效果图: