PERFORMANCE - looping over cells - suppress “numbe

Question

I am populating a ListObject with data from a database, and am pre-formatting ListColumns which come from VarChar (& similar) as Text before inserting the data.

This works well, but some affected cells now are showing the 'Number Stored As Text' error.

The answer https://stackoverflow.com/a/21869098/1281429 suppresses the error correctly, but requires looping through all cells (as it is not possible to perform the action on a range).

Unfortunately for large ranges this is unacceptably slow.

(n.b. - if you do it manually in Excel it's lightning fast)

Here is a code snippet in C# (for a particular column):

var columnDataRange = listColumn.DataBodyRange
var cells = columnDataRange.Cells;
for (var i = 1; i < cells.Count; i++)
{
    InteropExcel.Range cell = cells[i, 1];
    if (cell.Count > 1) break;

    if (cell.Errors != null)
    {
        var item = cell.Errors.Item[InteropExcel.XlErrorChecks.xlNumberAsText];
        item.Ignore = true;     
    }   
}

Does anyone know of a faster way of doing this?

(Or, more generally, a faster way of iterating through cells in a range?)

Hope someone can help - thanks.

Edit: this is a VSTO Application-Level add-in for Excel 2010/2013.

Answer 1

Just to be sure - you are going from a database to an Excel export? Are you creating a new, clean spreadsheet or overwriting existing data in an existing spreadsheet?

If you are overwriting data in an existing spreadsheet, I would first clear the columns and format the columns in Excel (programmatically of course). It is likely old data and new data going into the same space are causing type issues.

So something like: thisExcel.xlWorksheet.Range[yourrange].Value = "" thisExcel.xlWorksheet.Range[yourrange].NumberFormat = choseyourformat http://msdn.microsoft.com/en-us/library/office/ff196401(v=office.15).aspx

You should be able to apply that to a larger area.