Get or set multiple cell values

[Robby]

I want to get or set multiple cell values.
I have tested with this piece of code:

CellRange cellRange = new CellRange(0, 0, 5, 5);
CellSelection selection2 = worksheet.Cells[cellRange];
var vals = selection2.GetValue();

But the getvalue function does not act as expected.

I need this to cancel some performance issues. I discovered a heavy load when you read and write 100 cell values each second.

[Nikolay Demirev]

Hi Robby,

RadSpreadsheet stores information about every change you made in order for the Undo/Redo feature to work properly and this is resource consuming. If you write hundreds of values in RadSpreadsheet and you do not need to use undo for them you can disable the History or include all changes in one UndoGroup. This way you will speed up your app. Here you can read about the History feature of RadSpreadsheet.

I hope my answer is helpful. If you have further question do not hesitate to contact us again.

Regards,
Nikolay Demirev
Telerik

[Robby]

Hi Nikolay,
I had already found the disabling of the history. I know from my experience with interop Excel that it if very efficient to read and write arrays from the worksheet. The recalculate of the sheet will be done once in stead of hundredths times.
Good to know is that I will use the component in a parallel way. In my case I will use 20 components that read and write 100 cell values each second.

Regards,
Robby

[Nikolay Demirev]

Hi Robby,

Thank you for getting back to us!

Did disabling the history solve the performance issue you encountered? In case you have any additional feedback or questions do not hesitate to contact us again.

I am looking forward to your reply.
 

Regards,
Nikolay Demirev
Telerik

[Robby]

No,
the load was already too high after disabling the history.

Regards,
Robby

[Nikolay Demirev]

Hello Robby,

We tested RadSpreadsheet by setting and getting 10 000 values in cell range that is 100 x 100 cells. The operations were performed for 619 ms. 

 We used this piece of code to test the performance:

DateTime startTime = DateTime.Now;

radSpreadsheet.Workbook.History.IsEnabled = false;
for (int i = 0; i < 100; i++)
{
    for (int j = 0; j < 100; j++)
    {
        radSpreadsheet.ActiveWorksheet.Cells[i, j].SetValue(i + j);
        radSpreadsheet.ActiveWorksheet.Cells[i, j].GetValue().Value.GetResultValueAsString(CellValueFormat.GeneralFormat);
    }
}
radSpreadsheet.Workbook.History.IsEnabled = true;

TimeSpan totalTime = DateTime.Now - startTime;

MessageBox.Show(totalTime.TotalMilliseconds.ToString());

That said maybe you are setting different kind of data. If so, please open a support ticket and send us a sample project or sample data. This way we will be able to investigate the issue you have encountered on our side.

Thank you in advance for your cooperation!

Regards,
Nikolay Demirev
Telerik

[Carlos]

Hello

I have a problem with your spreadsheet.

Everytime i try to access the cell value i get =a2+b2 instead of the formula value, i've researched how to get the actual formula but i've been unable to do it.

Could you please help me?

Rango = Hoja.Cells[contador, posicion[D]];
                                                Celdas = Rango.GetValue().Value;//.GetResultValueAsString(CellValueFormat.GeneralFormat);                                                
                                                vector_total[D][XY] = Celdas.RawValue;

 

Getting the string value didn't work​

[Nikolay Demirev]

Hi Carlos,

If you want to get the formula expression string like "=1+2" you have to use 

string formulaString = worksheet.Cells[0, 0].GetValue().Value.RawValue;

If you want to get the calculated formula result value as string you have to use:

worksheet.Cells[0, 0].GetValue().Value.GetResultValueAsString(CellValueFormat.GeneralFormat);

If you want to get the formula value as FormulaCellValue object you have to use:

FormulaCellValue formulaValue = worksheet.Cells[0, 0].GetValue().Value as FormulaCellValue;

I hope this helps.

Regards,
Nikolay Demirev
Telerik