This is a migrated thread and some comments may be shown as answers.

Changing Cells colors with a 30 megs file

1 Answer 30 Views
SpreadProcessing
This is a migrated thread and some comments may be shown as answers.
Alain Trembley
Top achievements
Rank 1
Alain Trembley asked on 22 Mar 2019, 02:38 PM

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.       }

1 Answer, 1 is accepted

Sort by
0
Nikolay Demirev
Telerik team
answered on 25 Mar 2019, 06:12 AM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
SpreadProcessing
Asked by
Alain Trembley
Top achievements
Rank 1
Answers by
Nikolay Demirev
Telerik team
Share this question
or