Performance issue whilst writing to Excel

[fhillipo]

We have a code snippet that downloads data to Excel. it is writing row by
row. This causes a performance issue. Any ideas on how to speed this up will
be appreciated.

Please find below an excerpt of the code:

#region Method:WriteToExcel
/// <summary>
/// <para>Description : Method is used to Write the records into
excel</para>

public static void WriteToExcel(string excelFilePath, DataSet
dsPrConditions)
{
try
{
XYZLogManager.WriteToLog("Inside WriteToExcel of
dsPrCondition",
LogCategory.Information, typeof(DSPCondition).FullName);

string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + ";" +
"Extended Properties=\"Excel 8.0;MAXSCANROWS=1;HDR=YES;IMEX=2\"";
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
//CREATE SHEET IN THE EXCEL FILE
using (DbCommand CreateTableCommand =
connection.CreateCommand())
{
connection.Open();
CreateTableCommand.CommandText = CreateTableQuery();
CreateTableCommand.ExecuteNonQuery();
connection.Close();
}

//INSERT VALUES INTO THE NEWLY CREATED SHEET
using (DbCommand command = connection.CreateCommand())
{
CreateCommandParameters(command);
connection.Open();

command.CommandText = GenerateInsertQuery();
//Exclude Locked Records and write it to Excel
foreach (DataRow dr in
dsPrConditions.Tables[0].Select("CRITERIA_1 = 'N'"))
{
CreateInsertCommand(command, dr);
command.ExecuteNonQuery();
}
connection.Close();
}
//DROP THE BLANK SHEET FROM THE TEMPLATE
using (DbCommand DropTableCommand =
connection.CreateCommand())
{
connection.Open();
DropTableCommand.CommandText = "DROP TABLE [TEMP$]";
DropTableCommand.ExecuteNonQuery();
connection.Close();
}
}
}
catch (Exception ex)
{
XYZExceptionHandler.Process(ex,
ExceptionPolicies.WrapPolicy, Severity.Error,
"Error CodeL0136, Description : Error writing values
to excel");
}
}
#endregion

 

[mario]

Hi,

Well you can try to use some third party components for reading/
writing to Excel files. They work much faster. If you are want to
use .NET components I can recommend you to use GemBox.Spreadsheet
(http://www.gemboxsoftware.com/GBSpreadsheet.htm). You can use
GemBox.Spreadsheet free even in your commercial applications (http://
www.gemboxsoftware.com/GBSpreadsheetFree.htm). Free version comes with
150 rows limit.

Mario
GemBox Software