By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,870 Members | 1,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,870 IT Pros & Developers. It's quick & easy.

Performance issue whilst writing to Excel

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.