Changing Cells colors with a 30 megs file

[Alain Trembley]

Good day,

I'm importing an Excel file and I need to validate all the cells an then import the result in our database.

When I have and error I change the Cell color. For small file I don't fell a performance issue. But on big file it's not possible. It take about half a second to change the color.

I turn off the History mode on the Woorkbook.  wb.History.IsEnabled = false;

This is my code with cellSelection.SetFill(SolidErrorFill); commented to have better speed. How can I achieve a better speed ?

Thanks!

01.public override IExcelRuleResult Evaluate(Worksheet ws)
02.       {
03.           var result = new ExcelRuleResult();
04.           result.ExcelIsValid = true;
05.           CellRange cr = ws.UsedCellRange;
06.           if (cr != null)
07.           {
08.               for (int rowIndex = 1; rowIndex <= cr.ToIndex.RowIndex; rowIndex++)
09.               {
10.                   // Read Row 0 for header name and validate if all the cells have the same format
11.                   var sale = new Sale();
12.                   for (int columnIndex = cr.FromIndex.ColumnIndex; columnIndex <= cr.ToIndex.ColumnIndex; columnIndex++)
13.                   {
14.                       RangePropertyValue<ICellValue> rpv = null;
15.                       CellSelection cellSelection = null;
16.                       //var rpvName = ws.Cells[0, columnIndex].GetValue();
17.                       var columnName = (layout.Layouts[columnIndex] as XlsxLayoutBase)?.ColumnName;
18.                       var columnLayout = layout.Layouts[columnIndex] as XlsxLayoutBase;
19.                       string value = null;
20.                       if (columnLayout != null)
21.                       {
22.                           try
23.                           {
24.
25.                               cellSelection = ws.Cells[rowIndex, columnIndex];
26.                               rpv = cellSelection.GetValue();
27.                               if (rpv.Value is FormulaCellValue fcv)
28.                               {
29.                                   value = fcv.GetResultValueAsString(new CellValueFormat(columnLayout.FormatString));
30.                               }
31.                               else
32.                               {
33.                                   value = rpv.Value.RawValue;
34.                               }
35.
36.                               if (columnLayout.DataType == EnumDataType.Date)
37.                               {
38.                                   // Assembly: ServiceStack.Text .ToDoubleInvariant()
39.                                   value = FormatHelper.ConvertDoubleToDateTime(value.ToDoubleInvariant())?.Date.ToString("O");
40.                               }
41.
42.                               if (columnLayout.Mandatory && string.IsNullOrWhiteSpace(value))
43.                               {
44.                                   //cellSelection.SetFill(SolidErrorFill);
45.                                   // We have {rowIndex+1} so it's the same line excel, 0 Base array in c# and Excel start at 1
46.                                   result.Messages.Add($"Mandatory Value: ({value}) for ColumnName ({columnName}) at Row {rowIndex + 1}");
47.                                   result.ExcelIsValid = false;
48.                               }
49.                               else
50.                               {
51.                                   //MapperHelper.AssignFieldValue(value, sale, columnName);
52.                               }
53.                           }
54.                           catch (Exception ex)
55.                           {
56.                               //cellSelection?.SetFill(SolidErrorFill);
57.                               result.ExcelIsValid = false;
58.                               // We have {rowIndex+1} so it's the same line excel, 0 Base array in c# and Excel start at 1
59.                               result.Messages.Add($"Exception Column Value: ({value}) for ColumnName ({columnName}) at Row {rowIndex + 1}  {ex.Message}");
60.                           }
61.                       }
62.                   }
63.                   Sales.Add(sale);
64.                   Console.WriteLine(rowIndex);
65.               }
66.           }
67.           return result;
68.       }

[Nikolay Demirev]

Hello Alain,

The internal structure which is responsible for storing the values of all cell properties works faster if you set the same value on larger range of rows instead of once for each row.

My suggestion is to switch the places of the two for loops so first loop through columns and for each column iterate through its rows. If the fill for consecutive rows is the same, set it once after a different value for the fill is calculated. This way you will set the fill once per several rows and you will benefit from the way the internal structures works.

I hope this will speed up your application.

Regards,
Nikolay Demirev
Progress Telerik