Hi All,
I have developed a reporting tool, in which I am inserting the rows into the excel (having same no. columns as in macro enable excel sheet).
This snippet of code is working perfectly fine and downloading in the excel in decent time. But I need your help to improve and optimize speed of download.
Thanks for your advice and time.
What I have tried:
Here is the lines of code: Class under App_code.
public static bool fillExcelFile(string filePath, DataSet dataSet, string sheetName, string tempSheetName)
{
DataTable oledbdatatbl = null;
using (OleDbDataAdapter oledbadap = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", sheetName),
string.Format(@"Data Source={0};Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;", filePath)))
{
try
{
oledbdatatbl = new System.Data.DataTable("ReportTable");
oledbadap.Fill(oledbdatatbl);
}
catch (Exception ex)
{
return false;
}
}
System.Text.StringBuilder sbColumns = new System.Text.StringBuilder();
int coloumsCount = 0;
for (int icol = 0; icol < (oledbdatatbl.Columns.Count); icol++)
{
coloumsCount++;
sbColumns.Append(string.Format(",[{0}]", oledbdatatbl.Columns[icol].ColumnName));
}
if (sbColumns.Length > 0)
sbColumns.Remove(0, 1);
using (OleDbConnection oledbConn = new OleDbConnection(string.Format(@"Data Source={0};Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;", filePath)))
{
using (OleDbCommand oledbCmd = new OleDbCommand())
{
System.Text.StringBuilder sbValues = new System.Text.StringBuilder();
int valuesCount = 0;
for (int irow = 0; irow < dataSet.Tables[0].Rows.Count; irow++)
{
if (sbValues.Length > 0)
sbValues.Remove(0, sbValues.Length);
try
{
valuesCount = 0;
for (int idatacol = 0; idatacol < dataSet.Tables[0].Columns.Count; idatacol++)
{
valuesCount++;
sbValues.Append(string.Format(",'{0}'", Convert.ToString(dataSet.Tables[0].Rows[irow][idatacol]).Contains("'") ?
Convert.ToString(dataSet.Tables[0].Rows[irow][idatacol]).Replace("'", "''") :
Convert.ToString(dataSet.Tables[0].Rows[irow][idatacol])));
}
if (sbValues.Length > 0)
sbValues.Remove(0, 1);
}
catch (Exception ex)
{
continue;
}
if (coloumsCount == valuesCount)
{
try
{
oledbCmd.CommandText = string.Format("insert into [{0}$]({1})values({2})", tempSheetName, sbColumns.ToString(), sbValues.ToString());
oledbCmd.CommandType = System.Data.CommandType.Text;
if (oledbConn.State == System.Data.ConnectionState.Closed)
{
oledbConn.Open();
oledbCmd.Connection = oledbConn;
}
oledbCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (oledbConn.State == System.Data.ConnectionState.Open)
oledbConn.Close();
}
finally
{
}
}
}
if (oledbConn.State == System.Data.ConnectionState.Open)
oledbConn.Close();
}
}
return true;
}