none
Microsoft.Office.Interop.Excel.dll RRS feed

  • Question

  • hi everyone,
     i have code that works fine for tables that are small .. but when the datable size goes up to 10,000+ export from datatable to dataset becomes really slow .. can one give me some suggestions.  here is the code  that i am using ..

    public void WriteToExcelSpreadsheet(string fileName, System.Data.DataTable dt)

    {

    string filepath = getPath(fileName).Trim();

    //dt = SQLProductProvider.GetExcelImport();

    // dt.WriteXml(filepath, XmlWriteMode.IgnoreSchema);

    Microsoft.Office.Interop.Excel.Application ExlApp = new Microsoft.Office.Interop.Excel.Application();

    int iCol, iRow, iColVal;

    Object missing = System.Reflection.Missing.Value;

    // Open the document that was chosen by the dialog

    Microsoft.Office.Interop.Excel.Workbook aBook;

    try

    {

    //'re-initialize excel app

    ExlApp = new Microsoft.Office.Interop.Excel.Application();

    if (ExlApp == null)

    {

    //'throw an exception

    throw (new Exception("Unable to Start Microsoft Excel"));

    }

    else

    {

    //'supresses overwrite warnings

    ExlApp.DisplayAlerts = false;

    //aBook = New Excel.Workbook

    //'check if file exists

    if (File.Exists(filepath))

    {

    aBook = ExlApp.Workbooks._Open(filepath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

    }

    else

    {

    aBook = ExlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

    }//End If

    //With ExlApp

    ExlApp.SheetsInNewWorkbook = 1;

    //ExlApp.Worksheets[1].Select();

    //For displaying the column name in the the excel file.

    for (iCol = 0; iCol < dt.Columns.Count; iCol++)

    {

    //'clear column name before setting a new value

    ExlApp.Cells[1, iCol + 1] = "";

    ExlApp.Cells[1, iCol + 1] = dt.Columns[iCol].ColumnName.ToString();

    }//next

    //For displaying the column value row-by-row in the the excel file.

    for (iRow = 0; iRow < dt.Rows.Count ; iRow++)

    {

    try

    {

    for (iColVal = 0; iColVal < dt.Columns.Count; iColVal++)

    {

    if (dt.Rows[iRow].ItemArray[iColVal] is string)

    {

    ExlApp.Cells[iRow + 2, iColVal + 1] = "'" + dt.Rows[iRow].ItemArray[iColVal].ToString();

    }

    else

    {

    ExlApp.Cells[iRow + 2, iColVal + 1] = dt.Rows[iRow].ItemArray[iColVal].ToString();

    }//End If

    }//next

    }

    catch (Exception ex)

    {

    Console.Write("ERROR: " + ex.Message);

    }//End Try

    }//next

    if (File.Exists(filepath))

    {

    ExlApp.ActiveWorkbook.Save(); //fileName)

    }

    else

    {

    ExlApp.ActiveWorkbook.SaveAs(filepath.Trim(), missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

    }//End If

    ExlApp.ActiveWorkbook.Close(true, missing, missing);

    //End With

    //Console.Write("File exported sucessfully");

    }//End if

    }

    catch (System.Runtime.InteropServices.COMException ex)

    {



    Console.Write("ERROR: " + ex.Message);

    }

    catch (Exception ex)

    {



    Console.Write("ERROR: " + ex.Message);

    }

    finally

    {

    ExlApp.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp);

    aBook = null;

    ExlApp = null;

    }//End Try

    }//End Sub

    Saturday, October 18, 2008 7:56 PM

Answers