Can someone help me speed up this db 2 excel code

Question

My table.Rows.Count=8000.Work on C# vs05

            DataSet ds = null;
            DataTable dtTable = null;

            ds = oBOGeneralReport.GetsBoardExcelPreview(_oGeneralReport);//.GetsBoardPreview(_oGeneralReport);

            if (ds == null) return;
            if (ds.Tables.Count > 0) dtTable = ds.Tables[0];
            if (dtTable == null) return;

            string connectionString = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            int nRow = 1;
            int p = 0;
            int k = 0;
            string sFileName = null;
            for (k = 0; k < ds.Tables[0].Columns.Count; k++)
            {
                xlWorkSheet.Cells[nRow, p + 1] = ds.Tables[0].Columns[k].ColumnName;
                p++;                
            }

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 2, j + 1] = data;
                }
            }

            SaveFileDialog oDialog = new SaveFileDialog();
            oDialog.Filter = "Excel files | *.xls";
            if (oDialog.ShowDialog() == DialogResult.OK)
            {
                sFileName = oDialog.FileName;
            }

            if (sFileName != null)
            {
                xlWorkBook.SaveAs(sFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                MessageBox.Show("Report saved with file: " + sFileName, "To Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);


            }
            else
            {
                MessageBox.Show("Cannot export to excel...", "Can't export", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

How to decrease the export time?

Answer 1

SpreadsheetGear for .NET created an 8000 row by 10 column (you didn't say how many columns) workbook in 0.039 seconds on my almost 2 year old overclocked Intel QX6850.

The code I ran is below. You can download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC.

using System;
using System.Diagnostics;
using SpreadsheetGear;
using SpreadsheetGear.Advanced.Cells;

namespace WriteRows
{
    class Program
    {
        static void Main(string[] args)
        {
            // "Warm up" the code.
            WriteRows(100, 10, false);
            // Do the test.
            WriteRows(8000, 10, false);
        }

        static void WriteRows(int rows, int cols, bool openXML)
        {
            Stopwatch timer = Stopwatch.StartNew();
            IWorkbook workbook = Factory.GetWorkbook();
            IWorksheet worksheet = workbook.Worksheets[0];
            IValues values = (IValues)worksheet;
            for (int row = 0; row < rows; row++)
            {
                values.SetText(row, 0, "Row " + (row + 1));
                for (int col = 1; col < cols; col++)
                    values.SetNumber(row, col, row * cols + col);
            }
            string filename = @"c:\tmp\WriteRows" + (openXML ? ".xlsx" : ".xls");
            FileFormat fileFormat = openXML ? FileFormat.OpenXMLWorkbook : FileFormat.Excel8;
            workbook.SaveAs(filename, fileFormat);
            Console.WriteLine("Created {0} with {1}x{2} rows / cols in {3} seconds", filename, rows, cols, timer.Elapsed.TotalSeconds);
        }
    }
}

Answer 2

SpreadsheetGear.NET would be my prefered solution as well, but assuming you don't have the money to licence it, your biggest speed issue is the number of commands you are sending to Excel - with the Excel OLE automation the overhead of each command is often greater then the actual execution. Writing to cells is the most blatent case.

To speed up writing to cells, prepare your data as an array, then write the entire range in one shot (instead of assigning a value to a cell, you can assign an array to a range). You'll notice a huge speed increase with this one simple step.

Since you are reading from an ADO table, you might also want to look at the CopyFromRecordset command, which is a method of a range object (this is not available in older versions of the Excel automation interface). This lets you send the entire contents of a recordset to a range of cells in one shot, very fast.

xlWorkSheet.Range("A1").CopyFromRecordset ds

(That may command not be 100% correct, long time since I did VB and Excel automation together)