Spread大量的颜色,Lock设定导致的性能问题

收藏

由于实际需要，现有一个300列左右，行数未定的Spread,需要在读入数据以后，根据数据把每个单元格设置为不同的颜色。

但是数量级达到1000以上的时候，就出现了性能问题。
比如：单纯的设置每个单元格BackColor,ForeColor,Lock属性的时间就达到了40秒，严重影响了客户体验。

有没有什么好的方法能让颜色的设置快一些，或者有没有什么多线程设置Spread背景色的例子？

程序大体思路：
针对于Spread的没一行
如果第一列的值=已确认
      第一列的单元格颜色设置为红色
如果第一列的值=已驳回
      第一列的单元格颜色设置为黄色
其他
      白色

第二列

第三列
……

dexteryao · dexteryao

您尝试先设置 fpSpd.AllowCellOverflow = false; 在循环试试。

如果还是慢的话发一个Demo过来

bool oldAllowCellOverflow = fpSpd.AllowCellOverflow;
fpSpd.AllowCellOverflow = false;
for (int r = 0; r < 27; r++)
{
for (int c = 0; c < 26; c++)
{
var rowIndex = r + 5;
var colIndex = c + 3;
Cell cell = fpSpd.ActiveSheet.Cells[rowIndex, colIndex];
if (cell != null)
{
cell.BackColor = Color.Gray;
}
}
}
fpSpd.AllowCellOverflow = oldAllowCellOverflow;
}

复制代码

sxyweiren · sxyweiren

dexteryao 发表于 2016-12-22 16:34
您尝试先设置 fpSpd.AllowCellOverflow = false; 在循环试试。

如果还是慢的话发一个Demo过来

默认的AllowCellOverFlow就是False,所以应该不是这个的问题。

sxyweiren · sxyweiren

dexteryao 发表于 2016-12-22 16:34
您尝试先设置 fpSpd.AllowCellOverflow = false; 在循环试试。

如果还是慢的话发一个Demo过来

关于Demo的，由于我现在是实际项目里面用的，涉及到了DB什么的，你们那里跑不起来，所以我就把针对每一行设定颜色的代码抽出了一部分，贴出来了。

Sub Set_Color_Row(ByRef Row As Integer, ByRef Spd As SpreadConv.SpreadConv)
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).ForeColor = Lbl_Color_Def.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).BackColor = Lbl_Color_Def.BackColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Def.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Def.BackColor
Call Check_Wrun_Row(Row, Spd)
If Get_Spd_Num(G_Col_KbnDel, Row, Spd) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).Locked = True
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Del.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Del.BackColor
ElseIf Get_Spd_Num(G_Col_KbnJyogai, Row, Spd) = 1 Then
Spd.BlockMode = True
Spd.Row = Row
Spd.Col = G_Col_SheetHaba
Spd.Row2 = Row
Spd.Col2 = Spd.MaxCols
Spd.Lock = True
Spd.BlockMode = False
Set_Color_Kakutei(Row, Spd, 9, 0, 0)
ElseIf Get_Spd_Num(G_Col_KbnZaiko, Row, Spd) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).Locked = True
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Zaiko.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Zaiko.BackColor
Else
Spd.BlockMode = True
Spd.Row = Row
Spd.Col = G_Col_SheetHaba
Spd.Row2 = Row
Spd.Col2 = Spd.MaxCols
Spd.Lock = False
Spd.BlockMode = False
Spd.Col = G_Col_SouCatSu : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_CatSu : Spd.Row = Row : Spd.Lock = False
If Get_Spd_Num(G_Col_Zen, Row, Spd) = 1 Then
Spd.Col = G_Col_SL : Spd.Lock = True
Spd.Col = G_Col_Kei : Spd.Lock = True
Spd.Col = G_Col_SC1 : Spd.Lock = True
Spd.Col = G_Col_SC2 : Spd.Lock = True
Spd.Col = G_Col_SC3 : Spd.Lock = True
Spd.Col = G_Col_SC4 : Spd.Lock = True
Spd.Col = G_Col_SC5 : Spd.Lock = True
Spd.Col = G_Col_SC6 : Spd.Lock = True
Spd.Col = G_Col_SC7 : Spd.Lock = True
Spd.Col = G_Col_SC8 : Spd.Lock = True
Spd.Col = G_Col_SC9 : Spd.Lock = True
Spd.Col = G_Col_Torisu : Spd.Lock = True
Spd.Col = G_Col_SitaSL : Spd.Lock = True
If Get_Spd_Num(G_Col_Jyuchu_全板, Row, Spd) = 1 Then
Spd.Col = G_Col_Zen : Spd.Lock = True
Spd.Col = G_Col_Dansai : Spd.Lock = True
End If
Else
Spd.Col = G_Col_SL : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_Kei : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC1 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC2 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC3 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC4 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC5 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC6 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC7 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC8 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SC9 : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_Torisu : Spd.Row = Row : Spd.Lock = False
Spd.Col = G_Col_SitaSL : Spd.Row = Row : Spd.Lock = False
End If
If Get_Spd_Num(G_Col_DakuNo, Row, Spd) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_SouCatSu - 1).Locked = True
Spd.ActiveSheet.Cells(Row - 1, G_Col_CatSu - 1, Row - 1, Spd.MaxCols - 1).Locked = False
Spd.ActiveSheet.Cells(Row - 1, G_Col_SouCatSu - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Daku.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_SouCatSu - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Daku.BackColor
Else
If Get_Spd_Num(G_Col_DakuNo, Row, Spd) > 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_SouCatSu - 1).Locked = True
Spd.ActiveSheet.Cells(Row - 1, G_Col_CatSu - 1, Row - 1, Spd.MaxCols - 1).Locked = False
Spd.ActiveSheet.Cells(Row - 1, G_Col_SouCatSu - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Daku_Ko.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_SouCatSu - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Daku_Ko.BackColor
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Def.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Def.BackColor
End If
End If
If Get_Spd_Num(G_Col_KbnOchibo, Row, SpdKeikaku) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1).ForeColor = Lbl_Color_RotOchibo.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1).BackColor = Lbl_Color_RotOchibo.BackColor
Else
If Get_Spd_Num(G_Col_KbnHinOchibo, Row, SpdKeikaku) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1).ForeColor = Lbl_Color_HinOchibo.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1).BackColor = Lbl_Color_HinOchibo.BackColor
End If
End If
If Get_Spd_Num(G_Col_KbnWrun, Row, Spd) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).ForeColor = Lbl_Color_Wrun.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_SheetHaba - 1, Row - 1, Spd.MaxCols - 1).BackColor = Lbl_Color_Wrun.BackColor
End If
If Get_Spd_Num(G_Col_Hari_ロット固定, Row, Spd) = 1 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1).ForeColor = Lbl_Color_Rot.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Time - 1).BackColor = Lbl_Color_Rot.BackColor
End If
Set_Color_Err(Row, Spd)
End If
If Get_Spd_Str(G_Col_Iro1, Row, Spd) <> "" Then
Spd.Row = Row
Spd.Col = G_Col_Iro1
If Get_Spd_Num(G_Col_Paretto1, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro1 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_Paretto1, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro1 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(0)
End If
If Get_Spd_Num(G_Col_ParettoB1, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro1 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_ParettoB1, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro1 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(16777215)
End If
End If
If Get_Spd_Str(G_Col_Iro2, Row, Spd) <> "" Then
Spd.Row = Row
Spd.Col = G_Col_Iro2
If Get_Spd_Num(G_Col_Paretto2, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro2 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_Paretto2, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro2 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(0)
End If
If Get_Spd_Num(G_Col_ParettoB2, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro2 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_ParettoB2, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro2 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(16777215)
End If
End If
If Get_Spd_Str(G_Col_Iro3, Row, Spd) <> "" Then
Spd.Row = Row
Spd.Col = G_Col_Iro3
If Get_Spd_Num(G_Col_Paretto3, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro3 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_Paretto3, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro3 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(0)
End If
If Get_Spd_Num(G_Col_ParettoB3, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro3 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_ParettoB3, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro3 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(16777215)
End If
End If
If Get_Spd_Str(G_Col_Iro4, Row, Spd) <> "" Then
Spd.Row = Row
Spd.Col = G_Col_Iro4
If Get_Spd_Num(G_Col_Paretto4, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro4 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_Paretto4, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro4 - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(0)
End If
If Get_Spd_Num(G_Col_ParettoB4, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro4 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(Get_Spd_Num(G_Col_ParettoB4, Row, Spd))
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Iro4 - 1).BackColor = System.Drawing.ColorTranslator.FromOle(16777215)
End If
End If
If Microsoft.VisualBasic.Left(Get_Spd_Str(G_Col_Tokui, Row, Spd), 3) = "大内段" Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Tokui - 1).BackColor = System.Drawing.ColorTranslator.FromOle(&HC0E0FF)
End If
If Get_Spd_Num(G_Col_配送CD, Row, Spd) <> 0 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Haiso - 1).ForeColor = System.Drawing.ColorTranslator.FromOle(CInt(Get_Spd_Str(G_Col_配送文字カラー, Row, Spd)))
End If
If VBUP.VBUP.Format(Get_Spd_Str(G_Col_Jyuchu_納期, Row, Spd), "YYYY/MM/DD") <= VBUP.VBUP.Format(G_DATE, "YYYY/MM/DD") Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Nouki - 1).BackColor = System.Drawing.ColorTranslator.FromOle(&HC0C0FF)
End If
Select Case Get_Spd_Num(G_Col_Hari_確定区分, Row, Spd)
Case 1
Spd.ActiveSheet.Cells(Row - 1, 0, Row - 1, Spd.MaxCols - 1).Locked = False
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).ForeColor = Lbl_Color_KariKakutei.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).BackColor = Lbl_Color_KariKakutei.BackColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).Text = "仮"
Case 2, 3
Select Case Get_Spd_Num(G_Col_Hari_直区分, Row, Spd)
Case 1
Spd.ActiveSheet.Cells(Row - 1, 0, Row - 1, Spd.MaxCols - 1).Locked = True
If Get_Spd_Num(G_Col_Hari_確定区分, Row, Spd) = 2 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).ForeColor = Lbl_Color_1Tyoku.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).BackColor = Lbl_Color_1Tyoku.BackColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).Text = "1"
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).ForeColor = Lbl_Color_Sousin.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).BackColor = Lbl_Color_Sousin.BackColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).Text = "1"
End If
Case 2
Spd.ActiveSheet.Cells(Row - 1, 0, Row - 1, Spd.MaxCols - 1).Locked = True
If Get_Spd_Num(G_Col_Hari_確定区分, Row, Spd) = 2 Then
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).ForeColor = Lbl_Color_2Tyoku.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).BackColor = Lbl_Color_2Tyoku.BackColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).Text = "2"
Else
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).ForeColor = Lbl_Color_Sousin.ForeColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).BackColor = Lbl_Color_Sousin.BackColor
Spd.ActiveSheet.Cells(Row - 1, G_Col_Head - 1).Text = "2"
End If
End Select
End Select
End Sub

复制代码

dexteryao · dexteryao

代码收到，我这里再测试下看能重现出来不。

sxyweiren · sxyweiren

dexteryao 发表于 2016-12-22 17:41
代码收到，我这里再测试下看能重现出来不。

恩，现在数据条数大约是4000，以后基本是累加的

dexteryao · dexteryao

您好，我看到您代码中有大量的 Get_Spd_Num 方法，不清楚里面有什么逻辑。

我简单造了4000行 200列的数据测试。我这里测试下面的代码只需要4秒。

您在加上 fpSpread1.SuspendLayout(); 和 fpSpread1.ResumeLayout(); 在试试。

label1.Text = DateTime.Now.ToString();
fpSpread1.SuspendLayout();
fpSpread1.AllowCellOverflow = false;
for (var i = 0; i < 4000; i++)
{
for (var j = 0; j < 200; j++)
{
if ((i + j) % 2 == 0)
{
fpSpread1.ActiveSheet.Cells[i, j].Locked = true;
fpSpread1.ActiveSheet.Cells[i, j].BackColor = Color.Red;
fpSpread1.ActiveSheet.Cells[i, j].ForeColor = Color.Green;
fpSpread1.ActiveSheet.Cells[i, j].Value = i + "_" + j;
}
else
{
fpSpread1.ActiveSheet.Cells[i, j].Locked = false;
fpSpread1.ActiveSheet.Cells[i, j].BackColor = Color.Green;
fpSpread1.ActiveSheet.Cells[i, j].ForeColor = Color.Red;
fpSpread1.ActiveSheet.Cells[i, j].Value = i + "_" + j;
}
}
}
fpSpread1.ActiveSheet.Protect = true;
fpSpread1.ResumeLayout();
label2.Text = DateTime.Now.ToString();

复制代码

sxyweiren · sxyweiren

本帖最后由 sxyweiren 于 2016-12-23 16:41 编辑

dexteryao 发表于 2016-12-23 14:12
您好，我看到您代码中有大量的 Get_Spd_Num 方法，不清楚里面有什么逻辑。

我简单造了4000行 200列的数 ...

我试了一下，fpSpread1.ActiveSheet.Cells[i, j]改成fpSpread1.ActiveSheet.Cells[i, j, i, fpSpread1.ActiveSheet.ColumnCount - 1]就出现了性能问题了。

我的代码里也是这样的逻辑，因为业务需要某一块设置为某种颜色

dexteryao · dexteryao

建议您还是循环单独给cell设置颜色吧，这个还需要再调查下具体问题。

sxyweiren · sxyweiren

dexteryao 发表于 2016-12-23 17:18
建议您还是循环单独给cell设置颜色吧，这个还需要再调查下具体问题。

……这样的话业务还得梳理下，因为下面的IF条件里面并没有把所有的都给列举出来。

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

[已处理] Spread大量的颜色,Lock设定导致的性能问题

14 个回复

活字格认证

微信认证勋章

讲师达人

悬赏达人

元老葡萄

SpreadJS 认证

SpreadJS 高级认证