Improve creating excel sheet speed ( programatically)

Question

I need to generate some kind of reports in excel via web system. My current code is as follow (simplified):

//[javascript inside .aspx page]

ExcelApp = new ActiveXObject("Excel.Application");
ExcelSheet = new ActiveXObject("Excel.Sheet");

ExcelSheet.Windows(1).WindowState = 2;
ExcelSheet.Application.Visible = false;

for (i=1; i< [elementNumber]; i++)
{
   ExcelSheet.ActiveSheet.Cells(i,1).Value = myXML.documentElement.childNodes(i).text;
}

ExcelSheet.Application.Visible = true;

Even if I populate only few hunded rows it takes about 10 second. Is there any way to speed up this process like create entire sheet in memory instead of populate cell by cell?

Answer 1

You could try disabling recalculation to see if that helps

Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic

Other than that this page has a few good tips to squeeze as much performance out of excel

http://www.cpearson.com/excel/optimize.htm

Answer 2

Define an array of object[,] first. You will no longer see a time delay, no matter how big it is. Btw, the array can also hold formulas.

e.g. something like this function i use in my project:

    public static void AddValueArrayToSheet(_Worksheet ws, Range rangeTopLeft, object[,] values) {
        Range cellRange = ws.Cells.get_Range(rangeTopLeft, rangeTopLeft[values.GetUpperBound(0) + 1, values.GetUpperBound(1) + 1]);
        cellRange.Value = values;
    }