What is the most efficient way to clear a range of cells?

Aspose.Cells Product Family
1

Hi,

I have a question to do with performance - specifically, what is the most efficient way to clear a large number of cells in a spreadsheet? I have a client who needs to clear 50 cols * 30,000 rows - and my current coding approach is maxing out the CPU for an extended period of time.

My current approach is a brute force pair of loops after acquring a reference to a range object - wondering if there is a better way ?

The code below summarises my current approach - but it taks 10-15 minutes to execute.

Range range = --->.GetRange( "A1:ZZ30000" ) // not exact code - summarised here to make sense

for (int x = 0; x < range.ColumnCount; x++)

{

for (int y = 0; y < range.RowCount; y++)

{

range[y, x].PutValue(null);

range[y, x].Formula = String.Empty;

}

}

2

Hi,

Well, I think you may use Cells.ClearRange() to clear the contents, formulas, formattings etc. for a range of cells.

See the following line of code:
//Delete the contents. formulas, formattings for A1:ZZ30000
worksheet.Cells.ClearRange(0,0,29999,CellsHelper.ColumnNameToIndex(“ZZ”));

Also, you may try to use Cells.DeleteRange() method for your need.


Thank you.