[winform]cell不为空时，设置指定颜色，格式条件如何实现

收藏

[winform]cell不为空时，设置指定颜色，如何实现

ZenosZeng · ZenosZeng

你好

我可能没能完全明白你的需求，我的理解是：先判断该单元格是不是可以为空，然后通过fpSpread1.ActiveSheet.Cells[0,0].BackColor设置颜色。能否描述一下详细需求。

jbs3 · jbs3

你好，
1）我的意思是在设计器里面，怎么设置这样的条件格式：当单元格值不为空时，标记相应的颜色，就是这个空，我不会表示。
2）另外，cell上设置完条件格式后，如何查看当前单元格已经设置了哪些条件格式
3）如何把公式应用到整行，而且能做到相对引用。（第一行就是C1+D1,第二行自动变成C2+D2）
如果我说的还不够清楚，能不能麻烦您帮我优化一下以下代码，初学，水平有限，望多多指点，谢谢了。

public static void SetUiFumlar(FarPoint.Win.Spread.FpSpread fpSpread2)
{
//需要优化效率
//fpSpread2.Sheets[0].Cells[2.2].Value
fpSpread2.Sheets[0].Rows.Remove(2,fpSpread2.Sheets[0].Rows.Count-3);
//获取要显示的期数，从数据库读
string getViewQiShu = "SELECT viewQiShu FROM setting";
int viewQiShu = Convert.ToInt32(SqlHelper.getInstance.ExcuteScalar(getViewQiShu));
//viewQiShu = 100;
//从第二行开始，插入N行
//加载表格行
fpSpread2.Sheets[0].Rows.Add(2, viewQiShu);
//获取相应的号码list
List<HaoMaObj> listHaoMa = getHaoMaObjList(viewQiShu);
//横向设置过程
for (int i = 0; i < viewQiShu; i++)
{
//期号，开奖号
fpSpread2.Sheets[0].Cells[i+2, 0].Value = listHaoMa[i].QiHao;//期号
fpSpread2.Sheets[0].Cells[i + 2, 1].Value = listHaoMa[i].KaiJiangHao;//开奖号，五位，前三，后三
//二次合，二次差，三次合，三次差
string BI = "B" + (i + 3).ToString();
string CI = "C" + (i + 3).ToString();
string DI = "D" + (i + 3).ToString();
string EI = "E" + (i + 3).ToString();
string FI = "F" + (i + 3).ToString();
fpSpread2.Sheets[0].Cells[i + 2, 2].Formula = string.Format("MOD(MOD(MID({0},3,1)+MID({0},4,1)+MID({0},5,1),10)*2,10)", BI);
fpSpread2.Sheets[0].Cells[i + 2, 3].Formula = string.Format("MOD((MAX(MID({0},3,1),MID({0},4,1),MID({0},5,1))-MIN(MID({0},3,1),MID({0},4,1),MID({0},5,1)))*2,10)", BI);
fpSpread2.Sheets[0].Cells[i + 2, 4].Formula = string.Format("MOD({0}+{1},10)",CI,DI);
fpSpread2.Sheets[0].Cells[i + 2, 5].Formula = string.Format("ABS({0}-{1})", CI, DI);
//二类走势
fpSpread2.Sheets[0].Cells[i + 2, 7].Formula = string.Format("IF(OR({0}=0,{1}=0),0,"")",CI,DI);
fpSpread2.Sheets[0].Cells[i + 2, 8].Formula = string.Format("IF(OR({0}=2,{1}=2),2,"")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 9].Formula = string.Format("IF(OR({0}=4,{1}=4),4,"")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 10].Formula = string.Format("IF(OR({0}=6,{1}=6),6,"")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 11].Formula = string.Format("IF(OR({0}=8,{1}=8),8,"")", CI, DI);
//二类分析
fpSpread2.Sheets[0].Cells[i + 2, 13].Formula = string.Format("IF(OR({0}=0,{0}=2,{0}=4,{1}=0,{1}=2,{1}=4),"024","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 14].Formula = string.Format("IF(OR({0}=0,{0}=2,{0}=6,{1}=0,{1}=2,{1}=6),"026","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 15].Formula = string.Format("IF(OR({0}=0,{0}=2,{0}=8,{1}=0,{1}=2,{1}=8),"028","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 16].Formula = string.Format("IF(OR({0}=0,{0}=4,{0}=6,{1}=0,{1}=4,{1}=6),"046","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 17].Formula = string.Format("IF(OR({0}=0,{0}=4,{0}=8,{1}=0,{1}=4,{1}=8),"048","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 18].Formula = string.Format("IF(OR({0}=0,{0}=6,{0}=8,{1}=0,{1}=6,{1}=8),"068","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 19].Formula = string.Format("IF(OR({0}=2,{0}=4,{0}=6,{1}=2,{1}=4,{1}=6),"246","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 20].Formula = string.Format("IF(OR({0}=2,{0}=4,{0}=8,{1}=2,{1}=4,{1}=8),"248","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 21].Formula = string.Format("IF(OR({0}=2,{0}=6,{0}=8,{1}=2,{1}=6,{1}=8),"268","")", CI, DI);
fpSpread2.Sheets[0].Cells[i + 2, 22].Formula = string.Format("IF(OR({0}=4,{0}=6,{0}=8,{1}=4,{1}=6,{1}=8),"468","")", CI, DI);
//三类走势
fpSpread2.Sheets[0].Cells[i + 2, 24].Formula = string.Format("IF(OR({0}=0,{1}=0),0,"")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 25].Formula = string.Format("IF(OR({0}=2,{1}=2),2,"")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 26].Formula = string.Format("IF(OR({0}=4,{1}=4),4,"")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 27].Formula = string.Format("IF(OR({0}=6,{1}=6),6,"")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 28].Formula = string.Format("IF(OR({0}=8,{1}=8),8,"")", EI, FI);
//三类分析
fpSpread2.Sheets[0].Cells[i + 2, 30].Formula = string.Format("IF(OR({0}=0,{0}=2,{0}=4,{1}=0,{1}=2,{1}=4),"024","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 31].Formula = string.Format("IF(OR({0}=0,{0}=2,{0}=6,{1}=0,{1}=2,{1}=6),"026","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 32].Formula = string.Format("IF(OR({0}=0,{0}=2,{0}=8,{1}=0,{1}=2,{1}=8),"028","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 33].Formula = string.Format("IF(OR({0}=0,{0}=4,{0}=6,{1}=0,{1}=4,{1}=6),"046","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 34].Formula = string.Format("IF(OR({0}=0,{0}=4,{0}=8,{1}=0,{1}=4,{1}=8),"048","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 35].Formula = string.Format("IF(OR({0}=0,{0}=6,{0}=8,{1}=0,{1}=6,{1}=8),"068","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 36].Formula = string.Format("IF(OR({0}=2,{0}=4,{0}=6,{1}=2,{1}=4,{1}=6),"246","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 37].Formula = string.Format("IF(OR({0}=2,{0}=4,{0}=8,{1}=2,{1}=4,{1}=8),"248","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 38].Formula = string.Format("IF(OR({0}=2,{0}=6,{0}=8,{1}=2,{1}=6,{1}=8),"268","")", EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 39].Formula = string.Format("IF(OR({0}=4,{0}=6,{0}=8,{1}=4,{1}=6,{1}=8),"468","")", EI, FI);
//混合类走势
fpSpread2.Sheets[0].Cells[i + 2, 41].Formula = string.Format("IF(OR({0}=0,{1}=0,{2}=0,{3}=0),0,"")",CI,DI ,EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 42].Formula = string.Format("IF(OR({0}=2,{1}=2,{2}=2,{3}=2),2,"")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 43].Formula = string.Format("IF(OR({0}=4,{1}=4,{2}=4,{3}=4),4,"")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 44].Formula = string.Format("IF(OR({0}=6,{1}=6,{2}=6,{3}=6),6,"")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 45].Formula = string.Format("IF(OR({0}=8,{1}=8,{2}=8,{3}=8),8,"")", CI, DI, EI, FI);
//混合类分析
fpSpread2.Sheets[0].Cells[i + 2, 47].Formula = string.Format("IF(OR({0}=0,{0}=6,{1}=0,{1}=6,{2}=0,{2}=6,{3}=0,{3}=6),"06","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 48].Formula = string.Format("IF(OR({0}=0,{0}=8,{1}=0,{1}=8,{2}=0,{2}=8,{3}=0,{3}=8),"08","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 49].Formula = string.Format("IF(OR({0}=2,{0}=4,{1}=2,{1}=4,{2}=2,{2}=4,{3}=2,{3}=4),"24","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 50].Formula = string.Format("IF(OR({0}=2,{0}=6,{1}=2,{1}=6,{2}=2,{2}=6,{3}=2,{3}=6),"26","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 51].Formula = string.Format("IF(OR({0}=2,{0}=8,{1}=2,{1}=8,{2}=2,{2}=8,{3}=2,{3}=8),"28","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 52].Formula = string.Format("IF(OR({0}=4,{0}=6,{1}=4,{1}=6,{2}=4,{2}=6,{3}=4,{3}=6),"46","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 53].Formula = string.Format("IF(OR({0}=4,{0}=8,{1}=4,{1}=8,{2}=4,{2}=8,{3}=4,{3}=8),"48","")", CI, DI, EI, FI);
fpSpread2.Sheets[0].Cells[i + 2, 54].Formula = string.Format("IF(OR({0}=6,{0}=8,{1}=6,{1}=8,{2}=6,{2}=8,{3}=6,{3}=8),"68","")", CI, DI, EI, FI);
/***************************条件格式处理过程************************************************/
//色彩匹配处理过程
FarPoint.Win.Spread.NamedStyle styleCold = new FarPoint.Win.Spread.NamedStyle();
// styleCold.BackColor = Color.BlueViolet;//紫
//styleCold.BackColor = Color.Blue;
styleCold.BackColor = Color.YellowGreen;
styleCold.ForeColor =Color.White;
styleCold.Font = new Font("宋体",14,FontStyle.Bold) ;
FarPoint.CalcEngine.Expression one = new FarPoint.CalcEngine.StringExpression("");
//设置走势条件格式
fpSpread2.Sheets[0].SetConditionalFormat(i + 2, 7, 1, 48, styleCold, FarPoint.Win.Spread.ComparisonOperator.NotEqualTo, one);
}
}

复制代码

[img=780746,455]http://www.lhceo.com/data/attachment/forum/201210/29/215036n2r8qzgkmzi8abck.png[/img]

ZenosZeng · ZenosZeng

通过以下代码可以取到设置的条件格式：

int row = 0;
int col = 3;
FarPoint.Win.Spread.ConditionalFormat[] cf;
cf = fpSpread1.ActiveSheet.GetConditionalFormats(ref row, ref col, true);

复制代码

公式应用到整行，你是想给某一列设置一样的公式吗？可以使用下面的代码：

fpSpread1.ActiveSheet.Columns[0].Formula = "B1+C1";

复制代码

ZenosZeng · ZenosZeng

你给出的代码会涉及到循环操作Spread的UI和公式，所以建议在SetUiFumlar方法的开始结束位置分别增加以下代码：

public void SetUiFumlar(FarPoint.Win.Spread.FpSpread fpSpread2)
{
fpSpread1.SuspendLayout();
fpSpread1.Sheets[0].AutoCalculation = false;
...
...
fpSpread1.Sheets[0].AutoCalculation = true;
fpSpread1.ResumeLayout();
}

复制代码

jbs3 · jbs3

感谢版主耐心解答，服务真好。
另外，还有一个小问题，您帮我解答一下
如何应用不同的公式到一整列？
第一列：A1+B1
第二列：A2+B2
第三列：A3+B3
只能用循环来实现吗？
有没有方便一点的办法呢？感谢dof版主耐心解答。

jbs3 · jbs3

回复 4楼dof的帖子

公式应用到整行，你是想给某一列设置一样的公式吗？可以使用下面的代码：
fpSpread1.ActiveSheet.Columns[0].Formula = "B1+C1";

我是想让那个B1+C1随着行的变化而变化。有没有好办法？

ZenosZeng · ZenosZeng

使用以下代码，就可以实现你的功能：
Cells[0,0].Formula = "B1+C1"
Cells[1,0].Formula = "B2+C2"
Cells[2,0].Formula = "B3+C3"

private void Form1_Load(object sender, EventArgs e)
{
fpSpread1.AllowUserFormulas = true;
fpSpread1.ActiveSheet.Cells[0, 0, 5, 0].Formula = "B1+C1";
fpSpread1.ActiveSheet.Columns[3].Formula = "E1*F1";
}

复制代码

Demo VS2010 + Spread .NET 6.0

6671_Color.zip (10.08 KB, 下载次数: 501)

jbs3 · jbs3

谢谢问题解决了。

ZenosZeng · ZenosZeng

好的，谢谢反馈。

		自动登录	找回密码
密码			立即注册

[已处理] [winform]cell不为空时，设置指定颜色，格式条件如何实现

9 个回复

活字格认证

圣诞拼拼乐

微信认证勋章

讲师达人

元老葡萄

悬赏达人