Need your expertise - to optimise the code and speed the download of excel.

Question

1.67/5 (2 votes)

See more:

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.

C#

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)
            {
              //  Error_SendMail(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)
                    {
                      //  Error_SendMail(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;
                            // oledbCmd.CommandTimeout = 200;

                            if (oledbConn.State == System.Data.ConnectionState.Closed)
                            {
                                oledbConn.Open();
                                oledbCmd.Connection = oledbConn;
                            }
                            oledbCmd.ExecuteNonQuery();
                            // oledbConn.Close();


                        }
                        catch (Exception ex)
                        {

                            if (oledbConn.State == System.Data.ConnectionState.Open)
                                oledbConn.Close();
                            //  Error_SendMail(ex);
                        }
                        finally
                        { 
                        
                        }
                    }
                }

                if (oledbConn.State == System.Data.ConnectionState.Open)
                    oledbConn.Close();
            }
        }
        return true;
    }

Posted 11-Sep-16 23:32pm

ajitsit07

Updated 12-Sep-16 8:08am

Patrice T

v2

Add a Solution

Comments

Richard MacCutchan 12-Sep-16 5:53am

Where is the download taking place, and what is the problem?

ajitsit07 12-Sep-16 6:16am

There is no problem in the code, i have to improve the speed. so that the excel will get downloaded in less time. Ex. now it is take 40secs and i want to reduce its time to 20 secs.

This excel download in under C#. ASP.net website under a menu.

public static bool fillExcelFile(string filePath, DataSet dataSet, string sheetName, string tempSheetName)

Here: Filepath - is the server mapped path of file (macro enable excel file) available on the server.
Dataset: Contain the data need to insert into the temp sheet of excel.

Do you need any other info. too.

Richard MacCutchan 12-Sep-16 6:45am

Download speed is dependent on the network connections between the server and the client.

ajitsit07 12-Sep-16 8:16am

Thanks Richard.

Yes, I do agree with you. But optimized code makes difference.
Is there any scope to optimize the above code?

Dave Kreskowiak 12-Sep-16 8:28am

You'r combining to distinct operations in your question. You either need help with building the Excel sheet and speeding that up or you need help with the download operation.

So which is it? One has nothing to do with the other.

Richard MacCutchan 12-Sep-16 8:42am

Optimise in what way? And as Dave already pointed out, this code has nothing to do with downloading.

ajitsit07 12-Sep-16 9:06am

Here sbColumns contain all the name which is there in excel and sbValues contain all the value. we are inserting the it row by row into the temp sheet of excel. While debuging 'oledbCmd.ExecuteNonQuery();' line taking time.

oledbCmd.CommandText = string.Format("insert into [{0}$]({1})values({2})", tempSheetName, sbColumns.ToString(), sbValues.ToString());
oledbCmd.CommandType = System.Data.CommandType.Text;
// oledbCmd.CommandTimeout = 200;

if (oledbConn.State == System.Data.ConnectionState.Closed)
{
oledbConn.Open();
oledbCmd.Connection = oledbConn;
}
oledbCmd.ExecuteNonQuery();

Richard MacCutchan 12-Sep-16 10:43am

The time taken for a command to execute is a function of the amount of data to be processed. I cannot see anything that can be changed, unless you use the Excel Interop instead of OLE.

Nathan Minier 12-Sep-16 7:38am

You're using the OLE Provider, it's gonna take longer since it has to go through those ADO transforms before being written to Excel format.

There are 2 ways you can speed this up: Use the DocumentFormat.OpenXML namespace or build a .csv to order. Both would be significantly different than what you have now.

ajitsit07 12-Sep-16 8:15am

I need to user macro enabled excel, so can't use .csv.
Is there any scope to optimize the above code?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sreekanth Mothukuru · Answer 1 · 2016-09-12T08:08:00

Solution 1

Try to compress the file before allowing the user to download it. Use c# code to compress the file to zip/7z format.

This way you can optimise download speed.

Posted 12-Sep-16 8:08am

Sreekanth Mothukuru

Comments

ajitsit07 13-Sep-16 8:56am

Can't zip the file, because here are inserting the rows in the temp sheet of excel and further macro will run to use these dumped data and generate the report.