469,590 Members | 2,489 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,590 developers. It's quick & easy.

Performance issue whilst writing to Excel

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 Code:DL0136, Description : Error writing values
to excel");
}
}
#endregion


--
fhillipo
Jun 27 '08 #1
1 2781
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
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. See
http://www.gemboxsoftware.com/GBSpreadsheet.htm
--

On May 22, 12:48 pm, fhillipo <fhill...@discussions.microsoft.com>
wrote:
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 Code:DL0136, Description : Error writing values
to excel");
}
}
#endregion

--
fhillipo
Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

25 posts views Thread by Brian Patterson | last post: by
36 posts views Thread by Ron Johnson | last post: by
16 posts views Thread by David W. Fenton | last post: by
10 posts views Thread by Daniel Billingsley | last post: by
13 posts views Thread by bjarne | last post: by
3 posts views Thread by Glenn | last post: by
9 posts views Thread by Bob Achgill | last post: by
8 posts views Thread by Dmitry Akselrod | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.